Måneder siden - offentligt til PostgreSQL.
Du har brugt det over 6000 gange siden da, men en af de praktiske funktioner er måske gået ubemærket hen er strukturelle spor, som ser sådan ud:
Lyt til dem, og dine anmodninger vil "blive silkebløde". 🙂
Men seriøst, mange situationer, der gør en anmodning langsom og "frosser" med hensyn til ressourcer, er typiske og kan genkendes af planens struktur og data.
I dette tilfælde behøver hver enkelt udvikler ikke at lede efter en optimeringsmulighed alene, udelukkende afhængig af sin egen erfaring - vi kan fortælle ham, hvad der sker her, hvad der kunne være årsagen, og hvordan man kommer med en løsning. Hvilket vi gjorde.
Lad os se nærmere på disse sager – hvordan de defineres, og hvilke anbefalinger de fører til.
For en bedre fordybelse i emnet kan du først lytte til den tilsvarende blok fra , og kun derefter gå til en detaljeret analyse af hvert eksempel:
#1: indeks "undersortering"
Hvornår
Vis den sidste faktura for klienten "LLC Kolokolchik".
Sådan identificeres
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Anbefalinger
Indeks brugt udvide med sorteringsfelter.
Eksempel:
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 straks bemærke, at mere end 100 poster blev trukket fra indekset, som så alle blev sorteret, og så var den eneste tilbage.
Vi fikser:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Selv på sådan en primitiv prøve - 8.5x hurtigere og 33x færre læsninger. Effekten bliver tydeligere, jo flere "fakta" du har for hver værdi. fk.
Jeg bemærker, at et sådant indeks vil fungere som et "præfiks"-indeks, der ikke er værre end det forrige for andre forespørgsler med fk, hvor der sorteres efter pk var ikke og er ikke (du kan læse mere om dette ). Især vil det give alm eksplicit fremmednøglestøtte ved dette felt.
#2: indeks skæringspunkt (BitmapAnd)
Hvornår
Vis alle kontrakter for klienten "LLC Kolokolchik" indgået på vegne af "NJSC Lyutik".
Sådan identificeres
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanAnbefalinger
skabe sammensat indeks efter felter fra begge kilder eller udvid et af de eksisterende felter fra det andet.
Eksempel:
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 fikser:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Her er gevinsten mindre, da Bitmap Heap Scan er ret effektiv alene. Men alligevel 7x hurtigere og 2.5x færre læsninger.
#3: Kombination af indekser (BitmapOr)
Hvornår
Vis de første 20 ældste "egne" eller ikke-tildelte anmodninger til behandling, med egne i prioritet.
Sådan identificeres
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanAnbefalinger
Brug UNION [ALLE] at kombinere underforespørgsler for hver af betingelses-ELLER-blokkene.
Eksempel:
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 fikser:
(
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 udnyttede det faktum, at alle 20 nødvendige poster blev indhentet med det samme i den første blok, så den anden, med den mere "dyre" Bitmap Heap Scan, blev ikke engang udført - som et resultat 22x hurtigere, 44x færre læsninger!
En mere detaljeret historie om denne optimeringsmetode på konkrete eksempler kan læses i artikler и .
Generaliseret version ordnet valg efter flere taster (og ikke kun for const/NULL-parret) diskuteres i artiklen .
#4: Vi læser for meget
Hvornår
Som regel opstår det, når du vil "tilknytte endnu et filter" til en eksisterende anmodning.
“Og du har ikke det samme, men med perleknapper? " filmen "Diamond Hand"
For eksempel ved at ændre opgaven ovenfor, vis de første 20 ældste "kritiske" anmodninger til behandling, uanset deres formål.
Sådan identificeres
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Anbefalinger
Opret [mere] specialiseret indeks med WHERE-klausul eller inkludere yderligere felter i indekset.
Hvis filtreringsbetingelsen er "statisk" for dine opgaver - dvs omfatter ikke udvidelse liste over værdier i fremtiden - det er bedre at bruge et WHERE-indeks. Forskellige boolean/enum-statusser passer godt ind i denne kategori.
Hvis filtreringstilstanden kan antage forskellige værdier, er det bedre at udvide indekset med disse felter - som i situationen med BitmapAnd ovenfor.
Eksempel:
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 fikser:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Som du kan se, er filtreringen fra planen helt væk, og anmodningen er blevet 5 gange hurtigere.
#5: sparsomt bord
Hvornår
Forskellige forsøg på at lave din egen opgavebehandlingskø, når et stort antal opdateringer/sletninger af poster på bordet fører til en situation med et stort antal "døde" poster.
Sådan identificeres
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Anbefalinger
Manuelt udføres regelmæssigt VAKUUM [FULD] eller opnå tilstrækkelig hyppig behandling ved at finjustere sine parametre, herunder .
I de fleste tilfælde er sådanne problemer forårsaget af dårligt forespørgselslayout, når de kaldes fra forretningslogik, som dem, der er beskrevet i .
Men vi må forstå, at selv VAKUUM FULD ikke altid kan hjælpe. I sådanne tilfælde bør du sætte dig ind i algoritmen fra artiklen. .
#6: læsning fra "midten" af indekset
Hvornår
Det ser ud til, at de læste lidt, og alt var indekseret, og de filtrerede ikke nogen ekstra - men alligevel blev der læst betydeligt flere sider, end vi gerne ville.
Sådan identificeres
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Anbefalinger
Se nærmere på strukturen af det anvendte indeks og de nøglefelter, der er angivet i forespørgslen - højst sandsynligt, indeksdel ikke indstillet. Du skal højst sandsynligt oprette et lignende indeks, men uden præfiksfelter, eller .
Eksempel:
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;
Alt ser ud til at være i orden, selv med hensyn til indekset, men på en eller anden måde mistænkeligt - for hver af de 20 læste poster skulle 4 sider data trækkes fra, 32KB pr. post - er det ikke fed? Ja og indeksnavn tbl_fk_org_fk_cli_idx leder til eftertanke.
Vi fikser:
CREATE INDEX ON tbl(fk_cli);
Pludselig - 10 gange hurtigere og 4 gange mindre at læse!
For flere eksempler på ineffektiv brug af indekser, se artiklen .
#7: CTE × CTE
Hvornår
Efter anmodning scorede "fed" CTE fra forskellige borde, og derefter besluttede at gøre mellem dem JOIN.
Sagen er relevant for versioner under v12 eller anmodninger med WITH MATERIALIZED.
Sådan identificeres
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Anbefalinger
Analyser anmodningen omhyggeligt ? Hvis ja, så anvende "ordbog" i hstore/json efter modellen beskrevet i .
#8: swap til disk (temp skrevet)
Hvornår
Engangsbehandling (sortering eller entydiggørelse) af et stort antal poster passer ikke ind i den hukommelse, der er allokeret til dette.
Sådan identificeres
-> *
&& temp written > 0Anbefalinger
Hvis mængden af hukommelse, der bruges af operationen, ikke overstiger parameterens indstillede værdi væsentligt , bør det rettes. Du kan straks i konfigurationen for alle, eller du kan igennem SET [LOCAL] for en specifik anmodning/transaktion.
Eksempel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Vi fikser:
SET work_mem = '128MB'; -- перед выполнением запроса
Af indlysende årsager, hvis kun hukommelse bruges, og ikke disk, så vil forespørgslen blive udført meget hurtigere. Samtidig fjernes også en del af belastningen fra HDD'en.
Men du skal forstå, at tildeling af meget hukommelse heller ikke altid vil fungere - det vil simpelthen ikke være nok for alle.
#9: Irrelevant statistik
Hvornår
Der blev hældt meget i basen på én gang, men de nåede ikke at køre det væk ANALYZE.
Sådan identificeres
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Anbefalinger
Brug det samme ANALYZE.
Denne situation er beskrevet mere detaljeret i .
#10: "noget gik galt"
Hvornår
Der var en lås, der ventede på en konkurrerende anmodning, eller der var ikke nok CPU/hypervisor-hardwareressourcer.
Sådan identificeres
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Anbefalinger
Brug en ekstern overvågningssystem server til blokering eller unormalt ressourceforbrug. Vi har allerede talt om vores version af at organisere denne proces for hundredvis af servere. и .
Kilde: www.habr.com