Prije nekoliko mjeseci - javni u PostgreSQL.
Koristili ste ga više od 6000 puta tijekom godina, ali jedna od njegovih praktičnih značajki možda je ostala nezapažena - strukturni znakovi, koji izgledaju otprilike ovako:
Poslušajte ih i vaši će zahtjevi postati „glatki i svilenkasti“. 🙂
Ali ozbiljno, postoje mnoge situacije koje usporavaju upit i zahtijevaju puno resursa, tipični su i mogu se prepoznati po strukturi i podacima plana.
U ovom slučaju, svaki pojedini programer neće morati samostalno tražiti opciju optimizacije, oslanjajući se isključivo na svoje iskustvo - možemo im reći što se ovdje događa, koji bi mogao biti uzrok i Kako se može pristupiti rješenju?Što smo i učinili.
Pogledajmo pobliže ove 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 zatim prijeđite na detaljnu analizu svakog primjera:
#1: Indeksiranje "podsortiranja"
Kad nastane
Prikažite najnoviji račun za klijenta "OOO Kolokolchik".
Kako identificirati
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Preporuke
Korišteni indeks proširi s poljima 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 iz indeksa oduzeto više od 100 zapisa, koji su zatim svi sortirani, a zatim je ostao samo jedan.
Popravljamo:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Čak i s tako primitivnim uzorkom - 8.5 puta brže i 33 puta manje očitanjaUčinak će biti očitiji što više „činjenica“ imate za svaku vrijednost. fk.
Želio bih napomenuti da će takav indeks funkcionirati kao "prefiksni" indeks ne gore od prethodnog za ostale upite s fk, gdje sortiranje po pk nije bilo i nema (možete pročitati više o ovome) ). Posebno će osigurati normalno podrška za eksplicitne strane ključeve preko ovog polja.
#2: Presjek indeksa (BitmapAnd)
Kad nastane
Prikaži sve ugovore za klijenta "OOO Kolokolchik", sklopljene u ime "NAO Lyutik".
Kako identificirati
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanPreporuke
stvoriti kompozitni indeks poljima iz oba izvora ili proširiti jedno od postojećih polja poljima 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);
Dobitak je ovdje manji, budući da je Bitmap Heap Scan prilično učinkovit sam po sebi. Ali ipak 7 puta brže i 2.5 puta manje očitanja.
#3: Kombiniranje indeksa (BitmapOr)
Kad nastane
Prikažite prvih 20 najstarijih "vlastitih" ili nedodijeljenih zahtjeva za obradu, s prioritetom vlastitih zahtjeva.
Kako identificirati
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanPreporuke
Za korištenje UNIJA [SVI] kombinirati podupite za svaki od ILI blokova uvjeta.
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 primljeno u prvom bloku, pa drugi, sa "skupljim" Bitmap Heap Scan-om, nije ni izvršen - kao rezultat toga 22 puta brže, 44 puta manje očitanja!
Detaljniji prikaz ove metode optimizacije koristeći konkretne primjere možete pročitati u člancima и .
Generalizirana verzija uređeni odabir pomoću nekoliko ključeva (i ne samo za par const/NULL) raspravlja se u članku .
#4: Previše čitamo
Kad nastane
Obično se to javlja kada želite "dodati još jedan filter" postojećem upitu.
„I ti nemaš isto, ali s gumbima od sedefa? " igrani film "Dijamantna ruka"
Na primjer, izmjena gornjeg zadatka kako bi se prikazalo prvih 20 najstarijih "kritičnih" zahtjeva za obradu, bez obzira na njihovu dodjelu.
Kako identificirati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Preporuke
Izradite [više] specijaliziranih indeks s uvjetom WHERE ili uključite dodatna polja u indeks.
Ako je uvjet filtra "statičan" za vaše zadatke - tj. ne podrazumijeva proširenje Za popis vrijednosti u budućnosti, bolje je koristiti WHERE indeks. Različiti booleovski/enum statusi dobro se uklapaju u ovu kategoriju.
Ako je uvjet filtracije može poprimiti različite vrijednosti, onda je bolje proširiti indeks s tim 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 vidimo, filtriranje je potpuno nestalo iz plana, a upit je postao 5 puta brže.
#5: Rijetka tablica
Kad nastane
Razni pokušaji stvaranja prilagođenog reda čekanja za obradu zadataka kada veliki broj ažuriranja/brisanja zapisa u tablici dovede 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
Redovito to ručno provodite VAKUUM [PUN] ili postići dovoljno čestu obradu finim podešavanjem njegovih parametara, uključujući .
U većini slučajeva, takvi problemi su uzrokovani lošom kompozicijom upita prilikom pozivanja poslovne logike, kao što je to opisano u .
No važno je shvatiti da čak ni VACUUM FULL možda neće uvijek pomoći. Za takve slučajeve vrijedi se upoznati s algoritmom u članku. .
#6: Čitanje iz "sredine" indeksa
Kad nastane
Čini se kao da smo malo čitali, sve je bilo indeksirano i nisu izostavljene nepotrebne informacije - ali i dalje smo pročitali 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 postavljenNajvjerojatnije ć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; 
Sve se čini u redu, čak i s indeksom, ali je malo sumnjivo - za svaki od 20 pročitanih zapisa trebalo je pročitati 4 stranice podataka, 32 KB po zapisu - nije li to malo previše? I naziv indeksa također. tbl_fk_org_fk_cli_idx poticajno na razmišljanje.
Popravljamo:
CREATE INDEX ON tbl(fk_cli); 
Odjednom - 10 puta brže i 4 puta manje za čitanje!
Drugi primjeri situacija u kojima se indeksi ne koriste učinkovito mogu se vidjeti u članku .
#7: CTE × CTE
Kad nastane
U zahtjevu regrutirani "debeli" CTE iz različitih tablica, a zatim odlučili uspostaviti vezu između njih JOIN.
Slučaj je relevantan za verzije starije od v12 ili zahtjeve s WITH MATERIALIZED.
Kako identificirati
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Preporuke
Pažljivo analizirajte zahtjev - i Ako je tako, onda primijeni "rječnik" na hstore/json prema modelu opisanom u .
#8: prebacivanje na disk (privremeno zapisano)
Kad nastane
Jednokratna obrada (sortiranje ili unifikacija) velikog broja zapisa ne stane u memoriju dodijeljenu za tu svrhu.
Kako identificirati
-> *
&& temp written > 0Preporuke
Ako količina memorije koju operacija koristi ne prelazi znatno postavljenu vrijednost parametra , vrijedi prilagoditi. Možete to učiniti odmah u konfiguraciji za sve ili putem 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 prostor na disku, upit će se izvršiti puno brže. To također smanjuje opterećenje tvrdog diska.
Ali moramo shvatiti da također nije moguće uvijek dodijeliti puno memorije - jednostavno neće biti dovoljno za sve.
#9: Zastarjela statistika
Kad nastane
Ubacili su puno podataka u bazu podataka odjednom, ali nisu imali vremena sve to provjeriti. ANALYZE.
Kako identificirati
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Preporuke
Učinimo to svejedno ANALYZE.
Ova situacija je detaljnije opisana u .
#10: "Nešto je pošlo po zlu"
Kad nastane
Došlo je do čekanja na zaključavanje koje je nametnuo konkurentski zahtjev ili nije bilo dovoljno hardverskih resursa CPU-a/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žitelje za blokade ili abnormalnu potrošnju resursa. Već smo opisali naš pristup organiziranju ovog procesa za stotine poslužitelja. и .
Izvor: www.habr.com
