ischerbin Aug 17 2016 at 09:37

Пример реализации autocomplete с использованием FTS движка PostgreSQL tsearch2

4 min

8.7K

PostgreSQL*jQuery*Yii*

Comments 23

UFO just landed and posted this here

ischerbin Aug 17 2016 at 10:43

tsearch2 встроен в базовод, не надо ничего отдельно ставить, настраивать и запускать. Но в любом случае на вкус и цвет…

UFO just landed and posted this here

ischerbin Aug 17 2016 at 10:59

Это как ответить на вопрос
— Как пропатчить KDE под FreeBSD?
— Ставь винду!
Вроде бы и ответ и даже должно сработать, но чего то все-таки не хватает.

UFO just landed and posted this here

AxisPod Aug 17 2016 at 14:19

Да вроде всё правильно, вам посоветовали специализированные инструменты, которые это поддерживают. А вы прям маниакально их отторгнули, да, надо ставить, настраивать, но вы хотите семантический поиск, но при этом хотите использовать инструмент который этого не умеет. Это как надо забить гвоздь, а вам нравятся швейцарские ножи, но молотка там нет, но вы всё же хотите забить гвоздь именно ножом.

ischerbin Aug 17 2016 at 15:10

Если не лезть в дебри того, что такое «семантический поиск» и какой софт умеет его выполнять, но говорить исключительно о tsearch2 vs sphinx и ему подобных то с чего Вы взяли, что первый ничего не умеет? Желательно для начала бегло почитать что пишут, например здесь.

Shtucer Aug 17 2016 at 11:17

Я, конечно, ничего не понимаю в этих ваших полнотекстовых поисках, поэтому у меня вопрос на вопрос: зачем вы выбираете fts_txt вместо text? В автокомплит подавать кусок живого текста из документа, а не индекс по нему. Это несколько усложнит запрос, но решит проблему с окончаниями. Или так не принято в полнотекстовом поиске делать?

ischerbin Aug 17 2016 at 11:22

ts_stat по сути не предназначена для решения прикладных задач, она задумана для целей отладки и мониторинга. Она выдает статистику по тому, какие слова как часто и где встречаются. В качестве параметра она принимает текст SQL запроса, возвращающего набор полей типа tsvector, только так она сработает. В противном случае будет:

ERROR:  ts_stat query must return one tsvector column

Shtucer Aug 17 2016 at 11:37

Ну так и пройти немного дальше и использовать полученный tsvector для извлечения информации из текста что-то мешает? Иначе это не было полнотекстовым поиском, а всего лишь стеммером. Ну, так мне кажется. По крайней мере в документации описан довольно богатый арсенал функций, и даже есть раздел «Simple Search Engine», который начинается со слов: Building a search engine involves only a few improvements upon the rudimentary vector searches described in the last section. А в этом самом last section и рассматривался ваш случай — ВЫБРАТЬ tsvector.

Melkij Aug 17 2016 at 11:51

Использовать Yii и так грубо допускать SQL-инъекции? Вроде бы за такое уже больно били по рукам до появления Yii.

ischerbin Aug 17 2016 at 12:03

Спасибо! Заменил строку:

$_query_array = explode(' ', $_GET['term']);

на:

$_query_array = explode(' ', Yii::app()->db->quoteValue($_GET['term']));

shushu Aug 17 2016 at 13:35

А разве

Yii::app()->db->quoteValue

открывающую и закрывающую кавычку не добавляет?

ischerbin Aug 17 2016 at 14:54

Добавил trim и в этот раз без спешки проверил. Теперь работает как положено.

bobelev Aug 17 2016 at 17:46

А что не так со словом "формирован"? Ввели "фо" — дополнился "формирован", всё же правильно, разве нет?

ischerbin Aug 17 2016 at 17:49

Согласен, пример не самый удачный. Для более наглядной демонстрации результата стемминга привожу топ 10 слов из всей базы:

tasks=# SELECT * FROM ts_stat('SELECT fti_txt FROM texts') ORDER BY nentry DESC, ndoc DESC LIMIT 10;
  word  | ndoc | nentry 
