🥇O čem EXPLAIN molči in kako ga pripraviti do tega, da spregovori

Klasično vprašanje, ki ga razvijalec prinese svojemu DBA ali lastnik podjetja svetovalcu za PostgreSQL, skoraj vedno zveni enako: "Zakaj se zahteve v zbirki podatkov dokončajo tako dolgo?"

Tradicionalni niz razlogov:

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

In če je blokade precej težko ujeti in analizirati, potem za vse ostalo potrebujemo načrt poizvedbe, ki ga lahko dobite z uporabo Operator EXPLAIN (Seveda je bolje, da takoj RAZLOŽITE (ANALIZIRATE, POMNITE) ...) ali modul auto_explain.

Toda, kot je navedeno v isti dokumentaciji,

»Razumeti načrt je umetnost in za njegovo obvladovanje so potrebne določene izkušnje ...«

Lahko pa tudi brez tega, če uporabljate pravo orodje!

Kako običajno izgleda 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 v besedilu "z lista" je zelo težko in nejasno:

se prikaže v vozlišču vsota po poddrevesnih virih
to pomeni, da razumete, koliko časa je bilo potrebno za izvedbo določenega vozlišča ali koliko točno je to branje iz tabele prineslo podatkov z diska, morate nekako odšteti enega od drugega
potreben je čas vozlišča pomnožite z zankami
da, odštevanje ni najbolj zapletena operacija, ki jo je treba opraviti "v glavi" - navsezadnje je čas izvajanja naveden kot povprečje za eno izvedbo vozlišča in jih je lahko na stotine
no, in vse to skupaj nam onemogoča odgovor na glavno vprašanje – torej kdo "najšibkejši člen"?

Ko smo poskušali vse to razložiti več 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 pomagajo razumeti, "kdo je kriv in kaj storiti" glede na načrt in zahtevo. No, in delite del svoje izkušnje s skupnostjo.
Spoznajte in uporabite - expand.tensor.ru

Vidnost načrtov

Ali je lahko razumeti načrt, ko je videti tako?

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 takole, v skrajšani oblikiko so ključni indikatorji ločeni, je veliko bolj jasno:

Če pa je načrt bolj zapleten, bo priskočil na pomoč piechart časovna porazdelitev po vozliščih:

No, za najtežje možnosti se mudi na pomoč grafikon napredka:

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

Strukturni namigi

No, če so celotna struktura načrta in njegove boleče točke že postavljene in vidne, zakaj jih ne bi izpostavili razvijalcu in jih razložili v "ruskem jeziku"?

Zbrali smo že nekaj ducatov takih priporočilnih predlog.

Profiler poizvedb po vrsticah

Zdaj, če prvotno poizvedbo prekrijete z analiziranim načrtom, lahko vidite, koliko časa je bilo porabljenega za vsako posamezno izjavo - nekaj takega:

... ali celo takole:

Zamenjava parametrov v zahtevo

Če ste načrtu "priložili" ne samo zahtevo, ampak tudi njene parametre iz vrstice DETAIL dnevnika, jo lahko dodatno kopirate v eni od možnosti:

z zamenjavo vrednosti v zahtevi
za neposredno izvedbo na vaši bazi in nadaljnje profiliranje
```
SELECT 'const', 'param'::text;
```
z zamenjavo vrednosti prek PREPARE/EXECUTE
za posnemanje dela razporejevalnika, ko se parametrični del lahko prezre - na primer pri delu na particioniranih tabelah
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arhiv načrtov

Prilepite, analizirajte, delite s kolegi! Načrti bodo ostali arhivirani in k njim se lahko vrnete pozneje: expand.tensor.ru/archive

Če pa ne želite, da drugi vidijo vaš načrt, ne pozabite označiti polja »ne objavi v arhivu«.

V naslednjih člankih bom govoril o težavah in odločitvah, ki se pojavijo pri analizi načrta.

Vir: www.habr.com

O čem EXPLAIN molči in kako ga pripraviti do tega, da spregovori