🥇Zomwe EXPLAIN sanalankhulepo ndi momwe mungamupangitse kuti alankhule

Funso lachikale lomwe wopanga amabweretsa ku DBA yake kapena eni bizinesi amabweretsa kwa mlangizi wa PostgreSQL pafupifupi nthawi zonse limamveka chimodzimodzi: "N'chifukwa chiyani zopempha zimatenga nthawi yayitali kuti amalize pa database?"

Zifukwa zachikhalidwe:

algorithm yosagwira ntchito
mukaganiza KUJOWANA ma CTE angapo pamarekodi angapo masauzande
ziwerengero zosafunikira
ngati kugawa kwenikweni kwa deta mu tebulo kuli kale kosiyana kwambiri ndi komwe kunasonkhanitsidwa ndi ANALYZE nthawi yotsiriza
"plug" pa zothandizira
ndipo palibenso mphamvu yokwanira yodzipatulira ya CPU, ma gigabytes a kukumbukira akungoponyedwa nthawi zonse, kapena disk silingagwirizane ndi "zofuna" zonse za database.
kutseka kuchokera ku njira zopikisana

Ndipo ngati blocking ndizovuta kwambiri kugwira ndikusanthula, ndiye pa china chilichonse chomwe timafunikira funso dongosolo, yomwe ingapezeke pogwiritsa ntchito EXPLAIN woyendetsa (Ndikwabwino, inde, KUFOTOKOZERA (KUSANIZA, ZOPHUNZITSA) ...) kapena auto_explain module.

Koma, monga tafotokozera m'mabuku omwewo,

"Kumvetsetsa pulani ndi luso, ndipo kuti muidziwe bwino pamafunika kudziwa zambiri ..."

Koma mutha kuchita popanda izo ngati mugwiritsa ntchito chida choyenera!

Kodi dongosolo lamafunso limawoneka bwanji? Chinachake chonga icho:

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

kapena monga izi:

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

Koma kuwerenga ndondomekoyi m'mawu "kuchokera pa pepala" ndizovuta komanso zosamveka bwino:

ikuwonetsedwa mu node ndalama ndi subtree resources
ndiko kuti, kuti mumvetsetse kuchuluka kwa nthawi yomwe zidatenga kuti mupange mfundo inayake, kapena kuchuluka kwenikweni komwe kuwerengaku kuchokera patebulo kunabweretsa deta kuchokera pa diski, muyenera mwanjira ina kuchotsa imodzi kuchokera ku imzake.
nthawi ya node ndiyofunika chulukitsani ndi malupu
inde, kuchotsa si ntchito yovuta kwambiri yomwe iyenera kuchitidwa "pamutu" - pambuyo pake, nthawi yakupha ikuwonetsedwa ngati avareji ya kuphedwa kumodzi kwa node, ndipo pakhoza kukhala mazana a iwo.
chabwino, ndipo zonsezi pamodzi zimatilepheretsa kuyankha funso lalikulu - ndiye ndani "ulalo wofooka kwambiri"?

Titayesera kufotokozera zonsezi kwa mazana angapo a omwe amatipanga, tidazindikira kuti kuchokera kunja zikuwoneka motere:

Ndipo izi zikutanthauza kuti tiyenera ...

Chida

M'menemo tinayesera kusonkhanitsa makina onse ofunika omwe amathandiza kumvetsetsa "yemwe ali ndi mlandu ndi choti achite" malinga ndi ndondomeko ndi pempho. Chabwino, ndikugawana zomwe mwakumana nazo ndi anthu amdera lanu.
Kumanani ndi kugwiritsa ntchito - explain.tensor.ru

Kuwonekera kwa mapulani

Kodi ndizosavuta kumvetsetsa pulaniyo ikawoneka chonchi?

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

Osati kwenikweni.

Koma monga chonchi, mwachidulepamene zizindikiro zazikulu zilekanitsidwa, zimakhala zomveka bwino:

Koma ngati ndondomekoyo ndi yovuta kwambiri, adzabwera kudzathandiza kugawa nthawi ya piechart pa nodes:

Chabwino, chifukwa cha zosankha zovuta kwambiri iye akufulumira kuthandiza tchati chakupita patsogolo:

Mwachitsanzo, pali zochitika zosakhala zazing'ono pomwe dongosolo likhoza kukhala ndi mizu yopitilira umodzi:

Zizindikiro zamapangidwe

Chabwino, ngati dongosolo lonse la ndondomekoyi ndi mabala ake opweteka aikidwa kale ndikuwonekera, bwanji osawawunikira kwa wopanga mapulogalamuwo ndikuwafotokozera mu "chinenero cha Chirasha"?

Tasonkhanitsa kale ma tempuleti angapo olimbikitsa otere.

Mzere-ndi-mzere wamafunso profiler

Tsopano, ngati muwonjezera funso loyambirira pa pulani yowunikiridwa, mutha kuwona kuchuluka kwa nthawi yomwe idagwiritsidwa ntchito pachiganizo chilichonse - monga chonchi:

... kapena monga izi:

Kusintha magawo mu pempho

Ngati "mwaphatikizira" osati pempho lokhalo, komanso magawo ake kuchokera pamzere wa DETAIL wa chipikacho, mutha kuyikoperanso mu imodzi mwazosankha:

ndi mtengo m'malo mwa pempho
kuti muphatikizire mwachindunji pamaziko anu ndikuwonjezera mbiri yanu
```
SELECT 'const', 'param'::text;
```
m'malo mwa mtengo kudzera PREPARE/EXECUTE
kutsanzira ntchito ya scheduler, pamene gawo la parametric likhoza kunyalanyazidwa - mwachitsanzo, pogwira ntchito pa matebulo ogawidwa
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Archive wa mapulani

Matani, santhulani, gawani ndi anzanu! Mapulaniwo adzakhalabe osungidwa ndipo mutha kubwereranso pambuyo pake: explain.tensor.ru/archive

Koma ngati simukufuna kuti ena awone dongosolo lanu, musaiwale kuyang'ana bokosi la "osasindikiza pazosungidwa".

M'nkhani zotsatirazi ndilankhula za zovuta ndi zosankha zomwe zimakhalapo popenda ndondomeko.

Source: www.habr.com

Kodi EXPLAIN sichilankhulapo chiyani komanso momwe mungayankhire