Багато хто вже користується — нашим сервісом візуалізації планів PostgreSQL, можливо, не в курсі однієї з його суперздібностей — перетворювати шматок лога сервера, що складно читається…
… у красиво оформлений запит з контекстними підказками щодо відповідних вузлів плану:
У цій розшифровці другої частини свого я розповім, як нам удалося це зробити.
З транскриптом першої частини, присвяченої типовим проблемам продуктивності запитів та їх вирішенням, можна ознайомитись у статті .
Спочатку займемося розфарбуванням — і будемо розфарбовувати вже не план, його ми вже прикрасили, він у нас уже гарний і зрозумілий, а запит.
Нам здалося, що ось так неформатованим «простирадлом» витягнутий з лігва запит виглядає дуже негарно і тому — незручно.
Особливо коли розробники в коді «клеять» тіло запиту (це, звичайно, антипаттерн, але буває) в один рядок. Жах!
Давайте це намалюємо якось красивіше.
А якщо ми зможемо це намалювати красиво, тобто розібрати і зібрати назад тіло запиту, то потім зможемо і до кожного об'єкта цього запиту «причепити» підказку, що відбувалося у відповідній точці плану.
Синтаксичне дерево запиту
Щоб це зробити, спочатку потрібно розібрати запит.
Оскільки у нас , то ми зробили до нього модулі, можете . Насправді це є розширеними «біндингами» до нутрощів парсера самого PostgreSQL. Тобто просто бінарно скомпільована граматика і до неї зроблені біндінги з боку NodeJS. Ми взяли за основу чужі модулі — тут ніякої таємниці немає.
Згодовуємо тіло запиту на вхід нашої функції - на виході отримуємо розібране синтаксичне дерево у вигляді об'єкта JSON.
Тепер цим деревом можна пробігтися у зворотний бік і зібрати запит з тими відступами, розфарбуванням, форматуванням, яке нам хочеться. Ні, це не налаштовується, але нам здалося, що саме так буде зручно.
Зіставлення вузлів запиту та плану
Тепер подивимося, як можна поєднати план, який ми розібрали на першому кроці та запит, який розібрали на другому.
Давайте візьмемо простий приклад — ми маємо запит, який формує CTE і двічі з неї читає. Він генерує такий план.
КТР
Якщо на нього уважно подивитись, що до 12-ї версії (або починаючи з неї з ключовим словом MATERIALIZED) формування .
Отже, якщо ми бачимо десь у запиті генерацію 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 [ALL] (Оператор з'єднання двох вибірок), то йому в плані відповідає або вузол Append, або який-небудь Recursive Union.
Те, що «згори» над UNION — це перший нащадок нашого вузла, що знизу — другий. Якщо через UNION у нас «поклеєно» кілька блоків одразу, то Append-Вузол все одно буде тільки один, а ось дітей у нього буде не два, а багато - по порядку як вони йдуть, відповідно:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #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 = 1Result (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 між собою. Це зробити не завжди, але можна.
З погляду парсера запиту, у нас є вузол 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