Mwedzi yakati wandei yapfuura - paruzhinji kuPostgreSQL.
Wakatozvishandisa kanopfuura zviuru zvitanhatu kubva ipapo, asi chimwe chezvinhu zvinobatsira chingave chisina kucherechedzwa. zvimiro zvendangariro, izvo zvinotaridzika seizvi:
Teerera kwavari uye zvikumbiro zvako zvichava "silky smooth". 🙂
Asi zvakanyanya, mamiriro mazhinji anoita kuti chikumbiro chinonoka uye "chikara" maererano nezviwanikwa, zvakajairwa uye zvinogona kuzivikanwa nechimiro uye data yechirongwa.
Muchiitiko ichi, mugadziri wega wega haafanire kutsvaga sarudzo yekuvandudza ari ega, achivimba neruzivo rwake chete - tinogona kumuudza zviri kuitika pano, chingave chikonzero nei, uye nzira yekubuda nemhinduro. Ndizvo zvatakaita.
Ngatitarisei zvakanyanya nyaya idzi - kuti dzinotsanangurwa sei uye kuti ndedzipi kurudziro dzavanotungamira.
Kuti unyudzwe zvirinani mumusoro wenyaya, unogona kutanga wateerera kune inoenderana block kubva , uye chete woenda kuongororo yakadzama yemuenzaniso wega wega:
#1: index "undersorting"
Kana yamuka
Ratidza invoice yekupedzisira yemutengi "LLC Kolokolchik".
Kuziva sei
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
kurumbidza
Index yakashandiswa wedzera nemhando minda.
Muenzaniso:
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; 
Iwe unogona kuona pakarepo kuti zvinyorwa zvinopfuura zana zvakabviswa nendekisi, izvo zvakabva zvarongwa zvose, uye imwe chete yakasara.
Isu tinogadzirisa:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Kunyangwe pamuenzaniso wekare wakadaro - 8.5x nekukurumidza uye 33x mashoma kuverenga. Mhedzisiro yacho ichave yakajeka, iyo yakawanda "chokwadi" yaunayo kune yega yega kukosha. fk.
Ini ndinoona kuti indekisi yakadaro ichashanda se "prefix" index haina kushata kupfuura yapfuura kune mimwe mibvunzo fk, pakusarudza pk yakanga isiri uye haisi (unogona kuverenga zvakawanda nezve izvi ) Kunyanya, ichapa zvakajairika yakajeka yekunze kiyi rutsigiro nendima iyi.
#2: index intersection (BitmapAnd)
Kana yamuka
Ratidza zvibvumirano zvose zvemutengi "LLC Kolokolchik" yakapera panzvimbo ye "NJSC Lyutik".
Kuziva sei
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scankurumbidza
Gadzira composite index neminda kubva kune ese matsime kana kuwedzera imwe yeminda iripo kubva yechipiri.
Muenzaniso:
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); -- отбор по конкретной паре 
Isu tinogadzirisa:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Pano kuwana kwacho kudiki, sezvo Bitmap Heap Scan ichinyatso shanda pachayo. Asi zvakadaro 7x nekukurumidza uye 2.5x mashoma kuverenga.
#3: Kubatanidza Indexes (BitmapOr)
Kana yamuka
Ratidza ekutanga makumi maviri ekutanga "zvake" kana zvisina kupihwa zvikumbiro zvekugadzirisa, uine yako pamberi.
Kuziva sei
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scankurumbidza
Kushandisa MUUNGANO [ZVESE] kusanganisa subqueries kune yega yega mamiriro OR mabhuroko.
Muenzaniso:
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;
Isu tinogadzirisa:
(
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, больше и не надо 
Isu takatora mukana wekuti ese makumi maviri marekodhi anodiwa akakurumidza kuwanikwa mubhokisi rekutanga, saka yechipiri, ine "inodhura" Bitmap Heap Scan, haina kana kuurayiwa - semhedzisiro. 22x nekukurumidza, 44x mashoma kuverenga!
Imwe nyaya yakadzama nezve iyi optimization nzira pamienzaniso yakasimba inogona kuverengwa muzvinyorwa и .
Generalized version akarayira kusarudzwa nemakiyi akati wandei (uye kwete chete peiri ye const / NULL) inokurukurwa muchinyorwa .
#4: Tinoverenga zvakanyanya
Kana yamuka
Sezvo mutemo, zvinoitika kana iwe uchida "kubatanidza imwe sefa" kune chiripo chikumbiro.
"Uye iwe hauna zvakafanana, asi nemabhatani eparera? » firimu "Diamond Hand"
Semuenzaniso, kugadzirisa basa riri pamusoro, ratidza makumi maviri ekutanga "akakosha" zvikumbiro zvekugadzirisa, zvisinei nechinangwa chazvo.
Kuziva sei
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
kurumbidza
Gadzira [zvimwe] nyanzvi index with WHERE clause kana kuti sanganisira dzimwe ndima muindekisi.
Kana iyo yekusefa iri "static" yemabasa ako - ndiko haisanganisire kuwedzera rondedzero yezvakakosha mune ramangwana - zviri nani kushandisa WHERE index. Yakasiyana-siyana boolean/enum statuses inokodzera muchikamu ichi.
Kana iyo filtration condition inogona kutora maitiro akasiyana, zviri nani kuwedzera index neminda iyi - semamiriro ezvinhu neBitmapAnd pamusoro.
Muenzaniso:
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; 
Isu tinogadzirisa:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Sezvauri kuona, kusefa kubva kuchirongwa kwapera zvachose, uye chikumbiro chave 5 nguva nekukurumidza.
#5: tafura shoma
Kana yamuka
Kuedza kwakasiyana-siyana kugadzira yako wega basa rekugadzirisa mutsara, apo huwandu hukuru hwekuvandudza / kubviswa kwemarekodhi patafura kunotungamira kune huwandu hukuru hwe "vakafa" marekodhi.
Kuziva sei
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
kurumbidza
Ita nemaoko nguva dzose VACUUM [FULL] kana kuwana zvakakwana nguva nenguva kugadzirisa nekugadzirisa zvakanaka ma parameter ayo, kusanganisira .
Muzviitiko zvakawanda, matambudziko akadai anokonzerwa nekusagadzikana kwemubvunzo kurongeka kana yadaidzwa kubva kubhizinesi logic, senge idzo dzakakurukurwa mukati .
Asi isu tinofanira kunzwisisa kuti kunyangwe VACUUM FULL haigone kubatsira nguva dzose. Nezvezviitiko zvakadaro, iwe unofanirwa kuzvijaira iwe neiyo algorithm kubva kuchinyorwa. .
#6: kuverenga kubva "pakati" ye index
Kana yamuka
Zvinoita sekuti vakaverenga zvishoma, uye zvese zvaive zvakarongedzerwa, uye havana kusefa chero ani zvake - asi zvakadaro, mapeji akawanda akaverengwa kupfuura atinoda.
Kuziva sei
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
kurumbidza
Nyatsotarisa chimiro cheiyo index inoshandiswa uye minda yakakosha inotsanangurwa mumubvunzo - zvakanyanya, index chikamu chisina kuiswa. Iwe uchanyanya kuda kugadzira index yakafanana, asi isina prefix minda, kana .
Muenzaniso:
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; 
Zvose zvinoratidzika kunge zvakanaka, kunyange maererano ne index, asi neimwe nzira inonyumwira - kune imwe neimwe yezvinyorwa makumi maviri zvakaverengwa, mapeji mana e data aifanira kubviswa, 20KB pane rekodhi - hazvina kushinga here? Hongu uye indekisi zita tbl_fk_org_fk_cli_idx zvinotungamirira kufunga.
Isu tinogadzirisa:
CREATE INDEX ON tbl(fk_cli); 
Pakarepo - 10 nguva nekukurumidza uye 4 nguva shoma kuverenga!
Kuti uwane mimwe mienzaniso yekushandiswa zvisina kunaka kwemaindex, ona chinyorwa .
#7: CTE × CTE
Kana yamuka
Mukukumbira akarova "mafuta" CTE kubva pamatafura akasiyana, uye akabva afunga kuita pakati pavo JOIN.
Mhosva yacho inoenderana neshanduro pazasi v12 kana zvikumbiro ne WITH MATERIALIZED.
Kuziva sei
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
kurumbidza
Nyatsoongorora chikumbiro chacho ? Kana hongu, saka shandisa "duramazwi" muhstore/json maererano nemuenzaniso unotsanangurwa mu .
#8: chinjana kune dhisiki ( temp yakanyorwa)
Kana yamuka
Imwe-nguva yekugadzirisa (kuronga kana kusarudzika) yenhamba huru yemarekodhi haikodzeri mundangariro yakagoverwa izvi.
Kuziva sei
-> *
&& temp written > 0kurumbidza
Kana huwandu hwekuyeuka hunoshandiswa nekushanda husingapfuuri zvakanyanya kukosha kweiyo parameter , inofanira kugadziriswa. Iwe unogona pakarepo mukugadzirisa kune wese munhu, kana iwe unogona kuburikidza SET [LOCAL] kune chimwe chikumbiro/kutengeserana.
Muenzaniso:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Isu tinogadzirisa:
SET work_mem = '128MB'; -- перед выполнением запроса 
Nezvikonzero zviri pachena, kana chiyeuchidzo chete chikashandiswa uye kwete dhisiki, ipapo mubvunzo uchakurumidza kukurumidza. Panguva imwecheteyo, chikamu chemutoro chinobviswawo kubva kuHDD.
Asi iwe unofanirwa kunzwisisa kuti kugovera ndangariro zhinji hakuzoshande kana - hazvingakwanire munhu wese.
#9: Nhamba dzisina basa
Kana yamuka
Yakawanda yakadururwa muchigadziko kamwechete, asi havana kuwana nguva yekuidzinga ANALYZE.
Kuziva sei
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10kurumbidza
Shandisa zvakafanana ANALYZE.
Iyi mamiriro anotsanangurwa zvakadzama mu .
#10: "chimwe chinhu chakashata"
Kana yamuka
Paive nekiyi yakamirira chikumbiro chekukwikwidza, kana pakanga pasina kukwana CPU/hypervisor hardware zviwanikwa.
Kuziva sei
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
kurumbidza
Shandisa yekunze monitoring system server yekuvharira kana kusajaira kushandisa zviwanikwa. Takatotaura nezve vhezheni yedu yekuronga iyi maitiro kumazana emaseva. и .
Source: www.habr.com
