Многие, кто уже пользуется explain.tensor.ru — нашим сервисом визуализации планов PostgreSQL, возможно, не в курсе одной из его суперсособностей — превращать сложно читаемый кусок лога сервера…
![](https://habrastorage.org/webt/2y/06/cg/2y06cgmquqmhvuglnua38iup8di.png)
… в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:
![](https://habrastorage.org/webt/bg/pn/mz/bgpnmzdgbnsrrhqwtkbo5vyaat0.png)
В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.
Сначала займемся раскраской — и раскрашивать будем уже не план, его мы уже разукрасили, он у нас уже красивый и понятный, а запрос.
Нам показалось, что вот так неформатированной «простыней» вытащенный из лога запрос выглядит очень уж некрасиво и потому — неудобно.
![](https://habrastorage.org/webt/qu/-q/ix/qu-qixduxjwqsxe0zmpwizgnbze.jpeg)
Особенно, когда разработчики в коде «клеят» тело запроса (это, конечно, антипаттерн, но бывает) в одну строку. Жуть!
Давайте это нарисуем как-то более красиво.
![](https://habrastorage.org/webt/zp/t6/li/zpt6liahcacj3lp56z7zy6b-oru.jpeg)
А если мы сможем это нарисовать красиво, то есть разобрать и собрать обратно тело запроса, то потом сможем и к каждому объекту этого запроса «прицепить» подсказку — что происходило в соответствующей точке плана.
Чтобы это сделать, запрос сначала нужно разобрать.
![](https://habrastorage.org/webt/yj/nv/xl/yjnvxlhgwftw5s896x9qt0x6xyq.jpeg)
Поскольку, у нас ядро системы работает на NodeJS, то мы сделали к нему модулек, можете на GitHub его найти. На самом деле, это является расширенными «биндингами» к внутренностям парсера самого PostgreSQL. То есть просто бинарно скомпилирована грамматика и к ней сделаны биндинги со стороны NodeJS. Мы взяли за основу чужие модули — тут тайны никакой большой нет.
Скармливаем тело запроса на вход нашей функции — на выходе получаем разобранное синтаксическое дерево в виде JSON-объекта.
![](https://habrastorage.org/webt/ms/99/tf/ms99tfq5egaktvbetgis1ygsu5q.jpeg)
Теперь по этому дереву можно пробежаться в обратную сторону и собрать запрос с теми отступами, раскраской, форматированием, которое нам хочется. Нет, это не настраивается, но нам показалось, что именно так будет удобно.
![](https://habrastorage.org/webt/aa/tu/1d/aatu1docsqyuzea73yl7hm6akx4.jpeg)
Теперь посмотрим, как можно совместить план, который мы разобрали на первом шаге, и запрос, который разобрали на втором.
Давайте возьмем простой пример — у нас есть запрос, который формирует CTE и два раза из нее читает. Он генерирует такой план.
![](https://habrastorage.org/webt/qx/f4/hk/qxf4hk_pnntmygjnr9xv1cgxoka.jpeg)
Если на него внимательно посмотреть, что до 12-й версии (или начиная с нее с ключевым словом
![](https://habrastorage.org/webt/o1/hm/zt/o1hmztfly-l_qbtuhsdy3uvvfww.jpeg)
А, значит, если мы видим где-то в запросе генерацию CTE и где-то в плане узел
Задача «со звездочкой»: CTE бывают вложенные.
![](https://habrastorage.org/webt/lb/cj/de/lbcjde_zoqedqr39a2i4g65rpoo.jpeg)
Бывают очень плохо вложенные, и даже одноименные. Например, вы можете внутри
При сопоставлении вы должны это понимать. Понимать это «глазами» — даже видя план, даже видя тело запроса — очень тяжело. Если у вас генерация CTE сложная, вложенная, запросы большие — тогда и вовсе неосознаваемо.
Если у нас в запросе есть ключевое слово
![](https://habrastorage.org/webt/8j/zx/y1/8jzxy1ktxxxdrzf-6_43b-tfamo.jpeg)
То, что «сверху» над
Задача «со звездочкой»: внутри генерации рекурсивной выборки (
Такие примеры тоже надо уметь «расклеивать». Вот в этом примере мы видим, что
![](https://habrastorage.org/webt/c7/g0/vs/c7g0vsb-ejppn38-c0qggdh9olo.jpeg)
Все, разложили, теперь мы знаем, какой кусочек запроса какому кусочку плана соответствует. И в этих кусочках мы можем легко и непринужденно найти те объекты, которые «читаются».
С точки зрения запроса мы не знаем — таблица это или CTE, но обозначаются они одинаковым узлом
Структуру плана и запроса мы знаем, соответствие блоков знаем, имена объектов знаем — делаем однозначное сопоставление.
![](https://habrastorage.org/webt/de/hg/qt/dehgqtaizjxigba8oxfqm6ef8bq.jpeg)
Опять-таки задача «со звездочкой». Берем запрос, выполняем, у нас никаких алиасов нет — мы просто два раза из одной CTE почитали.
![](https://habrastorage.org/webt/2s/sc/f5/2sscf5i-ko_m82o6kf9-jrmsgly.jpeg)
Смотрим в план — что за беда? Почему у нас алиас вылез? Мы его не заказывали. Откуда он такой «номерной»?
PostgreSQL сам его добавляет. Надо просто понимать, что именно такой алиас для нас для целей сопоставления с планом никакого смысла не несет, он просто здесь добавлен. Не будем на него обращать внимания.
Вторая задача «со звездочкой»: если у нас идет чтение из секционированной таблицы, то мы получим узел
![](https://habrastorage.org/webt/pf/9-/wo/pf9-wovkdptgpt0rfqu_onywcf8.jpeg)
Такие узлы мы тоже понимаем, собираем «в одну кучку» и говорим: "все, что ты читал из megatable — это вот тут и вниз по дереву".
![](https://habrastorage.org/webt/ox/sh/tk/oxshtk77ldipqemkglxc8tpgin4.jpeg)
А вот с вложенными запросами все сложнее — к сожалению, они не всегда превращаются в
Опять-таки задача «со звездочкой»: несколько
![](https://habrastorage.org/webt/wy/nw/os/wynwospbnhtbtiu3hkojtfan1o0.jpeg)
Отличить их один от другого помогут «номерные» суффиксы — он добавляется именно в порядке нахождения соответствующих
Вроде все в нашем запросе разобрали — остался только
![](https://habrastorage.org/webt/nu/ub/ya/nuubyaykjfucqmjzl6ry9rgznne.jpeg)
Но тут все просто — такие узлы как
![](https://habrastorage.org/webt/_j/no/8n/_jno8ndayj3fzj68-l2ve0bsd5o.jpeg)
Сложности возникают, когда мы хотим совместить
![](https://habrastorage.org/webt/e2/d8/hy/e2d8hycq-0m5q0erygyxblpw4ce.jpeg)
С точки зрения парсера запроса, у нас есть узел
А с точки зрения плана это два потомка у какого-то
Воспользуемся простой логикой: если у нас есть таблички A и B, которые «джойнятся» между собой в плане, то в запросе они могли быть расположены либо
Возьмем наше синтаксическое дерево, возьмем наш план, посмотрим на них… непохоже!
![](https://habrastorage.org/webt/ey/qo/e5/eyqoe5tmdjvvvwb0_q7wvh3nahm.jpeg)
Перерисуем в виде графов — о, уже стало что-то на что-то похоже!
![](https://habrastorage.org/webt/ac/yk/_y/acyk_ypndyzxbapdyywwuncslk8.jpeg)
Давайте обратим внимание, что у нас есть узлы, у которых одновременно есть дети B и C — нам неважно в каком порядке. Совместим их и перевернем картинку узла.
![](https://habrastorage.org/webt/jt/c8/py/jtc8pywdzkawxfnkblowbk0_b2m.jpeg)
Посмотрим еще раз. Теперь у нас есть узлы с детьми A и пары (B + C) — совместим и их.
![](https://habrastorage.org/webt/bf/1i/rw/bf1irw0kf0pf4ty6rwtu7ypaei4.jpeg)
Отлично! Получается, что мы эти два
Увы, эта задача решается не всегда.
![](https://habrastorage.org/webt/ka/hr/x9/kahrx9rs1fj2_9ql5dvxh6zisvc.jpeg)
Например, если в запросе
Но, в большинстве случаев, почти все узлы удается «развязать» и получить вот такой профайлинг слева по времени — буквально, как в Google Chrome, когда вы код на JavaScript анализируете. Вы видите сколько времени каждая строка и каждый оператор «выполнялись».
![](https://habrastorage.org/webt/jj/8m/lw/jj8mlwjhthj9ydgqioivdbvalna.jpeg)
А чтобы вам всем этим было удобнее пользоваться, мы сделали хранение архива, где вы можете сохранить и потом найти свои планы вместе с ассоциированными запросами или с кем-то поделиться ссылкой.
Если же вам надо просто привести нечитаемый запрос в адекватный вид, воспользуйтесь нашим «нормализатором».
![](https://habrastorage.org/webt/sm/rl/99/smrl99iyjlw_ovdtt2mb_-hudyy.png)
![](https://habrastorage.org/webt/2y/06/cg/2y06cgmquqmhvuglnua38iup8di.png)
… в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:
![](https://habrastorage.org/webt/bg/pn/mz/bgpnmzdgbnsrrhqwtkbo5vyaat0.png)
В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.
С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».
Сначала займемся раскраской — и раскрашивать будем уже не план, его мы уже разукрасили, он у нас уже красивый и понятный, а запрос.
Нам показалось, что вот так неформатированной «простыней» вытащенный из лога запрос выглядит очень уж некрасиво и потому — неудобно.
![](https://habrastorage.org/webt/qu/-q/ix/qu-qixduxjwqsxe0zmpwizgnbze.jpeg)
Особенно, когда разработчики в коде «клеят» тело запроса (это, конечно, антипаттерн, но бывает) в одну строку. Жуть!
Давайте это нарисуем как-то более красиво.
![](https://habrastorage.org/webt/zp/t6/li/zpt6liahcacj3lp56z7zy6b-oru.jpeg)
А если мы сможем это нарисовать красиво, то есть разобрать и собрать обратно тело запроса, то потом сможем и к каждому объекту этого запроса «прицепить» подсказку — что происходило в соответствующей точке плана.
Синтаксическое дерево запроса
Чтобы это сделать, запрос сначала нужно разобрать.
![](https://habrastorage.org/webt/yj/nv/xl/yjnvxlhgwftw5s896x9qt0x6xyq.jpeg)
Поскольку, у нас ядро системы работает на NodeJS, то мы сделали к нему модулек, можете на GitHub его найти. На самом деле, это является расширенными «биндингами» к внутренностям парсера самого PostgreSQL. То есть просто бинарно скомпилирована грамматика и к ней сделаны биндинги со стороны NodeJS. Мы взяли за основу чужие модули — тут тайны никакой большой нет.
Скармливаем тело запроса на вход нашей функции — на выходе получаем разобранное синтаксическое дерево в виде JSON-объекта.
![](https://habrastorage.org/webt/ms/99/tf/ms99tfq5egaktvbetgis1ygsu5q.jpeg)
Теперь по этому дереву можно пробежаться в обратную сторону и собрать запрос с теми отступами, раскраской, форматированием, которое нам хочется. Нет, это не настраивается, но нам показалось, что именно так будет удобно.
![](https://habrastorage.org/webt/aa/tu/1d/aatu1docsqyuzea73yl7hm6akx4.jpeg)
Сопоставление узлов запроса и плана
Теперь посмотрим, как можно совместить план, который мы разобрали на первом шаге, и запрос, который разобрали на втором.
Давайте возьмем простой пример — у нас есть запрос, который формирует CTE и два раза из нее читает. Он генерирует такой план.
![](https://habrastorage.org/webt/qx/f4/hk/qxf4hk_pnntmygjnr9xv1cgxoka.jpeg)
CTE
Если на него внимательно посмотреть, что до 12-й версии (или начиная с нее с ключевым словом
MATERIALIZED
) формирование CTE является безусловным барьером для планировщика.![](https://habrastorage.org/webt/o1/hm/zt/o1hmztfly-l_qbtuhsdy3uvvfww.jpeg)
А, значит, если мы видим где-то в запросе генерацию CTE и где-то в плане узел
CTE
, то эти узлы однозначно между собой «бьются», мы можем сразу же их совместить.Задача «со звездочкой»: CTE бывают вложенные.
![](https://habrastorage.org/webt/lb/cj/de/lbcjde_zoqedqr39a2i4g65rpoo.jpeg)
Бывают очень плохо вложенные, и даже одноименные. Например, вы можете внутри
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
.![](https://habrastorage.org/webt/8j/zx/y1/8jzxy1ktxxxdrzf-6_43b-tfamo.jpeg)
То, что «сверху» над
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
.![](https://habrastorage.org/webt/c7/g0/vs/c7g0vsb-ejppn38-c0qggdh9olo.jpeg)
Чтение-запись данных
Все, разложили, теперь мы знаем, какой кусочек запроса какому кусочку плана соответствует. И в этих кусочках мы можем легко и непринужденно найти те объекты, которые «читаются».
С точки зрения запроса мы не знаем — таблица это или 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]
Структуру плана и запроса мы знаем, соответствие блоков знаем, имена объектов знаем — делаем однозначное сопоставление.
![](https://habrastorage.org/webt/de/hg/qt/dehgqtaizjxigba8oxfqm6ef8bq.jpeg)
Опять-таки задача «со звездочкой». Берем запрос, выполняем, у нас никаких алиасов нет — мы просто два раза из одной CTE почитали.
![](https://habrastorage.org/webt/2s/sc/f5/2sscf5i-ko_m82o6kf9-jrmsgly.jpeg)
Смотрим в план — что за беда? Почему у нас алиас вылез? Мы его не заказывали. Откуда он такой «номерной»?
PostgreSQL сам его добавляет. Надо просто понимать, что именно такой алиас для нас для целей сопоставления с планом никакого смысла не несет, он просто здесь добавлен. Не будем на него обращать внимания.
Вторая задача «со звездочкой»: если у нас идет чтение из секционированной таблицы, то мы получим узел
Append
или Merge Append
, который будет состоять из большого количества «детей», и каждый из которых будет каким-то Scan
'ом из таблицы-секции: Seq Scan
, Bitmap Heap Scan
или Index Scan
. Но, в любом случае, эти «дети» будут не сложными запросами — так эти узлы и можно отличать от Append
при UNION
.![](https://habrastorage.org/webt/pf/9-/wo/pf9-wovkdptgpt0rfqu_onywcf8.jpeg)
Такие узлы мы тоже понимаем, собираем «в одну кучку» и говорим: "все, что ты читал из megatable — это вот тут и вниз по дереву".
«Простые» узлы получения данных
![](https://habrastorage.org/webt/ox/sh/tk/oxshtk77ldipqemkglxc8tpgin4.jpeg)
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
.![](https://habrastorage.org/webt/wy/nw/os/wynwospbnhtbtiu3hkojtfan1o0.jpeg)
Отличить их один от другого помогут «номерные» суффиксы — он добавляется именно в порядке нахождения соответствующих
VALUES
-блоков по ходу запроса сверху вниз.Обработка данных
Вроде все в нашем запросе разобрали — остался только
Limit
.![](https://habrastorage.org/webt/nu/ub/ya/nuubyaykjfucqmjzl6ry9rgznne.jpeg)
Но тут все просто — такие узлы как
Limit
, Sort
, Aggregate
, WindowAgg
, Unique
«мапятся» один-в-один на соответствующие операторы в запросе, если они там есть. Тут никаких «звездочек» и сложностей нет.![](https://habrastorage.org/webt/_j/no/8n/_jno8ndayj3fzj68-l2ve0bsd5o.jpeg)
JOIN
Сложности возникают, когда мы хотим совместить
JOIN
между собой. Это сделать не всегда, но можно.![](https://habrastorage.org/webt/e2/d8/hy/e2d8hycq-0m5q0erygyxblpw4ce.jpeg)
С точки зрения парсера запроса, у нас есть узел
JoinExpr
, у которого ровно два потомка — левый и правый. Это, соответственно, то что «над» вашим JOIN и то что «под» ним в запросе написано.А с точки зрения плана это два потомка у какого-то
* Loop
/* Join
-узла. Nested Loop
, Hash Anti Join
,… — вот что-то такое.Воспользуемся простой логикой: если у нас есть таблички A и B, которые «джойнятся» между собой в плане, то в запросе они могли быть расположены либо
A-JOIN-B
, либо B-JOIN-A
. Попробуем совместить так, попробуем совместить наоборот, и так пока такие пары не кончатся.Возьмем наше синтаксическое дерево, возьмем наш план, посмотрим на них… непохоже!
![](https://habrastorage.org/webt/ey/qo/e5/eyqoe5tmdjvvvwb0_q7wvh3nahm.jpeg)
Перерисуем в виде графов — о, уже стало что-то на что-то похоже!
![](https://habrastorage.org/webt/ac/yk/_y/acyk_ypndyzxbapdyywwuncslk8.jpeg)
Давайте обратим внимание, что у нас есть узлы, у которых одновременно есть дети B и C — нам неважно в каком порядке. Совместим их и перевернем картинку узла.
![](https://habrastorage.org/webt/jt/c8/py/jtc8pywdzkawxfnkblowbk0_b2m.jpeg)
Посмотрим еще раз. Теперь у нас есть узлы с детьми A и пары (B + C) — совместим и их.
![](https://habrastorage.org/webt/bf/1i/rw/bf1irw0kf0pf4ty6rwtu7ypaei4.jpeg)
Отлично! Получается, что мы эти два
JOIN
из запроса с узлами плана удачно совместили.Увы, эта задача решается не всегда.
![](https://habrastorage.org/webt/ka/hr/x9/kahrx9rs1fj2_9ql5dvxh6zisvc.jpeg)
Например, если в запросе
A JOIN B JOIN C
, а в плане в первую очередь соединились «крайние» узлы A и C. А в запросе нет такого оператора, нам нечего подсветить, не к чему привязать подсказку. То же самое с «запятой», когда вы пишете A, B
.Но, в большинстве случаев, почти все узлы удается «развязать» и получить вот такой профайлинг слева по времени — буквально, как в Google Chrome, когда вы код на JavaScript анализируете. Вы видите сколько времени каждая строка и каждый оператор «выполнялись».
![](https://habrastorage.org/webt/jj/8m/lw/jj8mlwjhthj9ydgqioivdbvalna.jpeg)
А чтобы вам всем этим было удобнее пользоваться, мы сделали хранение архива, где вы можете сохранить и потом найти свои планы вместе с ассоциированными запросами или с кем-то поделиться ссылкой.
Если же вам надо просто привести нечитаемый запрос в адекватный вид, воспользуйтесь нашим «нормализатором».
![](https://habrastorage.org/webt/sm/rl/99/smrl99iyjlw_ovdtt2mb_-hudyy.png)