Plizyè mwa de sa - piblik pou PostgreSQL.
Ou te deja itilize li plis pase 6000 fwa, men yon karakteristik sou la men ki ka pase inapèsi se endikasyon estriktirèl, ki sanble yon bagay tankou sa a:
Koute yo, epi demann ou yo pral “vin lis ak swa”. 🙂
Men, seryezman, anpil sitiyasyon ki fè yon demann ralanti ak resous-grangou yo tipik epi yo ka rekonèt pa estrikti ak done plan an.
Nan ka sa a, chak pwomotè endividyèl pa bezwen chèche yon opsyon optimize poukont li, konte sèlman sou eksperyans li - nou ka di l 'sa k ap pase isit la, ki sa ki ta ka rezon an, ak kijan pou apwoche yon solisyon. Se sa nou te fè.
Ann pran yon gade pi pre nan ka sa yo - ki jan yo defini ak ki rekòmandasyon yo mennen nan.
Pou pi byen plonje tèt ou nan sijè a, ou ka premye koute blòk ki koresponn lan soti nan , epi sèlman Lè sa a, ale nan yon analiz detaye sou chak egzanp:
#1: endèks "undersorting"
Lè fè
Montre dènye fakti pou kliyan an "LLC Kolokolchik".
Ki jan yo idantifye
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Rekòmandasyon
Endèks itilize elaji ak jaden sòt.
Egzanp:
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;
Ou ka imedyatman remake ke plis pase 100 dosye yo te soustraksyon nan endèks la, ki te Lè sa a, tout klase, ak Lè sa a, youn nan sèlman te rete.
Korije:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Menm sou tankou yon echantiyon primitif - 8.5 fwa pi vit ak 33 fwa mwens lekti. Plis "reyalite" ou genyen pou chak valè, efè a pi evidan fk.
Mwen sonje ke yon endèks konsa ap travay kòm yon "prefiks" endèks pa pi mal pase anvan pou lòt requêtes ak fk, kote sòt pa pk pa te genyen e pa genyen (ou ka li plis sou sa ). Ki gen ladan, li pral bay nòmal sipò kle etranje eksplisit sou teren sa a.
#2: entèseksyon endèks (BitmapAnd)
Lè fè
Montre tout akò pou kliyan an "LLC Kolokolchik", konkli sou non "NAO Buttercup".
Ki jan yo idantifye
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRekòmandasyon
kreye endèks konpoze pa jaden ki soti nan tou de orijinal yo oswa elaji youn nan sa yo ki deja egziste ak jaden ki soti nan dezyèm lan.
Egzanp:
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); -- отбор по конкретной паре
Korije:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Peman an isit la pi piti, depi Bitmap Heap Scan se byen efikas poukont li. Men de tout fason 7 fwa pi vit ak 2.5 fwa mwens lekti.
#3: Fizyone endèks (BitmapOr)
Lè fè
Montre premye 20 premye "nous" ki pi ansyen oswa demann ki pa asiyen pou trete, ak pa w la an priyorite.
Ki jan yo idantifye
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRekòmandasyon
Sèvi ak UNION [TOUT] pou konbine subqueries pou chak nan OR-blòk kondisyon yo.
Egzanp:
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;
Korije:
(
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, больше и не надо
Nou te pwofite lefèt ke tout 20 dosye obligatwa yo te resevwa imedyatman nan premye blòk la, kidonk dezyèm lan, ak pi "chè" Bitmap Heap Scan, pa te menm egzekite - nan fen an. 22 fwa pi vit, 44 fwa mwens lekti!
Yon istwa pi detaye sou metòd optimize sa a lè l sèvi avèk egzanp espesifik ka li nan atik yo и .
Vèsyon jeneralize seleksyon òdone ki baze sou plizyè kle (e pa sèlman pè a const/NULL) diskite nan atik la .
#4: Nou li anpil bagay ki pa nesesè
Lè fè
Kòm yon règ, li rive lè ou vle "tache yon lòt filtè" nan yon demann ki deja egziste.
"Epi ou pa gen menm bagay la, men ak bouton manman pèl? » fim "The Diamond Arm"
Pou egzanp, modifye travay ki anwo a, montre premye 20 pi ansyen demann "kritik" pou trete, kèlkeswa objektif yo.
Ki jan yo idantifye
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Rekòmandasyon
Kreye [plis] espesyalize endèks ak kondisyon WHERE oswa mete lòt jaden nan endèks la.
Si kondisyon filtre a "estatik" pou rezon ou - sa vle di pa vle di ekspansyon lis valè nan tan kap vini an - li se pi bon yo sèvi ak yon endèks WHERE. Plizyè estati boolean/enum anfòm byen nan kategori sa a.
Si kondisyon filtraj la ka pran diferan siyifikasyon, Lè sa a, li pi bon yo elaji endèks la ak jaden sa yo - tankou nan sitiyasyon an ak BitmapAnd pi wo a.
Egzanp:
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;
Korije:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kòm ou ka wè, filtraj te konplètman disparèt nan plan an, epi demann lan te vin tounen 5 fwa pi vit.
#5: tab rar
Lè fè
Divès tantativ pou kreye pwòp keu pwosesis travay ou a, lè yon gwo kantite mizajou / efase dosye sou tab la mennen nan yon sitiyasyon nan yon gwo kantite "mouri" dosye.
Ki jan yo idantifye
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekòmandasyon
Fè manyèlman regilyèman VACUUM [PLEN] oswa reyalize fòmasyon ase souvan pa amann-akor paramèt li yo, ki gen ladan .
Nan pifò ka yo, pwoblèm sa yo ki te koze pa konpozisyon rechèch pòv lè w ap rele nan lojik biznis tankou sa yo diskite nan .
Men, ou bezwen konprann ke menm VACUUM FULL ka pa toujou ede. Pou ka sa yo, li vo familyarize tèt ou ak algorithm nan atik la .
# 6: Lekti nan "mitwayen an" nan endèks la
Lè fè
Li sanble ke nou li yon ti kras, ak tout bagay te endèks, epi nou pa t 'filtre nenpòt moun ki depase - men toujou nou li siyifikativman plis paj pase nou ta renmen.
Ki jan yo idantifye
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekòmandasyon
Pran yon gade byen nan estrikti a nan endèks la itilize ak jaden kle yo espesifye nan rechèch la - gen plis chans yon pati nan endèks la pa espesifye. Gen plis chans ou pral gen yo kreye yon endèks menm jan an, men san yo pa jaden yo prefiks oswa .
Egzanp:
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;
Tout bagay sanble ap byen, menm dapre endèks la, men li nan yon jan kanmenm sispèk - pou chak nan 20 dosye yo li, nou te oblije soustraksyon 4 paj done, 32KB pou chak dosye - èske sa pa fonse? Ak non endèks la tbl_fk_org_fk_cli_idx ki fè reflechi.
Korije:
CREATE INDEX ON tbl(fk_cli);
Menm lè a - 10 fwa pi vit, ak 4 fwa mwens pou li!
Lòt egzanp sitiyasyon nan itilizasyon efikas nan endèks ka wè nan atik la .
#7: CTE × CTE
Lè fè
Nan demann bay nòt CTE "grès". soti nan tab diferan, ak Lè sa a, deside fè li ant yo JOIN.
Ka a enpòtan pou vèsyon ki anba a v12 oswa demann ak WITH MATERIALIZED.
Ki jan yo idantifye
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Rekòmandasyon
Ak anpil atansyon analize demann lan - ak ? Si wi, lè sa a aplike "diksyonè" nan hstore/json dapre modèl ki dekri nan .
# 8: echanj sou disk (temp ekri)
Lè fè
Pwosesis yon sèl fwa (triye oswa inikizasyon) nan yon gwo kantite dosye pa anfòm nan memwa a resevwa lajan pou sa a.
Ki jan yo idantifye
-> *
&& temp written > 0Rekòmandasyon
Si kantite lajan an nan memwa itilize pa operasyon an pa depase anpil valè espesifye nan paramèt la , li vo korije li. Ou ka imedyatman nan konfigirasyon an pou tout moun, oswa ou ka atravè SET [LOCAL] pou yon demann/tranzaksyon espesifik.
Egzanp:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korije:
SET work_mem = '128MB'; -- перед выполнением запроса
Pou rezon evidan, si se sèlman memwa itilize epi yo pa disk, Lè sa a, rechèch la pral egzekite pi vit. An menm tan an, yon pati nan chaj la soti nan HDD a tou retire.
Men, ou bezwen konprann ke ou pa pral toujou kapab asiyen anpil ak anpil memwa - gen tou senpleman pa pral ase pou tout moun.
#9: estatistik petinan
Lè fè
Yo vide anpil nan baz done a nan yon fwa, men yo pa t 'gen tan kondwi li ale ANALYZE.
Ki jan yo idantifye
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rekòmandasyon
Pote li soti ANALYZE.
Sitiyasyon sa a dekri an plis detay nan .
# 10: "yon bagay ale mal"
Lè fè
Te gen yon rete tann pou yon seri enpoze pa yon demann konpetisyon, oswa te gen ensifizan CPU / ipèvizè resous pyès ki nan konpitè.
Ki jan yo idantifye
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Rekòmandasyon
Sèvi ak ekstèn sistèm siveyans sèvè pou bloke oswa konsomasyon resous nòmal. Nou te deja pale sou vèsyon nou an nan òganize pwosesis sa a pou dè santèn de serveurs и .
Sous: www.habr.com