Re: [pgsql-ru-general] Индекс для автокомплита

Имеется БД в которой есть текстовое поле где хранится одно или
несколько слов в каждом

id text

1 Привет
2 Привет медвед
3 вася
4 пупкин
5 вася пупкин
6 вася пупкин сказал привет медвед
...

Далее пользователь начинает вводить текст, хотим комплитить ему считая
что он вводит с начала слова. пользователь может ввести несколько слов

что сделали:

1. написали функцию которая по фразе возвращает массив составляющих ее
сабфраз array[] по убыванию, пометили ее immutable
(то есть на фразу 'вася пупкин сказал привет медвед' будет 5
вариантов: 'вася пупкин сказал привет медвед', 'пупкин сказал привет
медвед', 'сказал привет медвед', 'привет медвед' и 'медвед')
2. построили 5 индексов по первым 5 словам в каждой фразе
3. при поиске выбираем по 20 штук из каждого индекса и смешиваем

ну и получается человек начинает вводить 'мед' получает 2 и 6 вариант
а начинает вводить 'при' получает 1, 2, 6 варианты (в индексе lower
лежат)

все работает ок, но есть ОЧЕНЬ редкие случаи когда пользователь
начинает вводить 6 или 7 слово.

можно и по ним конечно индексы достроить...

но в целом примененный подход по сути итак равен 5 выборкам из
индекса, плюс куча избыточных данных в выборке.

может как-то можно подобную задачу решить более оригинально?
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

Добрый день!

Я не знаю конкретных деталей, но ваше решение представляется мне не очень
обобщенным.
Думаю, вам помогут две вещи:

1. pg_trgm [ http://www.postgresql.org/docs/9.4/static/pgtrgm.html ]
2. wildspeed [ http://www.sai.msu.su/~megera/wiki/wildspeed ]

Оба модуля абсолютно production-ready.
Wildspeed показывал очень хорошие результаты на 600 тысячах записей.

Обратите внимание на вопрос из рассылки [1] относительно wildspeed.

[1]
http://www.postgresql.org/message-id/CAFj8pRAoogUAuBTr=XTAPyo_c7vi6r3Ed8w6NmL9U07Fs5iDcg@mail.gmail.com

10 января 2015 г., 3:05 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>
написал:

> Имеется БД в которой есть текстовое поле где хранится одно или
> несколько слов в каждом
>
> id text
>
> 1 Привет
> 2 Привет медвед
> 3 вася
> 4 пупкин
> 5 вася пупкин
> 6 вася пупкин сказал привет медвед
> ...
>
>
> Далее пользователь начинает вводить текст, хотим комплитить ему считая
> что он вводит с начала слова. пользователь может ввести несколько слов
>
> что сделали:
>
> 1. написали функцию которая по фразе возвращает массив составляющих ее
> сабфраз array[] по убыванию, пометили ее immutable
> (то есть на фразу 'вася пупкин сказал привет медвед' будет 5
> вариантов: 'вася пупкин сказал привет медвед', 'пупкин сказал привет
> медвед', 'сказал привет медвед', 'привет медвед' и 'медвед')
> 2. построили 5 индексов по первым 5 словам в каждой фразе
> 3. при поиске выбираем по 20 штук из каждого индекса и смешиваем
>
> ну и получается человек начинает вводить 'мед' получает 2 и 6 вариант
> а начинает вводить 'при' получает 1, 2, 6 варианты (в индексе lower
> лежат)
>
> все работает ок, но есть ОЧЕНЬ редкие случаи когда пользователь
> начинает вводить 6 или 7 слово.
>
> можно и по ним конечно индексы достроить...
>
>
> но в целом примененный подход по сути итак равен 5 выборкам из
> индекса, плюс куча избыточных данных в выборке.
>
> может как-то можно подобную задачу решить более оригинально?
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlSwbK0ACgkQq4wAz/jiZTdvDQCfUW+IZXnMbp7aK5ET7rT/II9J
> 2L4An3P65NnxdiXDVX4V0XJKOcKU2CbC
> =15kK
> -----END PGP SIGNATURE-----
>
>

> Я не знаю конкретных деталей, но ваше решение представляется мне не очень
> обобщенным.
> Думаю, вам помогут две вещи:

> 1. pg_trgm [ http://www.postgresql.org/docs/9.4/static/pgtrgm.html ]

Я этот модуль пробовал, он в принципе неплохо подходит для поиска фраз
сопоставимой длины, но не для автокомплита.
для автокомплита нужно совпадение вида /(?:^|\s+)$text.*/i
То есть от начала строки или любого слова точное (с игнором регистра)
совпадение ввода.

> 2. wildspeed [ http://www.sai.msu.su/~megera/wiki/wildspeed ]

про это не слышал, сейчас посмотрю.

> Оба модуля абсолютно production-ready.
> Wildspeed показывал очень хорошие результаты на 600 тысячах записей.

> Обратите внимание на вопрос из рассылки [1] относительно wildspeed.

> [1] http://www.postgresql.org/message-id/CAFj8pRAoogUAuBTr=
> XTAPyo_c7vi6r3Ed8w6NmL9U07Fs5iDcg(at)mail(dot)gmail(dot)com

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

> 2. wildspeed [ http://www.sai.msu.su/~megera/wiki/wildspeed ]

ага, это прямо то что я уже сам было думал сваять, но решил спросить в
рассылке и Вы мне прямо на готовое решение ссылку дали.
спасибо!

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

Мне кажется, Вы немного поторопились с выбором.
Посмотрите ещё раз документацию [1].
pg_trgm поддерживает like и ilike (9.1+) и регулярные выражения (9.3+).

[1] http://www.postgresql.org/docs/9.4/static/pgtrgm.html

11 января 2015 г., 12:11 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>
написал:

> > 2. wildspeed [ http://www.sai.msu.su/~megera/wiki/wildspeed ]
>
> ага, это прямо то что я уже сам было думал сваять, но решил спросить в
> рассылке и Вы мне прямо на готовое решение ссылку дали.
> спасибо!
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlSyPisACgkQq4wAz/jiZTe8LgCeMbpWnbMml3E4zGM73pWlrLhs
> obYAnjurFoVFVUL2MT4/nXzesNh7LMEa
> =t29h
> -----END PGP SIGNATURE-----
>
>