🥇O čemu EXPLAIN šuti i kako ga natjerati da progovori

Klasično pitanje koje programer postavlja svom DBA ili vlasnik tvrtke postavlja konzultantu za PostgreSQL gotovo uvijek zvuči isto: "Zašto zahtjevi u bazi podataka tako dugo traju da se dovrše?"

Tradicionalni niz razloga:

neučinkovit algoritam
kada se odlučite PRIDRUŽITI nekoliko CTE-ova preko nekoliko desetaka tisuća zapisa
zastarjela statistika
ako se stvarna distribucija podataka u tablici već jako razlikuje od one koju je ANALYZE prikupio prošli put
"čep" na resursima
i više nema dovoljno namjenske računalne snage CPU-a, gigabajti memorije se neprestano pumpaju ili disk ne može pratiti sve "želje" baze podataka
blokiranje od konkurentskih procesa

I ako je blokade prilično teško uhvatiti i analizirati, onda za sve ostalo trebamo plan upita, koji se može dobiti pomoću EXPLAIN operator (Bolje je, naravno, odmah OBJASNITI (ANALIZIRATI, BUFERI) ...) ili auto_explain modul.

Ali, kako je navedeno u istoj dokumentaciji,

“Razumijevanje plana je umjetnost, a za njegovo svladavanje potrebno je određeno iskustvo...”

Ali možete i bez toga ako koristite pravi alat!

Kako obično izgleda plan upita? Nešto kao to:

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

ili ovako:

"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"

Ali čitanje plana u tekstu "s lista" vrlo je teško i nejasno:

prikazuje se u čvoru zbroj po resursima podstabla
to jest, da biste shvatili koliko je vremena bilo potrebno da se izvrši određeni čvor, ili koliko je točno ovo čitanje iz tablice donijelo podatke s diska, morate nekako oduzeti jedno od drugog
potrebno je vrijeme čvora umnožiti petljama
da, oduzimanje nije najkompleksnija operacija koja se mora raditi "u glavi" - uostalom, vrijeme izvršenja je naznačeno kao prosjek za jedno izvršenje čvora, a može ih biti na stotine
dobro, a sve to skupa sprječava nas da odgovorimo na glavno pitanje – pa tko "najslabija karika"?

Kada smo sve ovo pokušali objasniti nekoliko stotina naših programera, shvatili smo da izvana izgleda otprilike ovako:

A to znači da trebamo...

Oruđe

U njemu smo pokušali prikupiti sve ključne mehanike koje pomažu razumjeti "tko je kriv i što učiniti" prema planu i zahtjevu. Pa, i podijelite dio svog iskustva sa zajednicom.
Upoznajte i koristite - objasniti.tensor.ru

Vidljivost planova

Je li lako razumjeti plan kad ovako izgleda?

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

Ne baš.

Ali ovako, u skraćenom oblikukada se razdvoje ključni pokazatelji, puno je jasnije:

Ali ako je plan kompliciraniji, on će priskočiti u pomoć piechart vremenska distribucija po čvorovima:

Pa, za najteže opcije žuri pomoći grafikon napretka:

Na primjer, postoje prilično netrivijalne situacije kada plan može imati više od jednog stvarnog korijena:

Strukturni tragovi

Pa, ako su cijela struktura plana i njegove bolne točke već postavljene i vidljive, zašto ih ne istaknuti programeru i objasniti ih na "ruskom jeziku"?

Već smo prikupili nekoliko desetaka takvih predložaka preporuka.

Profiler upita redak po redak

Sada, ako postavite originalni upit na analizirani plan, možete vidjeti koliko je vremena potrošeno na svaku pojedinačnu izjavu - otprilike ovako:

...ili čak ovako:

Zamjena parametara u zahtjev

Ako ste planu "priložili" ne samo zahtjev, već i njegove parametre iz retka DETAIL dnevnika, možete ga dodatno kopirati u jednoj od opcija:

sa zamjenom vrijednosti u zahtjevu
za izravnu izvedbu na vašoj bazi i daljnje profiliranje
```
SELECT 'const', 'param'::text;
```
sa zamjenom vrijednosti preko PREPARE/EXECUTE
oponašati rad planera, kada se parametarski dio može zanemariti - na primjer, kada se radi na particioniranim tablicama
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arhiva planova

Zalijepite, analizirajte, podijelite s kolegama! Planovi će ostati arhivirani i možete im se vratiti kasnije: objasniti.tensor.ru/archive

Ali ako ne želite da drugi vide vaš plan, ne zaboravite označiti okvir "ne objavljivati u arhivi".

U sljedećim člancima govorit ću o poteškoćama i odlukama koje se javljaju prilikom analize plana.

Izvor: www.habr.com

O čemu EXPLAIN šuti i kako ga natjerati da progovori