Полнотекстовый поиск в PostgreSQL за миллисекунды

1. Полнотекстовый поиск в PostgreSQL за миллисекунды Коротков А.Е, Бартунов О.С.

2. Найти документы, которые удовлетворяют запросу • Вернуть результаты в порядке релевантности Полнотекстовый поискв базе данных: задача

3. Интеграция с ядром СУБД • Поддержка транзакций • Конкуретность, recovery • Обновление индекса «online» • Поддержка языка • Расширяемость, масштабируемость Полнотестовый поискв базе данных: требования

4. Произвольный текстовый атрибут • Комбинация текстовых атрибутов • Может быть полностью вирутальным. Например, результатом SQL объединения таблиц doc и autor Что такое документ? Title || Abstract || Keywords || Body || Author

5. Традиционные FTS операторы для атрибутовLIKE, ILIKE, ~, ~* Операторы полнотекстового поиска Проблемы • Отсутствие поддержки языка (стемминг, стоп слова) • Отсутствие ранжирования • Последовательное сканирование документов Решение • Предварительная обработка документов • Поддержка индексов

6. набор правил по преобразованию документа в его FTS представление – tsvector, tsquery • набор функций для получения tsvector, tsquery из текста • FTS операторы и индексы • функции ранжирования, подсветки результатов FTS в PostgreSQL

7. =# select'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; FTS в PostgreSQL • tsvector – представление документа, оптимизированное для поиска • отсортированный массив лексем • позиции и вес лексем • tsquery – тип данные для полнотекстового запроса • булевы операторы - & | ! () • поисковый оператор tsvector@@tsquery

8. Полная интеграция PostgreSQL • 27 встроенный конфигурацийдля 10 языков • Поддержка пользовательских конфигураций • Встраиваемые словари (ispell, snowball, thesaurus), парсеры • Ранжирование по релевантности • GiST и GIN индексы с поддержкой concurrency и recovery • Богатый язык запросов с поддержкой перезаписывания запросов Возможности FTS

9. OpenFTS — 2000, Pg как хранилище GiST index — 2000, спасибо Rambler Tsearch — 2001, contrib:без ранжирования Tsearch2 — 2003, contrib:config GIN —2006, спасибо JFG Networks FTS — 2006, в ядре, спасибо EnterpriseDB E-FTS — Enterprise FTS, спасибо??? FTS в PostgreSQL

10. Внешние решения: Sphinx, Solr, Lucene.... • Скачивание БД в «поисковый движок» (задержка) • Затруднен доступ к атрибутам • Дополнительная сложность • НО: Очень быстро ! • Можно ли ускорить встроенный FTS ? Накладные расходына ACID велики

11. Поиск релевантных документов: Index scan — как правило, довольно быстро • Расчет релевантности: Heap scan — как правило, медленно • Сортировка документов Можно ли ускоритьвстроенный FTS ?

12. 156676 статей Wikipedia: postgres=# explain analyze SELECT docid, ts_rank(text_vector, to_tsquery('english', 'title')) AS rank FROM ti2 WHERE text_vector @@ to_tsquery('english', 'title') ORDER BY rank DESC LIMIT 3; Limit (cost=8087.40..8087.41 rows=3 width=282) (actual time=433.750..433.752 rows=3 loops=1) -> Sort (cost=8087.40..8206.63 rows=47692 width=282) (actual time=433.749..433.749 rows=3 loops=1) Sort Key: (ts_rank(text_vector, '''titl'''::tsquery)) Sort Method: top-N heapsort Memory: 25kB -> Bitmap Heap Scan on ti2 (cost=529.61..7470.99 rows=47692 width=282) (actual time=15.094..423.452 rows=47855 loops=1) Recheck Cond: (text_vector @@ '''titl'''::tsquery) -> Bitmap Index Scan on ti2_index (cost=0.00..517.69 rows=47692 width=0) (actual time=13.736..13.736 rows=47855 loops=1) Index Cond: (text_vector @@ '''titl'''::tsquery) Total runtime: 433.787 ms Можно ли ускоритьвстроенный FTS ?

13. 156676 статей Wikipedia: postgres=# explain analyze SELECT docid, ts_rank(text_vector, to_tsquery('english', 'title')) AS rank FROM ti2 WHERE text_vector @@ to_tsquery('english', 'title') ORDER BY text_vector>< plainto_tsquery('english','title') LIMIT 3; Если бы был такой план Limit (cost=20.00..21.65 rows=3 width=282) (actual time=18.376..18.427 rows=3 loops=1) -> Index Scan using ti2_index on ti2 (cost=20.00..26256.30 rows=47692 width=282) (actual time=18.375..18.425 rows=3 loops=1) Index Cond: (text_vector @@ '''titl'''::tsquery) Order By: (text_vector >< '''titl'''::tsquery) Total runtime: 18.511 ms то было бы неплохо! Можно ли ускоритьвстроенный FTS ?

