Sokan, akik már használják - PostgreSQL-terv-vizualizációs szolgáltatásunk esetleg nem ismeri egyik szuperképességét - a szervernapló egy nehezen olvasható darabjának megfordítása...
... egy gyönyörűen megtervezett lekérdezésbe a megfelelő tervcsomópontokhoz kontextuális tippekkel:
Az ő második részének ebben az átiratában Elmondom, hogyan sikerült ezt megtennünk.
Az első rész átirata, amely a tipikus lekérdezési teljesítményproblémákkal és azok megoldásaival foglalkozik, megtalálható a cikkben .
Először is kezdjük el a színezést - és már nem színezzük ki a tervet, már kiszíneztük, már van szép és érthető, de kérés.
Számunkra úgy tűnt, hogy egy ilyen formázatlan „lappal” a rönkből kihúzott kérés nagyon csúnyán néz ki, és ezért kényelmetlen.
Főleg, ha a fejlesztők egy sorba „ragasztják” a kérés törzsét a kódba (ez persze egy antiminta, de előfordul). Szörnyű!
Rajzoljuk meg ezt valahogy szebben.
És ha ezt szépen meg tudjuk rajzolni, vagyis szétszedjük és újra összerakjuk a kérés törzsét, akkor ennek a kérésnek minden tárgyához „csatolhatunk” egy utalást – ami a terv megfelelő pontján történt.
Lekérdezés szintaktikai fa
Ehhez először elemezni kell a kérést.
Mert van , akkor készítettünk hozzá egy modult, megteheti . Valójában ezek kiterjesztett „kötések” magának a PostgreSQL elemzőnek a belső elemeihez. Vagyis a nyelvtan egyszerűen binárisan van lefordítva, és a NodeJS-ből kötések készülnek hozzá. Mások moduljait vettük alapul – itt nincs nagy titok.
A kérés törzsét betápláljuk függvényünk bemeneteként - a kimeneten egy elemzett szintaktikai fát kapunk JSON objektum formájában.
Most átfuthatunk ezen a fán az ellenkező irányba, és összeállíthatunk egy kérést a kívánt behúzással, színezéssel és formázással. Nem, ez nem testreszabható, de úgy tűnt számunkra, hogy ez kényelmes lesz.
Lekérdezés és terv csomópontok leképezése
Most nézzük meg, hogyan kombinálhatjuk az első lépésben elemzett tervet és a második lépésben elemzett lekérdezést.
Vegyünk egy egyszerű példát – van egy lekérdezésünk, amely CTE-t generál, és kétszer olvas belőle. Olyan tervet készít.
CTE
Ha figyelmesen megnézed, egészen a 12-es verzióig (vagy onnan kezdve a kulcsszóval MATERIALIZED) kialakulását .
Ez azt jelenti, hogy ha látunk egy CTE-generációt valahol a kérésben, és egy csomópontot valahol a tervben CTE, akkor ezek a csomópontok mindenképpen „harcolnak” egymással, azonnal kombinálhatjuk is őket.
Probléma a csillaggal: A CTE-k egymásba ágyazhatók.
Vannak nagyon rosszul beágyazottak, sőt még azonos nevűek is. Például lehet benne CTE A csinál CTE X, és belül ugyanazon a szinten CTE B csináld újra CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Összehasonlításkor ezt meg kell értened. Ezt a „szemmel” megérteni – még a tervet, a kérés szövegét is – nagyon nehéz. Ha a CTE-generáció összetett, egymásba ágyazott, és a kérések nagyok, akkor ez teljesen öntudatlan.
UNION
Ha van egy kulcsszó a lekérdezésben UNION [ALL] (két minta összekapcsolásának operátora), akkor a tervben bármelyik csomópontnak felel meg Append, vagy néhány Recursive Union.
Ami fent van "fent". UNION - ez a csomópontunk első leszármazottja, amely "lent" van - a második. Ha át UNION akkor több blokkot is „ragasztottunk” egyszerre Append- továbbra is csak egy csomópont lesz, de annak nem két, hanem sok gyereke lesz - sorrendben:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Probléma a csillaggal: belső rekurzív mintavételezés (WITH RECURSIVE) is lehet egynél több UNION. De mindig csak az utolsó utáni legutolsó blokk rekurzív UNION. Minden fent egy, de más UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Az ilyen példákat is ki kell tudni „kilógatni”. Ebben a példában ezt látjuk UNION-3 szegmens volt a kérésünkben. Ennek megfelelően egy UNION megfelel a Append-csomópont, és a másikhoz - Recursive Union.
Írás-olvasási adatok
Minden ki van rakva, most már tudjuk, hogy a kérés melyik része felel meg a terv melyik részének. Ezekben a darabokban pedig könnyen és természetesen megtalálhatjuk azokat a tárgyakat, amelyek „olvashatók”.
Lekérdezés szempontjából nem tudjuk, hogy ez egy tábla vagy egy CTE, de ugyanaz a csomópont jelöli őket RangeVar. És az „olvashatóság” szempontjából ez is egy meglehetősen korlátozott csomópontkészlet:
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]
Ismerjük a terv és a lekérdezés szerkezetét, ismerjük a blokkok megfeleltetését, ismerjük az objektumok nevét - egy az egyben összehasonlítást végzünk.
Újra feladat "csillaggal". Felvesszük a kérést, végrehajtjuk, nincs álnevünk - csak kétszer olvassuk el ugyanattól a CTE-től.
Megnézzük a tervet – mi a probléma? Miért volt álnevünk? Nem rendeltük meg. Honnan van neki ilyen „számszám”?
A PostgreSQL saját maga adja hozzá. Ezt csak meg kell értened csak egy ilyen álnév nekünk a tervvel való összehasonlítás végett ennek semmi értelme, egyszerűen csak ide van írva. Ne figyeljünk rá.
A második feladat "csillaggal": ha particionált táblából olvasunk, akkor kapunk egy csomópontot Append vagy Merge Append, amely nagyszámú „gyerekből” áll majd, és mindegyik lesz valahogy Scan'om a táblázat részből: Seq Scan, Bitmap Heap Scan vagy Index Scan. De mindenesetre ezek a „gyerekek” nem lesznek összetett lekérdezések – így lehet megkülönböztetni ezeket a csomópontokat Append -on UNION.
Megértjük az ilyen csomókat is, gyűjtsük össze őket „egy halomba”, és mondjuk: „minden, amit a megatable-ból olvasol, itt van és lent a fán".
"Egyszerű" adatfogadó csomópontok
Values Scan tervnek megfelel VALUES a kérésben.
Result nélküli kérés FROM fajta SELECT 1. Vagy ha szándékosan hamis kifejezést használ WHERE-block (ekkor megjelenik az attribútum 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 „térkép” az azonos nevű SRF-ekhez.
De a beágyazott lekérdezéseknél minden bonyolultabb - sajnos nem mindig válnak azzá InitPlan/SubPlan. Néha átalakulnak ... Join vagy ... Anti Join, főleg ha olyasmit írsz, mint WHERE NOT EXISTS .... És itt nem mindig lehetséges kombinálni őket - a terv szövegében nincsenek a terv csomópontjainak megfelelő operátorok.
Újra feladat "csillaggal": néhány VALUES a kérésben. Ebben az esetben és a tervben több csomópontot kap Values Scan.
A „számozott” utótagok segítenek megkülönböztetni őket egymástól - pontosan a megfelelő sorrendben kerülnek hozzáadásra VALUES-blokkolja a kérést fentről lefelé.
Adatfeldolgozás
Úgy tűnik, hogy kérésünkben mindent megoldottunk – már csak az van hátra Limit.
De itt minden egyszerű - olyan csomópontok, mint pl Limit, Sort, Aggregate, WindowAgg, Unique „leképezni” egy az egyhez a kérésben szereplő megfelelő operátorokat, ha ott vannak. Itt nincsenek „csillagok” vagy nehézségek.
JOIN
Nehézségek akkor merülnek fel, ha egyesíteni akarunk JOIN egymás között. Ez nem mindig lehetséges, de lehetséges.
A lekérdezéselemző szempontjából van egy csomópontunk JoinExpr, amelynek pontosan két gyermeke van - bal és jobb. Ennek megfelelően ez az, ami „felett” van a JOIN-ban, és ami „alatta” van írva a kérelemben.
És a terv szempontjából ez egyesek két leszármazottja * Loop/* Join-csomópont. Nested Loop, Hash Anti Join,... - valami hasonló.
Használjunk egyszerű logikát: ha vannak A és B táblák, amelyek „csatlakoznak” egymáshoz a tervben, akkor a kérésben ezek is elhelyezhetők A-JOIN-BVagy B-JOIN-A. Próbáljuk meg így kombinálni, próbáljuk meg fordítva, és így tovább, amíg el nem fogynak az ilyen párok.
Vegyük a szintaxisfánkat, vegyük a tervünket, nézzük meg őket... nem hasonló!
Rajzoljuk át grafikonok formájában – ó, már látszik is valami!
Vegyük észre, hogy vannak olyan csomópontjaink, amelyeknek egyszerre van B és C gyermeke – nem számít, milyen sorrendben. Kombináljuk őket, és fordítsuk meg a csomópont képét.
Nézzük újra. Most már vannak csomópontjaink A gyermekekkel és párokkal (B + C) - ezekkel is kompatibilisek.
Nagy! Kiderült, hogy mi ketten vagyunk JOIN a kérésből a tervcsomópontok sikeresen egyesültek.
Sajnos ez a probléma nem mindig oldódik meg.
Például, ha egy kérésben A JOIN B JOIN C, és a tervben mindenekelőtt a „külső” csomópontok A és C voltak összekötve.De a kérésben nincs ilyen operátor, nincs mit kiemelnünk, nincs mire utalni. Így van ez a "vesszővel" is, amikor írsz A, B.
De a legtöbb esetben szinte az összes csomópont „leoldható”, és időben megkaphatja ezt a fajta profilozást a bal oldalon - szó szerint, mint a Google Chrome-ban, amikor JavaScript-kódot elemez. Láthatja, hogy mennyi ideig tartott az egyes sorok és utasítások „végrehajtása”.
És hogy mindezt kényelmesebben használhassa, tárolót készítettünk , ahol elmentheti és később megtalálhatja terveit a kapcsolódó kérésekkel együtt, vagy megoszthatja valakivel a linket.
Ha csak egy olvashatatlan lekérdezést kell megfelelő formába vinnie, használja .
Forrás: will.com