🥇Hvad EXPLAIN er tavs om, og hvordan man får ham til at tale

Det klassiske spørgsmål, som en udvikler bringer til sin DBA eller en virksomhedsejer stiller til en PostgreSQL-konsulent, lyder næsten altid det samme: "Hvorfor tager anmodninger så lang tid at udfylde i databasen?"

Traditionelt sæt af årsager:

ineffektiv algoritme
når du beslutter dig for at JOINLE flere CTE'er over et par titusindvis af poster
forældede statistikker
hvis den faktiske fordeling af data i tabellen allerede er meget forskellig fra den, der blev indsamlet af ANALYSE sidste gang
"tilslut" ressourcer
og der er ikke længere nok dedikeret computerkraft til CPU'en, gigabyte hukommelse pumpes konstant, eller disken kan ikke følge med alle databasens "ønsker"
blokering fra konkurrerende processer

Og hvis blokeringer er ret svære at fange og analysere, så for alt andet, vi har brug for forespørgselsplan, som kan fås vha FORKLAR operatør (Det er selvfølgelig bedre straks at FORKLARE (ANALYSE, BUFFERE) ...) eller auto_explain-modul.

Men som anført i samme dokumentation,

"At forstå en plan er en kunst, og at mestre den kræver en vis mængde erfaring..."

Men du kan undvære det, hvis du bruger det rigtige værktøj!

Hvordan ser en forespørgselsplan typisk ud? Noget i den stil:

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 sådan her:

"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 at læse planen i teksten "fra arket" er meget vanskelig og uklar:

vises i noden sum efter undertræsressourcer
det vil sige, for at forstå, hvor meget tid det tog at udføre en bestemt node, eller hvor meget præcis denne læsning fra tabellen bragte data fra disken, skal du på en eller anden måde trække den ene fra den anden
nodetid er nødvendig gange med sløjfer
ja, subtraktion er ikke den mest komplekse operation, der skal udføres "i hovedet" - når alt kommer til alt, er udførelsestiden angivet som gennemsnit for én udførelse af en node, og der kan være hundredvis af dem
godt, og alt dette tilsammen forhindrer os i at besvare hovedspørgsmålet - så hvem "det svageste led"?

Da vi forsøgte at forklare alt dette til flere hundrede af vores udviklere, indså vi, at det udefra så nogenlunde sådan ud:

Og det betyder, at vi har brug for...

Tool

I den forsøgte vi at samle alle de nøglemekanikker, der hjælper med at forstå "hvem der har skylden, og hvad de skal gøre" i henhold til planen og anmodningen. Nå, og del en del af din oplevelse med fællesskabet.
Mød og brug - explain.tensor.ru

Synlighed af planer

Er det let at forstå planen, når den ser sådan ud?

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

Не очень.

Men sådan her, i forkortet formnår nøgleindikatorerne er adskilt, er det meget tydeligere:

Men hvis planen er mere kompliceret, vil han komme til undsætning piechart tidsfordeling efter noder:

Nå, for de sværeste muligheder har han travlt med at hjælpe fremskridtsdiagram:

For eksempel er der ganske ikke-trivielle situationer, hvor en plan kan have mere end én faktisk rod:

Strukturelle spor

Nå, hvis hele strukturen af planen og dens ømme pletter allerede er lagt ud og synlige, hvorfor så ikke fremhæve dem for udvikleren og forklare dem på "russisk sprog"?

Vi har allerede samlet et par dusin af sådanne anbefalingsskabeloner.

Linje-for-linje forespørgselsprofiler

Nu, hvis du overlejrer den oprindelige forespørgsel på den analyserede plan, kan du se, hvor meget tid der blev brugt på hver enkelt erklæring - noget som dette:

...eller endda sådan her:

Udskiftning af parametre i en anmodning

Hvis du "vedhæftede" ikke kun en anmodning til planen, men også dens parametre fra DETAIL-linjen i loggen, kan du desuden kopiere den i en af mulighederne:

med værdisubstitution i forespørgslen
til direkte udførelse på din base og yderligere profilering
```
SELECT 'const', 'param'::text;
```
med værdisubstitution via PREPARE/EXECUTE
at efterligne skemalæggerens arbejde, når den parametriske del kan ignoreres - for eksempel når der arbejdes på partitionerede tabeller
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arkiv af planer

Indsæt, analyser, del med kolleger! Planerne forbliver arkiverede, og du kan vende tilbage til dem senere: explain.tensor.ru/archive

Men hvis du ikke ønsker, at andre skal se din plan, så glem ikke at markere feltet "udgiv ikke i arkiv".

I de følgende artikler vil jeg tale om de vanskeligheder og beslutninger, der opstår, når man analyserer en plan.

Kilde: www.habr.com

Hvad EXPLAIN er tavs om, og hvordan man får det til at tale