Pirms dažiem mÄneÅ”iem ā publisks PostgreSQL.
Pa Å”o laiku jÅ«s jau esat to izmantojis vairÄk nekÄ 6000 reizes, taÄu viena no ÄrtajÄm funkcijÄm varÄja palikt nepamanÄ«ta ā tas ir strukturÄlie ieteikumi, kuri izskatÄs aptuveni Å”Ädi:
Ieklausieties tajos, un jÅ«su vaicÄjumi ākļūs gludi un zÄ«dainÄ«giā. š
Bet, runÄjot nopietni, daudzas situÄcijas, kas padara vaicÄjumu lÄnu un āresursu izsalkuÅ”uā, ir tipiskas un var tikt atpazÄ«tas pÄc struktÅ«ras un datiem plÄnÄ.
TÄdÄ gadÄ«jumÄ katram izstrÄdÄtÄjam nebÅ«s jÄmeklÄ optimizÄcijas variants paÅ”am, paļaujoties tikai uz savu pieredzi ā mÄs varam viÅam norÄdÄ«t, kas Å”eit notiek, kÄda varÄtu bÅ«t iemesls, un kÄ var pievÄrsties risinÄjumam. Ko mÄs arÄ« izdarÄ«jÄm.
ApskatÄ«sim nedaudz sÄ«kÄk Å”os gadÄ«jumus ā kÄ tie tiek identificÄti un pie kÄdÄm rekomendÄcijÄm tie noved.
Lai labÄk iegrimtu tÄma, vispirms var paklausÄ«ties atbilstoÅ”o sadaļu no , un tikai pÄc tam pÄriet pie detalizÄtas katra piemÄra analÄ«zes:
#1: ŠøŠ½Š“ŠµŠŗŃŠ½Š°Ń Ā«Š½ŠµŠ“Š¾ŃŠ¾ŃŃŠøŃŠ¾Š²ŠŗŠ°Ā»
Kad rodas
ParÄdÄ«t pÄdÄjo rÄÄ·inu klientam āSIA ZvanÄ«tÄjsā.
KÄ atpazÄ«t
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Ieteikumi
Izmantojamais indekss paplaÅ”inÄt ar kÄrtoÅ”anas laukiem.
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K āfaktiā
, (random() * 1000)::integer fk_cli; -- 1K dažÄdi ÄrÄjie atslÄgas
CREATE INDEX ON tbl(fk_cli); -- indekss ÄrÄjai atslÄgai
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- atlase pÄc konkrÄtas saites
ORDER BY
pk DESC -- vÄlamies tikai vienu āpÄdÄjoā ierakstu
LIMIT 1; 
Jau uzreiz varam pamanÄ«t, ka pÄc indeksa tika izÅemtas vairÄk nekÄ 100 ierakstu, kuri vÄlÄk visi tika kÄrtoti, un pÄc tam tika atstÄtas vienÄ«gÄ.
MÄs labojam:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- pievienojÄm kÄrtoÅ”anas atslÄgu
Pat uz Å”Ädas primitÄ«vas izlases ā 8.5 reizes ÄtrÄk un 33 reizes mazÄk lasÄ«jumu. Efekts bÅ«s vÄl acÄ«mredzamÄks, jo vairÄk jums bÅ«s āfaktuā par katru vÄrtÄ«bu fk.
PiezÄ«mÄju, ka Å”Äds indekss darbosies kÄ āprefiksÄtsā ne sliktÄk nekÄ iepriekÅ”Äjais arÄ« citiem vaicÄjumiem ar fk, kur kÄrtoÅ”anas pÄc pk nebija un nav (vairÄk par to var izlasÄ«t ). Ieskaitot to, tas nodroÅ”inÄs arÄ« normÄlu atbalstu skaidrai ÄrÄjai atslÄgai Å”ajÄ laukÄ.
#2: ŠæŠµŃŠµŃŠµŃŠµŠ½ŠøŠµ ŠøŠ½Š“ŠµŠŗŃŠ¾Š² (BitmapAnd)
Kad rodas
ParÄdÄ«t visus lÄ«gumus ar klientu Ā«SIA KolokolÄiksĀ», kas noslÄgti no Ā«NAO LjutikĀ».
KÄ atpazÄ«t
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanIeteikumi
Izveidot kompozÄ«tmateriÄla indeksi pÄc laukiem no abÄm avota tabulÄm vai paplaÅ”inÄt vienu no esoÅ”ajiem laukiem no otra.
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
IZVEIDOT TABULU tbl KÄ
IZVÄLÄTIES
Ä£enerÄt_sÄriju(1, 100000) pk -- 100K "faktu"
, (nejauÅ”s() * 100)::integer fk_org -- 100 dažÄdi ÄrÄjie atslÄgas
, (nejauÅ”s() * 1000)::integer fk_cli; -- 1K dažÄdi ÄrÄjie atslÄgas
IZVEIDOT INDEKS uz tbl(fk_org); -- indekss ÄrÄjai atslÄgai
IZVEIDOT INDEKS uz tbl(fk_cli); -- indekss ÄrÄjai atslÄgai
IZVÄLÄTIES
*
NO
tbl
KUR
(fk_org, fk_cli) = (1, 999); -- atlase pÄc konkrÄtas pÄra 
MÄs labojam:
DZÄST INDEKS tbl_fk_org_idx;
IZVEIDOT INDEKS uz tbl(fk_org, fk_cli);
Å eit ieguvums ir mazÄks, jo Bitmap Heap Scan ir pietiekami efektÄ«vs pats par sevi. Bet tomÄr ir 7 reizes ÄtrÄk un par 2.5 reizÄm mazÄk lasÄ«jumu.
#3: Š¾Š±ŃŠµŠ“ŠøŠ½ŠµŠ½ŠøŠµ ŠøŠ½Š“ŠµŠŗŃŠ¾Š² (BitmapOr)
Kad rodas
ParÄdÄ«t pirmos 20 visvecÄkos Ā«savasĀ» vai neizmantotÄs pieteikumus apstrÄdei, kur savas ir prioritÄte.
KÄ atpazÄ«t
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanIeteikumi
Izmantot UNION [VISU] lai apvienotu apakÅ”vaicÄjumus katram no OR bloka nosacÄ«jumiem.
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
IZVEIDOT TABULU tbl KÄ
IZVÄLÄTIES
Ä£enerÄt_sÄriju(1, 100000) pk -- 100K "faktu"
, PIEQRITOT
KAD nejauÅ”s() < 1::real/16 TAD NULL -- ar iespÄju 1:16 ieraksts "neizlemts"
CITAIS (nejauÅ”s() * 100)::integer -- 100 dažÄdi ÄrÄjie atslÄgas
GALA fk_own;
IZVEIDOT INDEKS uz tbl(fk_own, pk); -- indekss ar "it kÄ piemÄrotu" secÄ«bu
IZVÄLÄTIES
*
NO
tbl
KUR
fk_own = 1 VAI -- savi
fk_own IR NULL -- ... vai "neizlemti"
KÄRTOT
pk
, (fk_own = 1) DESC -- vispirms "savi"
IERBEÅ¢INÄT 20;
MÄs labojam:
(
IZVÄLÄTIES
*
NO
tbl
KUR
fk_own = 1 -- vispirms "savi" 20
KÄRTOT
pk
IERBEÅ¢INÄT 20
)
UNION VISU
(
IZVÄLÄTIES
*
NO
tbl
KUR
fk_own IR NULL -- tad "neizlemti" 20
KÄRTOT
pk
IERBEÅ¢INÄT 20
)
IERBEÅ¢INÄT 20; -- bet kopÄ - 20, vairÄk nav nepiecieÅ”ams 
MÄs izmantojÄm to, ka visi 20 nepiecieÅ”amie ieraksti tika iegÅ«ti jau pirmajÄ blokÄ, tÄpÄc otrs, ar dÄrgÄku Bitmap Heap Scan, pat netika izpildÄ«ts ā rezultÄtÄ ir 22 reizes ÄtrÄk, par 44 reizÄm mazÄk lasÄ«jumu!
DetalizÄtÄks stÄstÄ«jums par Å”o optimizÄcijas metodi uz konkrÄtiem piemÄriem var izlasÄ«t rakstos un .
VispÄrinÄta versija sakÄrtotas atlases pÄc vairÄkiem atslÄgas (nevis tikai pÄc pÄra const/NULL) aplÅ«kota rakstÄ .
#4: ŃŠøŃŠ°ŠµŠ¼ Š¼Š½Š¾Š³Š¾ Š»ŠøŃŠ½ŠµŠ³Š¾
Kad rodas
Parasti rodas, kad ir vÄlme "pievienot vÄl vienu filtru" jau esoÅ”ajam vaicÄjumam.
Ā«Un jums nav tÄda paÅ”a, bet ar perlamutra pogÄm?Ā» h/f Ā«Dimanta rokaĀ»
PiemÄram, modificÄjot iepriekÅ” minÄto uzdevumu, parÄdÄ«t pirmos 20 visvecÄkÄs "kritiskÄs" pieprasÄ«jumu apstrÄdei, neatkarÄ«gi no to mÄrÄ·a.
KÄ atpazÄ«t
-> SecÄ«gas skenÄÅ”anas | BitmaÅu kaudzes skenÄÅ”ana | Indekss [Tikai] skenÄÅ”ana [Atpakaļ]
&& 5 Ć rindas < RRbF -- filtrÄts >80% izlasÄ«tÄ
&& cikli Ć RRbF > 100 -- un tajÄ paÅ”Ä laikÄ vairÄk par 100 ierakstiem kopumÄ
Ieteikumi
Izveidot [vairÄk] specializÄtu indeksu ar WHERE-nosacÄ«jumu vai iekļaut indeksÄ papildu laukus.
Ja filtrÄÅ”anas nosacÄ«jums ir "statiskas" jÅ«su uzdevumiem ā proti, neparedz paplaÅ”inÄÅ”anos vÄrtÄ«bu saraksta nÄkotnÄ ā vislabÄk izmantot WHERE-indeksu. Å ajÄ kategorijÄ lieliski iederas dažÄdi boolean/enum statusi.
Ja filtrÄÅ”anas nosacÄ«jums var pieÅemt dažÄdas vÄrtÄ«bas, tad labÄk paplaÅ”inÄt indeksu ar Å”iem laukiem ā kÄ situÄcijÄ ar BitmapAnd iepriekÅ”.
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
IZVEIDOT TABULU tbl KÄ
IZVILKT
Ä£enerÄt_sÄriju(1, 100000) pk -- 100K "fakti"
, KAS
JA nejauÅ”i() < 1::reÄls/16 TAD NULL
CITÄDI (nejauÅ”i() * 100)::vesels -- 100 dažÄdas ÄrÄjÄs atsauces
BEIGÄ fk_own
, (nejauÅ”i() < 1::reÄls/50) kritisks; -- 1:50, ka pieprasÄ«jums "kritisks"
IZVEIDOT INDEX uz tbl(pk);
IZVEIDOT INDEX uz tbl(fk_own, pk);
IZVILKT
*
NO
tbl
KUR
kritisks
PASÅŖTÄŖJUMS PÄC
pk
IEROBEŽOT 20; 
MÄs labojam:
IZVEIDOT INDEX uz tbl(pk)
KUR kritisks; -- pievienojÄm "statisku" filtrÄÅ”anas nosacÄ«jumu
KÄ redzam, filtrÄÅ”ana no plÄna pilnÄ«bÄ aizgÄja, un pieprasÄ«jums kļuva 5 reizes ÄtrÄks.
#5: ŃŠ°Š·ŃŠµŠ¶ŠµŠ½Š½Š°Ń ŃŠ°Š±Š»ŠøŃŠ°
Kad rodas
DažÄdi mÄÄ£inÄjumi izveidot savu uzdevumu apstrÄdes rindu, kad lielais atjauninÄjumu/izdzÄÅ”anas ierakstu daudzums tabulÄ izraisa lielu "miruÅ”o" ierakstu skaitu.
KÄ atpazÄ«t
-> SecÄ«gas skenÄÅ”anas | BitmaÅu kaudzes skenÄÅ”ana | Indekss [Tikai] skenÄÅ”ana [Atpakaļ]
&& cikli Ć (rindas + RRbF) < (dalÄ«ts hits + dalÄ«ts lasÄ«jums) Ć 8
-- izlasÄ«ts vairÄk nekÄ 1KB katram ierakstam
&& dalīts hits + dalīts lasījums > 64
Ieteikumi
RegulÄri manuÄli veikt VACUUM [FULL] vai sasniegt adekvÄti biežu apstrÄdi ar tÄ parametru precÄ«zu pielÄgoÅ”anu, tostarp .
VisvairÄk Å”Ädas problÄmas izrÄdÄs izraisa slikta vaicÄjumu kompozÄ«cija, kad tiek izsaukti biznesa loÄ£ikÄ, piemÄram, tiem, kas tika aplÅ«koti .
Bet jÄizprot, ka pat VACUUM FULL nevar palÄ«dzÄt vienmÄr. Å Ädiem gadÄ«jumiem vÄrts iepazÄ«ties ar algoritmu no raksta .
#6: ŃŃŠµŠ½ŠøŠµ Ń Ā«ŃŠµŃŠµŠ“ŠøŠ½ŃĀ» ŠøŠ½Š“ŠµŠŗŃŠ°
Kad rodas
IzskatÄs, ka esam izlasÄ«juÅ”i nedaudz, un viss pÄc indeksa, un nevienu nevajadzÄ«gu nefiltrÄjÄm ā bet tomÄr izlasÄ«tas ievÄrojami vairÄk lapas nekÄ vÄlÄtos.
KÄ atpazÄ«t
-> Indekss [Tikai] Skatīt [Atpakaļ]
&& cikli Ć (rindiÅas + RRbF) 64
Ieteikumi
RÅ«pÄ«gi aplÅ«kojiet izmantotÄ indeksa struktÅ«ru un atslÄgas laukus, kas norÄdÄ«ti pieprasÄ«jumÄ ā visticamÄk, daļa indeksa nav norÄdÄ«ta. VisticamÄk, jums bÅ«s jÄizveido lÄ«dzÄ«gs indekss, bet bez prefiksa laukiem vai .
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
IZVEIDOT TABULU tbl KÄ
IZVÄLÄTIES
generate_series(1, 100000) pk -- 100K "fakti"
, (random() * 100)::integer fk_org -- 100 dažÄdi ÄrÄjie atslÄgas
, (random() * 1000)::integer fk_cli; -- 1K dažÄdi ÄrÄjie atslÄgas
IZVEIDOT INDEKSU uz tbl(fk_org, fk_cli); -- viss gandrÄ«z kÄ #2
-- tikai atseviŔķs indekss par fk_cli mÄs jau esam uzskatÄ«juÅ”i par lieku un izdzÄsuÅ”i
IZVÄLÄTIES
*
NO
tbl
KUR
fk_cli = 999 -- un fk_org nav norÄdÄ«ts, lai gan tas tika norÄdÄ«ts indeksÄ agrÄk
IEROBEŽOT 20; 
IzskatÄs, ka viss ir labi, pat pÄc indeksa, bet kaut kÄ aizdomÄ«gi ā katram no 20 nolasÄ«tajiem ierakstiem bija jÄlasÄs pa 4 datu lapÄm, 32KB uz ierakstu ā vai nav pÄrÄk daudz? Un indeksa nosaukums tbl_fk_org_fk_cli_idx aicina uz pÄrdomÄm.
MÄs labojam:
IZVEIDOT INDEKSU uz tbl(fk_cli); 
PÄkÅ”Åi ā 10 reizes ÄtrÄk, un 4 reizes mazÄk lasÄ«t!
Citus neefektÄ«vas indeksu izmantoÅ”anas piemÄrus var redzÄt rakstÄ .
#7: CTE Ć CTE
Kad rodas
PieprasÄ«jumÄ nobriedÄm "treknos" CTE no dažÄdÄm tabulÄm, un pÄc tam nolÄmÄm veikt starp tiem JOIN.
SituÄcija ir aktuÄla versijÄm zem v12 vai pieprasÄ«jumiem ar WITH MATERIALIZED.
KÄ atpazÄ«t
-> CTE SkatīŔana
&& cikli > 10
&& cikli Ć (rindiÅas + RRbF) > 10000
-- pÄrÄk liela dekarta reize CTE
Ieteikumi
RÅ«pÄ«gi analizÄjiet pieprasÄ«jumu ā vai ? ŠŃŠ»Šø Š²ŃŠµ-ŃŠ°ŠŗŠø Š“Š°, ŃŠ¾ uztvert "vÄrdnÄ«cu" hstore/json pÄc modeļa, kas aprakstÄ«ts .
#8: swap Š½Š° Š“ŠøŃŠŗ (temp written)
Kad rodas
VienreizÄja apstrÄde (ŔķiroÅ”ana vai unikÄlizÄcija) liela skaita ierakstu neievietojas atvÄlÄtajÄ atmiÅÄ.
KÄ atpazÄ«t
-> *
&& temp uzrakstīts > 0Ieteikumi
Ja operÄcijÄ izmantotÄ atmiÅas daudzums stipri pÄrsniedz iestatÄ«tÄ parametra , tÄ jÄpielÄgo. Var uzreiz konfigurÄt visiem, vai arÄ« caur SET [LOCAL] konkrÄtam pieprasÄ«jumam/transakcijai.
IsClosed = 0 AND Canceled = 0 AND Enabled = 0
PARÄDÄŖT work_mem;
-- "16MB"
IZVÄLÄTIES
random()
NO
generate_series(1, 1000000)
KÄRTOT
1; 
MÄs labojam:
SET work_mem = '128MB'; -- pirms pieprasÄ«juma izpildes 
Skaidru iemeslu dÄļ, ja tiek izmantota tikai atmiÅa, nevis disks, pieprasÄ«jums tiks izpildÄ«ts daudz ÄtrÄk. TajÄ paÅ”Ä laikÄ, daļa slodzes no HDD tiek noÅemta.
TomÄr jÄÅem vÄrÄ, ka nevar vienmÄr pieŔķirt daudz atmiÅas ā tÄs vienkÄrÅ”i nepietiks visiem.
#9: Š½ŠµŠ°ŠŗŃŃŠ°Š»ŃŠ½Š°Ń ŃŃŠ°ŃŠøŃŃŠøŠŗŠ°
Kad rodas
BÄzÄ tika iekļauti daudzi dati, bet nepaspÄja tos pÄrbaudÄ«t. ANALYZE.
KÄ atpazÄ«t
-> SecÄ«ba skenÄÅ”ana | Bitmap heap skenÄÅ”ana | Indekss [Tikai] skenÄÅ”ana [Atpakaļ]
&& attiecība >> 10Ieteikumi
Paveikt ANALYZE.
SÄ«kÄk Ŕī situÄcija aprakstÄ«ta .
#10: Ā«ŃŃŠ¾-ŃŠ¾ ŠæŠ¾ŃŠ»Š¾ Š½Šµ ŃŠ°ŠŗĀ»
Kad rodas
Notika gaidÄ«Å”ana bloÄ·ÄÅ”anai, ko uzlika konkurÄjoÅ”s pieprasÄ«jums, vai nebija pietiekami CPU/hipervizora resursi.
KÄ atpazÄ«t
-> *
&& (shared hit / 8K) + (shared read / 1K) < laiks / 1000
-- RAM hit = 64MB/s, HDD lasījums = 8MB/s
&& laiks > 100ms -- lasÄ«jÄm maz, bet pÄrÄk ilgi
Ieteikumi
Izmantojiet ÄrÄju sistÄmu monitorÄÅ”anai serveriem attiecÄ«bÄ uz bloÄ·Äjumiem vai neparastu resursu patÄriÅu. Par mÅ«su varianta organizÄÅ”anu Å”Ä procesa ietvaros simtiem serveru mÄs jau stÄstÄ«jÄm un .
Avots: habr.com
