Mabikirwo emibvunzo inorwara yeSQL

Mwedzi yakati wandei yapfuura takazivisa explain.tensor.ru - paruzhinji sevhisi yekutarisa uye kuona zvirongwa zvemubvunzo 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 mushumo wangu paPGConf.Russia 2020, 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;

[tarisa ku explain.tensor.ru]

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); -- добавили ключ сортировки

[tarisa ku explain.tensor.ru]

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 muchinyorwa changu pamusoro pekutsvaga zvisingaite indexes) 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 Scan

kurumbidza

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); -- отбор по конкретной паре

[tarisa ku explain.tensor.ru]

Isu tinogadzirisa:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[tarisa ku explain.tensor.ru]

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 Scan

kurumbidza

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;

[tarisa ku explain.tensor.ru]

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, больше и не надо

[tarisa ku explain.tensor.ru]

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 PostgreSQL Antipatterns: Inokuvadza JOINs uye ORs и PostgreSQL Antipatterns: Tale yeIterative Kunatsiridza Kwekutsvaga Nezita, kana "Kunatsiridza Kudzoka uye mberi".

Generalized version akarayira kusarudzwa nemakiyi akati wandei (uye kwete chete peiri ye const / NULL) inokurukurwa muchinyorwa SQL HowTo: nyora chinguva-loop zvakananga mumubvunzo, kana "Elementary nhatu-nzira".

#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;

[tarisa ku explain.tensor.ru]

Isu tinogadzirisa:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[tarisa ku explain.tensor.ru]

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 autovacuum nekugadzirisa zvakanaka ma parameter ayo, kusanganisira yetafura chaiyo.

Muzviitiko zvakawanda, matambudziko akadai anokonzerwa nekusagadzikana kwemubvunzo kurongeka kana yadaidzwa kubva kubhizinesi logic, senge idzo dzakakurukurwa mukati PostgreSQL Antipatterns: mapoka ehondo e "vakafa".

Asi isu tinofanira kunzwisisa kuti kunyangwe VACUUM FULL haigone kubatsira nguva dzose. Nezvezviitiko zvakadaro, iwe unofanirwa kuzvijaira iwe neiyo algorithm kubva kuchinyorwa. DBA: kana VACUUM ichipfuura, tinochenesa tafura nemaoko.

#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 dzidza kudzokorora hunhu hwavo.

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;

[tarisa ku explain.tensor.ru]

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);

[tarisa ku explain.tensor.ru]

Pakarepo - 10 nguva nekukurumidza uye 4 nguva shoma kuverenga!

Kuti uwane mimwe mienzaniso yekushandiswa zvisina kunaka kwemaindex, ona chinyorwa DBA: tsvaga zvisingabatsiri indexes.

#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 maCTE anodiwa pano zvachose? Kana hongu, saka shandisa "duramazwi" muhstore/json maererano nemuenzaniso unotsanangurwa mu PostgreSQL Antipatterns: Duramazwi Hit Heavy JOIN.

#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 > 0

kurumbidza

Kana huwandu hwekuyeuka hunoshandiswa nekushanda husingapfuuri zvakanyanya kukosha kweiyo parameter work_mem, 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;

[tarisa ku explain.tensor.ru]

Isu tinogadzirisa:

SET work_mem = '128MB'; -- перед выполнением запроса

[tarisa ku explain.tensor.ru]

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 >> 10

kurumbidza

Shandisa zvakafanana ANALYZE.

Iyi mamiriro anotsanangurwa zvakadzama mu PostgreSQL Antipatterns: nhamba ndiwo musoro wezvose.

#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. pano и pano.

Source: www.habr.com