Ferskate moannen lyn - iepenbier nei PostgreSQL.
Jo hawwe it al mear as 6000 kear brûkt, mar ien handige funksje dy't miskien ûngemurken is bleaun is strukturele oanwizings, dy't der sa útsjen:
Harkje nei har, en jo oanfragen sille "glêd en zijdeachtig wurde." 🙂
Mar serieus, in protte situaasjes dy't meitsje in fersyk stadich en boarne-hongerich binne typysk en kinne wurde werkend oan de struktuer en gegevens fan it plan.
Yn dit gefal hoecht elke yndividuele ûntwikkelder net op syn eigen te sykjen nei in optimisaasje-opsje, allinich fertroud op syn ûnderfining - wy kinne him fertelle wat hjir bart, wat de reden kin wêze, en hoe te benaderjen in oplossing. Dat hawwe wy dien.
Litte wy dizze gefallen in tichterby besjen - hoe't se wurde definieare en hokker oanbefellings se liede ta.
Om josels better yn it ûnderwerp te ferdjipjen, kinne jo earst harkje nei it byhearrende blok fan , en pas dan troch nei in detaillearre analyze fan elk foarbyld:
#1: yndeks "ûndersortearje"
Wannear ûntstiet
Lit de lêste faktuer foar de klant "LLC Kolokolchik" sjen.
Hoe te identifisearjen
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
oanbefellings
Yndeks brûkt útwreidzje mei sorte fjilden.
Foarbyld:
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;
Jo kinne fuortendaliks fernimme dat mear as 100 records waarden lutsen fan 'e yndeks, dy't dan allegear sorteare, en dan wie de ienige oerbleaun.
Korrigearje:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Sels op sa'n primitive stekproef - 8.5 kear rapper en 33 kear minder lêzen. Hoe mear "feiten" jo hawwe foar elke wearde, hoe dúdliker it effekt fk.
Ik merk op dat sa'n yndeks sil wurkje as in "foarheaksel" yndeks net minder as earder foar oare queries mei fk, wêr sortearje troch pk d'r wie net en d'r is net (jo kinne hjir mear oer lêze ). Ynklusyf, it sil soargje normaal eksplisite bûtenlânske kaai stipe op dit fjild.
#2: index krusing (BitmapAnd)
Wannear ûntstiet
Lit alle ôfspraken sjen foar de kliïnt "LLC Kolokolchik", konkludearre út namme fan "NAO Buttercup".
Hoe te identifisearjen
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanoanbefellings
oanmeitsje gearstalde yndeks troch fjilden fan beide oarspronklike of útwreidzje ien fan de besteande mei fjilden út de twadde.
Foarbyld:
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); -- отбор по конкретной паре
Korrigearje:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
De útkearing hjir is lytser, om't Bitmap Heap Scan op himsels frij effektyf is. Mar hoe dan ek 7 kear rapper en 2.5 kear minder lêzen.
#3: Yndeksen gearfoegje (BitmapOr)
Wannear ûntstiet
Lit de earste 20 âldste "ús" as net-tawiisde oanfragen foar ferwurking sjen, mei jo yn prioriteit.
Hoe te identifisearjen
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanoanbefellings
Brûke UNION [ALLE] om subqueries te kombinearjen foar elk fan 'e OR-blokken fan betingsten.
Foarbyld:
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;
Korrigearje:
(
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, больше и не надо
Wy profitearren fan it feit dat alle 20 fereaske records fuortendaliks ûntfongen waarden yn it earste blok, dus de twadde, mei de "djoere" Bitmap Heap Scan, waard net iens útfierd - op it lêst 22x rapper, 44x minder lêzen!
In mear detaillearre ferhaal oer dizze optimisaasjemetoade mei help fan spesifike foarbylden kin lêzen wurde yn artikels и .
Generalisearre ferzje bestelde seleksje basearre op ferskate kaaien (en net allinich it const / NULL-pear) wurdt besprutsen yn it artikel .
# 4: Wy lêze in protte ûnnedige dingen
Wannear ûntstiet
As regel ûntstiet it as jo "in oare filter taheakje wolle" oan in al besteande fersyk.
"En jo hawwe net deselde, mar mei pearelmem knoppen? " film "The Diamond Arm"
Bygelyks, it wizigjen fan de taak hjirboppe, lit de earste 20 âldste "krityske" oanfragen foar ferwurking sjen, nettsjinsteande har doel.
Hoe te identifisearjen
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
oanbefellings
Meitsje [mear] spesjalisearre yndeks mei WHERE betingst of befetsje ekstra fjilden yn 'e yndeks.
As de filterbetingst "statysk" is foar jo doelen - dat is betsjut gjin útwreiding list fan wearden yn 'e takomst - it is better om in WHERE-yndeks te brûken. Ferskate boolean / enum statusen passe goed yn dizze kategory.
As de filter betingst kin ferskate betsjuttings krije, dan is it better om de yndeks út te wreidzjen mei dizze fjilden - lykas yn 'e situaasje mei BitmapAnd hjirboppe.
Foarbyld:
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;
Korrigearje:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Sa't jo sjen kinne, filterjen is folslein ferdwûn út it plan, en it fersyk is wurden 5 kear flugger.
# 5: sparse tafel
Wannear ûntstiet
Ferskate besykjen om jo eigen taakferwurkingswachtrige te meitsjen, doe't in grut oantal updates / wiskjen fan records op 'e tafel liede ta in situaasje fan in grut oantal "deade" records.
Hoe te identifisearjen
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
oanbefellings
Fier de hân regelmjittich út VAKUUM [FULL] of berikke genôch faak training troch fyn-tuning syn parameters, ynklusyf .
Yn 'e measte gefallen wurde sokke problemen feroarsake troch minne query-komposysje by it roppen fan bedriuwslogika lykas dy besprutsen yn .
Mar jo moatte begripe dat sels VACUUM FULL miskien net altyd helpt. Foar sokke gefallen is it wurdich om josels bekend te meitsjen mei it algoritme út it artikel .
#6: Lêze fanút it "midden" fan 'e yndeks
Wannear ûntstiet
It liket derop dat wy in bytsje lêze, en alles waard yndeksearre, en wy hawwe gjinien yn oerfloed filtere - mar dochs lêze wy signifikant mear siden dan wy wolle.
Hoe te identifisearjen
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
oanbefellings
Besjoch de struktuer fan 'e brûkte yndeks goed en de kaaifjilden dy't spesifisearre binne yn' e query - wierskynlik diel fan 'e yndeks is net ynsteld. Meast wierskynlik sille jo in ferlykbere yndeks moatte oanmeitsje, mar sûnder de foarheakselfjilden of .
Foarbyld:
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 liket goed te wêzen, sels neffens de yndeks, mar it is op ien of oare manier fertocht - foar elk fan 'e 20 lêzen records moasten wy 4 siden mei gegevens ôflûke, 32KB per record - is dat net sa fet? En de yndeksnamme tbl_fk_org_fk_cli_idx tinken-provoking.
Korrigearje:
CREATE INDEX ON tbl(fk_cli);
Ynienen - 10 kear flugger, en 4 kear minder om te lêzen!
Oare foarbylden fan situaasjes fan ineffectief gebrûk fan yndeksen kinne sjoen wurde yn it artikel .
#7: CTE × CTE
Wannear ûntstiet
Op fersyk skoarde "fet" CTE út ferskillende tabellen, en doe besletten om te dwaan it tusken harren JOIN.
De saak is relevant foar ferzjes hjirûnder v12 of fersiken mei WITH MATERIALIZED.
Hoe te identifisearjen
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
oanbefellings
Foarsichtich analysearje it fersyk - en ? As ja, dan tapasse "wurdboek" yn hstore/json neffens it model beskreaun yn .
#8: wikselje nei skiif (temp skreaun)
Wannear ûntstiet
Ienmalige ferwurking (sortearje of unykalisearring) fan in grut tal records past net yn it dêrfoar tawiisd ûnthâld.
Hoe te identifisearjen
-> *
&& temp written > 0oanbefellings
As de hoemannichte ûnthâld brûkt troch de operaasje net folle grutter is as de opjûne wearde fan 'e parameter , it is it wurdich om it te korrigearjen. Jo kinne fuortendaliks yn 'e konfiguraasje foar elkenien, of jo kinne troch SET [LOCAL] foar in spesifyk fersyk / transaksje.
Foarbyld:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korrigearje:
SET work_mem = '128MB'; -- перед выполнением запроса
Om foar de hân lizzende redenen, as allinich ûnthâld wurdt brûkt en net skiif, dan sil de query folle flugger wurde útfierd. Tagelyk wurdt ek in diel fan 'e lading fan' e HDD fuortsmiten.
Mar jo moatte begripe dat jo net altyd in protte ûnthâld kinne allocearje - d'r sil gewoan net genôch wêze foar elkenien.
# 9: irrelevante statistiken
Wannear ûntstiet
Se stoarten in soad yn de databank yn ien kear, mar hiene gjin tiid om it fuort te riden ANALYZE.
Hoe te identifisearjen
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10oanbefellings
Fier it út ANALYZE.
Dizze situaasje wurdt beskreaun yn mear detail yn .
#10: "der gie der wat mis"
Wannear ûntstiet
Der wie in wachtsjen op in slot oplein troch in konkurrearjende fersyk, of der wiene net genôch CPU / hypervisor hardware boarnen.
Hoe te identifisearjen
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
oanbefellings
Brûk in eksterne tafersjoch systeem tsjinner foar blokkearjen of abnormale boarne konsumpsje. Wy hawwe al praat oer ús ferzje fan it organisearjen fan dit proses foar hûnderten servers и .
Boarne: www.habr.com