🥇Hva EXPLAIN er stille om og hvordan få ham til å snakke

Det klassiske spørsmålet som en utvikler bringer til sin DBA eller en bedriftseier kommer med til en PostgreSQL-konsulent høres nesten alltid ut: "Hvorfor tar det så lang tid å fullføre forespørsler på databasen?"

Tradisjonelt sett med grunner:

ineffektiv algoritme
når du bestemmer deg for å BLI MEDLEM på flere CTE-er over et par titusenvis av poster
irrelevant statistikk
hvis den faktiske fordelingen av data i tabellen allerede er svært forskjellig fra den som ble samlet inn av ANALYSE forrige gang
"plugg" på ressurser
og det er ikke lenger nok dedikert datakraft til CPU, gigabyte minne pumpes konstant, eller disken kan ikke holde tritt med alle "ønsker" til databasen
blokkering fra konkurrerende prosesser

Og hvis blokkeringer er ganske vanskelige å fange og analysere, så for alt annet vi trenger spørreplan, som kan fås ved hjelp av FORKLAR operatør (Det er selvfølgelig bedre å umiddelbart FORKLARE (ANALYSE, BUFFERE) ...) eller auto_explain-modul.

Men som nevnt i samme dokumentasjon,

"Å forstå en plan er en kunst, og for å mestre den krever en viss mengde erfaring ..."

Men du kan klare deg uten det hvis du bruker riktig verktøy!

Hvordan ser en spørringsplan vanligvis ut? Noe sånt:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

eller slik:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

Men å lese planen i teksten "fra arket" er veldig vanskelig og uklart:

vises i noden sum etter undertreressurser
det vil si, for å forstå hvor mye tid det tok å utføre en bestemt node, eller hvor mye nøyaktig denne lesingen fra tabellen hentet data fra disken, må du på en eller annen måte trekke den ene fra den andre
nodetid er nødvendig multiplisere med løkker
ja, subtraksjon er ikke den mest komplekse operasjonen som må gjøres "i hodet" - utførelsestiden er tross alt angitt som gjennomsnitt for én utførelse av en node, og det kan være hundrevis av dem
vel, og alt dette til sammen hindrer oss i å svare på hovedspørsmålet - så hvem "det svakeste leddet"?

Da vi prøvde å forklare alt dette til flere hundre av utviklerne våre, skjønte vi at fra utsiden så det omtrent slik ut:

Og det betyr at vi trenger...

Tool

I den prøvde vi å samle alle nøkkelmekanikkene som hjelper, i henhold til planen og forespørselen, for å forstå "hvem som har skylden og hva vi skal gjøre." Vel, og del deler av opplevelsen din med samfunnet.
Møt og bruk - explain.tensor.ru

Synlighet av planer

Er det lett å forstå planen når den ser slik ut?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

Ikke egentlig.

Men som dette, i forkortet formnår nøkkelindikatorene er atskilt, er det mye tydeligere:

Men hvis planen er mer komplisert, vil han komme til unnsetning piechart tidsfordeling etter noder:

Vel, for de vanskeligste alternativene har han det travelt med å hjelpe fremdriftsdiagram:

For eksempel er det ganske ikke-trivielle situasjoner når en plan kan ha mer enn én faktisk rot:

Strukturelle ledetråder

Vel, hvis hele strukturen til planen og dens såre punkter allerede er lagt ut og synlig, hvorfor ikke fremheve dem for utvikleren og forklare dem på "russisk språk"?

Vi har allerede samlet et par dusin slike anbefalingsmaler.

Linje-for-linje spørringsprofiler

Nå, hvis du legger den opprinnelige spørringen over på den analyserte planen, kan du se hvor mye tid som ble brukt på hver enkelt utsagn - noe sånt som dette:

...eller til og med slik:

Bytte inn parametere i en forespørsel

Hvis du "vedlagt" ikke bare en forespørsel til planen, men også dens parametere fra DETALJ-linjen i loggen, kan du i tillegg kopiere den i ett av alternativene:

med verdisubstitusjon i forespørselen
for direkte utførelse på basen din og videre profilering
```
SELECT 'const', 'param'::text;
```
med verdierstatning via PREPARE/EXECUTE
å emulere arbeidet til planleggeren, når den parametriske delen kan ignoreres - for eksempel når du arbeider på partisjonerte tabeller
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arkiv over planer

Lim inn, analyser, del med kolleger! Planene vil forbli arkivert, og du kan gå tilbake til dem senere: explain.tensor.ru/archive

Men hvis du ikke vil at andre skal se planen din, ikke glem å merke av for "ikke publiser i arkiv".

I de følgende artiklene vil jeg snakke om vanskelighetene og beslutningene som oppstår når man analyserer en plan.

Kilde: www.habr.com

Hva EXPLAIN er stille om og hvordan få det til å snakke