Здравствуйте! Посоветуйте, пожалуйста, организацию бэк-энда живого поиска на postgres+php+ условия (ниже). Ничего путного в Гугле-Яндексе не нашел. http://www.sai.msu.su/~megera/postgres/talks/fts_pgsql_intro.html - просмотрено (быть может, нужно заострить внимание). Сторонние движки поиска пока не рассматриваю!

Задача в том, чтобы сделать "живой поиск" на сайте по мере ввода данных пользователем. Т.е. ему должны предлагаться варианты из Базы по мере ввода символов с клавы путем периодических запросов (ajax через jquery). По мере ввода выдача сужается. Кнопка "поиск" с пост-обработкой не предусмотрена, пользователь может выбирать только из предлагающихся вариантов. Важно, чтобы в этих вариантах не было упущений.

Вводимые данные сопоставляются в БД (postgres 9.5) с двумя полями таблицы - name & brand (оба поля - character varying, размер не более 500, кодировка utf8, содержимое - могут использоваться не только цифры и буквы разных европейский алфавитов (акценты, умляуты и проч), но и спец. символы - кавычки, звездочки, проценты, точки, запятые и т.д.). Очень важно отметить, что в каждом поле может быть несколько слов И все символы и слова в этих полях значимые (не должны отбрасываться при поиске). Объединение двух полей (в запросе или в отдельную колонку) - не проблема.

Из коробки я могу воспользоваться:

а) ILIKE %word1 word2% - проблема в том, что этот вариант требует определенного порядка слов в водимой фразе и "в лоб" не подходит. Я могу в запросе варьировать полями (поле1-поле2 и поле2-поле1, но в каждом из полей может быть по нескольку слов, порядком которых я варьировать здесь не могу). Плюс в том, что этот вариант ищет и выводит результат по ЧАСТИ любого слова, по мере ввода пользователем.

б) tsvector & tsquery - проблема в том, что этот вариант не выводит результат по мере ввода символов пользователем, а только при совпадении лексем (или как их там), т.е.пользователь может вводиться в заблуждение отсутствием вариантов по мере ввода ЧАСТИ искомой фразы. а лексемы с обеих сторон (tsvector @@ tsquery) должны быть "нормализованы", чтобы соответствовать друг-другу. Как нормализовать tsquery, чтобы можно было искать по ЧАСТИ слова в tsvector (который уже "нормализован"? Префиксы word:* помогают при поиске по отдельному слову (tsquery(word:*)), но не помогают при поиске по фразе (типа to_tsquery('word1:* & word2:*') ) + возникают другие засады. Плюс этого варианта с полнотекстовым поиском в том, что порядок слов в запросе не важен (можно использовать доп. колонку fullname типа tsvector, куда слить данные колонок brand и name+ проиндексировать для ускорения процесса поиска). Часть вопросов (сделать все слова значимыми) можно, наверное, решить, задав пустые словари стоп-слов и тп...

Кто-нить сталкивался с этим? (Нужен вывод вариантов по мере ввода независимо от порядка слов с учетом части слов) куда копать? (повторюсь, если не рассматривать сторонние движки, которые мне сейчас просто не нужны (для поиска по двум полям с десятком слов и времени на их освоение нет) ?

массивы покурю. словари мне не нужны. от слова "совсем". Они тока мешают :)) Мне нужен произвольный порядок ввода слов и поиск (вывод результатов) по части слова

иначе говоря: чел вводит: "brand name" или "brand nam" или "name br" - должно выдаваться 'brand name' (да, под частью слова понимается часть последнего вводимого пользователем слова, корректность предыдущих не проверяется и не важна)

