Как заставить оптимизатор PostgresQL строить план выполнения ПОСЛЕ параметров привязки?

Я разрабатываю функцию Pg/PLSQL для PostgresQL 9.1. Когда я использую переменные в SQL-запросе, оптимизатор создает плохой план выполнения. Но если я заменю переменную ее значением, план будет в порядке. Например:

v_param := 100;
select count(*)
  into result
  from <some tables>
 where <some conditions>
       and id = v_param

выполняется за 3 с

а также

select count(*)
  into result
  from <some tables>
 where <some conditions>
       and id = 100

выполняется за 300 мс

В первом случае оптимизатор генерирует фиксированный план для любого значения v_param.

Во втором случае оптимизатор генерирует план на основе указанного значения, и это значительно эффективнее, несмотря на то, что не используется кэширование плана.

Можно ли заставить оптимизатор генерировать план без динамической привязки и генерировать план каждый раз, когда я выполняю запрос?


postgresql sql sql-optimization
person a.oberon    schedule 11.09.2012    source источник

Ответы (2)


arrow_upward
6
arrow_downward

Это было значительно улучшено Томом Лейном в только что выпущенном PostgreSQL 9.2; см. что нового в PostgreSQL 9.2, в частности:

Раньше подготовленные операторы оптимизировались один раз, без знания значений параметров. В 9.2 планировщик будет использовать конкретные планы в отношении отправленных параметров (запрос будет планироваться при выполнении), за исключением случаев, когда запрос выполняется несколько раз, и планировщик решает, что общий план не намного дороже, чем конкретные планы. .

Это была давняя и болезненная бородавка, которая ранее требовала SET enable_... параметров, использования функций-оболочек с использованием EXECUTE или других уродливых хаков. Теперь он должен «просто работать».

Обновление.

Для всех, кто читает это, вы можете сказать, кусает ли вас эта проблема, потому что auto_explain планы параметризованных / подготовленных запросов будут отличаться от тех, которые вы получаете, когда вы explain выполняете запрос самостоятельно. Чтобы проверить, попробуйте PREPARE ... SELECT, а затем EXPLAIN EXECUTE и посмотрите, получится ли у вас план, отличный от EXPLAIN SELECT.

См. также ">этот предыдущий ответ.

person Craig Ringer    schedule 11.09.2012
comment
+1 за нахождение идеального реального примера преимущества, описанного в документе «Что нового», который, возможно, не привлек всеобщего внимания. И за почти смехотворную скорость (‹24 часа с 9.2 GA). - person mdahlman; 11.09.2012
comment
@mdahlman Я был в восторге от этой функции с тех пор, как Том реализовал ее и некоторое время указывая на это. Это небольшое, но большое улучшение, которое должно помочь решить такие проблемы, как эта прозрачно в будущем. - person Craig Ringer; 12.09.2012

arrow_upward
3
arrow_downward

Динамические запросы не используют кэшированные планы, поэтому вы можете использовать оператор EXECUTE USING в версии 9.1 и старше. 9.2 должен работать без этого обходного пути, как написал Крейг.

v_param := 100;
EXECUTE 'select count(*) into result from <some tables> where <some conditions>
   and id = $1' USING v_param;
person Pavel Stehule    schedule 11.09.2012
comment
+1 за обходной путь, который еще не требует обновления до 9.2. Хороший. - person mdahlman; 12.09.2012
comment
Обратите внимание, что это работает только внутри функции PL/PgSQL. Обычный SQL EXECUTE используется для вызова подготовленных операторов. Я упомянул об этом в функциях-оболочках, используя EXECUTE, но должен был быть более конкретным; спасибо Павел. - person Craig Ringer; 12.09.2012
comment
конечно, SQL EXECUTE и PL/pgSQL EXECUTE - это два разных оператора - этот метод невозможен для подготовленных операторов, и это должно быть проблемой в некоторых средах - PostgreSQL имеет хорошую функциональность для однократного выполнения PQexecParams, которая может почти всегда можно использовать вместо подготовленных операторов, но это доступно только в некоторых интерфейсах - person Pavel Stehule; 12.09.2012