Recepti za bolesne SQL upite

Prije nekoliko mjeseci anonsirali smo explain.tensor.ru — javnu uslugu za analizu i vizualizaciju planova upita za PostgreSQL.

U proteklom vremenu koristili ste je više od 6000 puta, ali jedna od korisnih funkcija mogla bi ostati neprimijećena — to su strukturne sugestije, koje izgledaju otprilike ovako:

Recepti za bolesne SQL upite

Slušajte ih, i vaši upiti „bit će glatki i svilenkasti“. 🙂

A ozbiljno, mnoge situacije koje čine upit sporim i „resource-hungry“ su tipične i mogu se prepoznati prema strukturi i podacima plana..

U ovom slučaju svakom pojedinačnom programeru neće biti potrebno da samostalno traži opciju optimizacije, oslanjajući se isključivo na vlastito iskustvo — možemo mu sugerirati što se ovdje događa, koja bi mogla biti uzrok, i kako se može pristupiti rješenju.. Što smo i učinili.

Recepti za bolesne SQL upite

Pogledajmo malo detaljnije ove slučajeve — kako ih prepoznati i na koje preporuke dovode.

Za bolje uranjanje u temu, prvo možete poslušati odgovarajući djelić iz moje prezentacije na PGConf.Russia 2020, a zatim preći na detaljnu analizu svake situacije:

Pogledajte video

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

Kada

Prikaži posljednji račun za klijenta „OOO Kolokolicik“.

Kako prepoznati

-> Limit
   -> Sort
      -> Index [Samo] Skeniranje [Unazad] | Bitmap Heap Skeniranje

Preporuke

Koristi se indeks proširiti poljima sortiranja.

Primjer:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "faktora"
, (random() * 1000)::integer fk_cli; -- 1K raznih vanjskih ključeva

CREATE INDEX ON tbl(fk_cli); -- indeks za foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- odabir prema specifičnoj vezi
ORDER BY
  pk DESC -- želimo samo jedan "posljednji" zapis
LIMIT 1;

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Odmah se može primijetiti da je po indeksu pročitano više od 100 zapisa, koji su se potom svi sortirali, a zatim je ostavljen samo jedan.

Ispravka:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- dodali smo ključ sortiranja

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Čak i na tako primitivnom odabiru — 8.5 puta brže i 33 puta manje čitanja.Efekt će biti time jasniji, što više „faktora“ imate za svaku vrijednost fk..

Napominjem da će takav indeks raditi kao „prefiksni“ jednako dobro kao i prethodni i za druge upite s fk., gdje sortiranja prema pk nije bilo i nema (više o tome možete pročitati u mom članku o pronalaženju neefikasnih indeksa). Uključujući, osigurat će i normalnu podršku za izričiti vanjski ključ prema ovom polju.

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

Kada

Prikaži sve ugovore za klijenta «OOO Kolokolčik», sklopljene u ime «NAO Ljutik».

Kako prepoznati

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

Preporuke

Stvori kompozitni indeks po poljima iz oba izvorna ili proširiti jedan od postojećih polja iz drugog.

Primjer:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "faktora"
, (random() *  100)::integer fk_org  -- 100 različitih stranih ključeva
, (random() * 1000)::integer fk_cli; -- 1K različitih stranih ključeva

CREATE INDEX ON tbl(fk_org); -- indeks za strani ključ
CREATE INDEX ON tbl(fk_cli); -- indeks za strani ključ

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- filtriranje po konkretnoj pari

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Ispravka:

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

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Ovde je dobitak manji, jer je Bitmap Heap Scan već dovoljno efikasan. Ali svejedno 7 puta brže i 2.5 puta manje čitanja.

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

Kada

Prikaži prvih 20 najstarijih "svojih" ili nenamenskih zahteva za obradu, pri čemu su svoji u prioritetu.

Kako prepoznati

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

Preporuke