--------+------+--------
 сдела  | 1286 |   1507
 сервер |  628 |    830
 сегодн |  667 |    725
 дан    |  573 |    723
 эт     |  579 |    689
 нов    |  525 |    629
 дела   |  524 |    595
 работ  |  522 |    589
 файл   |  382 |    544
 1      |  256 |    538
(10 rows)

cmdx Aug 17 2016 at 23:45

Давайте по порядку.

У всей системы есть один крупный недостаток...

Откуда, позвольте поинтересоваться, вы откопали tsearch2? Его нужно закопать обратно и больше не трогать. Этот модуль используется только для обратной совместимости. Начиная с версии 8.3. в PostgreSQL есть встроенный полнотекстовый поиск:
The tsearch2 module provides backwards-compatible text search functionality for applications that used tsearch2 before text searching was integrated into core PostgreSQL in release 8.3.

… слова в поле типа tsvector записываются после стемминга

Скорее всего у вас просто не настроены словари.
Стеммер должен использоваться в последнюю очередь, только если слово не найдено в словаре:

SELECT
  token
, dictionary
, lexemes
  FROM ts_debug('Проверяю ксерокс searches ктулху')
  WHERE lexemes IS NOT NULL
;

Должно получиться что-то вроде:

     token   |  dictionary     |  lexemes
   ----------+-----------------+-------------
    Проверяю | dict_ispell_rus | {проверять}
    ксерокс  | thesaurus_tpro  | {xerox}
    searches | dict_ispell_eng | {search}
    ктулху   | russian_stem    | {ктулх}

P.S. А если еще синонимы настроить, то можно получить такой бонус:

SELECT to_tsvector('клапан запорный корозийностойкий') @@ plainto_tsquery('вентиль нержавеющий'); -- TRUE

ischerbin Aug 19 2016 at 04:31

Хабр — сила! Спасибо!

paradoxfm Aug 17 2016 at 23:45

Тут стоит посмотреть в сторону pg_trgm
https://postgrespro.ru/docs/postgrespro/9.5/pgtrgm

shk1r Aug 18 2016 at 14:53

Позвольте спросить, что выдаст запрос с частью слова, например, «обраб»(обработка)? Необходимо обязательно минимум одно полное слово для FTS?

cmdx Aug 18 2016 at 19:24

Для поиска по части слова лучше действительно использовать pg_trgm как советует paradoxfm

SELECT title FROM company WHERE LOWER(title) ~ 'дизель' ORDER BY 1;

АВТОДИЗЕЛЬ ПЛЮС
АВТОДИЗЕЛЬЗАПЧАСТЬ
Автодизель Актобе
Дизель-Резерв
ДизельГрупп
ДизельДеталь
...
Ярдизель Сервис

Но для этого желательно правильно настроить индексы:

CREATE INDEX ON company USING gin (LOWER(title) gin_trgm_ops);
EXPLAIN SELECT title FROM company WHERE LOWER(title) ~ 'дизель' ORDER BY 1;

...
Bitmap Index Scan on company_lower_title_trgm
  Index Cond: (lower(title) ~ 'дизель'::text)

cmdx Aug 18 2016 at 19:32

@shk1r: Необходимо обязательно минимум одно полное слово для FTS?

Теоретически, вы можете искать по части слова используя родной полнотекстовый поиск:

SELECT title FROM company WHERE tsv @@ to_tsquery('дизельн:*');

Балтийская дизельная компания
Дизельные Технологии
...
Саратовдизельаппарат
Сибирский дизельный центр

Я предпочитаю для поиска создавать отдельное поле, например

tsv::TSVECTOR

и делать индекс по нему

...
Bitmap Index Scan on company_tsv  (cost=0.00..496.22 rows=28 width=0)
  Index Cond: (tsv @@ to_tsquery('дизельн:*'::text))

shk1r Aug 19 2016 at 12:12

Спасибо большое за ответы

Show the best of all time