Maanden geleden - openbaar naar PostgreSQL.
Je hebt het al meer dan 6000 keer gebruikt, maar een handige functie die misschien onopgemerkt is gebleven, is structurele aanwijzingen, die er ongeveer zo uitzien:
Luister naar hen, en uw verzoeken zullen ‘soepel en zijdeachtig worden’. 🙂
Maar serieus, er zijn veel situaties die een verzoek traag maken en veel middelen vergen zijn typisch en herkenbaar aan de structuur en gegevens van het plan.
In dit geval hoeft elke individuele ontwikkelaar niet zelf naar een optimalisatieoptie te zoeken, uitsluitend op basis van zijn ervaring - we kunnen hem vertellen wat hier gebeurt, wat de reden zou kunnen zijn, en hoe je een oplossing aanpakt. Dat is wat wij deden.
Laten we deze gevallen eens nader bekijken: hoe ze worden gedefinieerd en tot welke aanbevelingen ze leiden.
Om je beter in het onderwerp te verdiepen, kun je eerst het bijbehorende blok beluisteren , en ga dan pas verder met een gedetailleerde analyse van elk voorbeeld:
#1: index “ondersortering”
Wanneer ontstaat
Toon de laatste factuur voor de klant "LLC Kolokolchik".
Hoe te identificeren
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Aanbevelingen
Gebruikte index uitbreiden met sorteervelden.
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;
Je merkt meteen dat er meer dan 100 records van de index zijn afgetrokken, die vervolgens allemaal zijn gesorteerd, en dat er toen nog maar één overbleef.
Corrigeren:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Zelfs op zo'n primitief monster - 8.5 keer sneller en 33 keer minder leesbewerkingen. Hoe meer ‘feiten’ je voor elke waarde hebt, hoe duidelijker het effect fk.
Ik merk op dat een dergelijke index niet slechter zal werken als een “prefix”-index dan voorheen voor andere zoekopdrachten met fk, waar gesorteerd op pk die was er niet en die is er niet (hierover kun je meer lezen ). Inclusief, het zal normaal zijn expliciete ondersteuning voor externe sleutels op dit veld.
#2: indexkruispunt (BitmapAnd)
Wanneer ontstaat
Toon alle overeenkomsten voor de opdrachtgever “LLC Kolokolchik”, gesloten namens “NAO Buttercup”.
Hoe te identificeren
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanAanbevelingen
Creëren samengestelde index door velden uit beide originele velden, of breid een van de bestaande uit met velden uit de tweede.
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); -- отбор по конкретной паре
Corrigeren:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
De winst is hier kleiner, omdat Bitmap Heap Scan op zichzelf behoorlijk effectief is. Maar in ieder geval 7 keer sneller en 2.5 keer minder leesbewerkingen.
#3: Indexen samenvoegen (BitmapOr)
Wanneer ontstaat
Toon de eerste 20 oudste ‘ons’- of niet-toegewezen verzoeken voor verwerking, met die van u als prioriteit.
Hoe te identificeren
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanAanbevelingen
Gebruiken UNIE [ALLE] om subquery's voor elk van de OR-blokken met voorwaarden te combineren.
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;
Corrigeren:
(
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, больше и не надо
We profiteerden van het feit dat alle 20 vereiste records onmiddellijk in het eerste blok werden ontvangen, zodat het tweede, met de duurdere Bitmap Heap Scan, uiteindelijk niet eens werd uitgevoerd. 22x sneller, 44x minder leesbewerkingen!
Een gedetailleerder verhaal over deze optimalisatiemethode op concrete voorbeelden is te lezen in artikelen и .
Gegeneraliseerde versie geordende selectie op basis van verschillende toetsen (en niet alleen het const/NULL-paar) wordt in het artikel besproken .
#4: We lezen veel onnodige dingen
Wanneer ontstaat
In de regel ontstaat het wanneer u “een ander filter wilt toevoegen” aan een reeds bestaand verzoek.
‘En je hebt niet dezelfde, maar met parelknopen? ' film "De diamanten arm"
Als u bijvoorbeeld de bovenstaande taak wijzigt, worden de eerste twintig oudste ‘kritieke’ verzoeken voor verwerking weergegeven, ongeacht hun doel.
Hoe te identificeren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Aanbevelingen
Creëer [meer] gespecialiseerd index met WHERE-voorwaarde of neem extra velden op in de index.
Als de filtervoorwaarde voor uw doeleinden 'statisch' is, tenminste impliceert geen uitbreiding lijst met waarden in de toekomst - het is beter om een WHERE-index te gebruiken. Verschillende booleaanse/enum-statussen passen goed in deze categorie.
Als de filtervoorwaarde kan verschillende betekenissen aannemen, dan is het beter om de index uit te breiden met deze velden - zoals in de situatie met BitmapAnd hierboven.
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;
Corrigeren:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Zoals je kunt zien, is het filteren volledig uit het plan verdwenen en is het verzoek geworden 5 keer sneller.
#5: schaarse tafel
Wanneer ontstaat
Verschillende pogingen om uw eigen taakverwerkingswachtrij te creëren, waarbij een groot aantal updates/verwijderingen van records op de tabel leidden tot een situatie van een groot aantal “dode” records.
Hoe te identificeren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Aanbevelingen
Regelmatig handmatig uitvoeren VACUÜM [VOL] of een voldoende frequente opleiding volgen door de parameters ervan te verfijnen, inclusief .
In de meeste gevallen worden dergelijke problemen veroorzaakt door een slechte samenstelling van de zoekopdrachten bij het bellen vanuit bedrijfslogica, zoals besproken in .
Maar u moet begrijpen dat zelfs VACUÜM VOLLEDIG niet altijd helpt. Voor dergelijke gevallen is het de moeite waard om vertrouwd te raken met het algoritme uit het artikel .
#6: Lezen vanuit het “midden” van de index
Wanneer ontstaat
Het lijkt erop dat we een beetje hebben gelezen, en alles is geïndexeerd, en we hebben niemand teveel eruit gefilterd - maar toch lezen we aanzienlijk meer pagina's dan we zouden willen.
Hoe te identificeren
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Aanbevelingen
Kijk eens goed naar de structuur van de gebruikte index en de sleutelvelden die in de query zijn opgegeven - hoogstwaarschijnlijk een deel van de index is niet gespecificeerd. Hoogstwaarschijnlijk zult u een vergelijkbare index moeten maken, maar zonder de voorvoegselvelden 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 lijkt in orde te zijn, zelfs volgens de index, maar het is op de een of andere manier verdacht - voor elk van de 20 gelezen records moesten we 4 pagina's aan gegevens aftrekken, 32 KB per record - is dat niet brutaal? En de indexnaam tbl_fk_org_fk_cli_idx tot nadenken stemmend.
Corrigeren:
CREATE INDEX ON tbl(fk_cli);
Plotseling - 10 keer sneller en 4 keer minder om te lezen!
Andere voorbeelden van situaties van ineffectief gebruik van indexen zijn te zien in het artikel .
#7: CTE × CTE
Wanneer ontstaat
In aanvraag scoorde “dikke” CTE van verschillende tafels, en besloot toen om het tussen hen te doen JOIN.
Dit geval is relevant voor versies onder v12 of verzoeken met WITH MATERIALIZED.
Hoe te identificeren
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Aanbevelingen
Analyseer het verzoek zorgvuldig - en ? Zo ja, dan pas "woordenboek" toe in hstore/json volgens het model beschreven in .
#8: wisselen naar schijf (temp geschreven)
Wanneer ontstaat
Het eenmalig verwerken (sorteren of uniek maken) van een groot aantal records past niet in het daarvoor bestemde geheugen.
Hoe te identificeren
-> *
&& temp written > 0Aanbevelingen
Als de hoeveelheid geheugen die door de bewerking wordt gebruikt, de opgegeven waarde van de parameter niet veel overschrijdt , het is de moeite waard om dit te corrigeren. Je kunt meteen in de configuratie voor iedereen, of je kunt er doorheen SET [LOCAL] voor een specifiek verzoek/transactie.
Voorbeeld:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Corrigeren:
SET work_mem = '128MB'; -- перед выполнением запроса
Om voor de hand liggende redenen zal de query veel sneller worden uitgevoerd als alleen geheugen wordt gebruikt en geen schijf. Tegelijkertijd wordt ook een deel van de belasting van de HDD verwijderd.
Maar u moet begrijpen dat u niet altijd heel veel geheugen kunt toewijzen; er zal simpelweg niet genoeg zijn voor iedereen.
#9: irrelevante statistieken
Wanneer ontstaat
Ze stopten veel tegelijk in de database, maar hadden geen tijd om het weg te jagen ANALYZE.
Hoe te identificeren
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Aanbevelingen
Voer het uit ANALYZE.
Deze situatie wordt nader beschreven in .
#10: “Er ging iets mis”
Wanneer ontstaat
Er is gewacht op een vergrendeling die werd opgelegd door een concurrerend verzoek, of er waren onvoldoende hardwarebronnen voor de CPU/hypervisor.
Hoe te identificeren
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Aanbevelingen
Gebruik extern controlesysteem server voor blokkering of abnormaal bronnengebruik. We hebben al gesproken over onze versie van het organiseren van dit proces voor honderden servers и .
Bron: www.habr.com