Как мне "мыслить лучше" при чтении плана запросов PostgreSQL?
Сегодня я провел более часа, ломая голову над планом запроса, который я не мог понять. Запрос был UPDATE и это просто не будет работать вообще. Полностью заблокирован: pg_locks показал, что ничего не ждал. Теперь я не считаю себя лучшим или худшим читателем плана запросов, но я считаю, что это исключительно сложно. Мне интересно, как это читать? Есть ли методология, которой руководствуются Pg Aces, чтобы точно определить ошибку?
Я планирую задать еще один вопрос о том, как обойти эту проблему, но сейчас я говорю конкретно о том, как читать эти типы планов.
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:
о, этот план потенциально катастрофичен, проблема с этим планом в том, что он запускает чрезвычайно дорогое хеш-соединение для каждой строки, проблема заключается в оценке rows=1 из другого соединения, и планировщик считает, что можно поместить чрезвычайно дорогой запрос во внутренний путь nestloop, где внешний путь должен возвращать только одну строку. Поскольку, очевидно, по оценке планировщика, дорогая часть будет запущена только один раз, но на практике это имеет тенденцию действительно испортить проблему, проблема в том, что планировщик в идеале верит в свои собственные оценки, планировщик должен знать разницу между "оценочной" вернуть "1 строку" и "невозможно вернуть более 1 строки", но не совсем понятно, как включить это в существующий код
Он продолжает говорить:
это может повлиять на любое объединение, но обычно объединения против подзапросов являются наиболее вероятными
Теперь, когда я прочитал этот план, первое, что я заметил, было Nested Loop Anti Joinэто стоило 169,413 (Я буду придерживаться верхних границ). Это Anti-Join ломается до результата Nested Loop по стоимости 31,738и результат Hash Join по цене 137,424, Теперь 137,424, намного больше, чем 31,738 так что я знал, что проблема в хэш-соединении.
Затем я приступаю к EXPLAIN ANALYZE сегмент Hash Join вне запроса. Выполнено за 7 секунд. Я удостоверился, что были индексы (lot_id, vin) и (co.code и v.code) - были. Я инвалид seq_scan а также hashjoin индивидуально и заметьте увеличение скорости менее чем за 2 секунды. Не достаточно близко, чтобы объяснить, почему он не прогрессировал через час.
Но после всего этого я совершенно не прав! Да, это была более медленная часть запроса, но потому что rows="1" немного (я предполагаю, что это было на Nested Loop Anti Join). Вот это ошибка (отсутствие способностей) в планировщике, неверно оценивающая количество строк? Как я должен читать это, чтобы прийти к такому же выводу RhodiumToad сделал?
Это просто rows="1" что должно заставить меня понять это?
Я бегал VACUUM FULL ANALYZE на всех задействованных таблицах, и это Postgresql 8.4.
2 ответа
Чтобы разобраться с такими проблемами, требуется некоторый опыт, когда все может пойти не так. Но чтобы найти проблемы в планах запросов, попробуйте проверить составленный план изнутри, проверьте, являются ли оценки количества строк нормальными, а оценки затрат соответствуют затраченному времени. Btw. две оценки затрат не являются нижними и верхними границами, во-первых, это предполагаемая стоимость для производства первой строки выходных данных, во втором числе это предполагаемая общая стоимость, подробности см. в документации по объяснению, также имеется некоторая документация по планированию. Это также помогает узнать, как работают разные методы доступа. В качестве отправной точки Википедия имеет информацию о вложенных циклах, хэшах и объединениях слиянием.
В вашем примере вы бы начали с:
-> 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.
Вы анализировали таблицы? А что pg_stats говорит об этих таблицах? План запроса основан на статистике, она должна быть в порядке. А какую версию вы используете? 8,4?
Затраты могут быть рассчитаны с использованием статистики, количества обновлений, количества строк и настроек в postgresql.conf для констант стоимости планировщика.
Также задействован work_mem, он может быть слишком низким и вынудить планировщика выполнить seqscan, чтобы снизить производительность...