Despre ce EXPLAIN este tăcut și despre cum să-l faci să vorbească

Întrebarea clasică pe care un dezvoltator o aduce DBA-ului său sau un proprietar de afaceri o aduce unui consultant PostgreSQL aproape întotdeauna sună la fel: „De ce solicitările durează atât de mult să se completeze în baza de date?”

Setul tradițional de motive:

• algoritm ineficient
  atunci când decideți să vă alăturați mai multor CTE-uri pe câteva zeci de mii de înregistrări
• statistici irelevante
  dacă distribuția efectivă a datelor din tabel este deja foarte diferită de cea colectată de ANALYZE data trecută
• „conectați” resursele
  și nu mai există suficientă putere de calcul dedicată a procesorului, gigaocteți de memorie sunt pompați în mod constant sau discul nu poate ține pasul cu toate „dorințele” bazei de date
• blocare din procese concurente

Și dacă blocajele sunt destul de greu de prins și analizat, atunci pentru tot ce avem nevoie plan de interogare, care poate fi obținut folosind operator EXPLAIN (Este mai bine, desigur, să EXPLICAȚI imediat (ANALIZAȚI, BUFFERE) ...) sau modulul auto_explain.

Dar, după cum se precizează în aceeași documentație,

„Înțelegerea unui plan este o artă, iar pentru a-l stăpâni necesită o anumită experiență...”

Dar te poți descurca fără el dacă folosești instrumentul potrivit!

Cum arată de obicei un plan de interogare? Ceva de genul:

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

sau cam asa:

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

Dar citirea planului în text „din foaie” este foarte dificilă și neclară:

• este afișat în nod suma prin resurse subarbore
  adică pentru a înțelege cât timp a durat pentru a executa un anumit nod sau cât de mult exact această citire din tabel a adus date de pe disc, trebuie să scădeți cumva unul din celălalt
• este nevoie de timp pentru nod înmulțiți cu bucle
  da, scăderea nu este cea mai complexă operație care trebuie făcută „în cap” - la urma urmei, timpul de execuție este indicat ca medie pentru o execuție a unui nod și pot exista sute de ele
• bine, și toate acestea împreună ne împiedică să răspundem la întrebarea principală - deci cine „cea mai slabă verigă”?

Când am încercat să explicăm toate acestea câteva sute de dezvoltatori noștri, ne-am dat seama că din exterior arată cam așa:

Și asta înseamnă că avem nevoie de...

Instrument

În el am încercat să colectăm toate mecanismele cheie care ajută la înțelegerea „cine este de vină și ce să facă” conform planului și solicitării. Ei bine, și împărtășește o parte din experiența ta cu comunitatea.
Întâlnește și folosește - explica.tensor.ru

Vizibilitatea planurilor

Este ușor de înțeles planul când arată așa?

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

Nu chiar.

Dar așa, în formă prescurtatăcând indicatorii cheie sunt separați, este mult mai clar:

Dar dacă planul este mai complicat, el va veni în ajutor distribuția timpului grafică pe noduri:

Ei bine, pentru cele mai dificile opțiuni se grăbește să ajute diagrama de progres:

De exemplu, există situații destul de netriviale când un plan poate avea mai multe rădăcini reale:

Indicii structurale

Ei bine, dacă întreaga structură a planului și punctele sale dureroase sunt deja așezate și vizibile, de ce să nu le evidențiezi dezvoltatorului și să le explici în „limba rusă”?

Am colectat deja câteva zeci de astfel de șabloane de recomandare.

Profiler de interogări linie cu linie

Acum, dacă suprapuneți interogarea inițială pe planul analizat, puteți vedea cât timp a fost petrecut pentru fiecare declarație individuală - ceva de genul acesta:

...sau chiar asa:

Înlocuirea parametrilor într-o cerere

Dacă ați „atașat” nu numai o solicitare la plan, ci și parametrii acestuia din linia DETALII a jurnalului, o puteți copia suplimentar într-una dintre opțiuni:

• cu substituție de valoare în interogare
  pentru execuție directă pe baza dvs. și profilare ulterioară

  SELECT 'const', 'param'::text;

• cu înlocuirea valorii prin PREPARE/EXECUTE
  pentru a emula munca planificatorului, când partea parametrică poate fi ignorată - de exemplu, atunci când lucrați pe tabele partiționate

  DEALLOCATE ALL;
  PREPARE q(text) AS SELECT 'const', $1::text;
  EXECUTE q('param'::text);
  

Arhiva de planuri

Lipiți, analizați, distribuiți colegilor! Planurile vor rămâne arhivate și puteți reveni la ele mai târziu: explica.tensor.ru/archive

Dar dacă nu doriți ca alții să vă vadă planul, nu uitați să bifați caseta „nu publicați în arhivă”.

În articolele următoare voi vorbi despre dificultățile și deciziile care apar la analizarea unui plan.

Sursa: www.habr.com