🥇Apie ką EXPLAIN tyli ir kaip priversti jį prabilti

Klasikinis klausimas, kurį kūrėjas pateikia savo DBA arba verslo savininkas „PostgreSQL“ konsultantui, beveik visada skamba taip pat: „Kodėl duomenų bazėje užklausos užtrunka taip ilgai?

Tradicinis priežasčių rinkinys:

neefektyvus algoritmas
kai nuspręsite PRISIJUNGTI prie kelių CTE per kelias dešimtis tūkstančių įrašų
nereikšminga statistika
jei tikrasis duomenų pasiskirstymas lentelėje jau labai skiriasi nuo praėjusį kartą surinkto ANALIZĖS
„prijungti“ išteklius
ir nebėra pakankamai dedikuotos procesoriaus skaičiavimo galios, nuolat pumpuojami gigabaitai atminties arba diskas negali patenkinti visų duomenų bazės „norų“
blokavimas nuo konkuruojančių procesų

Ir jei blokavimus gana sunku pagauti ir išanalizuoti, tada visa kita, ko mums reikia užklausų planas, kurį galima gauti naudojant PAAIŠKINTI operatorių (Žinoma, geriau iš karto PAAIŠKINTI (ANALIZUOTI, BUFERIAI) ...) arba auto_explain modulis.

Tačiau, kaip nurodyta tuose pačiuose dokumentuose,

„Plano supratimas yra menas, o norint jį įvaldyti reikia tam tikros patirties...“

Bet jūs galite išsiversti be jo, jei naudojate tinkamą įrankį!

Kaip paprastai atrodo užklausų planas? Kažkas panašaus:

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

arba taip:

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

Tačiau skaityti planą tekste „iš lapo“ yra labai sunku ir neaišku:

rodomas mazge suma pagal pomedžio išteklius
tai yra, norint suprasti, kiek laiko prireikė tam tikro mazgo vykdymui arba kiek tiksliai šis rodinys iš lentelės atnešė duomenų iš disko, turite kažkaip atimti vieną iš kito
reikia mazgo laiko padauginti iš kilpų
taip, atimtis nėra pati sudėtingiausia operacija, kurią reikia atlikti „galvoje“ - juk vykdymo laikas nurodomas kaip vidutinis vienam mazgo vykdymui, o jų gali būti šimtai
na, o visa tai kartu trukdo atsakyti į pagrindinį klausimą – tai kas "silpniausia grandis"?

Kai pabandėme visa tai paaiškinti keliems šimtams savo kūrėjų, supratome, kad iš išorės tai atrodė maždaug taip:

O tai reiškia, kad mums reikia...

Įrankis

Jame pagal planą ir pageidavimą bandėme surinkti visus pagrindinius mechanikus, kurie padeda suprasti „kas kaltas ir ką daryti“. Na, ir dalinkitės savo patirtimi su bendruomene.
Susipažinkite ir naudokitės - paaiškinti.tensor.ru

Planų matomumas

Ar lengva suprasti planą, kai jis atrodo taip?

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

Bet taip, sutrumpinta formaKai pagrindiniai rodikliai yra atskirti, tai daug aiškiau:

Bet jei planas sudėtingesnis, jis ateis į pagalbą skersinės diagramos laiko pasiskirstymas pagal mazgus:

Na, o dėl sunkiausių variantų jis skuba padėti progreso diagrama:

Pavyzdžiui, yra gana nereikšmingų situacijų, kai planas gali turėti daugiau nei vieną tikrąją šaknį:

Struktūriniai įkalčiai

Na, o jei visa plano struktūra ir jo skaudamos vietos jau išdėtos ir matomos, kodėl jų neišryškinus rengėjui ir nepaaiškinus „rusų kalba“?

Tokių rekomendacijų šablonų jau surinkome porą dešimčių.

Eilutės po eilutės užklausų profiliavimo priemonė

Dabar, jei pradinę užklausą įtrauksite į analizuojamą planą, pamatysite, kiek laiko buvo skirta kiekvienam atskiram teiginiui – maždaug taip:

...ar net taip:

Parametrų pakeitimas užklausoje

Jei prie plano „prisegėte“ ne tik užklausą, bet ir jo parametrus iš žurnalo eilutės DETAIL, galite ją papildomai nukopijuoti naudodami vieną iš parinkčių:

su reikšmės pakeitimu užklausoje
tiesioginiam vykdymui jūsų bazėje ir tolesniam profiliavimui
```
SELECT 'const', 'param'::text;
```
su vertės pakeitimu per PREPARE/EXECUTE
imituoti planuotojo darbą, kai parametrinė dalis gali būti nepaisoma - pavyzdžiui, dirbant su skaidytomis lentelėmis
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Planų archyvas

Įklijuokite, analizuokite, dalinkitės su kolegomis! Planai liks archyve, prie jų galėsite grįžti vėliau: paaiškinkite.tensor.ru/archive

Bet jei nenorite, kad kiti matytų jūsų planą, nepamirškite pažymėti laukelio „Neskelbti archyve“.

Tolesniuose straipsniuose kalbėsiu apie sunkumus ir sprendimus, kylančius analizuojant planą.

Šaltinis: www.habr.com

Apie ką EXPLAIN nutylima ir kaip priversti jį prabilti