Nga marama ki muri - tūmatanui ki PostgreSQL.
Neke atu i te 6000 nga wa kua whakamahia e koe, engari ko tetahi o nga waahanga kare pea i kitea ko tohu hanganga, he penei te ahua:
Whakarongo ki a raatau, ka "maeneene me te hiraka" o tono. 🙂
Engari he mea nui, he maha nga ahuatanga ka puhoi te tono me te hiakai rawa he angamaheni ka taea te mohio ma te hanganga me nga raraunga o te mahere.
I tenei keehi, kaore ia kaiwhakawhanake takitahi e rapu i tetahi whiringa arotautanga ki a ia ano, ma te whakawhirinaki anake ki tana wheako - ka taea e taatau te korero ki a ia he aha te mea kei konei, he aha te take, me me pehea te whakatata ki te otinga. Koia ta matou mahi.
Kia ata titiro ki enei keehi - he pehea te whakamaaramatanga me te aha nga taunakitanga ka arahi.
Kia pai ake te ruku i a koe ki roto i te kaupapa, ka taea e koe te whakarongo tuatahi ki te poraka e rite ana mai , katahi ka neke ki te tātaritanga mo ia tauira:
#1: taupū "whakaraupapa"
Ina whakatika
Whakaatuhia te nama hou mo te kaihoko "LLC Kolokolchik".
Me pehea te tautuhi
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
tūtohutanga
Taupū whakamahia whakawhänuihia me nga mara tohu.
Hei tauira:
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;
Ka kite tonu koe neke atu i te 100 nga rekoata i tangohia mai i te taurangi, katahi ka tohua katoa, katahi ka waiho ko te mea anake.
Whakatikatika:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ahakoa i runga i taua tauira tuatahi - 8.5 nga wa tere ake me te 33 nga wa iti ake te panui. Ko te nui ake o nga "meka" kei a koe mo ia uara, ka tino kitea te paanga fk.
Ka kite ahau ka mahi taua taurangi hei tohu "prefix" kaore he kino atu i mua mo etahi atu patai fk, kei hea te whakariterite pk karekau karekau (ka taea e koe te panui atu mo tenei ). Tae atu, ka whakarato noa tautoko matua tawahi i runga i tenei mara.
#2: te haupae taupū (BitmapMe)
Ina whakatika
Whakaatuhia nga whakaaetanga katoa mo te kaihoko "LLC Kolokolchik", ka mutu mo "NAO Buttercup".
Me pehea te tautuhi
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scantūtohutanga
waihanga taupū hiato mā ngā āpure mai i ngā mea taketake e rua, ka whakawhānuihia rānei tētahi o ngā āpure me ngā āpure mai i te tuarua.
Hei tauira:
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); -- отбор по конкретной паре
Whakatikatika:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
He iti ake te utu i konei, na te mea ko te Bitmap Heap Scan he tino whai hua ki a ia ano. Engari ahakoa 7 nga wa tere ake me te 2.5 nga wa iti ake te panui.
#3: Hanumi taupū (BitmapOr)
Ina whakatika
Whakaatuhia nga 20 tawhito tuatahi "tatou" me nga tono kaore ano i tohua mo te tukatuka, me o raatau te kaupapa matua.
Me pehea te tautuhi
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scantūtohutanga
Whakamahia UNION [KATOA] ki te whakakotahi i nga paatai mo ia poraka-OR o nga tikanga.
Hei tauira:
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;
Whakatikatika:
(
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, больше и не надо
I whai hua matou i te mea ko nga rekoata e 20 e hiahiatia ana i tae tonu mai ki te poraka tuatahi, na ko te tuarua, me te "nui ake" Bitmap Heap Scan, kaore i mahia - i te mutunga. 22x tere ake, 44x iti ake te panui!
He korero taipitopito mo tenei tikanga arotautanga te whakamahi i nga tauira motuhake ka taea te panui i nga tuhinga и .
Putanga whanui i whakahauhia te kowhiringa i runga i nga taviri maha (kaore ko te takirua const/NULL anake) ka korerohia i roto i te tuhinga .
#4: He maha nga mea koretake ka panuihia e matou
Ina whakatika
Ka rite ki te tikanga, ka puta ina hiahia koe ki te "whakapiri i tetahi atu tātari" ki tetahi tono o mua.
"A kaore koe i te mea kotahi, engari me nga patene whaea-o-peara? " kiriata "The Diamond Arm"
Hei tauira, ko te whakarereke i te mahi i runga ake nei, whakaatu i nga tono tuatahi 20 "arohaehae" mo te tukatuka, ahakoa he aha te kaupapa.
Me pehea te tautuhi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
tūtohutanga
Waihanga [atu] motuhake taupū me te ahua WHERE whakauruhia ranei etahi atu mara ki te taurangi.
Mena he "pateko" te ahuatanga tātari mo o kaupapa - ara e kore e kii te roha rarangi o nga uara kei te heke mai - he pai ake te whakamahi i te tohu WHERE. He pai nga ahuatanga boolean/enum ki tenei waahanga.
Ki te te huru tātari ka taea te tango i nga tikanga rereke, katahi ka pai ake te whakawhanui i te taurangi me enei mara - penei i te ahuatanga me BitmapAnd i runga ake nei.
Hei tauira:
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;
Whakatikatika:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Ka kite koe, kua ngaro katoa te tātari i te mahere, kua riro te tono 5 nga wa tere ake.
#5: tepu iti
Ina whakatika
He maha nga ngana ki te hanga i a koe ake rarangi tukatuka mahi, i te mea he maha nga whakahou / mukunga o nga rekoata i runga i te teepu ka arahi ki te ahua o te maha o nga rekoata "mate".
Me pehea te tautuhi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tūtohutanga
Me mahi ā-ringa i ia te wā KAUPAPA [KATI] te whakatutuki whakangungu auau ranei ma te whakatikatika i ona tawhā, tae atu ki .
I te nuinga o nga wa, ko enei raru ka puta mai i te ngoikore o te hanga patai i te wa e waea ana mai i nga whakaaro pakihi penei i nga korero i korerohia .
Engari me maarama koe ahakoa ko VACUUM FULL kaore pea e awhina i nga wa katoa. Mo enei keehi, he mea tika kia mohio koe ki te algorithm mai i te tuhinga .
#6: Te panui mai i te "waenganui" o te taurangi
Ina whakatika
Te ahua nei he iti ta matou panui, a kua taurangihia nga mea katoa, a kaore matou i tarai i nga tangata i te taikaha - engari he nui noa atu nga wharangi ka panuihia e matou.
Me pehea te tautuhi
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tūtohutanga
Tirohia te hanganga o te taupū i whakamahia me nga mara matua kua tohua i roto i te patai - te nuinga pea karekau he waahanga o te taurangi i tohua. Ko te nuinga ka whai koe ki te hanga i tetahi taurangi rite, engari karekau he mara prefix ranei .
Hei tauira:
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;
He pai nga mea katoa, ahakoa i runga i te taurangi, engari he mea whakapae - mo ia rekoata 20 ka panuihia, me tango e matou nga wharangi raraunga e 4, 32KB mo ia rekoata - kaore he maia? Me te ingoa tohu tbl_fk_org_fk_cli_idx whakahihiri whakaaro.
Whakatikatika:
CREATE INDEX ON tbl(fk_cli);
Inamata - 10 nga wa tere ake, me te 4 nga wa iti ake ki te panui!
Ko etahi atu tauira o nga ahuatanga o te whakamahi koretake o nga tohu ka kitea i roto i te tuhinga .
#7: CTE × CTE
Ina whakatika
I te tono piro "ngako" CTE mai i nga tepu rereke, katahi ka whakatau kia mahia i waenganui i a raatau JOIN.
E tika ana te keehi mo nga putanga kei raro i te v12 me nga tono ranei WITH MATERIALIZED.
Me pehea te tautuhi
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
tūtohutanga
Āta tātari i te tono - me ? Mena ae, na tono "papakupu" i te hstore/json kia rite ki te tauira i whakaahuatia i roto i .
#8: Hurihia ki te kōpae (kua tuhia te wa)
Ina whakatika
Ko te tukatuka kotahi-wa (te wehewehe, te wehewehe ranei) o te maha o nga rekoata kaore e uru ki te mahara kua tohaina mo tenei.
Me pehea te tautuhi
-> *
&& temp written > 0tūtohutanga
Mena ko te nui o te mahara e whakamahia ana e te mahi kaore e nui ake i te uara kua tohua o te tawhā , he mea tika kia whakatika. Ka taea e koe i roto i te whirihora mo te katoa, ka taea ranei e koe SET [LOCAL] mo te tono/tauwhitinga motuhake.
Hei tauira:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Whakatikatika:
SET work_mem = '128MB'; -- перед выполнением запроса
Mo nga tino take, mena ka whakamahia te mahara anake, kaua ko te kopae, ka tere ake te mahi o te patai. I te wa ano, ka tangohia ano tetahi waahanga o te kawenga mai i te HDD.
Engari me mohio koe kaore e taea e koe te toha i nga wa katoa me te maha o nga mahara - kaore e ranea mo te katoa.
#9: nga tatauranga kore whai take
Ina whakatika
I ringihia e ratou he maha ki roto i te papaarangi i te wa kotahi, engari kaore he wa ki te peia atu ANALYZE.
Me pehea te tautuhi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10tūtohutanga
Kawea mai ANALYZE.
Kei te whakaahuahia tenei ahuatanga i roto i nga korero taipitopito .
#10: “Kua raru tetahi mea”
Ina whakatika
I tatari mo te raka i tukuna e tetahi tono whakataetae, he iti rawa ranei nga rauemi taputapu CPU/hypervisor.
Me pehea te tautuhi
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
tūtohutanga
Whakamahia he waho pūnaha aroturuki tūmau mo te aukati, te kohi rawa rereke ranei. Kua korero kee matou mo ta matou putanga mo te whakarite i tenei mahi mo nga rau o nga kaitoro и .
Source: will.com