Mga bulan ang milabay - publiko sa PostgreSQL.
Nagamit na nimo kini labaw pa sa 6000 ka beses, apan usa ka magamit nga bahin nga mahimo’g wala namatikdan mao ang mga timailhan sa istruktura, nga tan-awon sama niini:
Paminaw kanila, ug ang imong mga hangyo “mahimong hapsay ug seda.” 🙂
Apan seryoso, daghang mga sitwasyon nga naghimo sa usa ka hangyo nga hinay ug gigutom sa kapanguhaan mga tipikal ug mahimong mailhan pinaagi sa istruktura ug datos sa plano.
Sa kini nga kaso, ang matag indibidwal nga developer dili kinahanglan nga mangita alang sa usa ka kapilian sa pag-optimize sa iyang kaugalingon, nga nagsalig lamang sa iyang kasinatian - mahimo naton isulti kaniya kung unsa ang nanghitabo dinhi, kung unsa ang mahimong hinungdan, ug unsaon pagduol sa solusyon. Mao na among gibuhat.
Atong tan-awon pag-ayo kini nga mga kaso - kung giunsa kini gihubit ug kung unsang mga rekomendasyon ang ilang gidala.
Aron mas maayo nga isubsob ang imong kaugalingon sa hilisgutan, mahimo ka una nga maminaw sa katugbang nga bloke gikan sa , ug unya mopadayon sa usa ka detalyado nga pagtuki sa matag pananglitan:
#1: indeks nga "undersorting"
Sa diha nga mitungha
Ipakita ang pinakabag-o nga invoice para sa kliyente nga "LLC Kolokolchik".
Unsaon pag-ila
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
rekomendasyon
Gigamit ang indeks pagpalapad uban sa mga natad sa pagsunud.
Pananglitan:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Mamatikdan dayon nimo nga labaw pa sa 100 nga mga rekord ang gikuha gikan sa indeks, nga pagkahuman tanan nga nahan-ay, ug unya ang usa ra ang nahabilin.
Pagtul-id:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Bisan sa ingon ka una nga sample - 8.5 ka beses nga mas paspas ug 33 ka beses nga mas gamay nga pagbasa. Ang mas daghang "mga kamatuoran" nga anaa kanimo alang sa matag bili, mas klaro ang epekto fk.
Namatikdan nako nga ang ingon nga indeks molihok ingon usa ka "prefix" nga indeks nga dili labi ka daotan kaysa kaniadto alang sa ubang mga pangutana nga adunay fk, diin gisunod pk wala ug wala (mahimo nimong basahon ang dugang bahin niini ). Lakip, maghatag kini og normal tin-aw nga langyaw nga yawe nga suporta niining natad.
#2: index intersection (BitmapAnd)
Sa diha nga mitungha
Ipakita ang tanan nga mga kasabutan alang sa kliyente "LLC Kolokolchik", gitapos sa ngalan sa "NAO Buttercup".
Unsaon pag-ila
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanrekomendasyon
paghimo komposit nga indeks pinaagi sa mga natad gikan sa pareho nga orihinal o pagpalapad sa usa sa mga naa na nga adunay mga uma gikan sa ikaduha.
Pananglitan:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Pagtul-id:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Ang bayad dinhi mas gamay, tungod kay ang Bitmap Heap Scan epektibo ra sa kaugalingon. Apan bisan pa man 7 ka beses nga mas paspas ug 2.5 ka beses nga mas gamay nga pagbasa.
#3: Paghiusa sa mga indeks (BitmapOr)
Sa diha nga mitungha
Ipakita ang una nga 20 ka labing karaan nga "kami" o wala gi-assign nga mga hangyo alang sa pagproseso, nga ang imo ang prayoridad.
Unsaon pag-ila
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanrekomendasyon
Paggamit UNION [TANANG] sa paghiusa sa mga subquery alang sa matag usa sa OR-block sa mga kondisyon.
Pananglitan:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Pagtul-id:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Gipahimuslan namo ang kamatuoran nga ang tanang 20 ka gikinahanglan nga mga rekord nadawat dayon sa unang bloke, mao nga ang ikaduha, nga adunay mas "mahal" nga Bitmap Heap Scan, wala gani mapatuman - sa katapusan 22x mas paspas, 44x mas gamay nga pagbasa!
Usa ka mas detalyado nga istorya bahin sa kini nga pamaagi sa pag-optimize gamit ang espesipikong mga pananglitan mabasa sa mga artikulo и .
Kinatibuk-ang bersyon gimando nga pagpili base sa daghang mga yawe (ug dili lang ang const/NULL nga pares) ang gihisgutan sa artikulo .
#4: Nagbasa kami daghang wala kinahanglana nga mga butang
Sa diha nga mitungha
Ingon sa usa ka lagda, kini mitungha kung gusto nimo "ilakip ang lain nga filter" sa usa ka naa na nga hangyo.
“Ug wala kay pareho, pero nga adunay mga butones nga inahan-sa-perlas? » pelikula nga "The Diamond Arm"
Pananglitan, ang pag-usab sa buluhaton sa ibabaw, ipakita ang unang 20 ka labing karaan nga "kritikal" nga mga hangyo alang sa pagproseso, bisan unsa pa ang ilang katuyoan.
Unsaon pag-ila
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
rekomendasyon
Paghimo [mas] espesyalidad index nga adunay kondisyon nga WHERE o ilakip ang dugang nga mga natad sa indeks.
Kung ang kondisyon sa pagsala "static" alang sa imong mga katuyoan - kana wala magpasabot sa pagpalapad lista sa mga kantidad sa umaabot - mas maayo nga gamiton ang indeks sa WHERE. Ang lain-laing mga kahimtang sa boolean/enum mohaum kaayo sa kini nga kategorya.
Kung ang kahimtang sa pagsala mahimong makakuha sa lain-laing mga kahulugan, nan mas maayo nga palapdan ang indeks sa kini nga mga natad - sama sa kahimtang sa BitmapAnd sa ibabaw.
Pananglitan:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Pagtul-id:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Sama sa imong makita, ang pagsala hingpit nga nawala gikan sa plano, ug ang hangyo nahimo na 5 ka beses nga mas paspas.
#5: gamay nga lamesa
Sa diha nga mitungha
Nagkalainlain nga mga pagsulay sa paghimo sa imong kaugalingon nga pila sa pagproseso sa buluhaton, kung ang daghang mga pag-update / pagtangtang sa mga rekord sa lamesa nagdala sa usa ka kahimtang sa daghang mga "patay" nga mga rekord.
Unsaon pag-ila
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
rekomendasyon
Buhata nga mano-mano kanunay VACUUM [PUNO] o pagkab-ot sa igo nga kanunay nga pagbansay pinaagi sa pag-ayo sa mga parameter niini, lakip ang .
Sa kadaghanan nga mga kaso, ang ingon nga mga problema gipahinabo sa dili maayo nga komposisyon sa pangutana kung nagtawag gikan sa lohika sa negosyo sama sa gihisgutan sa .
Apan kinahanglan nimong masabtan nga bisan ang VACUUM FULL dili kanunay makatabang. Alang sa ingon nga mga kaso, angay nga pamilyar ang imong kaugalingon sa algorithm gikan sa artikulo .
#6: Pagbasa gikan sa "tunga" sa indeks
Sa diha nga mitungha
Morag gamay ra ang among gibasa, ug na-index ang tanan, ug wala namo gisala ang bisan kinsa nga sobra - apan sa gihapon nagbasa kami labi pa nga mga panid kaysa gusto namon.
Unsaon pag-ila
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
rekomendasyon
Tan-awa pag-ayo ang istruktura sa indeks nga gigamit ug ang yawe nga mga natad nga gipiho sa pangutana - lagmit bahin sa index wala gitakda. Lagmit kinahanglan ka nga maghimo usa ka parehas nga indeks, apan kung wala ang mga natad sa prefix o .
Pananglitan:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Ang tanan ingon og maayo, bisan sumala sa indeks, apan kini sa usa ka paagi kadudahan - alang sa matag usa sa 20 ka mga rekord nga nabasa, kinahanglan namong ibawas ang 4 ka pahina sa datos, 32KB matag rekord - dili ba kana maisugon? Ug ang ngalan sa indeks tbl_fk_org_fk_cli_idx makapukaw sa hunahuna.
Pagtul-id:
CREATE INDEX ON tbl(fk_cli);
Sa kalit- 10 ka beses nga mas paspas, ug 4 ka beses nga mas gamay sa pagbasa!
Ang ubang mga pananglitan sa mga sitwasyon sa dili epektibo nga paggamit sa mga indeks makita sa artikulo .
#7: CTE × CTE
Sa diha nga mitungha
Sa hangyo nakaiskor og "tambok" nga CTE gikan sa lain-laing mga lamesa, ug unya nakahukom sa pagbuhat niini sa taliwala kanila JOIN.
Ang kaso may kalabutan alang sa mga bersyon sa ubos sa v12 o mga hangyo nga adunay WITH MATERIALIZED.
Unsaon pag-ila
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
rekomendasyon
Pag-analisar pag-ayo ang hangyo - ug ? Kung oo, nan gamita ang "diksyonaryo" sa hstore/json sumala sa modelo nga gihulagway sa .
#8: swap sa disk (temp nga gisulat)
Sa diha nga mitungha
Ang usa ka higayon nga pagproseso (pag-sort o uniqueization) sa daghang gidaghanon sa mga rekord dili mohaom sa memorya nga gigahin alang niini.
Unsaon pag-ila
-> *
&& temp written > 0rekomendasyon
Kung ang kantidad sa memorya nga gigamit sa operasyon dili kaayo molapas sa gitakda nga kantidad sa parameter , angay nga tul-iron kini. Mahimo nimo dayon sa config para sa tanan, o mahimo nimo SET [LOCAL] alang sa usa ka piho nga hangyo/transaksyon.
Pananglitan:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Pagtul-id:
SET work_mem = '128MB'; -- перед выполнением запроса
Alang sa klaro nga mga hinungdan, kung ang memorya lamang ang gigamit ug dili ang disk, nan ang pangutana mas paspas nga ipatuman. Sa samang higayon, ang bahin sa load gikan sa HDD gikuha usab.
Apan kinahanglan nimong sabton nga dili ka kanunay makagahin ug daghang memorya - dili gyud igo alang sa tanan.
#9: walay kalabotan nga estadistika
Sa diha nga mitungha
Nagbubo sila og daghan sa database sa makausa, apan wala'y panahon sa pag-abog niini ANALYZE.
Unsaon pag-ila
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10rekomendasyon
Buhata kini ANALYZE.
Kini nga kahimtang gihulagway sa mas detalyado sa .
#10: "may nahitabo nga sayup"
Sa diha nga mitungha
Adunay paghulat alang sa usa ka kandado nga gipahamtang sa usa ka nakigkompetensya nga hangyo, o adunay dili igo nga mga kapanguhaan sa CPU/hypervisor hardware.
Unsaon pag-ila
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
rekomendasyon
Paggamit sa gawas sistema sa pagmonitor server alang sa pagbabag o abnormal nga konsumo sa kapanguhaan. Naghisgot na kami bahin sa among bersyon sa pag-organisar niini nga proseso alang sa gatusan nga mga server и .
Source: www.habr.com