Prije nekoliko mjeseci - javno na PostgreSQL.
Od tada ste ga koristili preko 6000 puta, ali jedna od zgodnih funkcija koja je možda prošla nezapaženo je strukturalni tragovi, koji izgledaju otprilike ovako:
Slušajte ih i vaši zahtjevi će "postati svilenkasto glatki". 🙂
Ali ozbiljno, mnoge situacije koje čine zahtjev sporim i „proždrljivim“ u smislu resursa, tipični su i mogu se prepoznati po strukturi i podacima plana.
U ovom slučaju, svaki pojedinačni programer neće morati sam tražiti opciju optimizacije, oslanjajući se isključivo na vlastito iskustvo - možemo mu reći šta se ovdje događa, što bi mogao biti razlog i kako doći do rješenja. Što smo i uradili.
Pogledajmo pobliže ove slučajeve – kako su definirani i do kakvih preporuka vode.
Za bolje uranjanje u temu, prvo možete poslušati odgovarajući blok iz , a tek onda idite na detaljnu analizu svakog primjera:
#1: indeks "undersorting"
Kada nastaje
Prikaži posljednju fakturu za klijenta "LLC Kolokolchik".
Kako prepoznati
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
preporuke
Korišteni indeks proširite polja za sortiranje.
Primjer:
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;
Odmah možete primijetiti da je indeksu oduzeto više od 100 zapisa, koji su potom svi sortirani, a onda je ostao jedini.
popravljamo:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Čak i na tako primitivnom uzorku - 8.5x brže i 33x manje čitanja. Učinak će biti jasniji, što više „činjenica“ imate za svaku vrijednost. fk.
Napominjem da će takav indeks raditi kao "prefiksni" indeks ništa lošiji od prethodnog za druge upite sa fk, gdje sortiranje po pk nije bilo i nije (možete pročitati više o tome ). Konkretno, to će osigurati normalno eksplicitna podrška stranog ključa ovim poljem.
#2: presjek indeksa (BitmapAnd)
Kada nastaje
Prikaži sve ugovore za klijenta "LLC Kolokolchik" zaključene u ime "NJSC Lyutik".
Kako prepoznati
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanpreporuke
stvoriti kompozitni indeks po poljima iz oba izvora ili proširite jedno od postojećih polja iz drugog.
Primjer:
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); -- отбор по конкретной паре
popravljamo:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Ovde je dobitak manji, pošto je Bitmap Heap skeniranje prilično efikasno samo po sebi. Ali svejedno 7x brže i 2.5x manje čitanja.
#3: Kombiniranje indeksa (BitmapOr)
Kada nastaje
Prikaži prvih 20 najstarijih "vlastitih" ili nedodijeljenih zahtjeva za obradu, sa vlastitim prioritetom.
Kako prepoznati
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanpreporuke
Upotreba UNION [SVI] za kombiniranje potupita za svaki od blokova uvjeta ILI.
Primjer:
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;
popravljamo:
(
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, больше и не надо
Iskoristili smo činjenicu da je svih 20 potrebnih zapisa odmah dobijeno u prvom bloku, pa drugi, sa "skupljim" Bitmap Heap Scan, nije ni izvršen - kao rezultat 22x brže, 44x manje čitanja!
Detaljnija priča o ovoj metodi optimizacije na konkretnim primjerima može se pročitati u člancima и .
Generalizirana verzija poredani izbor po nekoliko tastera (i to ne samo za par const / NULL) raspravlja se u članku .
#4: Previše čitamo
Kada nastaje
Po pravilu se javlja kada želite da „prikačite drugi filter” postojećem zahtevu.
“I nemaš isto, ali sa bisernim dugmadima? film "Dijamantska ruka"
Na primjer, modificirajući gornji zadatak, prikažite prvih 20 najstarijih "kritičnih" zahtjeva za obradu, bez obzira na njihovu svrhu.
Kako prepoznati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
preporuke
Kreirajte [više] specijalizirano indeks sa WHERE klauzulom ili uključite dodatna polja u indeks.
Ako je uslov filtriranja "statičan" za vaše zadatke - tj ne uključuje proširenje listu vrijednosti u budućnosti - bolje je koristiti WHERE indeks. Različiti boolean/enum statusi dobro se uklapaju u ovu kategoriju.
Ako je stanje filtracije može poprimiti različite vrijednosti, bolje je proširiti indeks ovim poljima - kao u situaciji sa BitmapAnd iznad.
Primjer:
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;
popravljamo:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kao što vidite, filtriranje iz plana je potpuno nestalo, a zahtjev je postao 5 puta brže.
#5: rijetki sto
Kada nastaje
Različiti pokušaji da napravite vlastiti red za obradu zadataka, kada veliki broj ažuriranja/brisanja zapisa na tabeli dovode do situacije velikog broja "mrtvih" zapisa.
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
preporuke
Ručno izvodite redovno VAKUUM [PUNI] ili postići adekvatno čestu obradu finim podešavanjem njegovih parametara, uključujući .
U većini slučajeva, takvi problemi su uzrokovani lošim rasporedom upita kada se poziva iz poslovne logike, kao što su oni o kojima se govori u .
Ali moramo shvatiti da čak ni VAKUUM PUNI ne može uvijek pomoći. U takvim slučajevima trebali biste se upoznati s algoritmom iz članka. .
#6: čitanje od "sredine" indeksa
Kada nastaje
Čini se da su malo čitali, i sve je indeksirano, i nisu nikoga dodatno filtrirali - ali ipak je pročitano znatno više stranica nego što bismo željeli.
Kako prepoznati
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
preporuke
Pažljivo pogledajte strukturu korištenog indeksa i ključna polja navedena u upitu - najvjerovatnije, indeksni dio nije postavljen. Najvjerovatnije ćete morati kreirati sličan indeks, ali bez prefiks polja, ili .
Primjer:
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;
Čini se da je sve u redu, čak i u smislu indeksa, ali nekako sumnjivo - za svaki od 20 pročitanih zapisa trebalo je oduzeti 4 stranice podataka, 32 KB po zapisu - zar nije podebljano? Da i naziv indeksa tbl_fk_org_fk_cli_idx navodi na razmišljanje.
popravljamo:
CREATE INDEX ON tbl(fk_cli);
iznenada - 10 puta brže i 4 puta manje za čitanje!
Za više primjera neefikasne upotrebe indeksa pogledajte članak .
#7: CTE × CTE
Kada nastaje
Na zahtjev postigao "debeli" CTE sa različitih stolova, a zatim odlučio da radim između njih JOIN.
Slučaj je relevantan za verzije ispod v12 ili zahtjeve sa WITH MATERIALIZED.
Kako prepoznati
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
preporuke
Pažljivo analizirajte zahtjev ? Ako da, onda primijeniti "rječnik" u hstore/json prema modelu opisanom u .
#8: prebacite na disk (temp napisano)
Kada nastaje
Jednokratna obrada (sortiranje ili jedinstvenost) velikog broja zapisa ne uklapa se u memoriju koja je za to dodijeljena.
Kako prepoznati
-> *
&& temp written > 0preporuke
Ako količina memorije koju koristi operacija ne prelazi mnogo zadatu vrijednost parametra , to bi trebalo ispraviti. Možete odmah u konfiguraciji za svakoga, a možete i preko SET [LOCAL] za određeni zahtjev/transakciju.
Primjer:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
popravljamo:
SET work_mem = '128MB'; -- перед выполнением запроса
Iz očiglednih razloga, ako se koristi samo memorija, a ne disk, onda će se upit izvršiti mnogo brže. U isto vrijeme, dio opterećenja se također uklanja sa HDD-a.
Ali morate shvatiti da ni dodjela puno memorije uvijek neće raditi - jednostavno neće biti dovoljno za sve.
#9: Nebitna statistika
Kada nastaje
Odjednom je u bazu izliveno mnogo, ali nisu imali vremena da je otjeraju ANALYZE.
Kako prepoznati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10preporuke
Potrošite isto ANALYZE.
Ova situacija je detaljnije opisana u .
#10: "nešto je pošlo po zlu"
Kada nastaje
Došlo je do zaključavanja koje je čekalo konkurentski zahtjev ili nije bilo dovoljno CPU/hipervizora hardverskih resursa.
Kako prepoznati
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
preporuke
Koristite eksterno sistem za praćenje server za blokiranje ili abnormalnu potrošnju resursa. Već smo govorili o našoj verziji organizacije ovog procesa za stotine servera. и .
izvor: www.habr.com