Virun e puer Méint - ëffentlech zu PostgreSQL.
Dir hutt et scho méi wéi 6000 Mol benotzt, awer eng praktesch Feature déi vläicht onnotéiert gaang ass ass strukturell Hiweiser, déi sou ausgesinn:
Lauschtert hinnen, an Är Ufroe ginn "glatt a seideg." 🙂
Awer eescht, vill Situatiounen, déi eng Ufro lues a Ressource-hongereg maachen sinn typesch a kënnen duerch d'Struktur an d'Donnéeë vum Plang erkannt ginn.
An dësem Fall muss all eenzelnen Entwéckler net eleng no enger Optimisatiounsoptioun sichen, eleng op seng Erfahrung vertrauen - mir kënnen him soen wat hei geschitt, wat kéint de Grond sinn, an wéi eng Léisung unzegoen. Dat ass wat mir gemaach hunn.
Loosst eis dës Fäll méi no kucken - wéi se definéiert sinn a wéi eng Empfehlungen se féieren.
Fir Iech besser an d'Thema z'ënnerhuelen, kënnt Dir fir d'éischt de entspriechende Block aus , an nëmmen dann op eng detailléiert Analyse vun all Beispill weider:
#1: Index "ënnersortéieren"
Wann entsteet
Weist déi lescht Rechnung fir de Client "LLC Kolokolchik".
Wéi identifizéieren
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recommandatiounen
Index benotzt mat Zort Felder ausbaue.
Beispill:
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;
Dir mierkt direkt, datt méi wéi 100 records aus dem Index ofgezunn goufen, déi dann all zortéiert goufen, an dann deen eenzege war lénks.
Korrigéieren:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Och op sou enger primitiver Probe - 8.5 Mol méi séier an 33 Mol manner Liesungen. Wat méi "Fakten" Dir fir all Wäert hutt, wat méi offensichtlech den Effekt ass fk.
Ech bemierken datt sou en Index als "Präfix" Index funktionnéiert net méi schlecht wéi virdrun fir aner Ufroen mat fk, wou Zort vun pk et war net an et ass net (Dir kënnt méi iwwer dëst liesen ). Dorënner gëtt et normal explizit auslännesch Schlëssel Ënnerstëtzung op dësem Terrain.
#2: Index Kräizung (BitmapAnd)
Wann entsteet
Show all Accorde fir de Client "LLC Kolokolchik", ofgeschloss am Numm vun "NAO Buttercup".
Wéi identifizéieren
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecommandatiounen
schafen Komposit Index duerch Felder aus béide original oder ausbaue ee vun de bestehend mat Felder vun der zweeter.
Beispill:
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); -- отбор по конкретной паре
Korrigéieren:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
D'Ausbezuelung hei ass méi kleng, well Bitmap Heap Scan eleng ganz effektiv ass. Awer souwisou 7 Mol méi séier an 2.5 Mol manner Liesungen.
#3: Indizes fusionéieren (BitmapOr)
Wann entsteet
Weist déi éischt 20 eelst "eis" oder net zougewisen Ufroe fir d'Veraarbechtung, mat Ärer Prioritéit.
Wéi identifizéieren
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecommandatiounen
Ze benotzen UNION [ALL] fir Subqueries fir jiddereng vun den ODER-Blöcke vu Konditiounen ze kombinéieren.
Beispill:
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;
Korrigéieren:
(
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, больше и не надо
Mir hunn dovun profitéiert datt all 20 erfuerderlech Opzeechnungen direkt am éischte Block opgeholl goufen, sou datt deen zweeten, mam méi "deiere" Bitmap Heap Scan, net emol ausgefouert gouf - um Enn 22x méi séier, 44x manner Liesungen!
Eng méi detailléiert Geschicht iwwer dës Optimiséierungsmethod benotzt spezifesch Beispiller kann an Artikelen gelies ginn и .
Generaliséiert Versioun bestallt Auswiel baséiert op e puer Schlësselen (an net nëmmen de const / NULL Pair) gëtt am Artikel diskutéiert .
#4: Mir liesen vill onnéideg Saachen
Wann entsteet
Als Regel entsteet et wann Dir "en anere Filter" op eng scho existent Ufro wëllt befestigen.
"An Dir hutt net déiselwecht, awer mat Pärelmamm Knäppercher"? Film "The Diamond Arm"
Zum Beispill, d'Task hei uewen z'änneren, weisen déi éischt 20 eelst "kritesch" Ufroe fir d'Veraarbechtung, onofhängeg vun hirem Zweck.
Wéi identifizéieren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recommandatiounen
Schafen [méi] spezialiséiert Index mat WHERE Zoustand oder zousätzlech Felder am Index enthalen.
Wann de Filterbedingung "statesch" fir Är Zwecker ass - dat ass heescht net Expansioun Lëscht vu Wäerter an Zukunft - et ass besser e WHERE Index ze benotzen. Verschidde boolesch / enum Statuse passen gutt an dës Kategorie.
Wann d'Filterbedingung kann verschidden Bedeitungen huelen, dann ass et besser den Index mat dëse Felder auszebauen - wéi an der Situatioun mat BitmapAnd uewen.
Beispill:
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;
Korrigéieren:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Wéi Dir gesitt, ass d'Filterung komplett aus dem Plang verschwonnen, an d'Ufro ass ginn 5 Mol méi séier.
# 5: spatzen Dësch
Wann entsteet
Verschidde Versich Är eege Aufgab Veraarbechtung Schlaang ze schafen, wann eng grouss Zuel vun Aktualiséierungen / Läschen vun records op den Dësch féiert zu enger Situatioun vun enger grousser Zuel vun "dout" records.
Wéi identifizéieren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recommandatiounen
Maacht regelméisseg manuell VAKUUM [VOLL] oder erreechen adäquate heefeg Training andeems se seng Parameteren feinjustéieren, inklusiv .
An deene meeschte Fäll ginn esou Probleemer duerch eng schlecht Ufrokompositioun verursaacht wann Dir vu Geschäftslogik rufft wéi déi diskutéiert .
Awer Dir musst verstoen datt souguer VACUUM FULL net ëmmer hëlleft. Fir esou Fäll ass et derwäert Iech mam Algorithmus aus dem Artikel vertraut ze maachen .
#6: Liesen aus der "Mëtt" vum Index
Wann entsteet
Et schéngt, datt mir e bësse gelies hunn, an alles gouf indexéiert, a mir hunn keen iwwerschësseg gefiltert - awer trotzdem liesen mir däitlech méi Säiten wéi mir wëllen.
Wéi identifizéieren
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recommandatiounen
Kuckt emol d'Struktur vum benotzten Index an d'Schlësselfelder déi an der Ufro spezifizéiert sinn - héchstwahrscheinlech en Deel vum Index gëtt net uginn. Wahrscheinlech musst Dir en ähnlechen Index erstellen, awer ouni de Präfixfelder oder .
Beispill:
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 schéngt gutt ze sinn, och no dem Index, awer et ass iergendwéi verdächteg - fir jiddereng vun den 20 gelies records, mir hu misse 4 Säiten vun Daten subtrahéieren, 32KB pro Rekord - ass net dat fett? An den Index Numm tbl_fk_org_fk_cli_idx geduecht-provozéieren.
Korrigéieren:
CREATE INDEX ON tbl(fk_cli);
Op eemol - 10 Mol méi séier, a 4 Mol manner ze liesen!
Aner Beispiller vun Situatiounen vun ineffektiv Notzung vun Indexen kann am Artikel gesi ginn .
#7: CTE × CTE
Wann entsteet
Op Ufro geschoss "fett" CTE aus verschiddenen Dëscher, an dann decidéiert et tëscht hinnen ze maachen JOIN.
De Fall ass relevant fir Versiounen ënner v12 oder Ufroe mat WITH MATERIALIZED.
Wéi identifizéieren
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recommandatiounen
Virsiichteg analyséieren der Demande - an ? Wann jo, dann gëllen "Wörterbuch" an hstore/json no dem Modell beschriwwen an .
#8: Tauschen op Disk (temp geschriwwe)
Wann entsteet
Eemol Veraarbechtung (Sortéieren oder Eenzegaarteg) vun enger grousser Zuel vun Opzeechnungen passt net an d'Erënnerung, déi fir dës zougewisen ass.
Wéi identifizéieren
-> *
&& temp written > 0Recommandatiounen
Wann de Betrag vun Erënnerung benotzt vun der Operatioun net vill de spezifizéierte Wäert vum Parameter iwwerschreiden , ass et derwäert et ze korrigéieren. Dir kënnt direkt an der Configuratioun fir jiddereen, oder Dir kënnt duerch SET [LOCAL] fir eng spezifesch Ufro / Transaktioun.
Beispill:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korrigéieren:
SET work_mem = '128MB'; -- перед выполнением запроса
Aus offensichtleche Grënn, wann nëmmen Erënnerung benotzt gëtt an net Disk, da gëtt d'Ufro vill méi séier ausgefouert. Zur selwechter Zäit gëtt en Deel vun der Laascht vun der HDD och ewechgeholl.
Awer Dir musst verstoen datt Dir net ëmmer fäeg ass vill a vill Erënnerung ze verdeelen - et wäert einfach net genuch fir jiddereen sinn.
# 9: irrelevant Statistiken
Wann entsteet
Si hunn gläichzäiteg vill an d'Datebank gegoss, awer hate keng Zäit et ze verdreiwen ANALYZE.
Wéi identifizéieren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recommandatiounen
Maacht et aus ANALYZE.
Dës Situatioun gëtt am Detail beschriwwen .
#10: "Eppes ass falsch gaang"
Wann entsteet
Et gouf gewaart fir e Spär, deen duerch eng kompetitiv Ufro opgezwong gouf, oder et waren net genuch CPU / Hypervisor Hardware Ressourcen.
Wéi identifizéieren
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Recommandatiounen
Benotzt extern Iwwerwachung System Server fir Spär oder anormal Ressource Konsum. Mir hu schonn iwwer eis Versioun geschwat fir dëse Prozess fir Honnerte vu Serveren ze organiséieren и .
Source: will.com