14. Обучить индекс (GIN) считать релевантность и возвращать документы упорядоченно Хранить позиции лексем в индесу — больше не нужна колонка tsvecotr Использовать компрессию Изменить алгоритмы и интерфейсы Оптимизировать случай редкое_слово & частое_слово Было бы неплохо

15. Инвертированный индекс

16. Инвертированный индекс QUERY: compensation accelerometers INDEX: accelerometers compensation 5,10,25,28,30,36,58,59,61,73,74 30,68 RESULT: 30

17. Инвертированный индекс в PostgreSQL E N T R Y T R E E Posting list Posting tree Нет позиционной информации в индексе !

18. GIN • способ хранения • алгоритм поиска • поддержка ORDER BY • изменения интерфейса • Планировщик Список изменений

19. Изменение структуры GIN

20. Дополнительная информация (позиции слов)

21. typedef struct ItemPointerData { BlockIdData ip_blkid; OffsetNumber ip_posid; } typedef struct BlockIdData { uint16 bi_hi; uint16 bi_lo; } BlockIdData; ItemPointer 6 bytes

22. /* * Equivalent to * typedef struct { * uint16 * weight:2, * pos:14; * } */ typedef uint16 WordEntryPos; WordEntryPos 2 bytes

23. Varbyte сжатие BlockIdData

24. Varbyte сжатие OffsetNumber O0-O15 – биты OffsetNumber N –NULL бит дополнительной информаци

25. Varbyte сжатие WordEntryPos P0-P13 – биты позиции W0,W1 – биты веса

26. Пример

27. Top-N запросы Сканирование + вычисление релевантности Сортировка Возвращение результатов по одному с помощью gingettuple

28. Быстрое сканирование entry1 && entry2

29. Изменения интерфейса GIN

30. extractValue Datum *extractValue ( Datum itemValue, int32 *nkeys, bool **nullFlags, Datum *addInfo, bool *addInfoIsNull )

31. Datum *extractValue ( Datum query, int32 *nkeys, StrategyNumber n, bool **pmatch, Pointer **extra_data, bool **nullFlags, int32 *searchMode, ???bool **required??? ) extractQuery

32. bool consistent ( bool check[], StrategyNumber n, Datum query, int32 nkeys, Pointer extra_data[], bool *recheck, Datum queryKeys[], bool nullFlags[], Datum addInfo[], bool addInfoIsNull[] ) consistent

33. float8 calcRank ( bool check[], StrategyNumber n, Datum query, int32 nkeys, Pointer extra_data[], bool *recheck, Datum queryKeys[], bool nullFlags[], Datum addInfo[], bool addInfoIsNull[] ) calcRank

34. ???joinAddInfo??? Datum joinAddInfo ( Datum addInfos[] )

35. Оптимзация для планировщика

36. test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY slow_func(x,y) LIMIT 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3.09 rows=10 width=16) (actual time=11.344..103.443 rows=10 loops=1) Output: x, y, (slow_func(x, y)) -> Index Scan using test_idx on public.test (cost=0.00..309.25 rows=1000 width=16) (actual time=11.341..103.422 rows=10 loops=1) Output: x, y, slow_func(x, y) Total runtime: 103.524 ms (5 rows) До

37. test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY slow_func(x,y) LIMIT 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3.09 rows=10 width=16) (actual time=0.062..0.093 rows=10 loops=1) Output: x, y -> Index Scan using test_idx on public.test (cost=0.00..309.25 rows=1000 width=16) (actual time=0.058..0.085 rows=10 loops=1) Output: x, y Total runtime: 0.164 ms (5 rows) После

38. Результаты тестирования

39. avito.ru: 6.7 млн. документов

40. SELECT itemid, title FROM items WHERE fts @@ plainto_tsquery('russian', 'квартира') ORDER BY ts_rank(fts, plainto_tsquery('russian', 'квартира')) DESC LIMIT 10; С колонкой tsvector, без патча

