Многія, хто ўжо карыстаецца - Нашым сэрвісам візуалізацыі планаў 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