'N Paar maande gelede - publiek na PostgreSQL.
Jy het dit sedertdien meer as 6000 XNUMX keer gebruik, maar een van die handige kenmerke wat dalk onopgemerk gebly het, is strukturele leidrade, wat so iets lyk:
Luister na hulle en jou versoeke sal "syglad word". 🙂
Maar ernstig, baie situasies wat 'n versoek stadig en "vraatrig" maak in terme van hulpbronne, is tipies en kan herken word aan die struktuur en data van die plan.
In hierdie geval hoef elke individuele ontwikkelaar nie op sy eie na 'n optimaliseringsopsie te soek nie, en slegs op sy eie ervaring staatmaak - ons kan hom vertel wat hier gebeur, wat die rede kan wees, en hoe om met 'n oplossing vorendag te kom. Dit is wat ons gedoen het.
Kom ons kyk van naderby na hierdie gevalle – hoe dit gedefinieer word en tot watter aanbevelings dit lei.
Vir 'n beter onderdompeling in die onderwerp, kan jy eers na die ooreenstemmende blok van luister , en gaan dan eers na 'n gedetailleerde ontleding van elke voorbeeld:
#1: indeks "ondersorteer"
Wanneer ontstaan
Wys die laaste faktuur vir die kliënt "LLC Kolokolchik".
Hoe om te identifiseer
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Aanbevelings
Indeks gebruik brei uit met sorteervelde.
Voorbeeld:
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;
Jy kan dadelik agterkom dat meer as 100 rekords deur die indeks afgetrek is, wat dan almal gesorteer is, en dan het die enigste een oorgebly.
Ons maak reg:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Selfs op so 'n primitiewe monster - 8.5x vinniger en 33x minder leeswerk. Die effek sal duideliker wees, hoe meer "feite" jy vir elke waarde het. fk.
Ek neem kennis dat so 'n indeks sal werk as 'n "voorvoegsel" indeks nie slegter as die vorige een vir ander navrae met fk, waar sorteer volgens pk was nie en is nie (jy kan meer hieroor lees ). In die besonder sal dit normaal voorsien eksplisiete buitelandse sleutel ondersteuning deur hierdie veld.
#2: indeks kruising (BitmapAnd)
Wanneer ontstaan
Wys alle kontrakte vir die kliënt "LLC Kolokolchik" gesluit namens "NJSC Lyutik".
Hoe om te identifiseer
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanAanbevelings
Skep saamgestelde indeks deur velde uit beide bron of brei een van die bestaande velde uit die tweede uit.
Voorbeeld:
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); -- отбор по конкретной паре
Ons maak reg:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Hier is die wins kleiner, aangesien Bitmap Heap Scan op sy eie redelik effektief is. Maar in elk geval 7x vinniger en 2.5x minder leeswerk.
#3: Kombinasie van indekse (BitmapOr)
Wanneer ontstaan
Wys die eerste 20 oudste "eie" of nie-toegewysde versoeke vir verwerking, met eie in prioriteit.
Hoe om te identifiseer
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanAanbevelings
maak gebruik van UNIE [ALLE] om subnavrae vir elk van die voorwaarde OF blokke te kombineer.
Voorbeeld:
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;
Ons maak reg:
(
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, больше и не надо
Ons het voordeel getrek uit die feit dat al 20 nodige rekords onmiddellik in die eerste blok ontvang is, dus is die tweede een, met die "duurder" Bitmap Heap Scan, nie eers uitgevoer nie - gevolglik 22x vinniger, 44x minder leeswerk!
'N Meer gedetailleerde storie oor hierdie optimalisering metode op konkrete voorbeelde kan in artikels gelees word и .
Algemene weergawe georden seleksie deur verskeie sleutels (en nie net vir die const/NULL-paar nie) word in die artikel bespreek .
#4: Ons lees te veel
Wanneer ontstaan
As 'n reël vind dit plaas wanneer u 'n ander filter aan 'n bestaande versoek wil koppel.
“En jy het nie dieselfde nie, maar met pêrelknoppies? " film "Diamond Hand"
Byvoorbeeld, deur die taak hierbo te wysig, wys die eerste 20 oudste "kritieke" versoeke vir verwerking, ongeag hul doel.
Hoe om te identifiseer
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Aanbevelings
Skep [meer] gespesialiseerde indeks met WHERE klousule of sluit bykomende velde in die indeks in.
As die filtertoestand "staties" is vir jou take - dit wil sê sluit nie uitbreiding in nie lys van waardes in die toekoms - dit is beter om 'n WHERE-indeks te gebruik. Verskeie boolean/enum-statusse pas goed in hierdie kategorie.
As die filtrasie toestand verskillende waardes kan aanneem, is dit beter om die indeks uit te brei met hierdie velde - soos in die situasie met BitmapAnd hierbo.
Voorbeeld:
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;
Ons maak reg:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Soos u kan sien, is die filter van die plan heeltemal weg, en die versoek het geword 5 keer vinniger.
#5: yl tafel
Wanneer ontstaan
Verskeie pogings om jou eie taakverwerking-tou te maak, wanneer 'n groot aantal opdaterings / skrapings van rekords op die tafel lei tot 'n situasie van 'n groot aantal "dooie" rekords.
Hoe om te identifiseer
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Aanbevelings
Voer gereeld met die hand uit VAKUUM [VOL] of voldoende gereelde verwerking bereik deur sy parameters fyn in te stel, insluitend .
In die meeste gevalle word hierdie probleme veroorsaak deur swak navraaguitleg wanneer dit vanaf besigheidslogika geroep word, soos dié wat bespreek word in .
Maar ons moet verstaan dat selfs VAKUUM VOL nie altyd kan help nie. In sulke gevalle moet u uself vertroud maak met die algoritme uit die artikel. .
#6: lees vanaf die "middel" van die indeks
Wanneer ontstaan
Dit blyk dat hulle 'n bietjie gelees het, en alles is geïndekseer, en hulle het niemand ekstra gefiltreer nie - maar steeds is aansienlik meer bladsye gelees as wat ons sou wou hê.
Hoe om te identifiseer
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Aanbevelings
Kyk noukeurig na die struktuur van die indeks wat gebruik word en die sleutelvelde wat in die navraag gespesifiseer is - heel waarskynlik, indeks deel nie gestel nie. Jy sal heel waarskynlik 'n soortgelyke indeks moet skep, maar sonder voorvoegselvelde, of .
Voorbeeld:
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;
Alles blyk in orde te wees, selfs in terme van die indeks, maar op een of ander manier verdag - vir elk van die 20 rekords wat gelees is, moes 4 bladsye data afgetrek word, 32KB per rekord - is dit nie vetdruk nie? Ja en indeks naam tbl_fk_org_fk_cli_idx lei tot denke.
Ons maak reg:
CREATE INDEX ON tbl(fk_cli);
Skielik - 10 keer vinniger en 4 keer minder om te lees!
Vir meer voorbeelde van ondoeltreffende gebruik van indekse, sien die artikel .
#7: CTE × CTE
Wanneer ontstaan
Op versoek "vet" CTE behaal van verskillende tafels, en toe besluit om tussen hulle te doen JOIN.
Die saak is relevant vir weergawes onder v12 of versoeke met WITH MATERIALIZED.
Hoe om te identifiseer
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Aanbevelings
Ontleed die versoek noukeurig ? Indien wel, dan pas "woordeboek" toe in hstore/json volgens die model beskryf in .
#8: ruil na skyf (temp geskryf)
Wanneer ontstaan
Eenmalige verwerking (sortering of uniekmaking) van 'n groot aantal rekords pas nie in die geheue wat hiervoor toegewys is nie.
Hoe om te identifiseer
-> *
&& temp written > 0Aanbevelings
As die hoeveelheid geheue wat deur die operasie gebruik word nie die gestelde waarde van die parameter aansienlik oorskry nie , moet dit reggestel word. Jy kan dadelik in die konfigurasie vir almal, of jy kan deur SET [LOCAL] vir 'n spesifieke versoek/transaksie.
Voorbeeld:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ons maak reg:
SET work_mem = '128MB'; -- перед выполнением запроса
Om ooglopende redes, as slegs geheue gebruik word en nie skyf nie, sal die navraag baie vinniger wees. Terselfdertyd word 'n deel van die vrag ook van die HDD verwyder.
Maar jy moet verstaan dat die toekenning van baie geheue ook altyd nie sal werk nie - dit sal eenvoudig nie vir almal genoeg wees nie.
#9: Irrelevante statistieke
Wanneer ontstaan
Baie is op een slag in die basis gegooi, maar hulle het nie tyd gehad om dit weg te ry nie ANALYZE.
Hoe om te identifiseer
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Aanbevelings
Spandeer dieselfde ANALYZE.
Hierdie situasie word in meer besonderhede beskryf in .
#10: "iets het verkeerd geloop"
Wanneer ontstaan
Daar was 'n slot wat op 'n mededingende versoek gewag het, of daar was nie genoeg SVE/hipervisor hardeware hulpbronne nie.
Hoe om te identifiseer
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Aanbevelings
Gebruik ekstern moniteringstelsel bediener vir blokkering of abnormale hulpbronverbruik. Ons het reeds gepraat oor ons weergawe om hierdie proses vir honderde bedieners te organiseer. и .
Bron: will.com