PostgreSQL Query Profiler: hvordan matche planen og forespørselen

Mange som allerede bruker explain.tensor.ru — vår tjeneste for visualisering av PostgreSQL-planer, er kanskje ikke klar over en av dens superkrefter — å gjøre en vanskelig lesbar del av serverloggen til...

…en pent formatert forespørsel med kontekstuelle tips om de relevante nodene i planen:

I denne tolkningen av den andre delen av sin presentasjon på PGConf.Russia 2020 vil jeg fortelle hvordan vi klarte å gjøre dette.

Med transkriptet til den første delen, som omhandler typiske ytelsesproblemer med forespørslene og deres løsninger, kan man finne i artikkelen «Oppskrifter for syke SQL-forespørslene».

Først skal vi ta oss av fargeleggingen — og vi skal ikke fargelegge planen, den har vi allerede fargelagt, den er allerede pen og forståelig, men forespørselen.

Vi syntes at en slik uformaterte «klut» hentet fra loggen så veldig stygg ut og derfor — uhåndterlig.


Spesielt når utviklerne i koden «limer» forespørselens kropp (dette er selvfølgelig en antipattern, men det skjer) i én linje. Grusomt!

La oss tegne dette på en mer vakker måte.


Og hvis vi kan tegne det pent, det vil si dele og sette sammen forespørselens kropp igjen, så kan vi også «koble» tips til hvert objekt i denne forespørselen — hva som skjedde på det relevante punktet i planen.

Syntaktisk tre av forespørselen

For å gjøre dette, må forespørselen først deles opp.


Siden vår systemkjernen kjører på NodeJS, laget vi et lite modul til den, dere kan finne det på GitHub. Faktisk er dette utvidede «bindinger» til innsiden av selve PostgreSQL-parseren. Det vil si, bare binært kompilert grammatikk og bindinger laget på NodeJS-siden. Vi tok utgangspunkt i andres moduler — det er ingen stor hemmelighet her.

Vi mater inn forespørselens kropp i funksjonen vår — på utgangen får vi et syntaktisk tre delt opp i form av et JSON-objekt.


Nå kan vi gå tilbake gjennom dette treet og samle forespørselen med de innrykkene, fargingen og formateringen vi ønsker. Nei, dette er ikke konfigurerbart, men vi syntes det ville være praktisk slik.

Sammenføring av noder i forespørselen og planen

Nå skal vi se hvordan vi kan kombinere planen, som vi delte opp i første trinn, og forespørselen, som vi delte opp i det andre.

La oss ta et enkelt eksempel — vi har en spørring som danner en CTE og leser fra den to ganger. Den genererer en slik plan.

CTE

Hvis vi ser nøye på det, er det frem til versjon 12 (eller fra den med nøkkelordet MATERIALIZED) dannelse CTE er en ubestridelig barriere for planleggeren..


Så hvis vi ser en generasjon av CTE et sted i spørringen og et sted i planen en node, CTE, så slår disse nodene tydeligvis sammen, vi kan umiddelbart kombinere dem.

Oppgaven med en stjerne: CTE-er kan være innkapslet.

De kan være veldig dårlig innkapslet, og til og med med samme navn. For eksempel kan du inne i CTE A lage CTE X, og på samme nivå inne i CTE B gjøre igjen CTE X:

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

Når du sammenligner, må du forstå dette. Å forstå det med «øyne» — selv ved å se planen, selv ved å se kroppen av spørringen — er veldig vanskelig. Hvis genereringen av CTE er komplisert, innkapslet, og spørringene er store — så er det i det hele tatt ubevisst.

Den definerende spørringen må ha

Hvis vi har nøkkelordet i spørringen UNION [ALL] (operator for å bli med to utvalg), så svarer det i planen til enten en node Legg til, eller en eller annen Rekursiv Union.


