Ọtụtụ ọnwa gara aga - ọha na PostgreSQL.
Ị na-ejila ya ihe karịrị ugboro 6000 kemgbe ahụ, mana otu n'ime ihe ndị dị mma nwere ike ịhụtaghị ya. ntule ihe owuwu, nke dị ka nke a:
Gee ha ntị na arịrịọ gị "ga-abụkwa ire ụtọ silky". 🙂
Mana nke ọma, ọtụtụ ọnọdụ ndị na-eme ka a rịọ ngwa ngwa na “eribiga nri ókè” n'ihe gbasara akụrụngwa, bụ ndị a na-ahụkarị ma nwee ike ịmata ya site na nhazi na data nke atụmatụ ahụ.
N'okwu a, onye nrụpụta ọ bụla agaghị achọ nhọrọ njikarịcha n'onwe ya, dabere naanị na ahụmịhe nke ya - anyị nwere ike ịgwa ya ihe na-eme ebe a, ihe nwere ike ịbụ ihe kpatara ya, na ka esi wepụta ihe ngwọta. Nke bụ ihe anyị mere.
Ka anyị lebakwuo anya n'okwu ndị a - ka esi akọwa ha na ndụmọdụ ndị ha na-eduga.
Maka imikpu nke ọma na isiokwu a, ị nwere ike buru ụzọ gee ntị na ngọngọ kwekọrọ , na naanị wee gaa na nyocha zuru ezu nke ihe atụ ọ bụla:
#1: index "undersorting"
Mgbe ebilitere
Gosi akwụkwọ ọnụahịa ikpeazụ maka onye ahịa "LLC Kolokolchik".
Otu esi amata
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
na-atụ aro
A na-eji index gbasaa na ụdị ubi.
Ihe nlele:
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;
Ị nwere ike chọpụta ozugbo na ihe ndekọ index wepụrụ ihe karịrị 100 ndekọ, bụ nke a na-ahazi mgbe ahụ, ma ọ bụ naanị otu fọdụrụ.
Anyị na-edozi:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ọbụlagodi na ụdị ihe atụ ochie - 8.5x ngwa ngwa yana 33x ole na ole na-agụ. Mmetụta ga-edo anya karị, ka "eziokwu" ị nwere maka uru ọ bụla. fk.
Achọpụtara m na nrịbama dị otú ahụ ga-arụ ọrụ dị ka ndeksi "prefix" adịghị njọ karịa nke gara aga maka ajụjụ ndị ọzọ fk, ebe nhazi site pk abụghị ma ọ bụghị (ị nwere ike ịgụkwu banyere nke a ). Karịsịa, ọ ga-enye nkịtị nkwado igodo mba ofesi doro anya site na ubi a.
# 2: njikọ index (BitmapAnd)
Mgbe ebilitere
Gosi nkwekọrịta niile maka onye ahịa "LLC Kolokolchik" kwubiri n'aha "NJSC Lyutik".
Otu esi amata
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanna-atụ aro
Mepụta a nchịkọta ihe mejupụtara site na mpaghara sitere na isi mmalite abụọ ma ọ bụ gbasaa otu n'ime ubi ndị dị na nke abụọ.
Ihe nlele:
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); -- отбор по конкретной паре
Anyị na-edozi:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
N'ebe a, uru ahụ dị ntakịrị, ebe ọ bụ na Bitmap Heap Scan na-arụ ọrụ nke ọma n'onwe ya. Mana agbanyeghị 7x ngwa ngwa yana 2.5x ole na ole na-agụ.
#3: Na-ejikọta Index (BitmapOr)
Mgbe ebilitere
Gosi mbụ 20 kacha ochie arịrịọ "nkeonwe" ma ọ bụ ekenyeghị maka nhazi, na-ebute ụzọ.
Otu esi amata
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanna-atụ aro
Jiri UNION [NIILE] ijikọ subqueries maka nke ọ bụla n'ime ọnọdụ ma ọ bụ blocks.
Ihe nlele:
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;
Anyị na-edozi:
(
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, больше и не надо
Anyị jiri eziokwu ahụ nweta ihe ndekọ 20 niile dị mkpa ozugbo na ngọngọ nke mbụ, yabụ nke abụọ, nke nwere “ọnụ ọnụ” Bitmap Heap Scan, emeghịdị igbu ya - n'ihi ya. 22x ngwa ngwa, 44x ole na ole na-agụ!
Akụkọ zuru oke gbasara usoro nkwalite a na ihe atụ enwere ike ịgụ na akụkọ и .
Ụdị agbakọtara nyere iwu nhọrọ site na ọtụtụ igodo (ma ọ bụghị naanị maka const/NULL ụzọ) ka a tụlere n'isiokwu .
#4: Anyị na-agụ nke ukwuu
Mgbe ebilitere
Dị ka a na-achị, ọ na-eme mgbe ịchọrọ "ịgbakwunye nzacha ọzọ" na arịrịọ dị adị.
"Ma ị nweghị otu, mana na bọtịnụ pearl? » ihe nkiri "Diamond Hand"
Dịka ọmụmaatụ, ịmegharị ọrụ dị n'elu, gosi arịrịọ 20 mbụ "dị oke mkpa" maka nhazi, n'agbanyeghị nzube ha.
Otu esi amata
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
na-atụ aro
Mepụta ọpụrụiche index nwere nkebi ahịrịokwu WHERE ma ọ bụ tinye mpaghara agbakwunyere na ndeksi.
Ọ bụrụ na ọnọdụ nzacha ahụ bụ "static" maka ọrụ gị - ya bụ anaghị agụnye mgbasawanye ndepụta nke ụkpụrụ n'ọdịnihu - ọ ka mma iji akara ngosi WHERE. Ụdị boolean/enum dị iche iche dabara nke ọma na ngalaba a.
Ọ bụrụ na ọnọdụ filtration nwere ike were ụkpụrụ dị iche iche, ọ ka mma iji gbasaa index na mpaghara ndị a - dị ka ọ dị na ọnọdụ BitmapAnd n'elu.
Ihe nlele:
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;
Anyị na-edozi:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Dị ka ị pụrụ ịhụ, nzacha site na atụmatụ ahụ kpamkpam, na arịrịọ aghọwo 5 ugboro ngwa ngwa.
# 5: tebụl na-enweghị atụ
Mgbe ebilitere
Mgbalị dị iche iche iji mee kwụ n'ahịrị nhazi ọrụ nke gị, mgbe ọnụ ọgụgụ dị ukwuu nke mmelite / nhichapụ nke ndekọ na tebụl na-eduga n'ọnọdụ nke ọnụ ọgụgụ buru ibu nke ndekọ "nwụrụ anwụ".
Otu esi amata
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
na-atụ aro
Jiri aka mee ihe mgbe niile VACUUM [FULL] ma ọ bụ nweta nhazi oge niile nke ọma site n'imezigharị paramita ya nke ọma, gụnyere .
N'ọtụtụ oge, nsogbu ndị dị otú ahụ na-ebute site na nhazi ajụjụ adịghị mma mgbe a na-akpọ ya site na mgbagha azụmahịa, dị ka ndị a tụlere na ya .
Mana anyị ga-aghọta na ọbụlagodi VACUUM FULL enweghị ike inye aka mgbe niile. Maka ikpe ndị dị otú ahụ, ị kwesịrị ịmara onwe gị na algọridim sitere na isiokwu ahụ. .
#6: ịgụ site na "n'etiti" ndeksi
Mgbe ebilitere
Ọ dị ka ha gụrụ ntakịrị, wee depụta ihe niile, ma ha enyochaghị onye ọ bụla - mana ka ọ dị, a gụrụ ọtụtụ peeji karịa ka anyị ga-achọ.
Otu esi amata
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
na-atụ aro
Leba anya nke ọma na nhazi nke ndeksi ejiri na mpaghara igodo akọwapụtara na ajụjụ a - o yikarịrị, edobeghi akụkụ index. O yikarịrị ka ị ga-achọ ịmepụta ndeksi yiri ya, mana na-enweghị oghere prefix, ma ọ bụ .
Ihe nlele:
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;
Ihe niile dị ka ọ dị mma, ọbụlagodi n'ihe gbasara ndeksi, mana n'ụzọ ụfọdụ na-enyo enyo - maka ndekọ ndekọ 20 ọ bụla, a ga-ewepụrịrị ibe 4 nke data, 32KB kwa ndekọ - ọ bụghị nkwuwa okwu? Ee na aha ndeksi tbl_fk_org_fk_cli_idx na-eduga n'echiche.
Anyị na-edozi:
CREATE INDEX ON tbl(fk_cli);
Na mberede - 10 ugboro ọsọ ọsọ na 4 ugboro obere ka ịgụ!
Maka ọmụmaatụ ndị ọzọ nke iji ndeksi eme ihe na-adịghị mma, lee akụkọ a .
#7: CTE × CTE
Mgbe ebilitere
Na arịrịọ enwetara "abụba" CTE site na tebụl dị iche iche, wee kpebie ime n'etiti ha JOIN.
Ikpe ahụ dabara maka ụdịdị dị n'okpuru v12 ma ọ bụ arịrịọ nwere WITH MATERIALIZED.
Otu esi amata
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
na-atụ aro
Jiri nlezianya nyochaa arịrịọ ahụ ? Ọ bụrụ ee, yabụ tinye "akwụkwọ ọkọwa okwu" na hstore/json dị ka ihe nlereanya akọwara na .
#8: gbanwee na diski (ederede temprenụ)
Mgbe ebilitere
Nhazi otu oge (nhazi ma ọ bụ iche) nke ọnụ ọgụgụ dị ukwuu nke ndekọ adịghị adaba na ebe nchekwa ekenyere maka nke a.
Otu esi amata
-> *
&& temp written > 0na-atụ aro
Ọ bụrụ na ọnụ ọgụgụ ebe nchekwa nke ọrụ ahụ na-eji agafeghị oke uru setịpụrụ nke oke , ekwesịrị idozi ya. Ị nwere ike ozugbo na nhazi maka onye ọ bụla, ma ọ bụ ị nwere ike ịgafe SET [LOCAL] maka otu arịrịọ / azụmahịa.
Ihe nlele:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Anyị na-edozi:
SET work_mem = '128MB'; -- перед выполнением запроса
Maka ihe doro anya, ọ bụrụ na naanị ebe nchekwa na-eji ma ọ bụghị diski, mgbe ahụ ajụjụ ahụ ga-adị ngwa ngwa. N'otu oge ahụ, a na-ewepụkwa akụkụ nke ibu ahụ na HDD.
Mana ịkwesịrị ịghọta na ikenye ọtụtụ ebe nchekwa agaghị arụ ọrụ mgbe niile - ọ gaghị ezuru onye ọ bụla.
#9: Ndekọ ọnụ ọgụgụ na-adịghị mkpa
Mgbe ebilitere
A wụsara ọtụtụ ihe na ntọala ozugbo, ma ha enweghị oge ịchụpụ ya ANALYZE.
Otu esi amata
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10na-atụ aro
Na-emefu otu ihe ahụ ANALYZE.
A kọwara ọnọdụ a n'ụzọ zuru ezu na .
#10: "Ọ dị ihe na-aga nke ọma"
Mgbe ebilitere
Enwere mkpọchi na-echere arịrịọ asọmpi, ma ọ bụ enweghị akụrụngwa akụrụngwa CPU/hypervisor zuru ezu.
Otu esi amata
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
na-atụ aro
Jiri mpụga usoro nlekota oru ihe nkesa maka igbochi ma ọ bụ oriri akụrụngwa na-adịghị mma. Anyị ekwuworị banyere ụdị nhazi nke usoro a maka ọtụtụ narị sava. и .
isi: www.habr.com