с ранжированием пока не заморачиваюсь. В примере с ilike видно, что запрос нужно анализировать и разбивать на слова. То же придется делать с tsquery. С to_tsquery по всей фразе не пришлось бы анализировать, но эта функция не позволяет использовать префиксный поиск. Т.е. либо нормализация всей фразы без префиксного поиска, либо префиксный поиск без возможности автоматом нормализовать фразу (как я понял по http://www.sai.msu.su/~megera/postgres/talks/fts_pgsql_intro.html
А можно ли заставить при формировании поля tsvector НЕ сокращать слова и не использовать стоп-слова? Задать пустые словари или это не поможет? а потом уже в tsquery с минимальной ручной нормализацией (приведение всех слов к нижнему регистру) искать по всей фразе с учетом префиксного поиска :* ?

Размерность - 2 поля (не более 500 знаков или не более 5 слов в каждом поле) Х 50 000 строк.

Благодарю откликнувшихся!
Признаюсь, я задавал вопрос в расчете на то, что подобную задачу уже очень многие решали (дабы не изобретать велосипед, хвататься за первое, что пришло в голову и потом долго доводить до ума).

Возможно, я невнятно сформулировал в первом посте условия задачи. Постараюсь переформулировать, максимально упростив.

Дано: таблица с одним (упрощаю здесь) полем character varying (до 500 символов, до 10-20 "слов", разделенных пробелами в utf8, словарь уникальных "слов" раз в 5-10 меньше общего объема - грубая оценка) и 50000 строк.

Задача: находить по мере набора пользователя с клавы ВСЕ поля (LIMIT предусмотрен, но здесь не суть, как и ранжирование), в которых встречаются ВСЕ (конъюнкция) введенные на данный момент слова ИЛИ их части. Порядок слов при вводе может не совпадать с порядком слов в поле.

Вроде, простое условие и простая задача (судя по тому, что я ежедневно вижу при поиске на разных сайтах - как оч. продвинутых, так и очень среднего уровня), а поди ж ты разберись.... Или все сразу хватаются за Сфинкс и подобное? Пушкой по воробьям или нормально?

Что касается предложенных подходов. Все они, как я понял, подразумевают анализ вводимой пользователем фразы (разбиение на "слова"), динамическое построение в зависимости от этого анализа множественных ilike или массива...

Попробую двигаться от простого к сложному.
Значит, если строить ilike по каждому слову с обрамлением оного в %%, то запрос из трех таких слов (и их частей) обрабатывается на моей неоптимизированной базе 150 мс. Положение усугубляется тем, что под character varying невозможно построить приемлемые индексы (ilike с b-деревьями, насколько я понял, работает очень ограниченно). Для примера этой разницы сравним с индексированным GIN полнотекстовым поиском по спец.созданному полю tsvector. Используется запрос из трех слов: @@ tsquery('word1:* & word2:* & word3:*'). Этот запрос на той же базе обрабатывается в 5-10 раз быстрее!

Поэтому постараюсь покопать полнотекстовый поиск дальше. Здесь проблема в том, чтобы не отбрасывались окончания и не использовались стоп-слова при формировании tsvector.

ПРОМЕЖУТОЧНЫЙ ИТОГ (может, кому-то пригодится)

- Без ILIKE (медленно работает, нет подходящей для разных случаев индексации)
- Без надстроек в виде множеств (зачем лишние сущности, когда уже придуманы массивы в виде tsvector)

Создается поле типа tsvector, куда сливаются нужные нам текстовые поля для поиска той же таблицы. Все это производится несколькими простыми SQL-запросами и на моих данных достаточно быстро. Можно задать ранги для полей сразу же. При создании этого поля исходные фразы разбиваются на лексемы. В последующем можно расширять и уточнять работу, задавая свои словари и конфигурацию текстового поиска...

Создаем индекс GIN созданного поля tsvector.

в PHP формулируем SQL запрос такого вида: SELECT field1||' '||field2 AS output FROM table WHERE fts_output @@ to_tsquery($1)... (где output - то, что будет выведено пользователю в ответ на запрос, а fts_output -поле типа tsvector, созданное на основе field1 & field2, $1 - параметр для подстановки запроса пользователя в SQL, передается отдельно). LIMIT, ORDER BY, сортировка с рангами, подсветка совпадений - прикручивается по вкусу.

Функция подготовки запроса пользователя для передачи в качестве аргумента to_tsquery().
На php я использовал:
-экранирование спец.символов add c slashes($phrase, '\'"&:;*#$@`~,.=+()[]{}|/\\<>^') - ибо без этого возникала ошибка базы при их наборе
- простейшую и самую быструю замену пробела (разделяет слова) на операторы, требуемые для to_tsquery: $output=str_replace(' ', ' :*& ', $output)-- можно еще проще - $output=str_replace(' ', ' & ', $output) - только & между словами, но мне показалось, что возможность поиска по части слова внутри фразы - хороший бонус, не сокращающий скорость обработки (кстати, обработка 3 или 5 слов происходит здесь практически за одно и то же время, а скорость ILIKE сильно зависит от количества членов конъюнкции - тестировал)
- подстановку префикса в конце запроса (имея в виду, что последнее вводимое слово может быть не закончено) - $output = $output.':*';

Что еще можно сделать?
- о конфигурации поиска и словарях уже сказал выше. Пока не ищет по артиклям, предлогам и т.д. (а ведь многие слова могут начинаться с тех же символьных сочетаний) Если ввести the - пользователь не получает ответ, если продолжает ввод - thea - получает варианты.
- быструю предварительную проверку кол-ва пробелов (т.е. "слов" в запросе пользователя, чтобы отсечь более разумного). Общая длина запроса у меня проверяется, равно как строковый тип.

Покритикуйте, дополните. Пока по обрывкам слов в любом порядке выдает искомое, и выдает шустро. Но возможны еще подводные камни...

Oleg Bartunov,
О, автор! Спасибо за наводку со словарем. Сейчас как раз это читаю.

Кажись, разобрался в:
- словарях
- конфигурациях
- настройках конфигураций
- назначении конфигурации по умолчанию и переопределение конфигурации в функциях
для своего, прямо скажем, очень простого случая.

В словаре нужно явно задать пустой стоп-словарь (предварительно создав файл в utf8):
CREATE TEXT SEARCH DICTIONARY public.simple_search ( template = pg_catalog.simple, stopwords = simple_search );
Иначе, полагаю, продолжит использовать встроенные стоп-листы.

Что касается функций, то очень жаль, что plainto_query() не позволяет использовать префиксный поиск. Придется пользоваться to_tsquery и вручную обрабатывать пользовательский ввод, а это всегда непросто и чревато (кто знает, что у пользователя на уме и что он надумает вводить?) Простая замена str_replace (как я выше указал) в боевых условиях не сработает, придется рассматривать ввод более детально и придирчиво.

Кстати, очень хорошая статья по организации полнотекстового поиска на postgres - http://qualcode.ru/article/postgres_fts_dict_cfg/