Как я могу "думать лучше" при чтении плана запросов PostgreSQL? (Пример прилагается)

Я потратил более часа сегодня, озадачивая себя планом запросов, который я не мог понять. Запрос был UDPATE, и он просто не запускался AT ALL. Полностью зашел в тупик: pg_locks показал, что ничего не ждет. Теперь я не считаю себя лучшим или худшим человеком для чтения плана запросов, но я считаю, что это исключительно сложно. Мне интересно, как это читать? Существует ли методология, согласно которой Pg-асы следуют, чтобы точно определить ошибку?

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

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=47680.88..169413.12 rows=1 width=77)
   Join Filter: ((co.fkey_style = v.chrome_styleid) AND (co.name = o.name))
   ->  Nested Loop  (cost=5301.58..31738.10 rows=1 width=81)
         ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
               Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))
               ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                     Filter: (name IS NULL)
               ->  Hash  (cost=4547.33..4547.33 rows=36150 width=24)
                     ->  Seq Scan on vehicles iv  (cost=0.00..4547.33 rows=36150 width=24)
                           Filter: (date_sold IS NULL)
         ->  Index Scan using options_pkey on options co  (cost=0.00..8.79 rows=1 width=49)
               Index Cond: ((co.fkey_style = iv.chrome_styleid) AND (co.code = io.code))
   ->  Hash Join  (cost=42379.30..137424.09 rows=16729 width=26)
         Hash Cond: ((v.lot_id = o.lot_id) AND ((v.vin)::text = (o.vin)::text))
         ->  Seq Scan on vehicles v  (cost=0.00..4547.33 rows=65233 width=24)
         ->  Hash  (cost=20223.32..20223.32 rows=931332 width=44)
               ->  Seq Scan on options o  (cost=0.00..20223.32 rows=931332 width=44)
(17 rows)

Проблема с этим планом запроса - я считаю, что я понимаю - лучше всего сказать RhodiumToad (он определенно лучше в этом, поэтому я ставлю на его объяснение лучше) irc://irc.freenode.net/#postgresql:

ой, этот план потенциально опасен проблема с этим планом заключается в том, что он запускает чрезвычайно дорогое hashjoin для каждой строки проблема заключается в оценке строк = 1 от другого объединения и планировщик думает, что это нормально, чтобы поставить очень дорогостоящий запрос во внутреннем пути nestloop, где, по оценкам, внешний путь возвращает только одну строку. так как, очевидно, по оценке планировщика дорогостоящая часть будет выполняться только один раз но у этого есть очевидная тенденция действительно испортиться на практике проблема заключается в том, что планировщик считает свои собственные оценки в идеале планировщик должен знать разницу между "оценкой, чтобы возвратить 1 строку" и "невозможно вернуть более 1 строки", но это совсем не ясно, как включить это в существующий код

Далее он говорит:

это может повлиять на любое соединение, но обычно объединение с подзапросами является наиболее вероятным

Теперь, когда я прочитал этот план, первое, что я заметил, это Nested Loop Anti Join, это стоило 169,413 (я буду придерживаться верхних границ). Эта Anti-Join распадается на результат Nested Loop по цене 31,738 и результат Hash Join по цене 137,424. Теперь 137,424 намного больше, чем 31,738, поэтому я знал, что проблема связана с Hash Join. Затем переходим к EXPLAIN ANALYZE сегменту Hash Join вне запроса. Это выполнено за 7 секунд. Я убедился, что есть индексы (lot_id, vin) и (co.code, и v.code) - было. Я отключил seq_scan и hashjoin индивидуально и заметьте увеличение скорости менее 2 секунд. Не достаточно близко, чтобы объяснить, почему он не прогрессирует через час.

Но, после всего этого, я совершенно неправ! Да, это была медленная часть запроса, но из-за бит rows="1" (я предполагаю, что он был на Nested Loop Anti Join). Есть ли урок, который поможет мне идентифицировать эти типы проблем. Здесь ошибка (отсутствие способности) в планировщике неверно оценивает количество строк? Как я должен читать это, чтобы прийти к такому же выводу RhodiumToad did?

Это просто rows="1", который должен заставить меня понять это?

Я запускал VACUUM FULL ANALYZE для всех задействованных таблиц, и это Postgresql 8.4.

Ответы

Ответ 1

Прослеживание таких вопросов требует некоторого опыта в том, где все может пойти не так. Но чтобы найти проблемы в планах запросов, попробуйте выполнить проверку произведенного плана изнутри, проверьте, соответствует ли количество оценок строк здравым и стоимостным оценкам затраченное время. Btw. две оценки затрат не являются более низкими и верхними границами, во-первых, оценочная стоимость для создания первой строки выпуска, второе число - расчетная общая стоимость, см. пояснить документацию, имеется также документация планера. Это также помогает узнать, как работают различные методы доступа. В качестве отправной точки Wikipedia содержит информацию о вложенном цикле, hash и объединяет объединения.

В вашем примере вы должны начать с:

           ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                 Filter: (name IS NULL)

Запустите EXPLAIN ANALYZE SELECT * FROM options WHERE name IS NULL; и посмотрите, соответствуют ли возвращенные строки оценке. Фактор 2 выключен, как правило, не проблема, вы пытаетесь определить разницу по порядку величины.

Затем см. EXPLAIN ANALYZE SELECT * FROM vehicles WHERE date_sold IS NULL; возвращает ожидаемое количество строк.

Затем перейдите на один уровень к хеш-соединению:

     ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
           Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))

Смотрите, если EXPLAIN ANALYZE SELECT * FROM vehicles AS iv INNER JOIN options io ON (io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text) WHERE iv.date_sold IS NULL AND io.name IS NULL; приводит к 229 строкам.

На еще один уровень добавляется INNER JOIN options co ON (co.fkey_style = iv.chrome_styleid) AND (co.code = io.code), и ожидается, что он вернет только одну строку. Вероятно, проблема заключается в том, что если фактическое число строк составляет от 1 до 100, общая оценка затрат на перемещение внутреннего цикла содержащего вложенный цикл отключается в 100 раз.

Основная ошибка, которую делает планировщик, вероятно, заключается в том, что она ожидает, что два предиката для соединения в co не зависят друг от друга и умножают их селективность. Хотя на самом деле они могут быть сильно коррелированы, а селективность ближе к MIN (s1, s2), а не s1 * s2.

Ответ 2

Вы АНАЛИЗИЛИ таблицы? И что говорит pg_stats об этих таблицах? План запроса основан на статистике, они должны быть в порядке. И какую версию вы используете? 8,4?

Расходы можно рассчитать, используя статистику, количество переходов, количество строк и настройки в postgresql.conf для констант затрат Планировщика.

work_mem также задействован, он может быть слишком низким и заставить планировщика выполнить seqscan, чтобы убить производительность...