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

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

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

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

Я хочу извинить 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.

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

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

  1. Я положил geom @@ AREA в подвыбор -> Не изменил план выполнения
  2. Я создал временный вид с нужным подмножеством областей -> Не изменил план выполнения
  3. Я создал временную таблицу нужной области -> 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

Источник
Другие вопросы по тегам