🥇PostgreSQL Query Profiler: Wéi ee Plang an eng Query zesummebréngt

Vill déi schonn benotzen erklären.tensor.ru - eise PostgreSQL Plangvisualiséierungsservice ass vläicht net bewosst vun enger vu senge Supermuechten - dréit e schwéier liesbare Stéck vum Serverlog ...

... an eng wonnerschéin entworf Ufro mat kontextuellen Hiweiser fir déi entspriechend Plannoden:

An dësem Transkript vum zweeten Deel vu sengem Bericht bei PGConf.Russia 2020 Ech soen Iech wéi mir dat fäerdeg bruecht hunn.

Den Transkript vum éischten Deel, gewidmet fir typesch Query Performance Problemer an hir Léisungen, kann am Artikel fonnt ginn "Rezepter fir schlëmm SQL Ufroen".

Als éischt, loosst eis ufänken ze faarwen - a mir wäerten de Plang net méi faarwen, mir hunn et scho faarweg, mir hunn et scho schéin a verständlech, awer eng Ufro.

Et huet eis geschéngt datt mat sou engem onformatéierte "Blat" d'Ufro aus dem Logbuch ganz ellen ausgesäit an dofir onbequem.

Besonnesch wann d'Entwéckler de Kierper vun der Ufro am Code "gekollt" (dëst ass natierlech en Antimuster, awer et geschitt) an enger Linn. Schrecklech!

Loosst eis dat iergendwéi méi schéin zéien.

A wa mir dëst schéin zéien kënnen, dat heescht, de Kierper vun der Ufro ze disassemble an erëm zesummesetzen, da kënne mir dann en Hiweis un all Objet vun dëser Ufro "befestigen" - wat geschitt um entspriechende Punkt am Plang.

Ufro Syntax Bam

Fir dëst ze maachen, muss d'Ufro fir d'éischt parséiert ginn.

Well mir hunn de Kär vum System leeft op NodeJS, dann hu mir e Modul dofir gemaach, Dir kënnt fannen et op GitHub. Tatsächlech sinn dës verlängert "Bindungen" un d'Innere vum PostgreSQL Parser selwer. Dat ass, d'Grammatik ass einfach binär kompiléiert a Bindunge ginn dermat aus NodeJS gemaach. Mir hunn d'Moduler vun anere Leit als Basis geholl - et gëtt kee grousse Geheimnis hei.

Mir fidderen de Kierper vun der Ufro als Input fir eis Funktioun - beim Ausgang kréie mir e parséierte Syntaxbaum a Form vun engem JSON Objet.

Elo kënne mir duerch dëse Bam an déi entgéintgesate Richtung lafen an eng Ufro zesummestellen mat den Abriecher, Faarwen a Formatéierung déi mir wëllen. Nee, dëst ass net personaliséierbar, awer et huet eis geschéngt datt dëst bequem wier.

Mapping Ufro a Plannoden

Loosst eis elo kucken wéi mir de Plang kënne kombinéieren, dee mir am éischte Schrëtt analyséiert hunn an d'Ufro, déi mir an der zweeter analyséiert hunn.

Loosst eis en einfacht Beispill huelen - mir hunn eng Ufro déi e CTE generéiert an zweemol dovun liest. Hien generéiert esou e Plang.

Côte

Wann Dir et suergfälteg kuckt, bis op d'Versioun 12 (oder ufänkt dovun aus mam Schlësselwuert MATERIALIZED) Formatioun CTE ass eng absolut Barrière fir de Planer.

Dëst bedeit datt wa mir eng CTE Generatioun iergendwou an der Ufro an en Node iergendwou am Plang gesinn CTE, dann "kämpfen" dës Noden definitiv mateneen, mir kënnen se direkt kombinéieren.

Problem mat engem Stär: CTEs kënnen nestéiert ginn.