Koristiti UNION [ALL] za spajanje podupita po svakom od OR-blokova uslova.

Primjer:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "faktora"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- sa verovatnoćom 1:16 zapis "niko"
    ELSE (random() * 100)::integer -- 100 različitih stranih ključeva
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- indeks sa "naizgled odgovarajućim" sortiranim

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- svoji
  fk_own IS NULL -- ... ili "niko"
ORDER BY
  pk
, (fk_own = 1) DESC -- prvo "svoji"
LIMIT 20;

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Ispravka:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- prvo "svoji" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- zatim "niko" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- ali ukupno - 20, više nije potrebno

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Iskoristili smo to što je svih 20 potrebnih zapisa odmah dobijeno u prvom bloku, pa drugi, sa skupljim Bitmap Heap Scan, čak nije ni izvršen — na kraju 22 puta brže, 44 puta manje čitanja!

Detaljniji opis ovog načina optimizacije na konkretnim primerima može se pročitati u člancima PostgreSQL antipaterni: štetni JOIN i OR i PostgreSQL antipatern: bajka o iterativnom unapređenju pretraživanja po nazivu, ili „Optimizacija ovamo i natrag“.

Generalizovana varijanta rezultatskog izbora po više ključeva (a ne samo po paru const/NULL) obrađena je u članku SQL HowTo: pišemo while-petlju direktno u upitu, ili „Elementarna trodijelka“.

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

Kada

Obično se javlja kada postoji želja da se "dodaje još jedan filter" postojećem upitu.

"A nemate li sličan, ali s bisernim dugmadima?» h/f "Brijantna ruka"

Na primer, modifikujući prethodni zadatak, prikazati prvih 20 najstarijih "kritičnih" zahteva za obradu, bez obzira na njihovu namenu.

Kako prepoznati

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows 80% pročitanog
   && loops × RRbF > 100 -- i pritom više od 100 zapisa ukupno

Preporuke

Izraditi [više] specijalizirani indeks s WHERE-uvjetom ili uključiti dodatna polja u indeks.

Ako je uvjet filtriranja "statičan" za vaše zadatke — to jest ne očekuje proširenje popisa vrijednosti u budućnosti — bolje je koristiti WHERE-indeks. Ova kategorija dobro obuhvaća različite boolean/enum-status.

Ako uvjet filtriranja može primati različite vrijednosti, bolje je proširiti indeks tim poljima — kao u situaciji s BitmapAnd gore.

Primjer:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "faktora"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 različitih vanjskih ključeva
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, da je zahtjev "kritičan"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Ispravka:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- dodali smo "statičan" uvjet filtriranja

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Kao što vidimo, filtracija iz plana potpuno je nestala, a upit je postao 5 puta brži.

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

Kada

Različiti pokušaji izrade vlastitih redova za obradu zadataka, kada veliki broj ažuriranja/brisanja zapisa na tablici dovodi do situacije velikog broja "mrtvih" zapisa.

Kako prepoznati

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF)  64

Preporuke

Redovito ručno provoditi VACUUM [FULL] ili postići adekvatnu učestalost obrade autovacuum pomoću fine prilagodbe njegovih parametara, uključujući za konkretnu tablicu.

U većini slučajeva takvi problemi nastaju zbog lošeg sastava upita tijekom poziva iz poslovne logike poput onih koje su razmatrane u PostgreSQL antipatern: borba protiv hordi 'mrtvih' podataka.

No treba razumjeti da čak ni VACUUM FULL ne može uvijek pomoći. Za takve slučajeve vrijedi proučiti algoritam iz članka DBA: kada VACUUM ne djeluje — čišćenje tablica ručno.

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

Kada

Kao da smo malo pročitali, i sve po indeksu, i nikoga viška ne filtrirali — a svejedno je pročitan značajno veći broj stranica nego što bismo htjeli.

Kako prepoznati

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF)  64

Preporuke

