🥇UCHAZA ntoni athule kwaye umenze njani athethe

Umbuzo oqhelekileyo othi umphuhlisi awuzise kwi-DBA yakhe okanye umnini-shishini oza kumcebisi we-PostgreSQL phantse usoloko uvakala ngokufanayo: "Kutheni izicelo zithatha ixesha elide kangaka ukugqiba kwisiseko sedatha?"

Iseti yezizathu eziqhelekileyo:

i-algorithm engasebenziyo
xa uthatha isigqibo sokuJOYINA ii-CTE ezininzi ngaphezulu kwesibini samashumi amawaka erekhodi
izibalo ezingenamsebenzi
ukuba ukuhanjiswa kwangempela kwedatha kwitheyibhile sele kuhluke kakhulu kulowo uqokelelwe ngu-ANALYZE ngexesha lokugqibela
"plug" kwizibonelelo
kwaye akusekho mandla aneleyo ekhompyuter azinikeleyo e-CPU, iigigabytes zememori zihlala zimpontshwa, okanye idiski ayinakuqhubeka nayo yonke "imfuno" yedatha.
ukuvimba ukusuka kwiinkqubo ezikhuphisanayo

Kwaye ukuba iibhlokhi zinzima kakhulu ukubamba kunye nokuhlalutya, ngoko kuyo yonke enye into esiyifunayo isicwangciso sombuzo, enokufunyanwa kusetyenziswa CHAZA umsebenzisi (Kungcono, ewe, ukuba uchaze ngokukhawuleza (HLALUTYA, IZIBHAFU) ...) okanye auto_cacisa imodyuli.

Kodwa, njengoko kuchaziwe kuxwebhu olufanayo,

"Ukuqonda isicwangciso bubugcisa, kwaye ukusiqonda kufuna umyinge wamava athile..."

Kodwa unokwenza ngaphandle kwayo ukuba usebenzisa isixhobo esifanelekileyo!

Ingaba isicwangciso sombuzo siqhele ukujongeka njani? Into enjalo:

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

okanye 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 isicwangciso kwisicatshulwa "ukusuka kwiphepha" kunzima kakhulu kwaye akucaci:

iboniswe kwi-node isixa ngemithombo yomthi ongaphantsi
Oko kukuthi, ukuqonda ukuba lingakanani ixesha elithathiweyo ukwenza indawo ethile, okanye ukuba oku kufundwa kwetafile kuzise idatha esuka kwidiski ngandlela ithile, kufuneka ngandlela ithile uthabathe enye kwenye
ixesha le-node liyafuneka phinda-phinda ngamarhintyela
ewe, ukuthabatha akuwona msebenzi onzima kakhulu ekufuneka wenziwe "entloko" - emva kwayo yonke loo nto, ixesha lokubulawa liboniswa njengomndilili wokwenziwa kwenode enye, kwaye kunokubakho amakhulu abo.
kuhle, kwaye konke oku kunye kusithintela ekuphenduleni umbuzo oyintloko - ngoko ngubani "elona khonkco libuthathaka"?

Xa sasizama ukucacisa yonke le nto kumakhulu aliqela abaphuhlisi bethu, siye safumanisa ukuba ngaphandle kujongeka ngolu hlobo:

Kwaye oko kuthetha ukuba kufuneka...

Isixhobo

Kuyo sizame ukuqokelela zonke ii-mechanics eziphambili ezinceda ukuqonda "ngubani onetyala kunye nokuba enze ntoni" ngokwesicwangciso kunye nesicelo. Ewe, kwaye wabelane ngenxalenye yamava akho noluntu.
Dibana kwaye usebenzise - explain.tensor.ru

Ukubonakala kwezicwangciso

Ngaba kulula ukuqonda isicwangciso xa sibonakala sinje?

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

Kodwa ngale ndlela, ikwimo efinyeziweyoxa izikhombisi eziphambili zahluliwe, kucace ngakumbi:

Kodwa ukuba isicwangciso sinzima ngakumbi, uya kuhlangula unikezelo lwexesha piechart ngeenodi:

Ewe, kwezona ndlela zinzima kakhulu ungxamele ukunceda itshathi yenkqubela phambili:

Umzekelo, kukho iimeko ezingabalulekanga xa isicwangciso sinokuba neengcambu ezingaphezulu kwesinye:

Imikhondo yesakhiwo

Ewe, ukuba sonke isakhiwo sesicwangciso kunye neendawo ezibuhlungu sele zibekwe kwaye zibonakala, kutheni ungazigqamisi kumphuhlisi kwaye uchaze "ngolwimi lwesiRashiya"?

Sele siqokelele ishumi elinesibini leetemplates ezicetyiswayo.

Umgca-ngomgca wombuzo weprofayili

Ngoku, ukuba uphakamisa umbuzo wokuqala kwisicwangciso esihlalutyiweyo, unokubona ukuba lingakanani ixesha elichithwe kwingxelo nganye-into efana nale:

... okanye njengale:

Ukutshintsha iiparamitha kwisicelo

Ukuba "uncamathele" kungekuphela nje isicelo kwisicwangciso, kodwa kunye neeparamitha zayo ukusuka kumgca we-DETAIL welogi, ungaphinda uyikhuphele kwenye yeenketho:

ngexabiso endaweni yombuzo
ukuphunyezwa ngokuthe ngqo kwisiseko sakho kunye neprofayili engaphezulu
```
SELECT 'const', 'param'::text;
```
ngexabiso endaweni nge PREPARE/EXECUTE
ukulinganisa umsebenzi womcwangcisi, xa inxalenye yeparametric inokungahoywa - umzekelo, xa usebenza kwiitafile ezahluliweyo
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Uvimba wezicwangciso

Namathisela, hlalutya, wabelane nabalingane! Izicwangciso ziya kuhlala zigcinwe kwaye ungabuyela kuzo kamva: explain.tensor.ru/archive

Kodwa ukuba awufuni abanye babone isicwangciso sakho, ungalibali ukujonga ibhokisi ethi "musa ukupapasha kwindawo yokugcina".

Kumanqaku alandelayo ndiya kuthetha ngobunzima kunye nezigqibo ezivelayo xa kuhlalutywa isicwangciso.

umthombo: www.habr.com

Yintoni INGCACISO athule ngayo kwaye uyifumana njani ukuba ithethe