Recepti za bolne poizvedbe SQL

Pred meseci smo napovedali expand.tensor.ru - javni storitev za razčlenjevanje in vizualizacijo načrtov poizvedb v PostgreSQL.

Uporabili ste ga že več kot 6000-krat, vendar je ena priročna funkcija, ki je morda ostala neopažena, strukturne namige, ki izgledajo nekako takole:

Poslušajte jih in vaše zahteve bodo »postale gladke in svilnate«. 🙂

Toda resno, veliko situacij, zaradi katerih je zahteva počasna in požrešna so tipični in jih je mogoče prepoznati po strukturi in podatkih načrta.

V tem primeru vsakemu posameznemu razvijalcu ni treba sam iskati možnosti optimizacije, zanašajoč se zgolj na svoje izkušnje - lahko mu povemo, kaj se tu dogaja, kaj bi lahko bil razlog in kako pristopiti k rešitvi. To smo storili.

Oglejmo si te primere podrobneje – kako so opredeljeni in do kakšnih priporočil vodijo.

Če se želite bolje potopiti v temo, lahko najprej poslušate ustrezen blok iz moje poročilo na PGConf.Russia 2020, in šele nato preidite na podrobno analizo vsakega primera:

#1: indeks "slabo razvrščanje"

Kdaj

Prikaži zadnji račun za stranko "LLC Kolokolchik".

Kako prepoznati

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Priporočila

Uporabljeno kazalo razširite z razvrstitvenimi polji.

Primer:

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;

[ogled na expand.tensor.ru]

Takoj lahko opazite, da je bilo iz indeksa odštetih več kot 100 zapisov, ki so bili nato vsi razvrščeni, nato pa je ostal edini.

Popravljanje:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[ogled na expand.tensor.ru]

Tudi na tako primitivnem vzorcu - 8.5-krat hitreje in 33-krat manj branj. Več "dejstev" imate za vsako vrednost, bolj očiten je učinek fk.

Ugotavljam, da bo tak indeks deloval kot indeks "predpone" nič slabše kot prej za druge poizvedbe z fk, kjer razvrsti po pk ni bilo in ni (več o tem si lahko preberete v mojem članku o iskanju neučinkovitih indeksov). Vključno bo zagotovil normalno eksplicitna podpora za tuji ključ na tem polju.

#2: križišče indeksa (BitmapAnd)

Kdaj

Prikaži vse pogodbe za stranko “LLC Kolokolchik”, sklenjene v imenu “NAO Buttercup”.

Kako prepoznati

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Priporočila

ustvariti sestavljeni indeks po poljih iz obeh prvotnih ali pa enega od obstoječih razširite s polji iz drugega.

Primer:

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

[ogled na expand.tensor.ru]

Popravljanje:

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

[ogled na expand.tensor.ru]

Izplačilo tukaj je manjše, saj je Bitmap Heap Scan samo po sebi zelo učinkovito. Ampak vseeno 7-krat hitreje in 2.5-krat manj branj.

#3: Združite indekse (BitmapOr)

Kdaj

Pokažite prvih 20 najstarejših »nas« ali nedodeljenih zahtev za obdelavo, pri čemer ima vaša prednost.

Kako prepoznati

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Priporočila

Za uporabo UNIJA [VSE] za združevanje podpoizvedb za vsakega od blokov ALI pogojev.

Primer:

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;

[ogled na expand.tensor.ru]

Popravljanje:

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

[ogled na expand.tensor.ru]

Izkoristili smo dejstvo, da je bilo vseh 20 zahtevanih zapisov takoj prejetih v prvem bloku, zato drugi, z “dražjim” Bitmap Heap Scan, sploh ni bil izveden – na koncu 22x hitrejši, 44x manj branj!

Podrobnejša zgodba o tej metodi optimizacije z uporabo konkretnih primerov lahko preberete v člankih Protivzorci PostgreSQL: škodljivi JOIN in OR и Protivzorci PostgreSQL: zgodba o ponavljajočem se izboljšanju iskanja po imenu ali »Optimizacija naprej in nazaj«.

Posplošena različica urejen izbor na podlagi več ključev (in ne samo o paru const/NULL) je obravnavan v članku SQL HowTo: pisanje zanke while neposredno v poizvedbo ali »Elementarni trije koraki«.

#4: Beremo veliko nepotrebnih stvari

Kdaj

Praviloma se pojavi, ko želite že obstoječi zahtevi "priložiti drug filter".

»In nimaš istega, ampak z gumbi iz biserne barve? " film "Diamantna roka"

Na primer, spreminjanje zgornjega opravila prikaže prvih 20 najstarejših "kritičnih" zahtev za obdelavo, ne glede na njihov namen.

Kako prepoznati

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Priporočila

Ustvari [več] specializiranih indeks s pogojem WHERE ali vključite dodatna polja v kazalo.

Če je pogoj filtra za vaše namene "statičen", tj ne pomeni širitve seznam vrednosti v prihodnosti - bolje je uporabiti indeks WHERE. V to kategorijo se dobro ujemajo različni logični/enum statusi.

Če je pogoj filtriranja lahko prevzame različne pomene, potem je bolje razširiti indeks s temi polji - kot v primeru z BitmapAnd zgoraj.

Primer:

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;

[ogled na expand.tensor.ru]

Popravljanje:

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

[ogled na expand.tensor.ru]

Kot lahko vidite, je filtriranje popolnoma izginilo iz načrta in zahteva je postala 5-krat hitreje.

#5: redka miza

Kdaj

Različni poskusi ustvarjanja lastne čakalne vrste za obdelavo opravil, ko veliko število posodobitev/brisov zapisov v tabeli vodi do situacije velikega števila "mrtvih" zapisov.

Kako prepoznati

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Priporočila

Redno izvajajte ročno VAKUUM [POLNO] ali doseči ustrezno pogosto usposabljanje avtovakuum z natančno nastavitvijo njegovih parametrov, vključno z za določeno mizo.

V večini primerov so takšne težave posledica slabe sestave poizvedbe pri klicu iz poslovne logike, kot je opisano v PostgreSQL Antipatterns: boj proti hordam »mrtvih«.

Vendar morate razumeti, da tudi VACUUM FULL morda ne bo vedno pomagal. V takih primerih se je vredno seznaniti z algoritmom iz članka DBA: ko VAKUUM odpove, mizo očistimo ročno.

#6: Branje iz "sredine" kazala

Kdaj

Zdi se, da smo brali malo in vse je bilo indeksirano in nismo nikogar presežek filtrirali - a vseeno smo prebrali bistveno več strani, kot bi želeli.

Kako prepoznati

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Priporočila

Pozorno si oglejte strukturo uporabljenega indeksa in ključna polja, podana v poizvedbi - najverjetneje del indeksa ni nastavljen. Najverjetneje boste morali ustvariti podoben indeks, vendar brez predponskih polj oz naučijo se ponavljati svoje vrednote.

Primer:

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;

[ogled na expand.tensor.ru]

Zdi se, da je vse v redu, tudi po indeksu, vendar je nekako sumljivo - za vsakega od 20 prebranih zapisov smo morali odšteti 4 strani podatkov, 32 KB na zapis - ni to krepko? In ime indeksa tbl_fk_org_fk_cli_idx spodbujanje misli.

Popravljanje:

CREATE INDEX ON tbl(fk_cli);

[ogled na expand.tensor.ru]

Nenadoma - 10-krat hitreje in 4-krat manj časa za branje!

Druge primere situacij neučinkovite uporabe indeksov si lahko ogledate v članku DBA: iskanje neuporabnih indeksov.

#7: CTE × CTE

Kdaj

V zahtevi dosegel "mast" CTE z različnih miz, nato pa se odločil, da to stori med njima JOIN.

Zadeva je pomembna za različice pod v12 ali zahteve z WITH MATERIALIZED.

Kako prepoznati

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Priporočila

Previdno analizirajte zahtevo - in Ali so tukaj CTE sploh potrebni?? Če da, potem uporabite "slovar" v hstore/json po modelu, opisanem v PostgreSQL Antipatterns: udarimo po težkem JOIN s slovarjem.

#8: zamenjava na disk (začasno napisano)

Kdaj

Enkratna obdelava (razvrščanje ali unikatizacija) velikega števila zapisov ne ustreza pomnilniku, ki je za to dodeljen.

Kako prepoznati

-> *
   && temp written > 0

Priporočila

Če količina pomnilnika, ki ga uporablja operacija, ne presega močno podane vrednosti parametra delovni_mem, je vredno popraviti. Lahko takoj v configu za vse ali pa skozi SET [LOCAL] za določeno zahtevo/transakcijo.

Primer:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[ogled na expand.tensor.ru]

Popravljanje:

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

[ogled na expand.tensor.ru]

Iz očitnih razlogov, če se uporablja samo pomnilnik in ne disk, bo poizvedba izvedena veliko hitreje. Hkrati se odstrani tudi del obremenitve trdega diska.

Vendar morate razumeti, da ne boste mogli vedno dodeliti veliko in veliko pomnilnika - preprosto ga ne bo dovolj za vse.

#9: nepomembna statistika

Kdaj

V bazo so naenkrat vlili veliko, a niso imeli časa, da bi ga odgnali ANALYZE.

Kako prepoznati

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Priporočila

Izvedite ANALYZE.

To stanje je podrobneje opisano v PostgreSQL Antipatterns: statistika je vse.

#10: "nekaj je šlo narobe"

Kdaj

Prišlo je do čakanja na zaklepanje, ki ga je uvedla konkurenčna zahteva, ali pa ni bilo dovolj sredstev strojne opreme CPE/hipervizorja.

Kako prepoznati

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

Priporočila

Uporabite zunanje nadzorni sistem strežnik za blokiranje ali nenormalno porabo virov. O naši različici organizacije tega procesa za stotine strežnikov smo že govorili tukaj и tukaj.

Vir: www.habr.com