🥇Recetat për kërkesat SQL që kanë nevojë

Disa muaj më parë ne shpallëm explain.tensor.ru — publik shërbim për analizimin dhe vizualizimin e planeve të kërkesave në PostgreSQL.

Gjatë kësaj periudhe keni përdorur atë më shumë se 6000 herë, por një nga funksionet e dobishme mund të ketë mbetur e pandjerë — kjo është këshillave strukturore, që duken përafërsisht kështu:

Dëgjoni ato, dhe kërkesat tuaja "do të bëhen të lëmuara dhe mëndafshi“. 🙂

Por në mënyrë serioze, shumë situata që e bëjnë kërkesën të ngadalshme dhe "të zjarrtë" për burime, janë tipike dhe mund të njihet nga struktura dhe të dhënat e planit.

Në këtë rast, çdo zhvillues i veçantë nuk do të duhet të kërkojë një opsion optimizimi në mënyrë të pavarur, duke u mbështetur vetëm në përvojën e tij — ne mund t'i tregojmë se çfarë po ndodh, çfarë mund të jetë shkaku, dhe si mund të qasemi në zgjidhje. Kjo është ajo që bëmë.

Le të shqyrtojmë pak më në detaje këto raste — si përcaktohen ato dhe në cilat rekomandime çojnë.

Për një zhytje më të mirë në temë, së pari mund të dëgjoni bllokun përkatës nga referati im në PGConf.Russia 2020, dhe pastaj të kaloni në analizën e detajuar të çdo shembulli:

#1: индексная «недосортировка»

Kur lind

Trego faturën e fundit për klientin "OOO Kolokollchik".

Si të identifikoni

-> Limit
   -> Sort
      -> Index [Vetëm] Skano [Mbrapsht] | Bitmap Heap Scan

R rekomandimet

Indeksi i përdorur shkruani me fushat e renditjes.

Shembuj:

KRIJO TABELË tbl SI
SELECT
  generate_series(1, 100000) pk  -- 100K "fakte"
, (random() * 1000)::integer fk_cli; -- 1K çelësa të ndryshëm të huaj

KRIJO INDNEKS NË tbl(fk_cli); -- indeks për çelësa të huaj

SELECT
  *
FROM
  tbl
KU
  fk_cli = 1 -- selektim sipas lidhjes specifike
RENDIT MESATAREN
  pk DESC -- duam vetëm një "të fundit" përfaqësim
LIMIT 1;

[shiko në explain.tensor.ru]

Menjëherë mund të vërehet se më shumë se 100 shënime u lexuan nga indeksi, të cilat më pas u renditën, dhe pastaj u la një e vetme.

Korrigjojmë:

DROP INDEX tbl_fk_cli_idx;
KRIJO INDNEKS NË tbl(fk_cli, pk DESC); -- shtuam çelësin e renditjes

[shiko në explain.tensor.ru]

Edhe në një seleksion kaq primitive — 8.5 herë më shpejt dhe 33 herë më pak lexime. Efekti do të jetë më i dukshëm, sa më shumë "fakte" të keni për çdo vlerë fk.

Dua të theksoj se një indeks i tillë do të punojë si "prefiks" po aq mirë sa që më parë edhe për kërkesa të tjera me fk, ku nuk kishte dhe nuk ka renditje sipas pk nuk ka qenë dhe nuk ka (më shumë për këtë mund të lexoni në artikullin tim për kërkimin e indekseve joefektive). Përfshirë, ai do të ofrojë gjithashtu një mbështetje të përshtatshme për çelësin e jashtëm të huaj për këtë fushë. Trego të gjitha kontratat për klientin "OOO Kolokollchik", të nënshkruara nga emri "NAO Lyutik".

#2: пересечение индексов (BitmapAnd)

Kur lind

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

Si të identifikoni

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

R rekomandimet

Krijo indeksi i përbërë nga fushat e të dy burimeve ose të zgjerojnë një nga fushat ekzistuese me fushat e të dytës.

Shembuj:

KRIJO TABELA tbl SI
Zgjidh
  generate_series(1, 100000) pk      -- 100K "fakte"
