Flera mĂ„nader sedan â en offentlig för PostgreSQL.
Under denna tid har du redan anvĂ€nt den över 6000 gĂ„nger, men en av de praktiska funktionerna kan ha förblivit oupptĂ€ckta â det Ă€r strukturella tips, som ser ut ungefĂ€r sĂ„ hĂ€r:
Lyssna pĂ„ dem, och dina frĂ„gor kommer att bli "slĂ€ta och silkeslena". đ
Men allvarligt talat, mÄnga situationer som gör en frÄga lÄngsam och "resurskrÀvande" Àr typiska och kan kÀnnas igen genom strukturen och datan i planen..
I sĂ„dana fall behöver varje enskild utvecklare inte söka efter optimeringsalternativ pĂ„ egen hand, baserat enbart pĂ„ sin erfarenhet â vi kan ge honom en ledtrĂ„d om vad som hĂ€nder, vad som kan vara orsaken och hur man kan nĂ€rma sig en lösning. Det har vi gjort.
LĂ„t oss titta lite nĂ€rmare pĂ„ dessa fall â hur de identifieras och vilka rekommendationer de leder till.
För en bÀttre förstÄelse av Àmnet, kan man först lyssna pÄ motsvarande del av , och först sedan gÄ vidare till en detaljerad analys av varje exempel:
#1: ĐžĐœĐŽĐ”ĐșŃĐœĐ°Ń Â«ĐœĐ”ĐŽĐŸŃĐŸŃŃĐžŃĐŸĐČĐșа»
NÀr det uppstÄr
Visa senaste fakturan för kunden "AB Klocka".
Hur man identifierar
-> Limit
-> Sort
-> Index [Endast] Scan [BakÄt] | Bitmap Heap Scan
Rekommendationer
AnvÀnd index utöka med sorteringsfÀlt.
Exempel:
SKAPA TABELL tbl SOM
SELECT
generate_series(1, 100000) pk -- 100K "fakta"
, (random() * 1000)::integer fk_cli; -- 1K olika externa nycklar
SKAPA INDEX PĂ
tbl(fk_cli); -- index för foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- urval baserat pÄ specifik relation
ORDER BY
pk DESC -- vi vill bara ha en "senaste" post
LIMIT 1; 
Det kan genast noteras att mer Àn 100 poster lÀstes av indexet, som sedan sorteras och en enda behÄlls.
Vi ÄtgÀrdar:
DROP INDEX tbl_fk_cli_idx;
SKAPA INDEX PĂ
tbl(fk_cli, pk DESC); -- tillade sorteringsnyckel
Ăven pĂ„ en sĂ„ primitiv urval â 8.5 gĂ„nger snabbare och 33 gĂ„nger fĂ€rre lĂ€sningar. Effekten kommer att bli desto tydligare ju fler du har "fakta" per vĂ€rde fk.
Jag pÄpekar att detta index kommer att fungera som "prefix" lika bra som det tidigare och för andra frÄgor med fk, dÀr sorteringarna pÄ pk inte fanns och inte finns (mer om detta kan du lÀsa ). Det kommer Àven sÀkerstÀlla normal stöd för explicita foreign key för detta fÀlt.
#2: пДŃĐ”ŃĐ”ŃĐ”ĐœĐžĐ” ĐžĐœĐŽĐ”ĐșŃĐŸĐČ (BitmapAnd)
NÀr det uppstÄr
Visa alla avtal för kunden «OOO Kolokolchik», ingÄngna i namn av «NAO Lyutik».
Hur man identifierar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRekommendationer
Skapa kompositindex pÄ fÀlten frÄn bÄda kÀllorna eller utvidga en av de befintliga med fÀlt frÄn den andra.
Exempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fakta"
, (random() * 100)::integer fk_org -- 100 olika externa nycklar
, (random() * 1000)::integer fk_cli; -- 1K olika externa nycklar
CREATE INDEX ON tbl(fk_org); -- index för foreign key
CREATE INDEX ON tbl(fk_cli); -- index för foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- urval efter ett specifikt par 
Vi ÄtgÀrdar:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
HÀr Àr vinsten mindre eftersom Bitmap Heap Scan Àr tillrÀckligt effektiv i sig. Men ÀndÄ 7 gÄnger snabbare och 2.5 gÄnger fÀrre lÀsningar.
#3: ĐŸĐ±ŃĐ”ĐŽĐžĐœĐ”ĐœĐžĐ” ĐžĐœĐŽĐ”ĐșŃĐŸĐČ (BitmapOr)
NÀr det uppstÄr
Visa de första 20 Àldsta "egna" eller icke tilldelade Àrendena för behandling, dÀr de egna har prioritet.
Hur man identifierar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRekommendationer
AnvÀnd UNION [ALL] för att kombinera underfrÄgor för varje OR-block av villkor.
Exempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fakta"
, CASE
WHEN random() < 1::real/16 THEN NULL -- med sannolikhet 1:16 gör posten "oavgjort"
ELSE (random() * 100)::integer -- 100 olika externa nycklar
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- index med "det verkar passa" sortering
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- egna
fk_own IS NULL -- ... eller "oavgjorda"
ORDER BY
pk
, (fk_own = 1) DESC -- först "egna"
LIMIT 20;
Vi ÄtgÀrdar:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- först "egna" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- sedan "oavgjorda" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- men totalt - 20, mer behövs inte 
Vi utnyttjade att alla 20 nödvĂ€ndiga poster redan erhölls i det första blocket, sĂ„ den andra, med en "dyrare" Bitmap Heap Scan, kördes inte ens â slutligen 22 gĂ„nger snabbare, 44 gĂ„nger fĂ€rre lĂ€sningar!
En mer detaljerad berÀttelse om denna optimeringsmetod med konkreta exempel kan lÀsas i artiklar och .
En generaliserad version av ordnad urval efter flera nycklar (och inte bara efter paret const/NULL) diskuteras i artikeln .
#4: ŃĐžŃĐ°Đ”ĐŒ ĐŒĐœĐŸĐłĐŸ лОŃĐœĐ”ĐłĐŸ
NÀr det uppstÄr
Det uppstÄr vanligtvis nÀr man vill "koppla till Ànnu ett filter" till en redan befintlig frÄga.
"Och har ni inte en likadan, men med pÀrlemorknappar?» film "Den diamantiska handen"
Till exempel, genom att modifiera uppgiften ovan, visa de första 20 Àldsta "kritiska" ansökningarna för behandling, oavsett deras tilldelning.
Hur man identifierar
-> Sekvensanalys | Bitmap Heap Scan | Index [Endast] Scan [BakÄt]
&& 5 à rader < RRbF -- filtrerat >80% av det lÀsta
&& loopar à RRbF > 100 -- och dessutom mer Àn 100 poster totalt
Rekommendationer
Skapa [mer] specialiserad index med WHERE-villkor eller inkludera ytterligare fÀlt i indexet.
Om filtreringsvillkoret Àr "statiskt" för dina uppgifter - det vill sÀga inte förutser en utvidgning av vÀrden i framtiden - Àr det bÀttre att anvÀnda WHERE-index. Olika boolean/enum-statusar passar bra in i denna kategori.
Om dÀremot filtreringsvillkoret kan ta olika vÀrden, sÄ Àr det bÀttre att utvidga indexet med dessa fÀlt - som i situationen med BitmapAnd ovan.
Exempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fakta"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 olika externa nycklar
END fk_own
, (random() < 1::real/50) critical; -- 1:50, att ansökan Àr "kritisk"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20; 
Vi ÄtgÀrdar:
CREATE INDEX ON tbl(pk)
WHERE critical; -- lade till "statiskt" filtreringsvillkor
Som vi ser, försvann filtreringen frÄn planen helt, och förfrÄgan blev 5 gÄnger snabbare.
#5: ŃĐ°Đ·ŃĐ”Đ¶Đ”ĐœĐœĐ°Ń ŃаблОŃĐ°
NÀr det uppstÄr
Olika försök att skapa en egen kö för behandlingen av uppgifter, nÀr ett stort antal uppdateringar/borttagningar av poster i tabellen leder till en situation med ett stort antal "döda" poster.
Hur man identifierar
-> Sekvensanalys | Bitmap Heap Scan | Index [Endast] Scan [BakÄt]
&& loopar à (rader + RRbF) < (delad trÀff + delad lÀsning) à 8
-- lÀst mer Àn 1KB per poster
&& delad trÀff + delad lÀsning > 64
Rekommendationer
Regelbundet utföra VACUUM [FULL] eller uppnÄ tillrÀckligt frekvent bearbetning genom att justera dess parametrar, inklusive .
I de flesta fall orsakas sÄdana problem av dÄlig sammanstÀllning av förfrÄgningar vid anrop frÄn affÀrslogiken, som de som diskuterades i .
Men man mÄste förstÄ att Àven VACUUM FULL inte alltid kan hjÀlpa. För sÄdana fall Àr det vÀrt att bekanta sig med algoritmen frÄn artikeln .
#6: ŃŃĐ”ĐœĐžĐ” Ń Â«ŃĐ”ŃĐ”ĐŽĐžĐœŃ» ĐžĐœĐŽĐ”ĐșŃĐ°
NÀr det uppstÄr
Det verkar som vi har lÀst lite, och allt i index, och filtrerade inte bort nÄgon overksamhet - men ÀndÄ har betydligt fler sidor lÀsts Àn önskat.
Hur man identifierar
-> Index [Only] Scan [Backward]
&& loops Ă (rows + RRbF) 64
Rekommendationer
Titta noga pĂ„ strukturen av den anvĂ€nda index och nyckelomrĂ„dena som anges i förfrĂ„gan â det Ă€r troligtvis en del av indexet Ă€r inte angivet. Antagligen behöver du skapa ett liknande index, men utan prefixfĂ€lt eller .
Exempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fakta"
, (random() * 100)::integer fk_org -- 100 olika externa nycklar
, (random() * 1000)::integer fk_cli; -- 1K olika externa nycklar
CREATE INDEX ON tbl(fk_org, fk_cli); -- nÀstan allt som i #2
-- endast att vi redan anser att en separat index pÄ fk_cli Àr överflödig och har tagit bort den
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- och fk_org Àr inte angivet, Àven om det stÄr tidigare i indexet
LIMIT 20; 
Allt verkar bra, Ă€ven med indexet, men det verkar misstĂ€nkt â varje av de 20 lĂ€sta posterna krĂ€vde att vi lĂ€ste fyra datalag, 32KB per post â Ă€r det inte för mycket? Ăven namnet pĂ„ indexet tbl_fk_org_fk_cli_idx fĂ„r en att fundera.
Vi ÄtgÀrdar:
CREATE INDEX ON tbl(fk_cli); 
Plötsligt â 10 gĂ„nger snabbare, och 4 gĂ„nger mindre att lĂ€sa!
Andra exempel pÄ ineffektiv anvÀndning av index kan ses i artikeln .
#7: CTE Ă CTE
NÀr det uppstÄr
I förfrÄgan har vi skapat "feta" CTE frÄn olika tabeller, och sedan bestÀmde vi oss för att göra en JOIN.
Fallet Àr aktuellt för versioner under v12 eller förfrÄgningar med WITH MATERIALIZED.
Hur man identifierar
-> CTE Scan
&& loops > 10
&& loops Ă (rows + RRbF) > 10000
-- för stort kartesiskt produkt av CTE
Rekommendationer
Analysera noga förfrĂ„gan â Ă€r ? ĐŃлО ĐČŃĐ”-ŃĐ°ĐșĐž ĐŽĐ°, ŃĐŸ tillĂ€mpa "ordlista" i hstore/json enligt modellen som beskrivs i .
#8: swap ĐœĐ° ĐŽĐžŃĐș (temp written)
NÀr det uppstÄr
EngÄngshantering (sortering eller unifiering) av ett stort antal poster fÄr inte plats i det avsatta minnet.
Hur man identifierar
-> *
&& temp written > 0Rekommendationer
Om mÀngden minne som anvÀnds av operationen inte överstiger det angivna vÀrdet för parametern , bör det justeras. Kan göras direkt i konfigurationen för alla, eller via SET [LOCAL] för specifik förfrÄgan/transaktion.
Exempel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Vi ÄtgÀrdar:
SET work_mem = '128MB'; -- före utförande av förfrÄgan 
Av uppenbara skÀl, om bara minne anvÀnds och inte disk, sÄ kommer ocksÄ förfrÄgan att köras mycket snabbare. Samtidigt avlastas ocksÄ en del av belastningen frÄn HDD.
Men mĂ„ste förstĂ„ att det inte alltid gĂ„r att avsĂ€tta mycket minne â det kommer helt enkelt inte att rĂ€cka till alla.
#9: ĐœĐ”Đ°ĐșŃŃĐ°Đ»ŃĐœĐ°Ń ŃŃĐ°ŃĐžŃŃĐžĐșĐ°
NÀr det uppstÄr
Det har pumpats in mycket i databasen, men vi hann inte köra igenom det. ANALYZE.
Hur man identifierar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rekommendationer
Verkligen genomföra ANALYZE.
Mer detaljerad information om denna situation finns i .
#10: «ŃŃĐŸ-ŃĐŸ ĐżĐŸŃĐ»ĐŸ ĐœĐ” ŃĐ°Đș»
NÀr det uppstÄr
Det uppstod en vÀntan pÄ en lÄs som satts av en konkurrerande förfrÄgan, eller sÄ fanns det inte tillrÀckligt med CPU/hypervisor resurser.
Hur man identifierar
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- vi lÀste lite, men det tog för lÄng tid
Rekommendationer
AnvÀnd ett externt övervakningssystem för servrar för att kontrollera förekomsten av lÄsningar eller onormal resursanvÀndning. Vi har redan berÀttat om vÄr metod för att organisera denna process för hundratals servrar. och .
KĂ€lla: habr.com