Pažljivo proučite strukturu korištenog indeksa i ključna polja zadana u upitu – najvjerojatnije, dio indeksa nije zadan. Vjerojatno ćete morati stvoriti sličan indeks, ali bez prefiksnih polja ili naučiti iterirati njihove vrijednosti.

Primjer:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "faktora"
, (random() *  100)::integer fk_org  -- 100 različitih vanjskih ključeva
, (random() * 1000)::integer fk_cli; -- 1K različitih vanjskih ključeva

CREATE INDEX ON tbl(fk_org, fk_cli); -- sve gotovo kao u #2
-- samo je odvojeni indeks po fk_cli već suvišan i uklonili smo ga

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- a fk_org nije zadan, iako je ranije bio u indeksu
LIMIT 20;

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Izgleda da je sve u redu, čak i po indeksu, ali nekako sumnjivo – na svaku od 20 pročitanih zapisa trebali smo očitati 4 stranice podataka, 32KB po zapisu – nije li to previše? A i ime indeksa tbl_fk_org_fk_cli_idx potiče na razmišljanje.

Ispravka:

CREATE INDEX ON tbl(fk_cli);

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Iznenada – 10 puta brže, i 4 puta manje čitati!

Druge primjere situacija neučinkovitog korištenja indeksa možete vidjeti u članku DBA: nalazimo beskorisne indekse.

#7: CTE × CTE

Kada

U upitu koristili smo „debeli“ CTE iz različitih tablica, a zatim smo odlučili stvoriti između njih JOIN.

Ovaj slučaj vrijedi za verzije niže od v12 ili upite s WITH MATERIALIZED.

Kako prepoznati

-> CTE Scan
   && petlje > 10
   && petlje × (redovi + RRbF) > 10000
      -- preveliki kartezijski proizvod CTE

Preporuke

Pažljivo analizirajte upit – a da li su ovdje uopće potrebni CTE? Если все-таки да, то primjeniti „označavanje“ u hstore/json prema modelu opisanom u PostgreSQL antipaterni: udaramo rječnikom po teškim JOIN-ovima.

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

Kada

Jednokratna obrada (sortiranje ili jedinstveno uklanjanje) velikog broja zapisa ne stane u dodijeljenu memoriju za to.

Kako prepoznati

-> *
   && temp written > 0

Preporuke

Ako korištena količina memorije ne prelazi značajno postavljenu vrijednost parametra work_mem, vrijedi ga prilagoditi. Možete odmah u konfiguraciji za sve ili putem SET [LOCAL] za određeni upit/transaction.

Primjer:

SHOW work_mem;
-- "16MB"

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

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Ispravka:

SET work_mem = '128MB'; -- prije izvođenja upita

Recepti za bolesne SQL upite
[pogledati na explain.tensor.ru]

Iz razumljivih razloga, ako se koristi samo memorija, a ne disk, upit će biti izvršen mnogo brže. Ujedno, dio opterećenja s HDD-a također se smanjuje.

Ali treba razumjeti da nećemo uvijek moći dodijeliti puno memorije — nje jednostavno neće biti dovoljno za sve.

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

Kada

U bazu je odmah uneseno puno podataka, ali nisu imali vremena provesti ANALYZE.

Kako prepoznati

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

Preporuke

Ipak, provesti ANALYZE.

Više o ovoj situaciji opisano je u PostgreSQL Antipatterns: statistika je ključna.

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

Kada

Došlo je do čekanja na zaključavanje koje je postavio konkurentski upit ili nije bilo dovoljno hardverskih resursa CPU/hipervizora.

Kako prepoznati

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- malo smo čitali, ali predugo

Preporuke

Koristite vanjski sustav za praćenje poslužitelja radi otkrivanja zaključavanja ili nenormalne potrošnje resursa. O našem načinu organizacije ovog procesa za stotine poslužitelja već smo govorili ovdje i ovdje.

Recepti za bolesne SQL upite
Recepti za bolesne SQL upite

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster