Izinyanga ezedlulile
Usuvele uyisebenzise izikhathi ezingaphezu kuka-6000, kodwa isici esisodwa esiwusizo okungenzeka asizange sinakwe izinkomba zesakhiwo, okubukeka kanjena:
Zilalele, futhi izicelo zakho ‘ziyoba bushelelezi futhi zibe silika. 🙂
Kodwa ngokungathí sina, izimo eziningi ezenza isicelo singasheshi futhi silambele izinsiza zijwayelekile futhi zingabonwa ngesakhiwo nedatha yohlelo.
Kulokhu, umthuthukisi ngamunye akudingeki abheke inketho yokuthuthukisa eyedwa, encika kokuhlangenwe nakho kwakhe kuphela - singamtshela ukuthi kwenzekani lapha, kungaba yini isizathu, futhi indlela yokubhekana nesixazululo. Yilokho esakwenza.
Ake sibhekisise lezi zimo - ukuthi zichazwa kanjani nokuthi yiziphi izincomo eziholela kuzo.
Ukuze uzicwilise kangcono esihlokweni, ungaqala ulalele ibhulokhi elihambisanayo ukusuka
index "undersorting" inkomba yempambanondlela (BitmapAnd) ukuhlanganisa izinkomba (BitmapOr) sifunda izinto eziningi ezingadingekile itafula elingenalutho ukufunda kusukela "phakathi" kwenkomba I-CTE × CTE shintshela kudiski (isikhathi esibhaliwe) izibalo ezingabalulekile "Kukhona into engahambanga kahle"
#1: inkomba "undersorting"
Nini
Bonisa i-invoyisi yakamuva yeklayenti "LLC Kolokolchik".
Indlela yokubona
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Izincomo
Inkomba esetshenzisiwe nweba ngezinkambu zokuhlunga.
Isibonelo:
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;
Ungabona ngokushesha ukuthi amarekhodi angaphezu kwe-100 akhishwe ohlwini, okwabe sekuhlungwa wonke, kwabe sekusele elilodwa kuphela.
Ukulungisa:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ngisho nakusampula yakudala enjalo - 8.5 izikhathi ngokushesha futhi 33 izikhathi ezimbalwa ukufunda. Lapho uba “namaqiniso” engeziwe ngenani ngalinye, umphumela uba sobala kakhulu fk
.
Ngiyaqaphela ukuthi inkomba enjalo izosebenza njengenkomba “yesiqalo” ingebi kakhulu kunangaphambili kweminye imibuzo fk
, lapho hlunga khona pk
yayingekho futhi yayingekho (ungafunda kabanzi ngalokhu
#2: ukuphambana kwenkomba (BitmapAnd)
Nini
Bonisa zonke izivumelwano zeklayenti "LLC Kolokolchik", eziphethwe egameni le-"NAO Buttercup".
Indlela yokubona
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan
Izincomo
Dala inkomba eyinhlanganisela ngezinkambu ezisuka kuzo zombili ezangempela noma wandise eyodwa kwezikhona ngezinkambu ukusuka kweyesibili.
Isibonelo:
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); -- отбор по конкретной паре
Ukulungisa:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Inkokhelo lapha incane, njengoba i-Bitmap Heap Scan isebenza ngokwayo. Kodwa noma kunjalo 7 izikhathi ngokushesha futhi 2.5 izikhathi ezimbalwa ukufunda.
#3: Hlanganisa izinkomba (BitmapOr)
Nini
Bonisa "thina" endala kakhulu engu-20 noma izicelo ezingabelwe zokucutshungulwa, ezakho zize kuqala.
Indlela yokubona
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan
Izincomo
Sebenzisa Inyunyana [KONKE] ukuhlanganisa imibuzo emincane ngayinye ye-OR-blocks yemibandela.
Isibonelo:
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;
Ukulungisa:
(
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, больше и не надо
Sisebenzise leli thuba lokuthi wonke amarekhodi angama-20 adingekayo atholwe ngokushesha ebhulokhini lokuqala, ngakho elesibili, eline-Bitmap Heap Scan “emba eqolo”, alizange libulawe - ekugcineni. 22x ngokushesha, 44x ukufundwa okumbalwa!
Indaba enemininingwane eminingi ngale ndlela yokuthuthukisa ngokusebenzisa izibonelo ezithile ingafundwa ezihlokweni
I-PostgreSQL Antipatterns: ama-JOIN ayingozi nama-ORs иAma-PostgreSQL Antipatterns: inganekwane yokuphindaphinda kabusha kokusesha ngegama, noma “Ukuthuthukisa emuva naphambili” .Inguqulo ejwayelekile ukukhetha okukhethiwe ngokusekelwe kokhiye abambalwa (hhayi nje i-const/NULL pair) kuxoxwa ngayo esihlokweni
I-SQL HowTo: ukubhala i-loop yesikhashana ngqo embuzweni, noma "Izinyathelo ezintathu zokuqala" .
#4: Sifunda izinto eziningi ezingadingekile
Nini
Njengomthetho, kuvela uma ufuna "ukunamathisela esinye isihlungi" esicelweni esivele sikhona.
“Futhi awunayo efanayo, kodwa ngezinkinobho zikamama weparele? » ifilimu "The Diamond Arm"
Isibonelo, ukulungisa umsebenzi ongenhla, bonisa izicelo zokuqala ezingama-20 “ezibucayi” zokucutshungulwa, kungakhathaliseki ukuthi iyini injongo yazo.
Indlela yokubona
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Izincomo
Dala [okwengeziwe] okukhethekile inkomba enesimo sokuthi LAPHO noma ufake izinkambu ezengeziwe kunkomba.
Uma isimo sokuhlunga sithi "static" ngezinjongo zakho - okungukuthi akusho ukwanda uhlu lwamanani esikhathini esizayo - kungcono ukusebenzisa inkomba yokuthi KUPHI. Izimo ezihlukahlukene ze-boolean/enum zingena kahle kulesi sigaba.
Uma isimo sokuhlunga ingathatha izincazelo ezahlukene, ngakho-ke kungcono ukwandisa inkomba ngalezi zinkambu - njengakusimo nge-BitmapAnd ngenhla.
Isibonelo:
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;
Ukulungisa:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Njengoba ubona, ukuhlunga kunyamalale ngokuphelele ohlelweni, futhi isicelo sesiphenduke 5 izikhathi ngokushesha.
#5: itafula elincane
Nini
Imizamo ehlukahlukene yokuzakhela owakho umugqa wokucubungula umsebenzi, lapho inani elikhulu lezibuyekezo/ukususwa kwamarekhodi etafuleni kuholela esimweni senani elikhulu lamarekhodi “afile”.
Indlela yokubona
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Izincomo
Kwenze ngokwakho njalo IVACUUM [KUGCWELE] noma uthole ukuqeqeshwa njalo ngokwanele
Ezimweni eziningi, izinkinga ezinjalo zibangelwa ukubunjwa kwemibuzo okungekuhle lapho ufona usuka kungqondongqondo yebhizinisi njengalezo okuxoxwe ngazo kuyo
I-PostgreSQL Antipatterns: ukulwa nezindimbane “zabafileyo” .Kodwa udinga ukuqonda ukuthi ngisho ne-VACUUM FULL ingase ingasizi ngaso sonke isikhathi. Ezimweni ezinjalo, kufanelekile ukuzijwayeza nge-algorithm evela esihlokweni
I-DBA: lapho i-VACUUM ihluleka, sihlanza itafula mathupha .
#6: Ukufunda kusuka “phakathi” kwenkomba
Nini
Kubonakala sengathi sifunde kancane, futhi yonke into yayikhonjiwe, futhi asihlunga muntu ngokweqile - kodwa noma kunjalo sifunda amakhasi amaningi kakhulu kunalokho ebesingathanda.
Indlela yokubona
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Izincomo
Bhekisisa kahle ukwakheka kwenkomba esetshenzisiwe kanye nezinkambu ezibalulekile ezishiwo embuzweni - okungenzeka kakhulu ingxenye yenkomba ayisethiwe. Kungenzeka ukuthi udale inkomba efanayo, kodwa ngaphandle kwezinkambu zesiqalo noma
Isibonelo:
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;
Konke kubonakala kuhamba kahle, noma ngokusho kwenkomba, kodwa ngandlela thize kuyasolisa - kumarekhodi angama-20 afundiwe, bekumele sikhiphe amakhasi wedatha angu-4, ama-32KB ngerekhodi ngalinye - akusona isibindi leso? Negama lenkomba tbl_fk_org_fk_cli_idx
eshukumisa ingqondo.
Ukulungisa:
CREATE INDEX ON tbl(fk_cli);
Ngokushesha - 10 izikhathi ngokushesha, futhi izikhathi 4 kancane ukufunda!
Ezinye izibonelo zezimo zokungasebenzi kahle kwezinkomba zingabonakala esihlokweni
I-DBA: ukuthola izinkomba ezingenamsebenzi .
#7: CTE × CTE
Nini
Ngesicelo uthole "amafutha" CTE kusuka ematafuleni ahlukene, wabe esenquma ukukwenza phakathi kwabo JOIN
.
Icala lihambisana nezinguqulo ezingaphansi kwe-v12 noma izicelo nge WITH MATERIALIZED
.
Indlela yokubona
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Izincomo
Hlaziya ngokucophelela isicelo - futhi
#8: shintshela kudiski (ithembu libhaliwe)
Nini
Ukucubungula kwesikhathi esisodwa (ukuhlunga noma ukuhluka) kwenani elikhulu lamarekhodi akungeni kumemori eyabelwe lokhu.
Indlela yokubona
-> *
&& temp written > 0
Izincomo
Uma inani lememori elisetshenziswe umsebenzi lingadluli kakhulu inani elishiwo lepharamitha SET [LOCAL]
ngesicelo/ukuthenga okuthile.
Isibonelo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ukulungisa:
SET work_mem = '128MB'; -- перед выполнением запроса
Ngezizathu ezisobala, uma inkumbulo kuphela isetshenziswa hhayi idiski, khona-ke umbuzo uzokwenziwa ngokushesha okukhulu. Ngesikhathi esifanayo, ingxenye yomthwalo ovela ku-HDD nayo isusiwe.
Kodwa udinga ukuqonda ukuthi ngeke ukwazi njalo ukwaba inkatho nenkumbulo eningi - ngeke nje yanele wonke umuntu.
#9: izibalo ezingabalulekile
Nini
Bathululela okuningi ku-database ngesikhathi esisodwa, kodwa babengenaso isikhathi sokuyixosha ANALYZE
.
Indlela yokubona
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
Izincomo
Kwenze ANALYZE
.
Lesi simo sichazwe kabanzi ku
I-PostgreSQL Antipatterns: izibalo ziyikho konke .
#10: "kukhona okungahambanga kahle"
Nini
Kube nokulinda ukukhiya okubekwe yisicelo esiqhudelanayo, noma bekungekho izinsiza zehadiwe ye-CPU/hypervisor eyanele.
Indlela yokubona
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Izincomo
Sebenzisa kwangaphandle uhlelo lokuqapha iseva yokuvimbela noma ukusetshenziswa okungavamile kwensiza. Sesivele sikhulume ngenguqulo yethu yokuhlela le nqubo kumakhulu amaseva
Source: www.habr.com