Mõned kuud tagasi — avalikku PostgreSQL-ile.
Vahepeal olete seda juba kasutanud üle 6000 korra, kuid üks mugav funktsioon võis jääda märkamata — see on struktuurilised vihjed, mis näevad välja umbes nii:
Kuulake neid ja teie päringud "saavad siledaks ja siidiseks". 🙂
Ja tõsiselt räägides, paljusid olukordi, mis teevad päringu aeglaseks ja "ressursiksiteks", on tüüpilised ja neid saab tuvastada plaani struktuuri ja andmete põhjal.
Sel juhul ei pea iga eraldi arendaja ise optimeerimisvõimalusi otsima, tuginedes ainult oma kogemusele — me saame talle vihjata, mis siin toimub, milles võib olla probleem, ja kuidas võiks lahendusele läheneda. Mida me ka tegime.
Vaatame neid juhtumeid veidi põhjalikumalt — kuidas neid määratletakse ja millistele soovitustele nad viivad.
Selle teema paremaks mõistmiseks võite esmalt kuulata vastavat lõiku , ja seejärel minna edasi iga näite detailse analüüsi juurde:
#1: индексная «недосортировка»
Kui see tekib
Kuva viimane arve kliendile "OÜ Kelluke".
Kuidas tuvastada
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Soovitused
Kasutatav indeks laiendada sortimisväljadega.
Näide:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktid"
, (random() * 1000)::integer fk_cli; -- 1K erinevat välisvõtme
CREATE INDEX ON tbl(fk_cli); -- indeks välisvõtme jaoks
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- valik konkreetse seose järgi
ORDER BY
pk DESC -- tahame vaid ühte "viimast" kirjet
LIMIT 1; 
Kohe on näha, et indeksi põhjal loeti välja rohkem kui 100 kirjet, mis seejärel kõik sorteeriti ja seejärel jäi vaid üks.
Parandame:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- lisasime sortimisvõtme
Isegi sellise primitiivse valiku puhul — on see 8.5 korda kiirem ja 33 korda vähem lugemisi. Efekt on seda ilmekam, mida rohkem teil on "fakte" iga väärtuse kohta fk.
Tulen välja, et selline indeks töötab nagu "prefiksindeks" mitte halvemini kui eelmine ka muudes päringutes, kus fk, kus sortimisi pk ei olnud ja ei ole (täpsemalt sellest saate lugeda minu artiklis ebaefektiivsete indeksite kohta toetuse selgelt määratletud välisvõtmele selles väljas. Kuva kõik lepingud kliendile "OÜ Kelluke", mis on sõlmitud "NAO Lutike" nimel.
#2: пересечение индексов (BitmapAnd)
Kui see tekib
Kuvada kõik lepingud kliendi «OOO Kolokolchik» nimel, mis on sõlmitud «NAO Lyutik» poolt.
Kuidas tuvastada
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
Luua komposiitindeks väljadest mõlemast algallikast või laiendada üks olemasolev teise väljadega.
Näide:
Loo tabel tbl nagu
Vali
generate_series(1, 100000) pk -- 100K "fakti"
, (random() * 100)::integer fk_org -- 100 erinevat välismaist võtit
, (random() * 1000)::integer fk_cli; -- 1K erinevat välismaist võtit
Loo indeks tbl(fk_org) peal; -- indeks välismaise võtme jaoks
Loo indeks tbl(fk_cli) peal; -- indeks välismaise võtme jaoks
Vali
*
Kust
tbl
Kus
(fk_org, fk_cli) = (1, 999); -- valik kindla paaride alusel 
Parandame:
Kustuta indeks tbl_fk_org_idx;
Loo indeks tbl(fk_org, fk_cli);
Siin on kasu väiksem, kuna Bitmap Heap Scan on piisavalt efektiivne iseenesest. Kuid siiski 7 korda kiiremini ja 2.5 korda vähem lugemisi.
#3: объединение индексов (BitmapOr)
Kui see tekib
Näita esimesi 20 kõige vanemat "oma" või määramata taotlust töötlemiseks, kusjuures oma on prioritaarne.
Kuidas tuvastada
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
Kasutage UNION [ALL] alumine päringute ühendamiseks iga OR-bloki tingimuste kohaselt.
Näide:
Loo tabel tbl nagu
Vali
generate_series(1, 100000) pk -- 100K "fakti"
, CASE
KUI random() < 1::real/16 SIIS NULL -- tõenäosusega 1:16 on kirje "viik"
MUUL JUHUL (random() * 100)::integer -- 100 erinevat välismaist võtit
LÕPP fk_own;
Loo indeks tbl(fk_own, pk) peal; -- indeks "näiliselt sobiva" sortimisega
Vali
*
Kust
tbl
Kus
fk_own = 1 VÕI -- oma
fk_own IS NULL -- ... või "viik"
Korrasta
pk
, (fk_own = 1) DESC -- kõigepealt "oma"
Piira 20;
Parandame:
(
Valige
*
Kust
tbl
Kus
fk_own = 1 -- kõigepealt "oma" 20
Korrasta
pk
Piira 20
)
UNION ALL
(
Valige
*
Kust
tbl
Kus
fk_own IS NULL -- siis "viik" 20
Korrasta
pk
Piira 20
)
Piira 20; -- kuid kokku 20, rohkem ei ole vaja 
Kasutasime seda, et kõik 20 vajalikku kirjet saadi kohe esimese ploki kaudu, seega teist, kallimad olevat Bitmap Heap Scan, ei teostatud — tulemusena 22 korda kiiremini, 44 korda vähem lugemisi!
Detailsemat lugu sellest optimeerimisviisist konkreetselt näidete põhjal saab lugeda artiklitest ja .
Üldine variant kombineeritud valiku tegemine mitmete võtmete põhjal (mitte ainult paari const/NULL põhjal) arutatakse artiklis .
#4: читаем много лишнего
Kui see tekib
Tavaliselt tekib siis, kui soovitakse "veel üks filter" olemasolevale päringule lisada.
"Aga kas teil ei ole sama, aga meririkka nööbiga?» film "Teemantkäsi"
Näiteks, muutes eelnevat ülesannet, näidata esimesi 20 kõige vanemat "kriitilist" taotlust töötlemiseks, sõltumata nende määratud olekust.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows 80% loetust
&& loops × RRbF > 100 -- ja samal ajal üle 100 kirje kokku
Soovitused
Loo [rohkem] spetsialiseeritud indeks, kus on WHERE-tingimus või lisada indekse täiendavaid välja.
Kui filtreerimise tingimus on "staatiline" teie ülesannete jaoks - see tähendab, et ei eelda tulevikus väärtuste loetelu laiendamist - on parem kasutada WHERE-indeksit. Sellesse kategooriasse sobivad hästi erinevad boolean/enum-olekud. Kui aga filtreerimise tingimus
võib võtta erinevaid väärtusi , siis on parem laiendada indeksit nende väljadega - nagu BitmapAndi puhul eespool.LOO TABEL tbl KUI VALI genereeri_seeria(1, 100000) pk -- 100K "fakti" , JUHUL KUI juhuslik() < 1::rea/16 SIIS NULL MUUD KUI (juhuslik() * 100)::täisarv -- 100 erinevat välist võtit LÕPP fk_own , (juhuslik() < 1::rea/50) kriitiline; -- 1:50, et taotlus on "kriitiline"LOO INDEKS tbl(pk); LOO INDEKS tbl(fk_own, pk);VALI * KUS tbl KUIDAS kriitiline KORRALDA pk PIIRKOND 20;
Näide:
LOO INDEKS tbl(pk)
KUS kriitiline; -- lisatud "staatiline" filtreerimise tingimus 
Parandame:
Kuidas näeme, on plaanist filtreerimine täielikult kadunud ja päring on nüüd
kuni 5 korda kiirem Erinevad katsed luua oma ülesannete töötlemise järjekord, kui suur hulk uuendusi/kustutusi kirjeid tabelis viib olukorrani, kus on palju "suretatud" kirjeid..
#5: разреженная таблица
Kui see tekib
-> Seq Scan | Bitmap Heap Scan | Index [Ainult] Scan [Tagurpidi] && tsüklid × (read + RRbF) 64
Kuidas tuvastada
Regulaarselt käsitsi läbi viia
Soovitused
VACUUM [FULL] või saavutada piisavalt sagedane töötlemine kohtade täpse seadistamise kaudu, sealhulgas konkreetse tabeli jaoks .
Aga tuleb mõista, et isegi VACUUM FULL ei pruugi alati aidata. Selliste olukordade jaoks tasub tutvuda algoritmiga artiklis .
DBA: kui VACUUM ei tööta - puhastame tabeli käsitsi .
#6: чтение с «середины» индекса
Kui see tekib
-> Index [Ainult] Scan [Tagurpidi] && tsüklid × (read + RRbF) 64
Kuidas tuvastada
Hoolikalt vaatama kasutatud indeksi struktuuri ja päringus määratud võtmevälju - tõenäoliselt
Soovitused
osa indeksist pole määratud . Tõenäoliselt peate looma sarnase indeksi, kuid ilma prefiksiväljadeta võiõppima, kuidas iteratsioonida nende väärtusi .
Näide:
LOOJA TABEL tbl AS
VALI
generate_series(1, 100000) pk -- 100K "fakti"
, (random() * 100)::integer fk_org -- 100 erinevat välist võtit
, (random() * 1000)::integer fk_cli; -- 1K erinevat välist võtit
LOOJA INDEX tbl(fk_org, fk_cli); -- kõik peaaegu nagu #2
-- ainult, et eraldi indeks fk_cli kohta peame me üleliigseks ja eemaldasime
VALI
*
FROM
tbl
KUS
fk_cli = 999 -- ja fk_org ei ole määratud, kuigi see on indeksis varem
PIIRDA 20; 
Tundub, et kõik on korras, isegi indeksi osas, kuid see on kuidagi kahtlane — iga 20 loetud kirje puhul tuli lugeda 4 andmelehte, 32KB kirje kohta — kas see ei ole liiga palju? Ja indeksinimi tbl_fk_org_fk_cli_idx annab mõtlema.
Parandame:
LOOJA INDEx tbl(fk_cli); 
Ühtäkki — kümme korda kiirem, ja nelik korda vähem lugeda!
Teisi näiteid ebaefektiivsest indeksite kasutamisest võib näha artiklis .
#7: CTE × CTE
Kui see tekib
Päringus koostasime "rasvased" CTE-d erinevatest tabelitest ja siis otsustasime neid omavahel seostada JOIN.
Juhtum on asjakohane versioonide jaoks, mis on väiksemad kui v12 või päringud, kus on WITH MATERIALIZED.
Kuidas tuvastada
-> CTE Scan
&& silmad > 10
&& silmad × (read + RRbF) > 10000
-- liiga suur dekoratiivne toode CTE
Soovitused
Hästi analüüsida päring — kas ? Если все-таки да, то rakendada "võõrsõnaga" hstore/json-is moodi, nagu on kirjeldatud .
#8: swap на диск (temp written)
Kui see tekib
Ühe korra töötlemine (sortimine või unikaalsus) suure hulga kirjeid ei mahu ette nähtud mällu.
Kuidas tuvastada
-> *
&& temp kirjutatud > 0Soovitused
Kui operatsiooniga kasutatud mälu kogus ei ületa oluliselt seatud töömälu parameetrit , on mõistlik seda korrigeerida. Saab kohe konfigureerida kõigile või läbi SET [LOCAL] konkreetsel päringul/tehingul.
Näide:
KUIDAS work_mem;
-- "16MB"
VALI
random()
FROM
generate_series(1, 1000000)
KORDA
1; 
Parandame:
SET work_mem = '128MB'; -- enne päringu täitmist 
Mõistetavad põhjused, miks kui kasutatakse ainult mälu ja mitte ketast, siis päring täidetakse oluliselt kiiremini. Samal ajal ka osa koormusest HDD-lt eemaldatakse.
Kuid tuleb mõista, et eraldada väga palju mälu ei ole võimalik — seda lihtsalt ei jätku kõigile.
#9: неактуальная статистика
Kui see tekib
Andmebaasi sisestati kohe palju, kuid ei jõudnud joonestada ANALYZE.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& suhe >> 10Soovitused
Selleks, et viia lõpule ANALYZE.
Selle olukorra kohta on rohkem teavet toodud .
#10: «что-то пошло не так»
Kui see tekib
Oli blokeerimise ootus, mille pani kinni konkurentsitööd, või ei piisanud riistvaralistest ressurssidest CPU/virtuaalmasinast.
Kuidas tuvastada
-> *
&& (shared hit / 8K) + (shared read / 1K) Soovitused
Kasutage välist süsteemi jälgimiseks serverite tagamisel blokeeringute või ebanormaalse ressursikasutuse osas. Me oleme juba rääkinud meie variandist selle protsessi korraldamiseks sadade serverite jaoks ja .
Allikas: habr.com