41. Limit (cost=729272.24..729272.26 rows=10 width=398) (actual time=1871.31 Buffers: shared hit=696232 -> Sort (cost=729272.24..731294.81 rows=809028 width=398) (actual tim Sort Key: (ts_rank(fts, '''квартир'''::tsquery)) Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=696232 -> Bitmap Heap Scan on items (cost=8661.97..711789.43 rows=8090 Recheck Cond: (fts @@ '''квартир'''::tsquery) Buffers: shared hit=696232 -> Bitmap Index Scan on fts_idx (cost=0.00..8459.71 rows= Index Cond: (fts @@ '''квартир'''::tsquery) Buffers: shared hit=612 Total runtime: 1871.349 ms С колонкой tsvector, без патча

42. SELECT itemid, title FROM items WHERE fts @@ plainto_tsquery('russian', 'квартира') ORDER BY fts >< plainto_tsquery('russian', 'квартира') LIMIT 10; С колонкой tsvector, с патчем

43. С колонкой tsvector, с патчем Limit (cost=20.00..59.46 rows=10 width=400) (actual t -> Index Scan using fts_idx on items (cost=20.00.. Index Cond: (fts @@ '''квартир'''::tsquery) Order By: (fts >< '''квартир'''::tsquery) Total runtime: 143.952 ms

44. Без колонки tsvector, без патча SELECT itemid, title FROM items2 WHERE (setweight(to_tsvector('russian'::regconfig, title), 'A'::"char") || setweight(to_tsvector( 'russian'::regconfig, description), 'B'::"char")) @@ plainto_tsquery('russian', 'квартира') ORDER BY ts_rank((setweight(to_tsvector('russian'::regconfig, title), 'A'::"char") || setweight(to_tsvector('russian'::regconfig, description), 'B'::"char")), plainto_tsquery('russian', 'квартира')) DESC LIMIT 10;

45. Без колонки tsvector, без патча Limit (cost=749132.39..749132.41 rows=10 width=372) (actual time=52685.5 Buffers: shared hit=485458 -> Sort (cost=749132.39..751145.79 rows=805360 width=372) (actual tim Sort Key: (ts_rank((setweight(to_tsvector('russian'::regconfig, t Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=485458 -> Bitmap Heap Scan on items2 (cost=8625.55..731728.85 rows=805 Recheck Cond: ((setweight(to_tsvector('russian'::regconfig, Buffers: shared hit=485458 -> Bitmap Index Scan on fts_idx2 (cost=0.00..8424.21 rows Index Cond: ((setweight(to_tsvector('russian'::regcon Buffers: shared hit=612 Total runtime: 52685.595 ms

46. SELECT itemid, title FROM items2 WHERE (setweight(to_tsvector('russian'::regconfig, title), 'A'::"char") || setweight(to_tsvector( 'russian'::regconfig, description), 'B'::"char")) @@ plainto_tsquery('russian', 'квартира') ORDER BY (setweight(to_tsvector('russian'::regconfig, title), 'A'::"char") || setweight(to_tsvector('russian'::regconfig, description), 'B'::"char")) >< plainto_tsquery('russian', 'квартира') LIMIT 10; Без колонки tsvector, с патчем

47. Без колонки tsvector, с патчем Limit (cost=20.02..59.61 rows=10 width=373) (actual time=14 Buffers: shared hit=1556 -> Index Scan using fts_idx2 on items2 (cost=20.02..3282 Index Cond: ((setweight(to_tsvector('russian'::regco Order By: ((setweight(to_tsvector('russian'::regconf Buffers: shared hit=1556 Total runtime: 143.639 ms

48. SELECT itemid, title FROM items WHERE fts @@ plainto_tsquery('russian', 'квартира арбат') ORDER BY ts_rank(fts, plainto_tsquery('russian', 'квартира арбат')) DESC LIMIT 10; C колонкой tsvector, без патча

49. Limit (cost=6908.03..6908.05 rows=10 width=398) (actual time=92.049..92 Buffers: shared hit=1314 -> Sort (cost=6908.03..6912.44 rows=1766 width=398) (actual time=92.0 Sort Key: (ts_rank(fts, '''квартир'' & ''арбат'''::tsquery)) Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=1314 -> Bitmap Heap Scan on items (cost=61.69..6869.86 rows=1766 wid Recheck Cond: (fts @@ '''квартир'' & ''арбат'''::tsquery) Buffers: shared hit=1314 -> Bitmap Index Scan on fts_idx (cost=0.00..61.25 rows=17 Index Cond: (fts @@ '''квартир'' & ''арбат'''::tsquer Buffers: shared hit=616 Total runtime: 92.069 ms C колонкой tsvector, без патча

50. SELECT itemid, title FROM items WHERE fts @@ plainto_tsquery('russian', 'квартира арбат') ORDER BY fts >< plainto_tsquery('russian', 'квартира арбат') LIMIT 10; C колонкой tsvector, с патчем