🥇Kaj vam EXPLAINS ne pove in kako ga spraviti v pogovor

Klasično vprašanje, ki ga razvijalec zastavi svojemu administratorju baz podatkov ali lastnik podjetja svetovalcu za PostgreSQL, je skoraj vedno enako: "Zakaj se poizvedbe v bazi podatkov izvajajo tako dolgo?"

Tradicionalni nabor razlogov:

neučinkovit algoritem
ko se odločite PRIDRUŽITI več CTE-jem v nekaj deset tisoč zapisih
nepomembna statistika
če se dejanska porazdelitev podatkov v tabeli že zelo razlikuje od tiste, ki jo je funkcija ANALYZE zbrala zadnjič
»priklop« na vire
in ni več dovolj namenske računalniške moči procesorja, se nenehno črpajo gigabajti pomnilnika ali pa disk ne more slediti vsem "željam" baze podatkov
blokiranje iz konkurenčnih procesov

In če je blokiranje dovolj težko zaznati in analizirati, potem za vse ostalo potrebujemo le načrt poizvedbe, ki ga je mogoče dobiti z uporabo Operator RAZLAGA (Seveda je bolje takoj POJASNITI (ANALIZIRATI, PUFRIRATI) ...) ali modul auto_explain.

Vendar, kot je navedeno v isti dokumentaciji,

"Razumevanje načrta je umetnost, in za njegovo obvladovanje so potrebne določene izkušnje, ..."

Ampak lahko brez tega, če uporabite pravo orodje!

Kako je običajno videti načrt poizvedbe? Nekaj takega:

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

ali takole:

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

Toda branje načrta iz nič je zelo težko in neintuitivno:

je prikazano v vozlišču vsota virov poddrevesa
to pomeni, da če želite razumeti, koliko časa je trajalo izvajanje določenega vozlišča ali koliko podatkov je bilo natančno to branje iz tabele pridobljenih z diska, morate nekako odšteti eno od drugega
čas vozlišča je potreben množenje z zankami
Da, odštevanje ni najtežja operacija, ki jo je treba izvesti "v glavi" - navsezadnje je čas izvajanja naveden kot povprečje za eno izvedbo vozlišča, in teh je lahko na stotine.
No, vse to skupaj nam preprečuje, da bi odgovorili na glavno vprašanje - kdo torej najšibkejša povezava?

Ko smo vse to poskušali razložiti nekaj sto našim razvijalcem, smo ugotovili, da je od zunaj videti nekako takole:

In to pomeni, da potrebujemo…

Orodje

V njem smo poskušali zbrati vse ključne mehanike, ki nam pomagajo razumeti, na podlagi načrta in zahteve, "kdo je kriv in kaj storiti." In seveda smo s skupnostjo delili nekaj svojih izkušenj.
Spoznajte se in uživajte - expand.tensor.ru

Vidljivost načrtov

Je enostavno razumeti načrt, ko je videti takole?

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 res.

Ampak tako pač je, v skrajšani obliki, ko so ključni kazalniki ločeni, je veliko bolj jasno:

Če pa je načrt bolj zapleten, bo pomoč prišla krožni diagram časovne porazdelitve po vozliščih:

No, pri najtežjih možnostih hiti na pomoč diagram izvedbe:

Na primer, obstajajo precej netrivialne situacije, ko ima lahko načrt več kot en dejanski vzrok:

Strukturni namigi

No, če je celotna struktura načrta in njegove šibke točke že določene in vidne, zakaj jih ne bi izpostavili razvijalcu in jih razložili v preprostem jeziku?

Zbrali smo že nekaj ducatov teh predlog priporočil.

Profiler poizvedb po vrsticah

Če zdaj na analizirani načrt nanesete izvirno poizvedbo, lahko vidite, koliko časa je bilo porabljenega za vsakega posameznega operatorja – nekaj takega:

... ali celo takole:

Vstavljanje parametrov v poizvedbo

Če ste načrtu priložili ne le poizvedbo, temveč tudi njene parametre iz vrstice DETAIL v dnevniku, jih lahko dodatno kopirate z eno od naslednjih možnosti:

z zamenjavo vrednosti v poizvedbi
za neposredno izvedbo na lastni podlagi in nadaljnje profiliranje
```
SELECT 'const', 'param'::text;
```
z zamenjavo vrednosti prek PREPARE/EXECUTE
za posnemanje dela razporejevalnika, ko je parametrični del mogoče prezreti - na primer pri delu na particioniranih tabelah
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```