Запрос Postgres выполняется очень медленно при использовании параметра вместо жестко заданной строки

Я сталкиваюсь с этой проблемой Postgres, когда выполнение одного и того же запроса занимает много времени, если я использую параметр вместо жесткого кодирования его значения в строке запроса. Имя столбца — «media_type», и это VARCHAR (20). Я запускаю эти запросы из PHP, используя Symfony2 и Doctrine2 ORM, и рассматриваемая таблица содержит около 1 000 000 записей.

У меня есть проблема с моим запросом? Может ли это быть проблемой конфигурации Postgres?

1 — жестко заданное значение для media_type

duration: 5.365 ms  parse pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
duration: 0.142 ms  bind pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
parameters: $1 = '1', $2 = '1', $3 = '100', $4 = '0'
duration: 8.667 ms  execute pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
parameters: $1 = '1', $2 = '1', $3 = '100', $4 = '0'

План выполнения:

duration: 8.640 ms  plan:
    Query Text: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
    Limit  (cost=8.38..8.38 rows=1 width=12) (actual time=8.516..8.595 rows=24 loops=1)
      Buffers: shared hit=10 read=15
        ->  Sort  (cost=8.38..8.38 rows=1 width=12) (actual time=8.505..8.530 rows=24 loops=1)
            Sort Key: id
            Sort Method: quicksort  Memory: 26kB
            Buffers: shared hit=10 read=15
            ->  Index Scan using item_media_type_index on item  (cost=0.00..8.37 rows=1 width=12) (actual time=7.955..8.397 rows=24 loops=1)
                    Index Cond: ((media_type)::text = 'Collection'::text)
                    Filter: (enabled AND (site_id = $1) AND (user_id = $2))
                    Buffers: shared hit=8 read=15

2 – Использование параметра для media_type (SLOWER)

duration: 5.557 ms  parse pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
duration: 1.322 ms  bind pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
parameters: $1 = 'Collection', $2 = '1', $3 = '1', $4 = '100', $5 = '0'
duration: 71564.998 ms  execute pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
parameters: $1 = 'Collection', $2 = '1', $3 = '1', $4 = '100', $5 = '0'

План выполнения:

duration: 71564.922 ms  plan:
    Query Text: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
    Limit  (cost=90663.16..181326.31 rows=17184 width=12) (actual time=3.667..71564.864 rows=24 loops=1)
      Buffers: shared hit=183786 read=96585
        ->  Index Scan Backward using item_pkey on item  (cost=0.00..906610.46 rows=171836 width=12) (actual time=3.655..71564.798 rows=24 loops=1)
               Filter: (enabled AND ((media_type)::text = $1) AND (site_id = $2) AND (user_id = $3))
               Buffers: shared hit=183786 read=96585

Заранее спасибо.


postgresql symfony sql-execution-plan doctrine-orm postgresql-9.1
person luis    schedule 30.05.2012    source источник

Ответы (3)


arrow_upward
9
arrow_downward

Это своего рода давняя проблема в PostgreSQL, которая исторически требовала некоторой интересной настройки планировщика, чтобы обойти ее. Это исправлено в PostgreSQL 9.2 (сейчас в бета-версии), хотя, как обычно, спасибо Тому Лейну.

Е.1.3.1.3. Оптимизатор

Улучшение способности планировщика выбирать параметризованные планы (Том Лейн)

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

См. примечания к выпуску бета-версии 9.2 и небольшое примечание Я писал об этом на lwn.net. В списках рассылки есть много информации о том, как обрабатывать подготовленные/параметризованные операторы, работающие медленнее, чем обычные.

person Craig Ringer    schedule 31.05.2012
comment
Спасибо! Есть ли ожидаемая дата выпуска PostgreSQL 9.2? Я искал его безуспешно. Кроме того, обычно насколько стабильны бета-версии PostgreSQL? - person luis; 31.05.2012
comment
@luis Я запускаю бета-версии PostgreSQL, где это возможно, для моей работы разработчиков и никогда не сталкивался с проблемой. Однако я не использовал их в производстве. Если вы все еще находитесь на стадии разработки, использовать бета-версию не составляет труда. Что касается продакшна, то Pg в целом удивительно стабилен, и, несмотря на то, что имеется очень случайная опасная ошибка, они, как правило, обнаруживаются в нечетных крайних случаях благодаря регрессионным тестам. Я не могу посоветовать вам запускать бета-версию в продакшене — это бета-версия по какой-то причине, команда не думает, что она готова — но я бы сделал это, если бы мне пришлось решить критическую проблему. Если возможно, поработайте и дождитесь релиза. - person Craig Ringer; 03.06.2012
comment
@luis PostgreSQL 9.2 был выпущен сегодня, к вашему сведению. - person Craig Ringer; 12.09.2012

arrow_upward
2
arrow_downward

Однажды я столкнулся с очень похожей проблемой, когда привязывался к полю SMALLINT и передавал значение, которое Postgres неявно преобразовывал из INTEGER в SMALLINT. Я исправил это, сделав приведение явным. Поскольку media_type имеет тип VARCHAR(20), Postgres выполняет неявное приведение типа TEXT. Попробуй это:

where media_type = $1::VARCHAR(20)

person Sam Choukri    schedule 30.05.2012
comment
Похоже, это стоит попробовать, хотя это также делает приведение в более эффективном плане поиска по индексу. - person Edmund; 31.05.2012

arrow_upward
1
arrow_downward

В вашем статическом, более быстром запросе используется item_media_type_index. В вашем связанном, более медленном запросе item_media_type_index не используется.

Какова селективность столбца «media_type» в таблице «item»? Если вы сделали:

SELECT media_type, COUNT(*)
  FROM item
  GROUP BY media_type
  ORDER BY 2 desc

Равномерно ли сбалансированы типы медиа_типов или относительно мало медиа_типов «Коллекции» по сравнению с другими? Если элементов «Коллекции» относительно мало, я бы рискнул предположить: со статическим запросом синтаксический анализатор знает, что вы запрашиваете «Коллекцию», и может определить, что «Коллекция» имеет низкое количество и индекс, вероятно, стоит использовать. Но в случае переменной привязки синтаксический анализатор не знает, какой media_type вы используете. Некоторое другое значение media_type может составлять большую часть записей в таблице (скажем, 20%). В этом случае было бы быстрее выполнить сканирование, чем даже возиться с использованием индекса. Анализатору необходимо принять решение, и он решает не использовать индекс (неправильно для вашего случая, но, вероятно, правильно для другого media_type). Это всего лишь предположение, основанное на том, как работают некоторые другие rdbms.

В этом случае ответ заключается в жестком коде, если вы знаете, что атрибуты селективности очень искажены, используйте динамический sql для принудительного позднего синтаксического анализа или принудительного использования индекса, если вы считаете, что это правильно.

person Glenn    schedule 31.05.2012