Как повлиять на анализатор запросов Postgres при работе с текстовым поиском и геопространственными данными

У меня довольно серьезная проблема с производительностью со следующим утверждением, которое я не могу исправить самостоятельно.

Данная ситуация

  • У меня есть база данных postgres 8.4 с установленным Postgis 1.4.
  • У меня есть геопространственная таблица с ~ 9 миллионами записей. В этой таблице есть столбец геометрии (postgis) и столбец tsvector.
  • У меня есть индекс GIST для геометрии и индекс VNAME для столбца vname.
  • Таблица ANALYZE

Я хочу выполнить поиск to_tsquerytext в подмножестве этих геометрий, который должен вернуть мне все затронутые идентификаторы.

Область для поиска ограничит 9 миллионов наборов данных примерно до 100 000, а набор результатов ts_query внутри этой области, скорее всего, даст результат 0..1000 записей.

Проблема

Анализатор запросов решает, что он хочет сначала выполнить сканирование индекса растрового изображения для vname, а затем объединяет и помещает фильтр в геометрию (и другие условия, которые я имею в этом утверждении).

Вывод анализатора запросов:

Aggregate  (cost=12.35..12.62 rows=1 width=510) (actual time=5.616..5.616 rows=1 loops=1)
->  Bitmap Heap Scan on mxgeom g  (cost=8.33..12.35 rows=1 width=510) (actual time=5.567..5.567 rows=0 loops=1)
     Recheck Cond: (vname @@ '''hemer'' & ''hauptstrasse'':*'::tsquery)
     Filter: (active AND (geom && '0107000020E6100000010000000103000000010000000B0000002AFFFF5FD15B1E404AE254774BA8494096FBFF3F4CC11E40F37563BAA9A74940490200206BEC1E40466F209648A949404DF6FF1F53311F400C9623C206B2494024EBFF1F4F711F404C87835954BD4940C00000B0E7CA1E4071551679E0BD4940AD02004038991E40D35CC68418BE49408EF9FF5F297C1E404F8CFFCB5BBB4940A600006015541E40FAE6468054B8494015040060A33E1E4032E568902DAE49402AFFFF5FD15B1E404AE254774BA84940'::geometry) AND (mandator_id = ANY ('{257,1}'::bigint[])))
     ->  Bitmap Index Scan on gis_vname_idx  (cost=0.00..8.33 rows=1 width=0) (actual time=5.566..5.566 rows=0 loops=1)
           Index Cond: (vname @@ '''hemer'' & ''hauptstrasse'':*'::tsquery)

что вызывает МНОГО ввода-вывода - AFAIK Было бы разумнее сначала ограничить геометрию, а затем выполнить поиск vname.

Попытки решения

Чтобы добиться желаемого поведения, я попытался

  1. Поставил геом@@AREA в подзапрос -> План выполнения не менял
  2. Я создал временное представление с нужным подмножеством области -> Не изменил план выполнения
  3. Я создал временную таблицу нужной области -> Создание занимает 4-6 секунд, так что стало еще хуже.

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

ИЗМЕНИТЬ

У Ричарда была очень хорошая мысль: вы можете добиться желаемого поведения планировщика запросов с помощью инструкции width. Плохо то, что эта временная таблица (или CTE) искажает индекс vname, из-за чего в некоторых случаях запрос ничего не возвращает.

Я смог исправить это, создав новое vname на лету с помощью to_tsvector(), но это (слишком) дорого — около 300–500 мс на запрос.

Мое решение

Я отказался от поиска vname и пошел с простым LIKE('%query_string%') (10-20 мс/запрос), но это быстро только в моей среде. YMMV.


postgresql postgis query-analyzer
person Christian Uhl    schedule 18.11.2011    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Внесены некоторые улучшения в обработку статистики для tsvector (и я думаю PostGIS тоже, но я им не пользуюсь). Если у вас есть время, возможно, стоит попробовать еще раз с версией 9.1 и посмотреть, что она вам даст.

Однако для этого единственного запроса вам может понадобиться взглянуть на конструкцию WITH.

http://www.postgresql.org/docs/8.4/static/queries-with.html

Если вы поместите геометрическую часть в предложение WITH, она будет оцениваться первой (гарантировано), а затем этот набор результатов будет отфильтрован с помощью следующего SELECT. Это может закончиться медленнее, вы не узнаете, пока не попробуете.

Возможно, также поможет корректировка work_mem — вы можете делать это для каждого сеанса («SET work_mem = …»), но будьте осторожны с установкой слишком высокого значения — одновременные запросы могут быстро сжечь всю вашу оперативную память.

http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

person Richard Huxton    schedule 18.11.2011
comment
Спасибо Ричард! Как я уже говорил в своем редактировании, конструкция with - действительно много разрабатывала план запроса, как и ожидалось, но в конце концов он получился слишком медленным, и я нашел обходной путь. К сожалению, я не могу переключиться на 9.1 из-за других устаревших фреймворков, зависящих от 8.4 :( - person Christian Uhl; 21.11.2011