Оптимизация ORDER BY в запросе полнотекстового поиска

У меня есть большая таблица entities с ~ 15M записи. Я хочу найти лучшие 5 строк, соответствующих 'хоккею' в их name.

У меня есть полнотекстовый индекс на name, который используется: gin_ix_entity_full_text_search_name

Запрос:

 SELECT "entities".*,
         ts_rank(to_tsvector('english', "entities"."name"::text),
         to_tsquery('english', 'hockey'::text)) AS "rank0.48661998202865475"
    FROM "entities" 
         WHERE "entities"."place" = 'f' 
              AND (to_tsvector('english', "entities"."name"::text) @@ to_tsquery('english', 'hockey'::text)) 
         ORDER BY "rank0.48661998202865475" DESC LIMIT 5

Продолжительность 25 623 мс

Explain plan
1 Limit  (cost=12666.89..12666.89 rows=5 width=3116)
2   ->  Sort  (cost=12666.89..12670.18 rows=6571 width=3116)
3           Sort Key: (ts_rank(to_tsvector('english'::regconfig, (name)::text), '''hockey'''::tsquery))
4         ->  Bitmap Heap Scan on entities  (cost=124.06..12645.06 rows=6571 width=3116)
5               Recheck Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''hockey'''::tsquery)
6               Filter: (NOT place)
7               ->  Bitmap Index Scan on gin_ix_entity_full_text_search_name  (cost=0.00..123.74 rows=6625 width=0)
8                     Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''hockey'''::tsquery)

Я не понимаю, почему это проверяет индексное условие дважды. (Шаг 4 и 7 плана запросов). Это из-за моего булева условия (not place)? Если так, я должен добавить его к своему индексу для получения очень быстрого запроса? Или условие сортировки заставляет его замедлиться?

EXPLAIN ANALYZE вывод:

  Limit  (cost=4447.28..4447.29 rows=5 width=3116) (actual time=18509.274..18509.282 rows=5 loops=1)
  ->  Sort  (cost=4447.28..4448.41 rows=2248 width=3116) (actual time=18509.271..18509.273 rows=5 loops=1)
         Sort Key: (ts_rank(to_tsvector('english'::regconfig, (name)::text), '''test'''::tsquery))
         Sort Method:  top-N heapsort  Memory: 19kB
     ->  Bitmap Heap Scan on entities  (cost=43.31..4439.82 rows=2248 width=3116) (actual time=119.003..18491.408 rows=2533 loops=1)
           Recheck Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''test'''::tsquery)
           Filter: (NOT place)
           ->  Bitmap Index Scan on gin_ix_entity_full_text_search_name  (cost=0.00..43.20 rows=2266 width=0) (actual time=74.093..74.093 rows=2593 loops=1)
                 Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''test'''::tsquery)
 Total runtime: 18509.381 ms

Вот мои параметры DB. Это размещается Heroku на услугах Amazon. Они описывают это как наличие 1.7 ГБ RAM, 1 блока обработки и DB макс. 1 ТБ.

 name                         | current_setting
------------------------------+---------------------------------------------------------------------------------------------------------
 version                      | PostgreSQL 9.0.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
 archive_command              | test -f /etc/postgresql/9.0/main/wal-e.d/ARCHIVING_OFF || envdir /etc/postgresql/9.0/resource29857_heroku_com/wal-e.d/env wal-e wal-push %p
 archive_mode                 | on
 archive_timeout              | 1min
 checkpoint_completion_target | 0.7
 checkpoint_segments          | 40
 client_min_messages          | notice
 cpu_index_tuple_cost         | 0.001
 cpu_operator_cost            | 0.0005
 cpu_tuple_cost               | 0.003
 effective_cache_size         | 1530000kB
 hot_standby                  | on
 lc_collate                   | en_US.UTF-8
 lc_ctype                     | en_US.UTF-8
 listen_addresses             | *
 log_checkpoints              | on
 log_destination              | syslog
 log_line_prefix              | %u [YELLOW] 
 log_min_duration_statement   | 50ms
 log_min_messages             | notice
 logging_collector            | on
 maintenance_work_mem         | 64MB
 max_connections              | 500
 max_prepared_transactions    | 500
 max_stack_depth              | 2MB
 max_standby_archive_delay    | -1
 max_standby_streaming_delay  | -1
 max_wal_senders              | 10
 port                         | 
 random_page_cost             | 2
 server_encoding              | UTF8
 shared_buffers               | 415MB
 ssl                          | on
 syslog_ident                 | resource29857_heroku_com
 TimeZone                     | UTC
 wal_buffers                  | 8MB
 wal_keep_segments            | 127
 wal_level                    | hot_standby
 work_mem                     | 100MB
 (39 rows)

Править

Похож ORDER BY медленная часть:

d6ifslbf0ugpu=> EXPLAIN ANALYZE SELECT "entities"."name",
     ts_rank(to_tsvector('english', "entities"."name"::text),
     to_tsquery('english', 'banana'::text)) AS "rank0.48661998202865475"
FROM "entities" 
     WHERE (to_tsvector('english', "entities"."name"::text) @@ to_tsquery('english', 'banana'::text)) 
     LIMIT 5;

QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=43.31..53.07 rows=5 width=24) (actual time=76.583..103.623 rows=5 loops=1)
->  Bitmap Heap Scan on entities  (cost=43.31..4467.60 rows=2266 width=24) (actual time=76.581..103.613 rows=5 loops=1)
     Recheck Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''banana'''::tsquery)
     ->  Bitmap Index Scan on gin_ix_entity_full_text_search_name  (cost=0.00..43.20 rows=2266 width=0) (actual time=53.592..53.592 rows=1495 loops=1)
           Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''banana'''::tsquery)
 Total runtime: 103.680 ms

