🥇Mille üle EXPLAIN vaikib ja kuidas teda vestlema saada

Klasikaline küsimus, millega arendaja pöördub oma DBA või äriomaniku poole – PostgreSQL konsultandi juurde – kõlab peaaegu alati ühtemoodi: „Miks päringud andmebaasis nii kaua kestavad?“

Traditsiooniline põhjuste komplekt:

ebaefektiivne algoritm
kui otsustasite teha JOIN mitme CTE järgi paari kümne tuhande kirje kaupa
aktuaalne statistika
kui tegelik andmete jaotus tabelis erineb juba oluliselt sellest, mis viimane ANALYZE'iga kogutud
"ressursside ummistus"
ja arvutusvõimsus CPU-lt on nüüd liiga madal, igasugustes mälukeeristes on liiga palju andmeid või kõvaketas ei suuda andmebaasi kõiki „soove“ järgida
lukud konkurenteest protsessidest

Ja kui lukustusi on keeruline tabada ja analüüsida, siis kõik muu osas piisab meile päringu plaanist, mille saab kätte EXPLAIN lause abil (muidugi on parem kohe EXPLAIN (ANALYZE, BUFFERS) ...) või auto_explain moodulist.

Kuid nagu öeldud samas dokumentatsioonis,

„Päringu plaani mõistmine on kunst, ja selleks, et sellega hakkama saada, on vajalik kindel kogemus, ...“

Kuid saab hakkama ka ilma selleta, kui kasutada sobivat tööriista!

Kuidas näeb päringu plaan tavaliselt välja? Umbes nii:

Indeksi skanni kasutamine pg_class_relname_nsp_index'il pg_class (reaegaalne aeg=0.049..0.050 read=1 tsüklid=1)
  Indeksi tingimus: (relname = $1)
  Filter: (oid = $0)
  Mälu: jagatud hit=4
  Algplaan 1 (tagastab $0,$1)
    ->  Piirang (reaegaalne aeg=0.019..0.020 read=1 tsüklid=1)
          Mälu: jagatud hit=1
          ->  Sekv Scan pg_class pg_class_1 (reaegaalne aeg=0.015..0.015 read=1 tsüklid=1)
                Filter: (relkind = 'r'::"char")
                Filteri kaudu eemaldatud read: 5
                Mälu: jagatud hit=1

või siis nii:

"Append  (kulu=868.60..878.95 read=2 laius=233) (reaegaalne aeg=0.024..0.144 read=2 tsüklid=1)"
"  Mälu: jagatud hit=3"
"  CTE cl"
"    ->  Sekv Scan pg_class  (kulu=0.00..868.60 read=9972 laius=537) (reaegaalne aeg=0.016..0.042 read=101 tsüklid=1)"
"          Mälu: jagatud hit=3"
"  ->  Piirang  (kulu=0.00..0.10 read=1 laius=233) (reaegaalne aeg=0.023..0.024 read=1 tsüklid=1)"
"        Mälu: jagatud hit=1"
"        ->  CTE skannimine cl  (kulu=0.00..997.20 read=9972 laius=233) (reaegaalne aeg=0.021..0.021 read=1 tsüklid=1)"
"              Mälu: jagatud hit=1"
"  ->  Piirang  (kulu=10.00..10.10 read=1 laius=233) (reaegaalne aeg=0.117..0.118 read=1 tsüklid=1)"
"        Mälu: jagatud hit=2"
"        ->  CTE skannimine cl cl_1  (kulu=0.00..997.20 read=9972 laius=233) (reaegaalne aeg=0.001..0.104 read=101 tsüklid=1)"
"              Mälu: jagatud hit=2"
"Planeerimise aeg: 0.634 ms"
"Täitmise aeg: 0.248 ms"

Aga plaani lugemine otse „lehelt” on väga keeruline ja mitteütlev:

sisse sõlmes kuvatakse ressursside summa alampuu
ehk et mõista, kui kaua kulus aega konkreetse sõlme täitmiseks, või kui palju andmeid see lugemine tabelist tõi kettalt – peab kuidagi ühest teiseni lahutama
sõlme aeg tuleb korrutada tsüklitega
Jah, lahkumine pole veel kõige keerulisem tehe, mida on vaja „mõttes“ teha — sest töötlemise aeg on keskmiselt näidatud ühe sõlme täitmise jaoks, ja neid võib olla sadu.
Noh, ja kõik see kokku takistab vastata peamisele küsimusele — nii kes siis on. „kõige nõrgem lüli“?

Kui me püüdsime seda selgitada mitmesajale meie arendajale, siis mõistsime, et sealtpoolt näeb see välja umbes nii:

Seega, meil on vaja...

Tööriist

Oleme püüdnud kokku koguda kõik peamised mehhanismid, mis aitavad plaani ja päringu mõistmisel, „kes on süüdi ja mida teha“. Noh, ja jagada osa oma kogemusest kogukonnaga.
Tere tulemast ja kasutage — explain.tensor.ru

Plaanide selgus

Kas plaani arusaamine on lihtne, kui see välja näeb nii?

Seq Scan pg_class peal (reaalne aeg=0.009..1.304 read=6609 tsüklis=1) Buffers: jagatud hit=263 Planeerimise aeg: 0.108 ms Täitmise aeg: 1.800 ms

Ei, justkui ei.

Aga nii, kokkuvõtlikult, kui peamised näitajad on eraldi — on juba palju arusaadavam:

Aga kui plaan on keerulisem — tuleb appi piechart ajajaotuse sõlmedes:

Noh, ja kõige keerulisemate variantide puhul aitab täitmisdiagramm:

Näiteks esinevad piisavalt ebatavalised olukorrad, kui plaanil võib olla rohkem kui üks tegelik juur:

Struktuursed näpunäited

Noh, ja kui kogu plaani struktuur ja selle nõrgad kohad on juba välja pakitud ja nähtavad — miks mitte tõsta need arendajale esile ja seletada "lihtsas keeles"?

Oleme juba kogunud selliseid soovituste malli paar tosinat.

Ridade profiilimise päring

Nüüd, kui analüüsitavale plaanile on kohandatud algne päring, siis saab näha, kui palju aega kulus iga eraldi operaatori jaoks — umbes nii:

… või isegi nii:

Parameetrite asendamine päringus

Kui oled „kinni pannud“ plaani mitte ainult päringu, vaid ka selle parameetritega DETAIL-reas logis, siis saad sellekoopida täiendavalt ühes variandis:

parameetrite asemele päringus
otseseks täitmiseks oma andmebaasis ja edasise profiilimise jaoks
```
SELECT 'const', 'param'::text;
```
parameetrite asemele koos PREPARE/EXECUTE
planeerija töö simuleerimiseks, kui parameetriline osa võib jääda tähelepanuta — näiteks sektsioonitud tabelite puhul
```
DEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
```