, (random() *  100)::integer fk_org  -- 100 çelësa të ndryshëm të jashtëm
, (random() * 1000)::integer fk_cli; -- 1K çelësa të ndryshëm të jashtëm

KRIJO INDEKSO KRAH tbl(fk_org); -- indeks për çelësin e jashtëm
KRIJO INDEKSO KRAH tbl(fk_cli); -- indeks për çelësin e jashtëm

Zgjidh
  *
NGA
  tbl
KU
  (fk_org, fk_cli) = (1, 999); -- seleksionim nga një çift i veçantë

[shiko në explain.tensor.ru]

Korrigjojmë:

DROP INDEX tbl_fk_org_idx;
KRIJO INDEKSO KRAH tbl(fk_org, fk_cli);

[shiko në explain.tensor.ru]

Këtu fitimi është më i vogël, pasi Bitmap Heap Scan është mjaft efikase vetë. Por gjithsesi 7 herë më shpejt dhe 2.5 herë më pak leximet.

#3: объединение индексов (BitmapOr)

Kur lind

Trego 20 aplikacione më të vjetra "të tua" ose të pazgjedhura për përpunim, ku të tuat kanë përparësi.

Si të identifikoni

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

R rekomandimet

Përdorni UNION [ALL] për bashkimin e nënkërkesave për secilin nga bloket OR të kushteve.

Shembuj:

KRIJO TABELA tbl SI
Zgjidh
  generate_series(1, 100000) pk  -- 100K "fakte"
, RAST
    KUR random() < 1::real/16 ATËHERË NULL -- me probabilitet 1:16 regjistrimi "i barabartë"
    PASTAJ (random() * 100)::integer -- 100 çelësa të ndryshëm të jashtëm
  MBI fk_own;

KRIJO INDEKSO KRAH tbl(fk_own, pk); -- indeks me renditje "duke u dukur si e përshtatshme"

Zgjidh
  *
NGA
  tbl
KU
  fk_own = 1 OSE -- e tua
  fk_own IS NULL -- ... ose "të barabarta"
ORDER BY
  pk
, (fk_own = 1) DESC -- fillimisht "të tua"
LIMIT 20;

[shiko në explain.tensor.ru]

Korrigjojmë:

(
  Zgjidh
    *
  NGA
    tbl
  KU
    fk_own = 1 -- fillimisht "të tua" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  Zgjidh
    *
  NGA
    tbl
  KU
    fk_own IS NULL -- pastaj "të barabarta" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- por gjithsej - 20, më shumë nuk nevojitet

[shiko në explain.tensor.ru]

Ne shfrytëzuam faktin që të gjitha 20 regjistrimet e nevojshme u morën menjëherë në bllokun e parë, kështu që i dyti, me Bitmap Heap Scan më "të kushtueshëm", as që u ekzekutua — në përfundim 22 herë më shpejt, 44 herë më pak leximet!

Një tregim më i detajuar në lidhje me këtë metodë optimizimi në shembuj konkretë mund të lexohet në artikuj Antipatterns të PostgreSQL: JOIN dhe OR të dëmshme dhe PostgreSQL Antipatterns: një përrallë mbi rregullimin iterativ të kërkimit sipas emrit, ose "Optimizimi përpara dhe prapa".
Varianti i përgjithshëm i renditjes sipas disa çelësave (e jo vetëm sipas çiftit const/NULL) është shqyrtuar në artikullin SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar".

#4: читаем много лишнего

Kur lind

Rregullisht ndodh kur dëshirohet "të shtohet një filtrë tjetër" në një kërkesë ekzistuese.

"A keni ndonjë të tillë, por me düallë të perlave?» film "Përgjimi i Diamantëve"

Për shembull, duke modifikuar detyrën e lartpërmendur, të tregojmë 20 aplikacione më të vjetra "kritike" për përpunim, pavarësisht nga emërtimi i tyre.

Si të identifikoni

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rreshta < RRbF -- filtruar >80% e lexuar
   && loops × RRbF > 100 -- dhe në të njëjtën kohë më shumë se 100 regjistrime gjithsej

R rekomandimet

Krijo [më] të specializuar indeks me kushtin WHERE ose ose të përfshini në indeks fusha shtesë.

Nëse kushti i filtrimit është "statik" për detyrat tuaja — domethënë nuk parashikon zgjerimin e listës së vlerave në të ardhmen — është më mirë të përdorni një indeks WHERE. Në këtë kategori bien mirë statuset e ndryshme boolean/enum.
Megjithatë, nëse kushti i filtrimit mund të marrë vlera të ndryshme, atëherë është më mirë të zgjeroni indeksin me këto fusha — si në situatën me BitmapAnd më sipër.

Shembuj:

KRIJO TABELË tbl SI
Zgjedh
  genere_series(1, 100000) pk -- 100K "fakte"
, RASTISHA
    KUR rastësia() < 1::real/16 ATËHERË NULL
    TË TJERA (rastësia() * 100)::integer -- 100 çelësa të ndryshëm të jashtëm
  FUND fk_own
, (rastësia() < 1::real/50) kritike; -- 1:50, që aplikimi është "kritik"

KRIJO INDËKSI PËR tbl(pk);
KRIJO INDËKSI PËR tbl(fk_own, pk);

ZGJEDH
  *
PREJ
  tbl
KU
  kritike
RENDIT PËR
  pk
LIMIT 20;

[shiko në explain.tensor.ru]

Korrigjojmë:

KRIJO INDËKSI PËR tbl(pk)
  KU kritike; -- shtuam kushtin "statik" të filtrimit

[shiko në explain.tensor.ru]

Siç e shohim, filtrimi nga plani është larguar plotësisht, dhe kërkesa është bërë 5 herë më e shpejtë.

#5: разреженная таблица

Kur lind

Përpjekje të ndryshme për të krijuar një radhë të vetën për përpunimin e detyrave, kur një numër i madh azhurnimesh/fshirjesh të regjistrimeve në tabelë çon në një situatë me shumë regjistrime "të vdekura".

Si të identifikoni

-> Skanime Sekuenciale | Skanime Bitmap Heap | Indeksi [Vetëm] Skanim [Prapa]
   && cikle × (rreshtat + RRbF)  64

R rekomandimet

Rregullisht të kryeni manualisht VACUUM [FULL] ose të arrini të punoni mjaft shpesh autovacuum me anë të rregullimit të parametrave të tij, duke përfshirë për një tabelë specifike.

Në shumicën e rasteve, probleme të tilla rezultojnë nga keqformimi i kërkesave kur thirren nga logjika e biznesit si ato që u shqyrtuan në PostgreSQL Antipatterns: luftojmë me hordhitë e 'të vdekurve'..
Por duhet të kuptohet se edhe VACUUM FULL nuk mund të ndihmojë gjithmonë. Për raste të tilla, është mirë të njiheni me algoritmin nga artikulli DBA: kur VACUUM dështon — pastroni tabelën manualisht.

#6: чтение с «середины» индекса

Kur lind

Duket se kemi lexuar disi, gjithçka është për indeksin, dhe nuk kemi filtruar askënd të panevojshëm — megjithatë, është lexuar dukshëm më shumë faqe sesa do të dëshironim.

Si të identifikoni

-> Indeksi [Vetëm] Skanim [Prapa]
   && cikle × (rreshtat + RRbF)  64

R rekomandimet

Të shikoni me kujdes strukturën e indeksit të përdorur dhe fushat kryesore të caktuara në kërkesë — me sa duket, një pjesë e indeksit nuk është caktuar. Me sa duket, do t'ju duhet të krijoni një indeks të ngjashëm, por pa fushat prefix ose të mësoni si të iteroni vlerat e tyre.

Shembuj:

KRIJONI TABELLA tbl SI
SELECT
  generate_series(1, 100000) pk      -- 100K "fakte"
, (random() *  100)::integer fk_org  -- 100 çelëra të ndryshme të jashtme
, (random() * 1000)::integer fk_cli; -- 1K çelëra të ndryshme të jashtme

KRIJONI INDEN ON tbl(fk_org, fk_cli); -- gjithçka pothuajse si në #2
-- vetëm se indeksi i veçantë për fk_cli e kemi konsideruar të panevojshme dhe e kemi fshirë

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- dhe fk_org nuk është caktuar, ndonëse është në indeks më herët
LIMIT 20;

[shiko në explain.tensor.ru]

Duket se gjithçka është mirë, madje edhe sipas indeksit, por disi dyshues — për çdo një nga 20 regjistrimet e lexuara na duhej të lexonim 4 faqe të dhënash, 32KB për regjistrim — a nuk është shumë? Po ashtu emri i indeksit tabela_fk_org_fk_cli_idx na bën të mendojmë.

Korrigjojmë:

KRIJONI INDEN ON tbl(fk_cli);

[shiko në explain.tensor.ru]

Papritur — 10 herë më shpejt, dhe 4 herë më pak për të lexuar!

Shembuj të tjerë të situatave të paefektshme të përdorimit të indekseve mund të shihen në artikullin DBA: gjejmë indekse të padobishme.

#7: CTE × CTE

Kur lind

Në kërkesë kemi vendosur "indeks të madh" CTE nga tabela të ndryshme, dhe pastaj vendosëm të bëjmë midis tyre JOIN.

Rasti është i rëndësishëm për versione më të ulëta se v12 ose kërkesa me ME MATERIALIZUAR.

Si të identifikoni

-> CTE Skano
   && cikle > 10
   && cikle × (rresht + RRbF) > 10000
      -- produkti dekartov i CTE është shumë i madh

R rekomandimet

Të analizohet me kujdes kërkesa — a kanë nevojë për CTE këtu? Если все-таки да, то përdorim "shkëmbim" në hstore/json sipër modelit, të përshkruar në PostgreSQL Antipatterns: goditim me fjalorin në JOIN e rëndë.

#8: swap на диск (temp written)

Kur lind

Përpunimi i veçantë (renditja ose unikizimi) i një numri të madh regjistrimesh nuk i kalon memorie të caktuar për këtë.

Si të identifikoni

-> *
   && temp shkruar > 0

R rekomandimet

Nëse sasia e memories e përdorur nga operacioni nuk kalon shumë vlerën e parametrit të vendosur work_mem, vlen ta korrigjoni atë. Mund ta bëni menjëherë në konfigurim për të gjithë, ose përmes SET [LOCAL] për kërkesën/transaction specifike.

Shembuj:

SHIKO work_mem;
-- "16MB"

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

[shiko në explain.tensor.ru]

Korrigjojmë:

SET work_mem = '128MB'; -- përpara ekzekutimit të kërkesës

[shiko në explain.tensor.ru]

Për arsye të qarta, nëse përdoret vetëm memoria dhe jo disku, atëherë kërkesa do të ekzekutohet shumë më shpejt. Ndërkohë, gjithashtu, një pjesë e ngarkesës është hequr nga HDD.

Por duhet kuptuar se ndarja e shumë-më shumë memories gjithashtu nuk do të jetë e mundshme — ajo thjesht nuk do të mjaftojë për të gjithë.

#9: неактуальная статистика

Kur lind

Në bazë u lëshua menjëherë shumë, por nuk arritëm ta kalojmë ANALIZO.

Si të identifikoni

-> Skano Sekuencial | Skano Bitmap Heap | Indeks [Vetëm] Skano [Mbrapsht]
   && raporti >> 10

R rekomandimet

Duhet të zhvillohet ANALIZO.

Kjo situatë është përshkruar më në detaje në PostgreSQL Antipatterns: statistika është gjithçka.

#10: «что-то пошло не так»

Kur lind

I ndodhi presioni i bllokimit, i vendosur nga kërkesat konkuruese, ose mungesa e burimeve harduerike CPU/hypervisor.

Si të identifikoni

-> *
   && (godit e përbashkët / 8K) + (lexim i përbashkët / 1K)  100ms -- lexuam pak, por shumë gjatë

R rekomandimet

Përdorni një të jashtme sistem për monitorimin e serverit për bllokime ose konsum të papërshtatshëm të burimeve. Ne kemi folur tashmë për variantin tonë të organizimit të këtij procesi për qindra serverë këtu dhe këtu.

Burimi: habr.com