Как повлиять на анализатор запросов Postgres при работе с текстовым поиском и геопространственными данными
У меня довольно серьезная проблема с производительностью со следующим утверждением, которое я не могу исправить самостоятельно.
Данная ситуация
- У меня есть база данных postgres 8.4 с установленным Postgis 1.4
- У меня есть геопространственная таблица с ~9 миллионами записей. Эта таблица имеет столбец (postgis) геометрии и столбец tsvector
- У меня есть индекс GIST по геометрии и индекс VNAME в столбце vname
- Стол
ANALYZE
d"
Я хочу извинить to_tsquery
текстовый поиск в подмножестве этих геометрий, который должен вернуть мне все идентифицированные идентификаторы.
Область для поиска будет ограничивать 9 миллионов наборов данных примерно до 100.000 и набор результатов ts_query
внутри этой области, скорее всего, будет выдано 0..1000 записей.
проблема
Анализатор запросов решает, что он хочет сначала выполнить растровое индексное сканирование для vname, а затем объединяет и устанавливает фильтр для геометрии (и других условий, которые я имею в этом утверждении)
Выходные данные Query Analyzer:
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.
Попытки Решения
Чтобы добиться желаемого поведения, я пытался
- Я положил geom @@
AREA
в подвыбор -> Не изменил план выполнения - Я создал временный вид с нужным подмножеством областей -> Не изменил план выполнения
- Я создал временную таблицу нужной области -> 4~6 секунд, чтобы сделать ее еще хуже.
Кстати, извините за то, что я не опубликовал реальный запрос: я думаю, что мой босс был бы действительно зол на меня, если бы я это сделал, также я ищу больше теоретических указателей для кого-то, чтобы исправить мой фактический запрос. Пожалуйста, спросите, если вам нужны дополнительные разъяснения
РЕДАКТИРОВАТЬ
У Ричарда было очень хорошее замечание: вы можете достичь желаемого поведения Планировщика запросов с помощью width
заявление. Плохо то, что эта временная таблица (или CTE) портит индекс vname, поэтому в некоторых случаях запрос ничего не возвращает.
Я смог исправить это с помощью создания нового vname на лету с to_tsvector()
, но это (слишком) дорого - около 300 - 500 мс на запрос.
Мое решение
Я бросил поиск по vname и пошел с простым LIKE('%query_string%')
(10-20 мс / запрос), но это быстро только в моей среде. YMMV.
1 ответ
Были некоторые улучшения в обработке статистики для 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