[PostgreSQL, SQL, Администрирование баз данных, Визуализация данных] PostgreSQL Query Profiler: как сопоставить план и запрос
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Многие, кто уже пользуется explain.tensor.ru — нашим сервисом визуализации планов PostgreSQL, возможно, не в курсе одной из его суперсособностей — превращать сложно читаемый кусок лога сервера…
… в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:
В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.
С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».
Извините, данный ресурс не поддреживается. :(
Сначала займемся раскраской — и раскрашивать будем уже не план, его мы уже разукрасили, он у нас уже красивый и понятный, а запрос.
Нам показалось, что вот так неформатированной «простыней» вытащенный из лога запрос выглядит очень уж некрасиво и потому — неудобно.
Особенно, когда разработчики в коде «клеят» тело запроса (это, конечно, антипаттерн, но бывает) в одну строку. Жуть!
Давайте это нарисуем как-то более красиво.
А если мы сможем это нарисовать красиво, то есть разобрать и собрать обратно тело запроса, то потом сможем и к каждому объекту этого запроса «прицепить» подсказку — что происходило в соответствующей точке плана.
Синтаксическое дерево запроса
Чтобы это сделать, запрос сначала нужно разобрать.
Поскольку, у нас ядро системы работает на NodeJS, то мы сделали к нему модулек, можете на GitHub его найти. На самом деле, это является расширенными «биндингами» к внутренностям парсера самого PostgreSQL. То есть просто бинарно скомпилирована грамматика и к ней сделаны биндинги со стороны NodeJS. Мы взяли за основу чужие модули — тут тайны никакой большой нет.
Скармливаем тело запроса на вход нашей функции — на выходе получаем разобранное синтаксическое дерево в виде JSON-объекта.
Теперь по этому дереву можно пробежаться в обратную сторону и собрать запрос с теми отступами, раскраской, форматированием, которое нам хочется. Нет, это не настраивается, но нам показалось, что именно так будет удобно.
Сопоставление узлов запроса и плана
Теперь посмотрим, как можно совместить план, который мы разобрали на первом шаге, и запрос, который разобрали на втором.
Давайте возьмем простой пример — у нас есть запрос, который формирует CTE и два раза из нее читает. Он генерирует такой план.
CTE
Если на него внимательно посмотреть, что до 12-й версии (или начиная с нее с ключевым словом MATERIALIZED) формирование CTE является безусловным барьером для планировщика.
А, значит, если мы видим где-то в запросе генерацию CTE и где-то в плане узел CTE, то эти узлы однозначно между собой «бьются», мы можем сразу же их совместить.
Задача «со звездочкой»: CTE бывают вложенные.
Бывают очень плохо вложенные, и даже одноименные. Например, вы можете внутри CTE A сделать CTE X, и на том же уровне внутри CTE B сделать опять CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...
При сопоставлении вы должны это понимать. Понимать это «глазами» — даже видя план, даже видя тело запроса — очень тяжело. Если у вас генерация CTE сложная, вложенная, запросы большие — тогда и вовсе неосознаваемо.
UNION
Если у нас в запросе есть ключевое слово UNION [ALL] (оператор соединения двух выборок), то ему в плане соответствует либо узел Append, либо какой-нибудь Recursive Union.
То, что «сверху» над UNION — это первый потомок нашего узла, что «снизу» — второй. Если через UNION у нас «поклеено» несколько блоков сразу, то Append-узел все равно будет только один, а вот детей у него будет не два, а много — по порядку как они идут, соответственно:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3
Append
-> ... #1
-> ... #2
-> ... #3
Задача «со звездочкой»: внутри генерации рекурсивной выборки (WITH RECURSIVE) тоже может быть больше одного UNION. Но всегда рекурсивным является только самый последний блок после последнего UNION. Все, что выше — это один, но другой UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Такие примеры тоже надо уметь «расклеивать». Вот в этом примере мы видим, что UNION-сегментов в нашем запросе было 3 штуки. Соответственно, одному UNION соответствует Append-узел, а другому — Recursive Union.
Чтение-запись данных
Все, разложили, теперь мы знаем, какой кусочек запроса какому кусочку плана соответствует. И в этих кусочках мы можем легко и непринужденно найти те объекты, которые «читаются».
С точки зрения запроса мы не знаем — таблица это или CTE, но обозначаются они одинаковым узлом RangeVar. А в плане «читается» — это тоже достаточно ограниченный набор узлов:
- Seq Scan on [tbl]
- Bitmap Heap Scan on [tbl]
- Index [Only] Scan [Backward] using [idx] on [tbl]
- CTE Scan on [cte]
- Insert/Update/Delete on [tbl]
Структуру плана и запроса мы знаем, соответствие блоков знаем, имена объектов знаем — делаем однозначное сопоставление.
Опять-таки задача «со звездочкой». Берем запрос, выполняем, у нас никаких алиасов нет — мы просто два раза из одной CTE почитали.
Смотрим в план — что за беда? Почему у нас алиас вылез? Мы его не заказывали. Откуда он такой «номерной»?
PostgreSQL сам его добавляет. Надо просто понимать, что именно такой алиас для нас для целей сопоставления с планом никакого смысла не несет, он просто здесь добавлен. Не будем на него обращать внимания.
Вторая задача «со звездочкой»: если у нас идет чтение из секционированной таблицы, то мы получим узел Append или Merge Append, который будет состоять из большого количества «детей», и каждый из которых будет каким-то Scan'ом из таблицы-секции: Seq Scan, Bitmap Heap Scan или Index Scan. Но, в любом случае, эти «дети» будут не сложными запросами — так эти узлы и можно отличать от Append при UNION.
Такие узлы мы тоже понимаем, собираем «в одну кучку» и говорим: "все, что ты читал из megatable — это вот тут и вниз по дереву".
«Простые» узлы получения данных
Values Scan в плане соответствует VALUES в запросе.
Result — это запрос без FROM вроде SELECT 1. Или когда у вас заведомо ложное выражение в WHERE-блоке (тогда возникает атрибут One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1
Result (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
One-Time Filter: false
Function Scan «мапятся» на одноименные SRF.
А вот с вложенными запросами все сложнее — к сожалению, они не всегда превращаются в InitPlan/SubPlan. Иногда они превращаются в ... Join или ... Anti Join, особенно когда вы пишете что-то вроде WHERE NOT EXISTS .... И вот там совмещать не всегда получается — в тексте плана соответствующих узлам плана операторов нет.
Опять-таки задача «со звездочкой»: несколько VALUES в запросе. В этом случае и в плане вы получите несколько узлов Values Scan.
Отличить их один от другого помогут «номерные» суффиксы — он добавляется именно в порядке нахождения соответствующих VALUES-блоков по ходу запроса сверху вниз.
Обработка данных
Вроде все в нашем запросе разобрали — остался только Limit.
Но тут все просто — такие узлы как Limit, Sort, Aggregate, WindowAgg, Unique «мапятся» один-в-один на соответствующие операторы в запросе, если они там есть. Тут никаких «звездочек» и сложностей нет.
JOIN
Сложности возникают, когда мы хотим совместить JOIN между собой. Это сделать не всегда, но можно.
С точки зрения парсера запроса, у нас есть узел JoinExpr, у которого ровно два потомка — левый и правый. Это, соответственно, то что «над» вашим JOIN и то что «под» ним в запросе написано.
А с точки зрения плана это два потомка у какого-то * Loop/* Join-узла. Nested Loop, Hash Anti Join,… — вот что-то такое.
Воспользуемся простой логикой: если у нас есть таблички A и B, которые «джойнятся» между собой в плане, то в запросе они могли быть расположены либо A-JOIN-B, либо B-JOIN-A. Попробуем совместить так, попробуем совместить наоборот, и так пока такие пары не кончатся.
Возьмем наше синтаксическое дерево, возьмем наш план, посмотрим на них… непохоже!
Перерисуем в виде графов — о, уже стало что-то на что-то похоже!
Давайте обратим внимание, что у нас есть узлы, у которых одновременно есть дети B и C — нам неважно в каком порядке. Совместим их и перевернем картинку узла.
Посмотрим еще раз. Теперь у нас есть узлы с детьми A и пары (B + C) — совместим и их.
Отлично! Получается, что мы эти два JOIN из запроса с узлами плана удачно совместили.
Увы, эта задача решается не всегда.
Например, если в запросе A JOIN B JOIN C, а в плане в первую очередь соединились «крайние» узлы A и C. А в запросе нет такого оператора, нам нечего подсветить, не к чему привязать подсказку. То же самое с «запятой», когда вы пишете A, B.
Но, в большинстве случаев, почти все узлы удается «развязать» и получить вот такой профайлинг слева по времени — буквально, как в Google Chrome, когда вы код на JavaScript анализируете. Вы видите сколько времени каждая строка и каждый оператор «выполнялись».
А чтобы вам всем этим было удобнее пользоваться, мы сделали хранение архива, где вы можете сохранить и потом найти свои планы вместе с ассоциированными запросами или с кем-то поделиться ссылкой.
Если же вам надо просто привести нечитаемый запрос в адекватный вид, воспользуйтесь нашим «нормализатором».
===========
Источник:
habr.com
===========
Похожие новости:
- [Java, NoSQL] Как Spring Data работает с Redis
- [IT-инфраструктура, NoSQL, Серверное администрирование] Дружим ELK и Exchange. Часть 1
- [JavaScript, TypeScript, Визуализация данных] How to display a gazillion of metrics and keep your sanity
- [Алгоритмы, Высокая производительность, Математика, Программирование] Динамическая балансировка нагрузки в pull-схеме
- [Angular, Open source, Rust, Визуализация данных, Отладка] Обновления в Chipmunk
- [Oracle, Администрирование баз данных] AWR: насколько «экзадатится» работа базы данных?
- [SQL, Финансы в IT] Скулятчер
- [Data Mining, Визуализация данных, Data Engineering] Август-2020 в Беларуси с точки зрения данных
- [Java, ERP-системы, DIY или Сделай сам] Складская программа C2 WMS 1.1
- [Python, Big Data, Визуализация данных] Tableau Hyper API – BI-команда скажет вам спасибо
Теги для поиска: #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_vizualizatsija_dannyh (Визуализация данных), #_postgresql, #_dba, #_explain.tensor.ru, #_profajling (профайлинг), #_sql, #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
), #_vizualizatsija_dannyh (
Визуализация данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 02:03
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Многие, кто уже пользуется explain.tensor.ru — нашим сервисом визуализации планов PostgreSQL, возможно, не в курсе одной из его суперсособностей — превращать сложно читаемый кусок лога сервера…  … в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:  В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать. С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».
Извините, данный ресурс не поддреживается. :( Сначала займемся раскраской — и раскрашивать будем уже не план, его мы уже разукрасили, он у нас уже красивый и понятный, а запрос. Нам показалось, что вот так неформатированной «простыней» вытащенный из лога запрос выглядит очень уж некрасиво и потому — неудобно.  Особенно, когда разработчики в коде «клеят» тело запроса (это, конечно, антипаттерн, но бывает) в одну строку. Жуть! Давайте это нарисуем как-то более красиво.  А если мы сможем это нарисовать красиво, то есть разобрать и собрать обратно тело запроса, то потом сможем и к каждому объекту этого запроса «прицепить» подсказку — что происходило в соответствующей точке плана. Синтаксическое дерево запроса Чтобы это сделать, запрос сначала нужно разобрать.  Поскольку, у нас ядро системы работает на NodeJS, то мы сделали к нему модулек, можете на GitHub его найти. На самом деле, это является расширенными «биндингами» к внутренностям парсера самого PostgreSQL. То есть просто бинарно скомпилирована грамматика и к ней сделаны биндинги со стороны NodeJS. Мы взяли за основу чужие модули — тут тайны никакой большой нет. Скармливаем тело запроса на вход нашей функции — на выходе получаем разобранное синтаксическое дерево в виде JSON-объекта.  Теперь по этому дереву можно пробежаться в обратную сторону и собрать запрос с теми отступами, раскраской, форматированием, которое нам хочется. Нет, это не настраивается, но нам показалось, что именно так будет удобно.  Сопоставление узлов запроса и плана Теперь посмотрим, как можно совместить план, который мы разобрали на первом шаге, и запрос, который разобрали на втором. Давайте возьмем простой пример — у нас есть запрос, который формирует CTE и два раза из нее читает. Он генерирует такой план.  CTE Если на него внимательно посмотреть, что до 12-й версии (или начиная с нее с ключевым словом MATERIALIZED) формирование CTE является безусловным барьером для планировщика.  А, значит, если мы видим где-то в запросе генерацию CTE и где-то в плане узел CTE, то эти узлы однозначно между собой «бьются», мы можем сразу же их совместить. Задача «со звездочкой»: CTE бывают вложенные.  Бывают очень плохо вложенные, и даже одноименные. Например, вы можете внутри CTE A сделать CTE X, и на том же уровне внутри CTE B сделать опять CTE X: WITH A AS (
WITH X AS (...) SELECT ... ) , B AS ( WITH X AS (...) SELECT ... ) ... При сопоставлении вы должны это понимать. Понимать это «глазами» — даже видя план, даже видя тело запроса — очень тяжело. Если у вас генерация CTE сложная, вложенная, запросы большие — тогда и вовсе неосознаваемо. UNION Если у нас в запросе есть ключевое слово UNION [ALL] (оператор соединения двух выборок), то ему в плане соответствует либо узел Append, либо какой-нибудь Recursive Union.  То, что «сверху» над UNION — это первый потомок нашего узла, что «снизу» — второй. Если через UNION у нас «поклеено» несколько блоков сразу, то Append-узел все равно будет только один, а вот детей у него будет не два, а много — по порядку как они идут, соответственно: (...) -- #1
UNION ALL (...) -- #2 UNION ALL (...) -- #3 Append
-> ... #1 -> ... #2 -> ... #3 Задача «со звездочкой»: внутри генерации рекурсивной выборки (WITH RECURSIVE) тоже может быть больше одного UNION. Но всегда рекурсивным является только самый последний блок после последнего UNION. Все, что выше — это один, но другой UNION: WITH RECURSIVE T AS(
(...) -- #1 UNION ALL (...) -- #2, тут кончается генерация стартового состояния рекурсии UNION ALL (...) -- #3, только этот блок рекурсивный и может содержать обращение к T ) ... Такие примеры тоже надо уметь «расклеивать». Вот в этом примере мы видим, что UNION-сегментов в нашем запросе было 3 штуки. Соответственно, одному UNION соответствует Append-узел, а другому — Recursive Union.  Чтение-запись данных Все, разложили, теперь мы знаем, какой кусочек запроса какому кусочку плана соответствует. И в этих кусочках мы можем легко и непринужденно найти те объекты, которые «читаются». С точки зрения запроса мы не знаем — таблица это или CTE, но обозначаются они одинаковым узлом RangeVar. А в плане «читается» — это тоже достаточно ограниченный набор узлов:
Структуру плана и запроса мы знаем, соответствие блоков знаем, имена объектов знаем — делаем однозначное сопоставление.  Опять-таки задача «со звездочкой». Берем запрос, выполняем, у нас никаких алиасов нет — мы просто два раза из одной CTE почитали.  Смотрим в план — что за беда? Почему у нас алиас вылез? Мы его не заказывали. Откуда он такой «номерной»? PostgreSQL сам его добавляет. Надо просто понимать, что именно такой алиас для нас для целей сопоставления с планом никакого смысла не несет, он просто здесь добавлен. Не будем на него обращать внимания. Вторая задача «со звездочкой»: если у нас идет чтение из секционированной таблицы, то мы получим узел Append или Merge Append, который будет состоять из большого количества «детей», и каждый из которых будет каким-то Scan'ом из таблицы-секции: Seq Scan, Bitmap Heap Scan или Index Scan. Но, в любом случае, эти «дети» будут не сложными запросами — так эти узлы и можно отличать от Append при UNION.  Такие узлы мы тоже понимаем, собираем «в одну кучку» и говорим: "все, что ты читал из megatable — это вот тут и вниз по дереву". «Простые» узлы получения данных  Values Scan в плане соответствует VALUES в запросе. Result — это запрос без FROM вроде SELECT 1. Или когда у вас заведомо ложное выражение в WHERE-блоке (тогда возникает атрибут One-Time Filter): EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1 Result (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
One-Time Filter: false Function Scan «мапятся» на одноименные SRF. А вот с вложенными запросами все сложнее — к сожалению, они не всегда превращаются в InitPlan/SubPlan. Иногда они превращаются в ... Join или ... Anti Join, особенно когда вы пишете что-то вроде WHERE NOT EXISTS .... И вот там совмещать не всегда получается — в тексте плана соответствующих узлам плана операторов нет. Опять-таки задача «со звездочкой»: несколько VALUES в запросе. В этом случае и в плане вы получите несколько узлов Values Scan.  Отличить их один от другого помогут «номерные» суффиксы — он добавляется именно в порядке нахождения соответствующих VALUES-блоков по ходу запроса сверху вниз. Обработка данных Вроде все в нашем запросе разобрали — остался только Limit.  Но тут все просто — такие узлы как Limit, Sort, Aggregate, WindowAgg, Unique «мапятся» один-в-один на соответствующие операторы в запросе, если они там есть. Тут никаких «звездочек» и сложностей нет.  JOIN Сложности возникают, когда мы хотим совместить JOIN между собой. Это сделать не всегда, но можно.  С точки зрения парсера запроса, у нас есть узел JoinExpr, у которого ровно два потомка — левый и правый. Это, соответственно, то что «над» вашим JOIN и то что «под» ним в запросе написано. А с точки зрения плана это два потомка у какого-то * Loop/* Join-узла. Nested Loop, Hash Anti Join,… — вот что-то такое. Воспользуемся простой логикой: если у нас есть таблички A и B, которые «джойнятся» между собой в плане, то в запросе они могли быть расположены либо A-JOIN-B, либо B-JOIN-A. Попробуем совместить так, попробуем совместить наоборот, и так пока такие пары не кончатся. Возьмем наше синтаксическое дерево, возьмем наш план, посмотрим на них… непохоже!  Перерисуем в виде графов — о, уже стало что-то на что-то похоже!  Давайте обратим внимание, что у нас есть узлы, у которых одновременно есть дети B и C — нам неважно в каком порядке. Совместим их и перевернем картинку узла.  Посмотрим еще раз. Теперь у нас есть узлы с детьми A и пары (B + C) — совместим и их.  Отлично! Получается, что мы эти два JOIN из запроса с узлами плана удачно совместили. Увы, эта задача решается не всегда.  Например, если в запросе A JOIN B JOIN C, а в плане в первую очередь соединились «крайние» узлы A и C. А в запросе нет такого оператора, нам нечего подсветить, не к чему привязать подсказку. То же самое с «запятой», когда вы пишете A, B. Но, в большинстве случаев, почти все узлы удается «развязать» и получить вот такой профайлинг слева по времени — буквально, как в Google Chrome, когда вы код на JavaScript анализируете. Вы видите сколько времени каждая строка и каждый оператор «выполнялись».  А чтобы вам всем этим было удобнее пользоваться, мы сделали хранение архива, где вы можете сохранить и потом найти свои планы вместе с ассоциированными запросами или с кем-то поделиться ссылкой. Если же вам надо просто привести нечитаемый запрос в адекватный вид, воспользуйтесь нашим «нормализатором».  =========== Источник: habr.com =========== Похожие новости:
Блог компании Тензор ), #_postgresql, #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ), #_vizualizatsija_dannyh ( Визуализация данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 02:03
Часовой пояс: UTC + 5