🥇Vad EXPLAIN är tyst om och hur man får honom att prata

Den klassiska frågan som en utvecklare ställer till sin DBA eller en företagsägare ställer till en PostgreSQL-konsult låter nästan alltid likadant: "Varför tar förfrågningar så lång tid att slutföra i databasen?"

Traditionella skäl:

ineffektiv algoritm
när du bestämmer dig för att GÅ MED flera CTE:er över ett par tiotusentals poster
föråldrad statistik
om den faktiska distributionen av data i tabellen redan skiljer sig mycket från den som samlades in av ANALYS senast
"plugga" på resurser
och det finns inte längre tillräckligt med dedikerad datorkraft för processorn, gigabyte minne pumpas ständigt, eller så kan disken inte hålla jämna steg med alla "önskningar" i databasen
blockering från konkurrerande processer

Och om blockeringar är ganska svåra att fånga och analysera, då för allt annat vi behöver frågeplan, som kan erhållas med hjälp av FÖRKLARA operatör (Det är naturligtvis bättre att omedelbart FÖRKLARA (ANALYSERA, BUFFARE) ...) eller auto_explain-modulen.

Men, som det står i samma dokumentation,

"Att förstå en plan är en konst, och för att bemästra den krävs en viss mängd erfarenhet..."

Men du klarar dig utan det om du använder rätt verktyg!

Hur ser en frågeplan vanligtvis ut? Något sådant:

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å här:

"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 att läsa planen i texten "från arket" är mycket svårt och oklart:

visas i noden summa av underträdsresurser
det vill säga, för att förstå hur mycket tid det tog att köra en viss nod, eller hur mycket exakt denna läsning från tabellen tog upp data från disken, måste du på något sätt subtrahera den ena från den andra
nodtid behövs multiplicera med loopar
ja, subtraktion är inte den mest komplexa operationen som måste göras "i huvudet" - trots allt indikeras exekveringstiden som genomsnitt för en exekvering av en nod, och det kan finnas hundratals av dem
ja, och allt detta tillsammans hindrar oss från att svara på huvudfrågan - så vem "den svagaste länken"?

När vi försökte förklara allt detta för flera hundra av våra utvecklare insåg vi att det från utsidan såg ut ungefär så här:

Och det betyder att vi behöver...

Verktyg

I den försökte vi samla alla nyckelmekaniker som hjälper till att förstå "vem som bär skulden och vad man ska göra" enligt planen och begäran. Tja, och dela en del av din erfarenhet med samhället.
Möt och använd - explain.tensor.ru

Synlighet av planer

Är det lätt att förstå planen när den ser ut så här?

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

Inte riktigt.

Men så här, i förkortad formnär nyckelindikatorerna separeras är det mycket tydligare:

Men om planen är mer komplicerad kommer han att komma till undsättning piechart tidsfördelning efter noder:

Tja, för de svåraste alternativen har han bråttom att hjälpa framstegsdiagram:

Till exempel finns det ganska icke-triviala situationer när en plan kan ha mer än en faktisk rot:

Strukturella ledtrådar

Tja, om hela strukturen i planen och dess ömma fläckar redan är utlagda och synliga, varför inte lyfta fram dem för utvecklaren och förklara dem på "ryska språket"?

Vi har redan samlat ett par dussin sådana rekommendationsmallar.

Rad för rad frågeprofilerare

Om du nu lägger över den ursprungliga frågan på den analyserade planen kan du se hur mycket tid som spenderades på varje enskilt uttalande - ungefär så här:

...eller till och med så här:

Ersätter parametrar i en begäran

Om du "bifogade" inte bara en begäran till planen, utan också dess parametrar från DETAIL-raden i loggen, kan du dessutom kopiera den i ett av alternativen:

med värdesubstitution i begäran
för direkt utförande på din bas och ytterligare profilering
```
SELECT 'const', 'param'::text;
```
med värdesubstitution via PREPARE/EXECUTE
att emulera schemaläggarens arbete, när den parametriska delen kan ignoreras - till exempel när du arbetar med partitionerade tabeller
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arkiv över planer

Klistra in, analysera, dela med kollegor! Planerna kommer att förbli arkiverade och du kan återvända till dem senare: explain.tensor.ru/archive

Men om du inte vill att andra ska se din plan, glöm inte att markera rutan "publicera inte i arkiv".

I de följande artiklarna kommer jag att prata om de svårigheter och beslut som uppstår när man analyserar en plan.

Källa: will.com

Vad EXPLAIN är tyst om och hur man får det att prata