Et gi ganz schlecht nestéiert, a souguer déi mam selwechten Numm. Zum Beispill, kënnt Dir bannen CTE A ze maachen CTE X, an um selwechten Niveau bannen CTE B maachen et erëm CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Wann Dir vergläicht, musst Dir dëst verstoen. Dëst "mat Ären Aen" ze verstoen - och de Plang ze gesinn, souguer de Kierper vun der Ufro ze gesinn - ass ganz schwéier. Wann Är CTE Generatioun komplex, nestéiert ass, an et gi grouss Ufroe, dann ass et komplett onbewosst.

UNIOUN

Wa mir e Schlësselwuert an der Ufro hunn UNION [ALL] (Bedreiwer vun zwee Echantillon verbannen), dann am Plang entsprécht entweder engem Node Append, oder puer Recursive Union.

Dat wat "uewen" uewen ass UNION - dëst ass den éischten Nofolger vun eisem Node, deen "ënnert" ass - déi zweet. Wann duerch UNION mir hunn e puer Blöcke "gepecht" op eemol, dann Append-et gëtt nach ëmmer nëmmen een Node, awer et wäert net zwee, awer vill Kanner hunn - an der Uerdnung, respektiv:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Problem mat engem Stär: bannent rekursive Sampling Generatioun (WITH RECURSIVE) kann och méi wéi een sinn UNION. Awer nëmmen dee ganz leschte Block nom leschte Block ass ëmmer rekursiv UNION. Alles uewen ass een, awer anescht UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Dir musst och fäeg sinn esou Beispiller "auszehalen". An dësem Beispill gesi mir dat UNION-et waren 3 Segmenter an eiser Demande. Deementspriechend eng UNION entsprécht Append-Node, an zum aneren - Recursive Union.

Liesen-schreiwen Daten

Alles ass ugeluecht, elo wësse mer wéi ee Stéck vun der Demande zu deem Stéck vum Plang entsprécht. An an dëse Stécker fanne mer einfach an natierlech déi Objeten déi "liesbar" sinn.

Aus enger Ufro Siicht wësse mir net ob et en Dësch oder e CTE ass, awer si gi vum selwechte Node bezeechent RangeVar. A wat "Liesbarkeet" ugeet, ass dëst och e relativ limitéierten Set vu Wirbelen:

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]

Mir kennen d'Struktur vum Plang an d'Ufro, mir kennen d'Korrespondenz vun de Blocken, mir kennen d'Nimm vun den Objeten - mir maachen en een-zu-eent Verglach.

Nach eng Kéier Aufgab "mat engem Stern". Mir huelen d'Ufro, maachen se aus, mir hu keng Aliasen - mir liesen et just zweemol vum selwechte CTE.

Mir kucken de Plang - wat ass de Problem? Firwat hu mir en Alias? Mir hunn et net bestallt. Wou kritt hien esou eng "Nummernummer"?

PostgreSQL füügt et selwer derbäi. Dir musst just dat verstoen just esou en Alias fir eis, am Verglach zum Plang, mécht et kee Sënn, et gëtt einfach hei bäigefüügt. Loosst eis net op hien oppassen.

Déi zweet Aufgab "mat engem Stern": wa mir aus engem opgedeelt Dësch liesen, da wäerte mir en Node kréien Append oder Merge Append, déi aus enger grousser Zuel vu "Kanner" besteet, a jidderee wäert iergendwéi sinn Scan'om aus der Sektiounstabell: Seq Scan, Bitmap Heap Scan oder Index Scan. Awer op alle Fall wäerten dës "Kanner" keng komplex Ufroe sinn - sou kënnen dës Wirbelen ënnerscheeden Append at UNION.

Mir verstinn och esou Kniet, sammelen se "an engem Koup" a soen: "alles wat Dir aus Megatable liest ass hei an de Bam erof".

"Einfach" Daten empfaangen Noden

Values Scan entsprécht am Plang VALUES an der Demande.

