🥇Yini I-CHAZELA athule ngayo nokuthi ungamenza kanjani akhulume

Umbuzo wakudala olethwa unjiniyela ku-DBA yakhe, noma umnikazi webhizinisi awuletha kumxhumanisi we-PostgreSQL, cishe njalo uzwakala ufana: "Kungani izicelo zithatha isikhathi eside kangaka ukuqedwa ku-database?"

Isethi yezizathu zendabuko:

i-algorithm engasebenzi kahle
uma unquma UKUJOYINA ama-CTE ambalwa ngaphezu kwamarekhodi ambalwa ezinkulungwane eziyishumi
izibalo ezingabalulekile
uma ukusatshalaliswa kwangempela kwedatha kuthebula sekuvele kuhluke kakhulu kuleyo eqoqwe ngu-ANALYZE okokugcina
"plug" ezinsizeni
futhi amandla ekhompuyutha azinikele e-CPU awasaneli, amagigabhayithi enkumbulo ahlala empontshwa, noma idiski ayikwazi ukuhambisana nakho konke “okufunwa” kusizindalwazi.
ukuvimba kusukela ezinqubweni eziqhudelanayo

Futhi uma ukuvimba kunzima kakhulu ukubamba nokuhlaziya, ngakho konke okunye esikudingayo uhlelo lombuzo, engatholakala kusetshenziswa CHAZA u-opharetha (Kungcono, kunjalo, ukuthi UCHAZE ngokushesha (HLAZIYA, AMABHUFU) ...) noma auto_explain module.

Kodwa, njengoba kushiwo kumadokhumenti afanayo,

"Ukuqonda uhlelo kuwubuciko, futhi ukuze ukwazi ukuluqonda kahle kudinga ulwazi oluthile..."

Kodwa ungakwenza ngaphandle kwayo uma usebenzisa ithuluzi elifanele!

Ingabe uhlelo lombuzo luvame ukubukeka kanjani? Okufana nalokhu:

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

noma kanje:

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

Kodwa ukufunda uhlelo embhalweni "kusuka eshidini" kunzima kakhulu futhi akucacile:

kuboniswa ku-node isamba ngezinsizakusebenza ezincane
okungukuthi, ukuqonda ukuthi kuthathe isikhathi esingakanani ukwenza i-node ethile, noma ukuthi lokhu kufundwa kwetafula kukhiphe idatha engakanani kudiski, udinga ukuthi ngandlela thize ukhiphe eyodwa kwenye.
isikhathi se-node siyadingeka phindaphinda ngezihibe
yebo, ukususa akuyona umsebenzi oyinkimbinkimbi kakhulu okumelwe wenziwe "ekhanda" - emva kwakho konke, isikhathi sokwenza sikhonjiswe njengesilinganiso sokwenziwa okukodwa kwe-node, futhi kungaba namakhulu abo.
kahle, futhi konke lokhu ndawonye kusivimbela ekuphenduleni umbuzo oyinhloko - ngakho ngubani "isixhumanisi esibuthakathaka"?

Lapho sizama ukuchaza konke lokhu kumakhulu ambalwa wabathuthukisi bethu, sabona ukuthi ngaphandle kubukeka kanjena:

Futhi lokho kusho ukuthi sidinga...

Insimbi

Kuyo sizame ukuqoqa wonke ama-mechanics abalulekile asiza ukuqonda ukuthi “ubani okufanele asolwe nokuthi kufanele enzeni” ngokohlelo nesicelo. Hhayi-ke, futhi wabelane ngengxenye yolwazi lwakho nomphakathi.
Hlangana futhi usebenzise - chaza.tensor.ru

Ukubonakala kwezinhlelo

Ingabe kulula ukuqonda uhlelo uma lubukeka kanje?

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

Akunjalo ngempela.

Kodwa kanje, ngendlela efushanisiwelapho izinkomba ezibalulekile zihlukaniswa, kucace kakhulu:

Kodwa uma uhlelo luyinkimbinkimbi kakhulu, uzosiza ukusatshalaliswa kwesikhathi se-piechart ngamanodi:

Hhayi-ke, ngezinketho ezinzima kakhulu usejahile ukusiza ishadi lokuqhubeka:

Isibonelo, kunezimo ezingezona ezincane lapho uhlelo lungaba nezimpande zangempela ezingaphezu kweyodwa:

Imikhondo yesakhiwo

Hhayi-ke, uma sonke isakhiwo sohlelo kanye nezindawo zalo ezibuhlungu sezivele zibekwe futhi zibonakala, kungani ungaziqokomisi kumthuthukisi futhi uzichaze "ngolimi lwesiRashiya"?

Sesivele siqoqe izifanekiso zezincomo ezinjalo ezimbalwa.

Iphrofayili yombuzo womugqa ngomugqa

Manje, uma ubeka umbuzo wokuqala ohlelweni oluhlaziyiwe, ungabona ukuthi singakanani isikhathi esichithwe kusitatimende ngasinye - into efana nalena:

... noma njengokuthi:

Ifaka esikhundleni amapharamitha esicelweni

Uma "unganamathiseli" isicelo kuphela ohlelweni, kodwa futhi nemingcele yalo evela kumugqa we-DETAIL welogi, ungakwazi futhi ukuyikopisha kwenye yezinketho:

ngokushintshwa kwenani esicelweni
ngokusebenza okuqondile esisekelweni sakho kanye nokuphrofayili okwengeziwe
```
SELECT 'const', 'param'::text;
```
ngokushintshwa kwenani nge-PREPARE/EXECUTE
ukulingisa umsebenzi womhleli, lapho ingxenye yepharamitha inganakwa - isibonelo, lapho usebenza kumatafula ahlukanisiwe
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Ingobo yomlando yezinhlelo

Namathisela, hlaziya, wabelane nozakwenu! Izinhlelo zizohlala kungobo yomlando, futhi ungabuyela kuzo kamuva: explain.tensor.ru/archive

Kodwa uma ungafuni ukuthi abanye babone uhlelo lwakho, ungakhohlwa ukumaka ibhokisi elithi “ungashicileli kungobo yomlando”.

Ezihlokweni ezilandelayo ngizokhuluma ngobunzima nezinqumo eziqhamukayo uma kuhlaziywa uhlelo.

Source: www.habr.com

Yini I-CHAZELA athule ngayo nokuthi ungayithola kanjani ikhulume