По сравнению с с ORDER BY:

d6ifslbf0ugpu=> EXPLAIN ANALYZE SELECT "entities"."name",
     ts_rank(to_tsvector('english', "entities"."name"::text),
     to_tsquery('english', 'banana'::text)) AS "rank0.48661998202865475"
FROM "entities" 
     WHERE (to_tsvector('english', "entities"."name"::text) @@ to_tsquery('english', 'banana'::text)) 
     ORDER BY "rank0.48661998202865475" DESC
     LIMIT 5;

QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=4475.12..4475.13 rows=5 width=24) (actual time=15013.735..15013.741 rows=5 loops=1)
->  Sort  (cost=4475.12..4476.26 rows=2266 width=24) (actual time=15013.732..15013.735 rows=5 loops=1)
     Sort Key: (ts_rank(to_tsvector('english'::regconfig, (name)::text), '''banana'''::tsquery))
     Sort Method:  top-N heapsort  Memory: 17kB
     ->  Bitmap Heap Scan on entities  (cost=43.31..4467.60 rows=2266 width=24) (actual time=0.872..15006.763 rows=1495 loops=1)
           Recheck Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''banana'''::tsquery)
           ->  Bitmap Index Scan on gin_ix_entity_full_text_search_name  (cost=0.00..43.20 rows=2266 width=0) (actual time=0.549..0.549 rows=1495 loops=1)
                 Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@ '''banana'''::tsquery)
 Total runtime: 15013.805 ms

Бит, который я все еще не понимаю, почему это медленнее. Похож это выбирает ту же сумму строк от Растрового Сканирования "кучи", но это берет настолько дольше?

8
08.01.2020, 00:25
3 ответа

То, что я все еще не понимаю, то, почему это медленнее.

Та сортировка строк будет стоить чего-то, очевидно. Но почему так?
Без ORDER BY rank0... Пост-ГРЭС может просто

  • выберите первые 5 строк, которые это находит, и остановите выбирающие строки, как только это имеет 5.

    Растровое Сканирование "кучи" на объектах... rows=5...

  • затем вычислите ts_rank() всего для 5 строк.
Во втором случае Пост-ГРЭС имеет к

  • выберите все (1495 согласно Вашему плану запросов) строки, которые квалифицируют.

    Растровое Сканирование "кучи" на объектах... rows=1495...

  • вычислить ts_rank() для всех них.
  • отсортируйте всех их для нахождения первых 5 согласно расчетному значению.
Попробовать ORDER BY name только видеть стоимость вычислений to_tsquery('english', 'hockey'::text)) для лишних строк и сколько остается для выборки большего количества строк и сортировки.
8
24.01.2020, 23:03
  • 1
    Кэширование происходит в способ, которым... оно примерно дает представление в качестве плохого. 10secs 1 500 строк. Я понимаю Ваше объяснение.Все понятно. Но в то время как выполнение текста ищет.... какой-либо способ создать мой индекс для надлежащей качественной сортировки, не извлекая все? –  xlash 14.04.2012, 21:51

Растровое сканирование работает как это: индекс сканируется для нахождения местоположений кортежей, соответствующих индексным условиям. Битовый массив мог бы пройти логическую комбинацию с результатами других растровых сканирований, с помощью булевой логики на битах. Затем к страницам, содержащим данные, получают доступ в порядке номера страницы, чтобы уменьшить доступ к диску и надо надеяться превратить некоторые случайные чтения в последовательные чтения.

Растровое индексное сканирование, возможно, должно стать "с потерями" для умещений в памяти - оно уменьшает свою точность от уровня кортежа до уровня страницы. При увеличении work_mem (по крайней мере, для этого запроса), Вы могли бы избежать этого. Это не будет способным для пропуска растрового сканирования "кучи" на строке 4 или фильтр на строке 6, но это смогло пропускать перепроверку на строке 5.

5
24.01.2020, 23:03
  • 1
    Увеличенный от 100 МБ до 650 МБ, не дал различия в производительности. (work_mem) сэр –  xlash 13.04.2012, 05:19

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

Вы могли бы хотеть считать KNN-GiST (для Ближайшего Соседа K - Обобщенное Дерево поиска) индексом, который может вытянуть прямо от индекса в порядке подобия - таким образом, Вы не должны случайным образом читать все те кортежи "кучи" и сортировать их вниз, чтобы найти, что K лучше всего соответствует.

До настоящего времени я не думаю, что любой реализовал поддержку KNN-СУТИ запросов tsearch (хотя меня уверили, что она может быть сделана, это - просто вопрос кого-то не торопящегося, чтобы сделать это), но возможно триграммная поддержка (который сделан), будет работать на Ваше приложение. Основное различие - то, что триграммные поиски не используют словари для стемминга и синонимов, которые делает путь tsearch; Вы только найдете соответствия точного слова.

Для попытки триграмм за пример Вы, вероятно, хотите индексировать "имя" как это:

CREATE INDEX entities_name_trgm ON entities USING gist (name gist_trgm_ops);

Затем можно искать как это:

SELECT
    *,
    name <-> 'banana' AS sim
  FROM entities 
  WHERE name % 'banana'
  ORDER BY sim DESC
  LIMIT 5;

Отметьте используемые операторы и ORDER BY использование псевдонима столбца "подобия". Я не отклонился бы слишком далекий от этого шаблона при испытании его. Индекс на tsvector не используется для этого поиска.

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

5
24.01.2020, 23:03

Теги

Похожие вопросы