Månader sedan - offentlig till PostgreSQL.
Du har använt den över 6000 XNUMX gånger sedan dess, men en av de praktiska funktionerna kan ha gått obemärkt förbi är strukturella ledtrådar, som ser ut ungefär så här:
Lyssna på dem så kommer dina önskemål "bli silkeslen". 🙂
Men allvarligt talat, många situationer som gör en begäran långsam och "frossig" när det gäller resurser, är typiska och kan kännas igen av planens struktur och data.
I det här fallet kommer varje enskild utvecklare inte behöva leta efter ett optimeringsalternativ på egen hand, enbart förlita sig på sin egen erfarenhet - vi kan berätta för honom vad som händer här, vad kan vara orsaken och hur man kommer på en lösning. Vilket är vad vi gjorde.
Låt oss titta närmare på dessa fall – hur de definieras och vilka rekommendationer de leder till.
För en bättre fördjupning i ämnet kan du först lyssna på motsvarande block från , och först därefter gå till en detaljerad analys av varje exempel:
#1: indexera "undersortering"
När inträffar det
Visa den sista fakturan för kunden "LLC Kolokolchik".
Hur man identifierar
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Rekommendationer
Index används utöka med sorteringsfält.
Exempel:
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;
Du kan omedelbart märka att mer än 100 poster subtraherades av indexet, som sedan alla sorterades, och sedan var den enda kvar.
Vi fixar:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Även på ett sådant primitivt prov - 8.5x snabbare och 33x färre läsningar. Effekten blir tydligare, ju mer "fakta" du har för varje värde. fk.
Jag noterar att ett sådant index fungerar som ett "prefix"-index inte sämre än det föregående för andra frågor med fk, där sortering efter pk var inte och är inte (du kan läsa mer om detta ). I synnerhet kommer det att ge normala uttryckligt stöd för främmande nyckel av detta fält.
#2: index skärningspunkt (BitmapAnd)
När inträffar det
Visa alla kontrakt för kunden "LLC Kolokolchik" som ingåtts på uppdrag av "NJSC Lyutik".
Hur man identifierar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRekommendationer
skapa sammansatt index efter fält från båda källorna eller utöka ett av de befintliga fälten från det andra.
Exempel:
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); -- отбор по конкретной паре
Vi fixar:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Här är vinsten mindre, eftersom Bitmap Heap Scan är ganska effektiv på egen hand. Men ändå 7x snabbare och 2.5x färre läsningar.
#3: Kombinera index (BitmapOr)
När inträffar det
Visa de första 20 äldsta "egna" eller otilldelade förfrågningarna för behandling, med egna i prioritet.
Hur man identifierar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRekommendationer
Använd UNION [ALLA] för att kombinera underfrågor för vart och ett av villkors-ELLER-blocken.
Exempel:
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;
Vi fixar:
(
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, больше и не надо
Vi utnyttjade det faktum att alla 20 nödvändiga poster erhölls omedelbart i det första blocket, så det andra, med den "dyrare" Bitmap Heap Scan, kördes inte ens - som ett resultat 22x snabbare, 44x färre läsningar!
En mer detaljerad berättelse om denna optimeringsmetod på konkreta exempel kan läsas i artiklar и .
Generaliserad version beställt urval med flera nycklar (och inte bara för paret const/NULL) diskuteras i artikeln .
#4: Vi läser för mycket
När inträffar det
Som regel inträffar det när du vill "bifoga ett annat filter" till en befintlig begäran.
"Och du har inte samma sak, men med pärlknappar? » filmen "Diamond Hand"
Om du till exempel ändrar uppgiften ovan, visar de första 20 äldsta "kritiska" förfrågningarna för behandling, oavsett syfte.
Hur man identifierar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Rekommendationer
Skapa [mer] specialiserad index med WHERE-sats eller inkludera ytterligare fält i indexet.
Om filtreringsvillkoret är "statiskt" för dina uppgifter - dvs omfattar inte expansion lista över värden i framtiden - det är bättre att använda ett WHERE-index. Olika booleska/enum-statusar passar väl in i denna kategori.
Om filtreringsvillkoret kan anta olika värderingar, är det bättre att utöka indexet med dessa fält - som i situationen med BitmapAnd ovan.
Exempel:
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;
Vi fixar:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Som du kan se är filtreringen från planen helt borta, och begäran har blivit 5 gånger snabbare.
#5: glest bord
När inträffar det
Olika försök att göra din egen uppgiftsbehandlingskö, när ett stort antal uppdateringar / raderingar av poster på bordet leder till en situation med ett stort antal "döda" poster.
Hur man identifierar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekommendationer
Utför manuellt regelbundet VAKUUM [FULL] eller uppnå tillräckligt frekvent behandling genom att finjustera dess parametrar, inklusive .
I de flesta fall orsakas sådana problem av dålig frågelayout när de anropas från affärslogik, som de som diskuteras i .
Men vi måste förstå att även VAKUUM FULL inte alltid kan hjälpa. I sådana fall bör du bekanta dig med algoritmen från artikeln. .
#6: läsning från "mitten" av indexet
När inträffar det
Det verkar som att de läste lite, och allt var indexerat, och de filtrerade inte någon extra - men ändå lästes betydligt fler sidor än vi skulle önska.
Hur man identifierar
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekommendationer
Ta en närmare titt på strukturen för det använda indexet och nyckelfälten som anges i frågan - troligen, indexdel ej inställd. Du kommer troligen att behöva skapa ett liknande index, men utan prefixfält, eller .
Exempel:
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;
Allt verkar vara bra, även när det gäller indexet, men på något sätt misstänkt - för var och en av de 20 lästa posterna måste 4 sidor data subtraheras, 32KB per post - är det inte fetstilt? Ja och indexnamn tbl_fk_org_fk_cli_idx leder till tankar.
Vi fixar:
CREATE INDEX ON tbl(fk_cli);
Plötsligt - 10 gånger snabbare och 4 gånger mindre att läsa!
För fler exempel på ineffektiv användning av index, se artikeln .
#7: CTE × CTE
När inträffar det
På begäran fick "fet" CTE från olika bord och bestämde sig sedan för att göra mellan dem JOIN.
Fallet är relevant för versioner under v12 eller förfrågningar med WITH MATERIALIZED.
Hur man identifierar
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Rekommendationer
Analysera begäran noggrant ? Om ja, då tillämpa "ordbok" i hstore/json enligt den modell som beskrivs i .
#8: byt till disk (temp skrivet)
När inträffar det
Engångsbearbetning (sortering eller unikisering) av ett stort antal poster passar inte in i minnet som tilldelats för detta.
Hur man identifierar
-> *
&& temp written > 0Rekommendationer
Om mängden minne som används av operationen inte avsevärt överstiger det inställda värdet för parametern , det bör korrigeras. Du kan direkt i konfigurationen för alla, eller så kan du gå igenom SET [LOCAL] för en specifik begäran/transaktion.
Exempel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Vi fixar:
SET work_mem = '128MB'; -- перед выполнением запроса
Av uppenbara skäl, om bara minne används, och inte disk, så kommer frågan att exekveras mycket snabbare. Samtidigt tas också en del av belastningen bort från hårddisken.
Men vi måste förstå att det inte alltid kommer att fungera att tilldela mycket minne heller - det räcker inte för alla.
#9: Irrelevant statistik
När inträffar det
Det hälldes mycket i basen på en gång, men de hann inte köra bort det ANALYZE.
Hur man identifierar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rekommendationer
Spendera samma sak ANALYZE.
Denna situation beskrivs mer i detalj i .
#10: "något gick fel"
När inträffar det
Det fanns ett lås som väntade på en konkurrerande begäran, eller så fanns det inte tillräckligt med CPU/hypervisor-hårdvaruresurser.
Hur man identifierar
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Rekommendationer
Använd en extern övervakningssystem server för blockering eller onormal resursförbrukning. Vi har redan pratat om vår version av att organisera denna process för hundratals servrar. и .
Källa: will.com