🥇 Maxaa SHARAXA ka aamusan yahay iyo sidii uu u hadli lahaa

Su'aasha caadiga ah ee horumariyuhu u keeno DBA-giisa ama milkiilaha ganacsigu u keeno la taliyaha PostgreSQL had iyo jeer waxay u egtahay isku mid: "Waa maxay sababta ay codsiyadu u qaataan waqti dheer in lagu dhammaystiro xogta?"

Sababo dhaqameed:

algorithm aan waxtar lahayn
marka aad go'aansato inaad ku biirto dhowr CTE-yada dhowr iyo toban kun oo diiwaan
tirakoob aan khusayn
haddii xogta dhabta ah ee shaxda ku jirta ay aad uga duwan tahay tii ay soo ururisay FALANQAYNTA markii u dambaysay
"ku xir" kheyraadka
oo aysan jirin awood xisaabeed oo ku filan oo ku filan CPU-ga, gigabytes ee xusuusta si joogto ah ayaa loo shubaa, ama diskku ma la socon karo dhammaan "rabitaannada" kaydka
xannibid laga bilaabo hababka tartanka

Oo haddii xannibaadaha ay aad u adag tahay in la qabto oo la falanqeeyo, markaa wax kasta oo kale oo aan u baahanahay qorshaha weydiinta, kaas oo lagu heli karo isticmaalka SHARAX hawlwadeenka (Way fiicantahay, dabcan, inaad isla markiiba SHARAXID (FALANQEYSO, BUFFERS) ...) ama auto_explain module.

Laakiin, sida lagu sheegay isla dukumeenti la mid ah,

"Fahamka qorshuhu waa farshaxan, si aad u barato waxay u baahan tahay qadar khibrad leh..."

Laakiin waad samayn kartaa la'aanteed haddii aad isticmaasho qalabka saxda ah!

Sidee buu u eg yahay qorshaha weydiintu? Wax la mid ah:

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

ama sida tan:

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

Laakiin akhrinta qorshaha qoraalka "ka soo baxa xaashida" waa mid aad u adag oo aan caddayn:

waxaa lagu soo bandhigay noodhka marka la isu geeyo kheyraadka geed hoosaadka
taas oo ah, si aad u fahamto intee in le'eg ayay qaadatay in la fuliyo nood gaar ah, ama inta saxda ah ee akhrinta miiska laga keenay xogta diskka, waxaad u baahan tahay inaad si uun ka jarto kan kale.
waqti go'an ayaa loo baahan yahay ku dhufo siddo
Haa, kala-goyntu maaha hawlgalka ugu adag ee loo baahan yahay in lagu sameeyo "madaxa" - ka dib oo dhan, waqtiga fulinta waxaa lagu tilmaamay sida celceliska hal fulinta ee noodhka, waxaana jiri kara boqolaal iyaga ka mid ah.
si fiican, oo waxaas oo dhan si wada jir ah ayaa naga horjoogsanaya inaan ka jawaabno su'aasha ugu weyn - haddaba waa ayo "Xiriirka ugu liita"?

Markii aan isku daynay inaan waxaas oo dhan u sharaxno dhowr boqol oo ka mid ah horumarintayada, waxaan ogaanay in dibadda ay u egtahay sidan:

Taasina waxay la macno tahay inaan u baahanahay...

Qalabka

Waxa aanu isku daynay in aanu ururino dhammaan makaanikada muhiimka ah ee ka caawinaya in la fahmo "qofka eedda leh iyo waxa la sameeyo" sida ku cad qorshaha iyo codsiga. Hagaag, oo qayb ka mid ah khibradaada la wadaag bulshada.
La kulan oo isticmaal - sharax.tensor.ru

Muuqaalka qorshayaasha

Ma fududahay in la fahmo qorshaha marka ay sidan u egtahay?

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

Runtii maahan.

Laakin sidan oo kale qaab la soo gaabiyeymarka tilmaamayaasha muhiimka ah la kala saaro, aad bay u caddahay:

Laakiin haddii qorshuhu ka sii adag yahay, wuu u soo gurman doonaa qaybinta waqtiga pichart xagga qanjidhada:

Hagaag, xulashooyinka ugu adag wuxuu ku degdegaa inuu caawiyo shaxda horumarka:

Tusaale ahaan, waxaa jira xaalado aan sahlanayn marka qorshuhu uu yeelan karo wax ka badan hal xidid oo dhab ah:

Tilmaamaha qaab dhismeedka

Waa hagaag, haddii dhammaan qaab-dhismeedka qorshaha iyo dhibcihiisa nabarrada horeba loo dhigay oo la arki karo, maxaad u muujin weyday horumariyaha oo ku sharax "Luqadda Ruushka"?

Waxaan horey u soo aruurinay dhowr iyo toban qaab oo talo bixin ah.

Profile-ka-line weydiinta

Hadda, haddii aad ku dhejiso weydiinta asalka ah qorshaha la falanqeeyay, waxaad arki kartaa inta wakhtiga lagu qaatay bayaan kasta - wax sidan oo kale ah:

... ama xitaa sida tan:

Ku beddelashada cabbirada codsi

Haddii aad "ku lifaaqdo" kaliya maahan codsi qorshaha, laakiin sidoo kale xaddidaadyadeeda khadka FAAHFAAHIN ee log, waxaad sidoo kale ku koobi kartaa mid ka mid ah xulashooyinka:

oo leh beddelka qiimaha ee weydiinta
si toos ah loogu fuliyo saldhiggaaga iyo sii faafinta
```
SELECT 'const', 'param'::text;
```
oo leh beddelka qiimaha iyada oo loo marayo DIYAARINTA/FULIN
in lagu daydo shaqada jadwalaha, marka qaybta parametric la iska indho tiri karo - tusaale ahaan, marka la shaqaynayo miisaska qaybsan.
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Kaydka qorshayaasha

Ku dheji, falanqee, la wadaag asxaabtaada! Qorshayaashu waxay ahaan doonaan kuwo kaydsan oo aad dib ugu soo noqon kartaa: sharax.tensor.ru/archive

Laakin haddii aadan rabin in kuwa kale ay arkaan qorshahaaga, ha ilaawin inaad saxdo sanduuqa "ha ku daabicin kaydka".

Maqaallada soo socda waxaan kaga hadli doonaa dhibka iyo go’aamada ka dhasha marka la gorfeeyo qorshaha.

Source: www.habr.com

Maxaa SHARAXA ka aamusan yahay iyo sida looga hadlo