Nā mahina i hala aku nei - lehulehu i PostgreSQL.
Ua hoʻohana mua ʻoe iā ia ma mua o 6000 mau manawa, akā hoʻokahi hiʻohiʻona i ʻike ʻole ʻia. nā hōʻailona hoʻolālā, e like me kēia:
E hoʻolohe iā lākou, a ʻo kāu mau noi e "lilo a maʻemaʻe." 🙂
Akā ʻo ka mea koʻikoʻi, nui nā kūlana e hoʻolōʻihi i kahi noi a pōloli waiwai maʻamau a hiki ke ʻike ʻia e ke ʻano a me ka ʻikepili o ka hoʻolālā.
I kēia hihia, ʻaʻole pono kēlā me kēia mea hoʻomohala e ʻimi i kahi koho optimization nona iho, e hilinaʻi wale ana i kāna ʻike - hiki iā mākou ke haʻi iā ia i ka mea e hana nei, he aha ke kumu, a pehea e hoʻokokoke ai i kahi hoʻonā. ʻO ia kā mākou i hana ai.
E nānā pono kākou i kēia mau hihia - pehea ka wehewehe ʻana a me nā ʻōlelo aʻoaʻo a lākou e alakaʻi ai.
No ka hoʻomaʻamaʻa maikaʻi ʻana iā ʻoe iho i ke kumuhana, hiki iā ʻoe ke hoʻolohe mua i ka poloka pili mai , a laila e neʻe aku i kahi hōʻuluʻulu kikoʻī o kēlā me kēia laʻana:
#1: kuhikuhi “undersorting”
Ke ala mai
E hōʻike i ka helu hou loa no ka mea kūʻai aku "LLC Kolokolchik".
Pehea e ʻike ai
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
koi
Hoʻohana ʻia ka papa kuhikuhi e hoʻonui me nā kahua ʻano.
Pākuhi:
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;
Hiki iā ʻoe ke ʻike koke ua ʻoi aku ma mua o 100 mau moʻolelo i unuhi ʻia mai ka papa kuhikuhi, a laila hoʻokaʻawale ʻia a pau, a laila koe wale nō.
Hoʻoponopono:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
ʻOiai ma kahi hiʻohiʻona mua - 8.5 manawa ʻoi aku ka wikiwiki a he 33 mau manawa liʻiliʻi ka heluhelu. ʻOi aku ka nui o nā "ʻoiaʻiʻo" i loaʻa iā ʻoe no kēlā me kēia waiwai, ʻoi aku ka maopopo o ka hopena fk.
Hoʻomaopopo wau e hana ana kēlā ʻano kuhikuhi ma ke ʻano he "prefix" index ʻaʻole i ʻoi aku ka hewa ma mua o nā nīnau ʻē aʻe me fk, ma kahi e hoʻokaʻawale ai pk ʻaʻohe a ʻaʻole (hiki iā ʻoe ke heluhelu hou aku e pili ana i kēia ). E komo pū ana, e hāʻawi maʻamau kākoʻo kī haole ma keia kahua.
#2: hui kuhikuhi (BitmapAnd)
Ke ala mai
Hōʻike i nā ʻaelike āpau no ka mea kūʻai aku "LLC Kolokolchik", i hoʻopau ʻia ma ka inoa o "NAO Buttercup".
Pehea e ʻike ai
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scankoi
ho okumu i helu helu hui e nā māla mai nā mea kumu ʻelua a i ʻole e hoʻonui i kekahi o nā mea i loaʻa me nā māla mai ka lua.
Pākuhi:
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); -- отбор по конкретной паре
Hoʻoponopono:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
ʻOi aku ka liʻiliʻi o ka uku ma aneʻi, no ka mea, ʻoi aku ka maikaʻi o ka Bitmap Heap Scan iā ia iho. Akā naʻe 7 manawa ʻoi aku ka wikiwiki a he 2.5 mau manawa liʻiliʻi ka heluhelu.
#3: Hoʻohui i nā kuhikuhi (BitmapOr)
Ke ala mai
E hōʻike i ka 20 mua loa "mākou" a i ʻole nā noi i hāʻawi ʻole ʻia no ka hoʻoponopono ʻana, me kāu mea nui.
Pehea e ʻike ai
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scankoi
Hoʻohana UNIONA [ALL] e hoʻohui i nā subqueries no kēlā me kēia OR-poloka o nā kūlana.
Pākuhi:
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;
Hoʻoponopono:
(
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, больше и не надо
Ua hoʻohana mākou i ka ʻoiaʻiʻo ua loaʻa koke nā moʻolelo 20 i koi ʻia ma ka poloka mua, no laila ʻaʻole i hoʻokō ʻia ka lua, me ka ʻoi aku ka "uku" Bitmap Heap Scan, i ka hopena. 22x ka wikiwiki, 44x ka liʻiliʻi o ka heluhelu!
ʻO kahi moʻolelo kikoʻī e pili ana i kēia ʻano loiloi hoʻohana i nā laʻana kikoʻī hiki ke heluhelu ʻia ma nā ʻatikala и .
Manaʻo laulā kauoha ʻia ke koho ma muli o kekahi mau kī (a ʻaʻole wale ka const/NULL pair) i kūkākūkā ʻia ma ka ʻatikala .
#4: Heluhelu mākou i nā mea pono ʻole
Ke ala mai
E like me ke kānāwai, e kū mai inā makemake ʻoe e "hoʻopili i kahi kānana hou" i kahi noi i loaʻa.
“A ʻaʻohe āu mea like, akā me nā pihi makuahine o ka momi? " kiʻiʻoniʻoni "The Diamond Arm"
No ka laʻana, hoʻololi i ka hana ma luna, e hōʻike i nā noi "koʻikoʻi" mua 20 mua loa no ka hana ʻana, me ka nānā ʻole i ko lākou kumu.
Pehea e ʻike ai
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
koi
E hana [mea hou] kūikawā kuhikuhi me ke kūlana WHERE a i ʻole e hoʻokomo i nā kahua ʻē aʻe ma ka papa kuhikuhi.
Inā "static" ke kūlana kānana no kāu mau kumu - ʻo ia ʻaʻole manaʻo e hoʻonui papa inoa o nā waiwai i ka wā e hiki mai ana - ʻoi aku ka maikaʻi o ka hoʻohana ʻana i kahi index WHERE. Ua kūpono nā kūlana boolean/enum i kēia māhele.
Inā ke kūlana kānana hiki ke lawe i nā manaʻo like ʻole, a laila ʻoi aku ka maikaʻi o ka hoʻonui ʻana i ka index me kēia mau māla - e like me ke kūlana me BitmapAnd ma luna.
Pākuhi:
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;
Hoʻoponopono:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
E like me kāu e ʻike ai, ua nalo loa ka kānana mai ka hoʻolālā, a ua lilo ka noi 5 manawa ʻoi aku ka wikiwiki.
#5: papaʻaina kakaʻikahi
Ke ala mai
ʻO nā hoʻāʻo like ʻole e hana i kāu laina hoʻoponopono hana ponoʻī, ke alakaʻi ka nui o nā mea hou / holoi ʻia o nā moʻolelo ma ka papa i kahi kūlana o ka nui o nā moʻolelo "make".
Pehea e ʻike ai
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
koi
Hana lima mau MĀWAI [PIHA] a i ʻole ka hoʻomaʻamaʻa pinepine ʻana ma ka hoʻoponopono maikaʻi ʻana i kāna mau ʻāpana, me .
I ka hapanui o nā hihia, ʻo ia mau pilikia ke kumu o ka hoʻohui ʻana i nā nīnau maikaʻi ʻole i ke kāhea ʻana mai ka loiloi ʻoihana e like me nā mea i kūkākūkā ʻia .
Akā pono ʻoe e hoʻomaopopo ʻaʻole hiki i ka VACUUM FULL ke kōkua mau. No kēlā mau hihia, pono e hoʻomaʻamaʻa iā ʻoe iho me ka algorithm mai ka ʻatikala .
#6: Heluhelu mai ka "waena" o ka papa kuhikuhi
Ke ala mai
Me he mea lā ua heluhelu liʻiliʻi mākou, a ua helu ʻia nā mea a pau, a ʻaʻole mākou i kānana i kekahi - akā naʻe, heluhelu mākou i nā ʻaoʻao ʻoi aku ka nui ma mua o kā mākou makemake.
Pehea e ʻike ai
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
koi
E nānā pono i ke ʻano o ka index i hoʻohana ʻia a me nā kī kī i kuhikuhi ʻia ma ka nīnau - ʻoi aku paha ʻaʻole i hoʻonohonoho ʻia kahi hapa o ka papa kuhikuhi. Pono paha ʻoe e hana i kahi kuhikuhi like, akā me ka ʻole o nā kahua prefix a i ʻole .
Pākuhi:
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;
Ua maikaʻi nā mea a pau, ʻoiai e like me ka papa kuhikuhi, akā he mea kānalua ia - no kēlā me kēia o nā moʻolelo he 20 i heluhelu ʻia, pono mākou e unuhi i 4 ʻaoʻao o ka ʻikepili, 32KB no kēlā me kēia moʻolelo - ʻaʻole anei he wiwo ʻole? A me ka inoa kuhikuhi tbl_fk_org_fk_cli_idx hoonaauao.
Hoʻoponopono:
CREATE INDEX ON tbl(fk_cli);
Hiki koke - He 10 manawa ʻoi aku ka wikiwiki, a he 4 mau manawa i emi iho ka heluhelu!
Hiki ke ʻike ʻia nā hiʻohiʻona ʻē aʻe o ka hoʻohana pono ʻole ʻana i nā indexes ma ka ʻatikala .
#7: CTE × CTE
Ke ala mai
Ma ke noi kiʻi "momona" CTE mai nā papa like ʻole, a laila hoʻoholo e hana ma waena o lākou JOIN.
Pili ka hihia no nā mana ma lalo o v12 a i ʻole nā noi me WITH MATERIALIZED.
Pehea e ʻike ai
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
koi
E nānā pono i ka noi - a ? Inā ʻae, a laila e hoʻohana i "ka puke wehewehe'ōlelo" ma hstore/json e like me ke kumu hoʻohālike i wehewehe ʻia ma .
#8: hoʻololi i ka disk (kau ʻia ka manawa)
Ke ala mai
ʻAʻole kūpono i ka hoʻomanaʻo hoʻomanaʻo i hoʻokaʻawale ʻia no kēia hana hoʻokahi manawa (ka hoʻokaʻawale a ʻokoʻa paha) o ka nui o nā moʻolelo.
Pehea e ʻike ai
-> *
&& temp written > 0koi
Inā ʻaʻole ʻoi aku ka nui o ka hoʻomanaʻo i hoʻohana ʻia e ka hana ma mua o ka waiwai i ʻōlelo ʻia o ka parameter , pono ke hooponopono. Hiki iā ʻoe ke komo koke i ka config no nā mea a pau, a i ʻole hiki iā ʻoe SET [LOCAL] no kahi noi/kuai kiko'ī.
Pākuhi:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Hoʻoponopono:
SET work_mem = '128MB'; -- перед выполнением запроса
No nā kumu maopopo, inā hoʻohana wale ʻia ka hoʻomanaʻo a ʻaʻole ka disk, a laila e hoʻokō wikiwiki ʻia ka nīnau. I ka manawa like, wehe ʻia kekahi hapa o ka ukana mai ka HDD.
Akā, pono ʻoe e hoʻomaopopo ʻaʻole hiki iā ʻoe ke hoʻokaʻawale i ka nui a me ka nui o ka hoʻomanaʻo - ʻaʻole lawa no kēlā me kēia.
#9: nā helu helu pili ʻole
Ke ala mai
Ua ninini nui lākou i ka waihona i ka manawa hoʻokahi, akā ʻaʻohe manawa e kipaku aku ai ANALYZE.
Pehea e ʻike ai
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10koi
E hoʻokō ANALYZE.
Hōʻike hou ʻia kēia kūlana ma .
#10: "Ua hewa kekahi mea"
Ke ala mai
He kali no ka laka i kau ʻia e kekahi noi hoʻokūkū, a i ʻole lawa ʻole nā kumuwaiwai CPU/hypervisor hardware.
Pehea e ʻike ai
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
koi
E hoʻohana i waho ʻōnaehana nānā kikowaena no ka ālai ʻana a i ʻole ka hoʻohana ʻana i nā kumuwaiwai. Ua kamaʻilio mua mākou e pili ana i kā mākou mana o ka hoʻonohonoho ʻana i kēia kaʻina hana no nā haneli o nā kikowaena и .
Source: www.habr.com