Prije nekoliko mjeseci - javni u PostgreSQL.
Od tada ste ga upotrijebili više od 6000 puta, ali jedna od korisnih značajki možda je prošla nezapaženo strukturalne naznake, koji izgledaju otprilike ovako:
Poslušajte ih i vaši će zahtjevi "postati svilenkasto glatki". 🙂
Ali ozbiljno, mnoge situacije koje zahtjev čine sporim i "proždrljivim" u smislu resursa, su tipični i mogu se prepoznati po strukturi i podacima plana.
U tom slučaju svaki pojedini programer neće morati sam tražiti opciju optimizacije, oslanjajući se isključivo na vlastito iskustvo - možemo mu reći što se ovdje događa, koji bi mogao biti razlog i kako doći do rješenja. Što smo i učinili.
Pogledajmo pobliže te slučajeve - kako su definirani i do kojih preporuka vode.
Za bolje uranjanje u temu, prvo možete poslušati odgovarajući blok iz , a tek onda prijeđite na detaljnu analizu svakog primjera:
#1: indeks "slabo sortiranje"
Kad nastane
Prikaži posljednju fakturu za klijenta "LLC Kolokolchik".
Kako identificirati
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Preporuke
Korišten indeks proširiti s poljima sortiranja.
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 se primjećuje da je indeksom oduzeto više od 100 zapisa, koji su zatim svi sortirani, a onda je ostao samo jedan.
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 "prefiks" indeks ništa lošiji od prethodnog za druge upite s fk, gdje sortiranje po pk nije bilo i nije (više o tome možete pročitati ). Konkretno, to će osigurati normalno eksplicitna podrška stranog ključa ovim poljem.
#2: sjecište indeksa (BitmapAnd)
Kad nastane
Prikaži sve ugovore za klijenta "LLC Kolokolchik" sklopljene u ime "NJSC Lyutik".
Kako identificirati
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanPreporuke
stvoriti kompozitni indeks 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);
Ovdje je dobitak manji, jer je Bitmap Heap Scan samo po sebi prilično učinkovito. Ali svejedno 7x brže i 2.5x manje čitanja.
#3: Kombiniranje indeksa (BitmapOr)
Kad nastane
Prikaži prvih 20 najstarijih "vlastitih" ili nedodijeljenih zahtjeva za obradu, s vlastitim prioritetom.
Kako identificirati
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanPreporuke
Za korištenje UNIJA [SVE] za kombiniranje podupita 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 dobiveno u prvom bloku, pa drugi, sa “skupljim” Bitmap Heap Scanom, nije niti 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 naređen izbor po nekoliko tipki (a ne samo za const/NULL par) raspravlja se u članku .
#4: Previše čitamo
Kad nastane
U pravilu se događa kada se postojećem zahtjevu želi “priložiti još jedan filter”.
“I nemaš isto, ali s bisernim gumbima? " film "Dijamantna ruka"
Na primjer, izmjenom gornjeg zadatka, prikazati prvih 20 najstarijih "kritičnih" zahtjeva za obradu, bez obzira na njihovu svrhu.
Kako identificirati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Preporuke
Stvorite [više] specijaliziranih indeks s klauzulom WHERE ili uključite dodatna polja u indeks.
Ako je uvjet filtriranja "statičan" za vaše zadatke - tj ne uključuje proširenje popis vrijednosti u budućnosti - bolje je koristiti WHERE indeks. Razni Boolean/enum statusi dobro se uklapaju u ovu kategoriju.
Ako je uvjet filtracije može poprimiti različite vrijednosti, onda je bolje proširiti indeks ovim poljima - kao u situaciji s BitmapAnd gore.
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: rijetka tablica
Kad nastane
Razni pokušaji da sami napravite red čekanja za obradu zadataka, kada veliki broj ažuriranja / brisanja zapisa na tablici dovode do situacije velikog broja "mrtvih" zapisa.
Kako identificirati
-> 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 provodite redovito VAKUUM [PUN] 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 izgledom upita kada se pozivaju iz poslovne logike, poput onih o kojima se govori u .
Ali moramo shvatiti da čak ni VACUUM FULL ne može uvijek pomoći. U takvim slučajevima trebali biste se upoznati s algoritmom iz članka. .
#6: čitanje iz "sredine" indeksa
Kad nastane
Čini se da su malo čitali, i sve je bilo indeksirano, i nisu nikoga dodatno filtrirali - ali ipak, pročitano je znatno više stranica nego što bismo željeli.
Kako identificirati
-> 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 - najvjerojatnije, dio indeksa nije postavljen. Najvjerojatnije ćete morati stvoriti sličan indeks, ali bez prefiksnih 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 što se tiče indeksa, ali nekako sumnjivo - za svaki od 20 pročitanih zapisa trebalo je oduzeti 4 stranice podataka, 32 KB po zapisu - nije li masno? Da i naziv indeksa tbl_fk_org_fk_cli_idx navodi na razmišljanje.
Popravljamo:
CREATE INDEX ON tbl(fk_cli);
Odjednom - 10 puta brže i 4 puta manje za čitanje!
Više primjera neučinkovitog korištenja indeksa potražite u članku .
#7: CTE × CTE
Kad nastane
Na zahtjev postigao "masni" CTE s različitih stolova, a zatim odlučio učiniti između njih JOIN.
Slučaj je relevantan za verzije ispod v12 ili zahtjeve s WITH MATERIALIZED.
Kako identificirati
-> 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: zamijeniti na disk (privremeno napisano)
Kad nastane
Jednokratna obrada (sortiranje ili jedinstvenost) velikog broja zapisa ne stane u memoriju koja je za to dodijeljena.
Kako identificirati
-> *
&& temp written > 0Preporuke
Ako količina memorije koju operacija koristi ne premašuje uvelike postavljenu vrijednost parametra , trebalo bi to ispraviti. Možeš odmah u konfiguraciji za sve, ili možeš kroz 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čitih razloga, ako se koristi samo memorija, a ne disk, tada će upit biti mnogo brži. Istodobno, dio opterećenja se također uklanja s HDD-a.
Ali morate shvatiti da dodjeljivanje puno memorije također neće uvijek funkcionirati - jednostavno neće biti dovoljno za sve.
#9: Nebitna statistika
Kad nastane
Puno se ulilo u bazu odjednom, ali nisu imali vremena to otjerati ANALYZE.
Kako identificirati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Preporuke
Potrošite isto ANALYZE.
Ova situacija je detaljnije opisana u .
#10: "nešto nije u redu"
Kad nastane
Došlo je do zaključavanja koje je čekalo na konkurentski zahtjev ili nije bilo dovoljno hardverskih resursa procesora/hipervizora.
Kako identificirati
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Preporuke
Koristite vanjski sustav praćenja poslužitelj za blokiranje ili abnormalnu potrošnju resursa. Već smo govorili o našoj verziji organiziranja ovog procesa za stotine poslužitelja. и .
Izvor: www.habr.com