Det som er «ovenfor» noden vår, er den første avkommet, mens det som er «nedenfor» — er den andre. Hvis det er «limt» flere blokker sammen, så Den definerende spørringen må ha -noden vil fortsatt være bare én, men den har mange barn — i rekkefølge som de kommer, henholdsvis: Den definerende spørringen må ha (...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3 Legg tilVedlegg -> ... #1 -> ... #2 -> ... #3

  : inne i genereringen av rekursiv utvelgelse (

) kan det også være mer enn én

Oppgaven med en stjerne. Men alltid er det bare den siste blokken etter den sisteWITH RECURSIVE. Alt som er over — er én, men en annen Den definerende spørringen må haWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, her slutter genereringen av starttilstanden for rekursivitet UNION ALL (...) -- #3, bare denne blokken er rekursiv og kan referere til T ) ... Den definerende spørringen må haSånne eksempler må vi også kunne «lime». Her ser vi at Den definerende spørringen må ha:

-segmentene i vår spørring var 3 stykker. Følgelig, en til

-node, og en annen — Den definerende spørringen må haLesing-skriving av data. Den definerende spørringen må ha tilsvarer Legg tilDa, la oss si, nå vet vi hvilken del av forespørselen som tilsvarer hvilken del av planen. Og i disse delene kan vi enkelt og uanstrengt finne de objektene som «leses». Rekursiv Union.

Fra spørringens synspunkt vet vi ikke — er det en tabell eller CTE, men de betegnes med samme node.

Все, разложили, теперь мы знаем, какой кусочек запроса какому кусочку плана соответствует. И в этих кусочках мы можем легко и непринужденно найти те объекты, которые «читаются».

С точки зрения запроса мы не знаем — таблица это или CTE, но обозначаются они одинаковым узлом RangeVar. Og i planen «leses» - dette er også et ganske begrenset sett med noder:

• Sekvensskanning på [tbl]
• Bitmap Haugskanning på [tbl]
• Indeks [Only] Skann [Bakover] bruker [idx] på [tbl]
• CTE-skanning på [cte]
• Sett inn/Update/Delete på [tbl]

Vi kjenner strukturen til planen og forespørselen, vi vet hvordan blokkene samsvarer, vi kjenner objektenes navn - så vi gjør en entydig tilknytning.


Igjen oppgaven med «stjernene». Vi tar forespørselen, utfører den, og vi har ingen aliaser - vi har bare lest fra én CTE to ganger.


Vi ser på planen - hva er problemet? Hvorfor har vi fått opp et alias? Vi ba ikke om det. Hvor kommer denne «nummerte» fra?

PostgreSQL legger det til selv. Man må bare forstå at akkurat det aliaset for våre formål i tilknytningen til planen ikke har noen mening, det er bare lagt til her. Vi vil ikke ta hensyn til det.

Andre oppgaven med «stjernene»: hvis vi leser fra en partisjonert tabell, så får vi en node Legg til eller Slå sammen og legg til, som vil bestå av et stort antall «barn», og hver av dem vil være en slags Scanfra tabell-partisjonen: Sekvensi Skanning, Bitmap Haugskanning eller Index Scan. Men uansett vil disse «barnene» ikke være kompliserte forespørselene - så disse nodene kan skilles fra Legg til når Den definerende spørringen må ha.


Sådanne noder forstår vi også, samler «i en haug» og sier: "alt du leste fra megatable - dette er her og nedover i treet".

«Enkle» noder for datainnhenting

Verdi Scan i planen tilsvarer Spesifisere i forespørselen.

Result - dette er en forespørsel uten FROM slik SELECT 1. Eller når du har en bevisst falsk uttrykk i Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt.-blokken (da oppstår attributten One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- eller 0 = 1

Resultat  (kostnad=0.00..0.00 rader=0 bredde=230) (faktisk tid=0.000..0.000 rader=0 løkker=1)
  One-Time Filter: falsk

Funksjon Skanning «kartlegges» til de tilsvarende SRF-ene.

Men med innviklede forespørsler er det vanskeligere - dessverre endres de ikke alltid til InitPlan/SubPlan. Noen ganger endres de til ... Join eller ... Anti Join, spesielt når du skriver noe som WHERE NOT EXISTS .... Og der er det ikke alltid mulig å kombinere - i tekstplanen finnes det ingen tilsvarende noder for operatørene.

Igjen oppgaven med «stjernene»: flere Spesifisere i forespørselen. I slike tilfeller vil du i planen også få flere noder Verdi Scan.


Å skille dem fra hverandre kan hjelper «nummerte» suffikser - de legges til nøyaktig i rekkefølgen som de tilsvarende Spesifisere-blokkene finnes i forespørselen ovenfra og ned.

Databehandling

Det ser ut som vi har gått gjennom alle i forespørselen vår - det er bare igjen Grense.


Men her er alt enkelt - slike noder som Begrens, Sorter, Aggregere, WindowAgg, Unik «kartlegges» en-til-en på de tilsvarende operatørene i forespørselen, hvis de finnes. Her er det ingen «stjerner» og vanskeligheter.

JOIN

Vanskeligheter oppstår når vi ønsker å kombinere JOIN med hverandre. Dette er ikke alltid mulig, men det kan gjøres.


Fra parserens synspunkt har vi en node Bli med, som har nøyaktig to etterkommere – venstre og høyre. Dette er henholdsvis det som er "over" din JOIN og det som er "under" det som står i forespørselen.

Og sett fra planens synspunkt har dette to etterkommere av en eller annen * Løkka/* Bli med-node. Nested Loop, Hash Anti Join,… – noe slik.

La oss bruke enkel logikk: hvis vi har tabeller A og B som "joiner" seg med hverandre i planen, kan de i forespørselen være plassert enten A-JOIN-B, eller B-JOIN-A. La oss prøve å kombinere dem på denne måten, så prøver vi det motsatte, og så videre til slike par tar slutt.

La oss ta vårt syntaktiske tre, ta planen vår, se på dem... det ser ikke likt ut!


Vi tegner det på nytt som grafer – åh, nå begynner det å ligne noe!


La oss merke oss at vi har noder som samtidig har barn B og C – vi bryr oss ikke om rekkefølgen. La oss kombinere dem og snu bildet av noden.


La oss ta en ny titt. Nå har vi noder med barn A og paret (B + C) – la oss kombinere dem også.


Flott! Det viser seg at vi på en vellykket måte har kombinert disse to JOIN fra forespørselen med nodene i planen.

Dessverre løses ikke denne oppgaven alltid.


For eksempel, hvis i forespørselen A JOIN B JOIN C, men i planen har de "ytterste" nodene A og C blitt kombinert først. Og i forespørselen finnes det ingen slik operator, så vi har ingen å fremheve, ingen å knytte hintet til. Det samme gjelder "komma" når du skriver A, B.

Men i de fleste tilfeller klarer vi å "løse opp" nesten alle noder og få denne profiling til venstre etter tid – bokstavelig talt, som i Google Chrome når du analyserer JavaScript-koden. Du ser hvor lang tid hver linje og hver operatør "ble utført".


Og for at dere skal kunne bruke alt dette mer behagelig, har vi laget lagring arkiv, hvor du kan lagre og deretter finne planene dine sammen med de tilknyttede forespørslene eller dele en lenke med noen.

Hvis du bare må bringe en ulest forespørsel i en forståelig form, bruk gjerne vår "normalisator".

Kilde: habr.com