Result ass eng Demande ouni FROM Zort SELECT 1. Oder wann Dir e bewosst falschen Ausdrock hutt WHERE-block (da erschéngt den Attribut 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 "Kaart" op d'SRFs mam selwechten Numm.

Awer mat nestet Ufroen ass alles méi komplizéiert - leider ginn se net ëmmer an InitPlan/SubPlan. Heiansdo ginn se an ... Join oder ... Anti Join, besonnesch wann Dir eppes schreift wéi WHERE NOT EXISTS .... An hei ass et net ëmmer méiglech, se ze kombinéieren - am Text vum Plang gëtt et keng Betreiber, déi den Noden vum Plang entspriechen.

Nach eng Kéier Aufgab "mat engem Stern": puer VALUES an der Demande. An dësem Fall an am Plang kritt Dir e puer Noden Values Scan.

"Nummeréiert" Suffixen hëllefen se vuneneen z'ënnerscheeden - si gi genee an der Rei bäigefüügt an där déi entspriechend fonnt ginn VALUES-blockéiert laanscht d'Ufro vun uewe bis ënnen.

Donnéeën Veraarbechtung

Et schéngt wéi wann alles an eiser Ufro ausgeriicht ass - alles wat iwwreg ass Limit.

Awer hei ass alles einfach - sou Wirbelen wéi Limit, Sort, Aggregate, WindowAgg, Unique "Kaart" eent-zu-eent op déi entspriechend Opérateuren an der Demande, wa se do sinn. Et gi keng "Stären" oder Schwieregkeeten hei.

JOIN

Schwieregkeeten entstinn wa mir wëllen kombinéieren JOIN tëscht sech. Dëst ass net ëmmer méiglech, awer et ass méiglech.

Aus der Siicht vum Query Parser hu mir en Node JoinExpr, déi genee zwee Kanner huet - lénks a riets. Dëst ass deementspriechend wat "iwwer" Ärem JOIN ass a wat "ënnert" et an der Ufro geschriwwen ass.

An aus der Siicht vum Plang sinn dat zwee Nokommen vun e puer * Loop/* Join-node. Nested Loop, Hash Anti Join,... - eppes wei dat.

Loosst eis einfach Logik benotzen: wa mir Dëscher A a B hunn, déi sech am Plang "matmaachen", dann an der Ufro kënnen se entweder lokaliséiert ginn A-JOIN-B, oder B-JOIN-A. Loosst eis probéieren dës Manéier ze kombinéieren, loosst eis probéieren ëmgedréint ze kombinéieren, a sou weider bis mir aus esou Pairen sinn.

Loosst eis eise Syntaxbam huelen, eise Plang huelen, se kucken ... net ähnlech!

Loosst eis et a Form vu Grafiken nei zéien - oh, et gesäit schonn no eppes aus!

Loosst eis feststellen datt mir Noden hunn déi gläichzäiteg Kanner B an C hunn - mir egal a wéi enger Reiefolleg. Loosst eis se kombinéieren an d'Bild vum Node ëmdréinen.

Loosst eis nach eng Kéier kucken. Elo hu mir Node mat Kanner A a Pairen (B + C) - och mat hinnen kompatibel.

Super! Et stellt sech eraus datt mir dës zwee sinn JOIN aus der Demande mat de Plang Wirbelen erfollegräich kombinéiert.

Leider ass dëse Problem net ëmmer geléist.

Zum Beispill, wann an enger Demande A JOIN B JOIN C, an am Plang, fir d'éischt waren d'"äusseren" Noden A a C verbonnen. Et ass d'selwecht mam "Komma" wann Dir schreift A, B.

Awer, an deene meeschte Fäll, kënne bal all Node "ontkoppelt" ginn an Dir kënnt dës Aart vu Profiléierung op der lénker Zäit kréien - wuertwiertlech, wéi am Google Chrome wann Dir JavaScript Code analyséiert. Dir kënnt gesinn wéi laang all Zeil an all Ausso gedauert huet fir ze "ausféieren".

A fir et méi bequem ze maachen fir Iech all dëst ze benotzen, hu mir Lagerung gemaach archiv, wou Dir kënnt späicheren a spéider Är Pläng zesumme mat verbonnen Ufroe fannen oder de Link mat engem deelen.