Antaŭ kelkaj monatoj - publiko al PostgreSQL.
Vi jam uzis ĝin pli ol 6000 fojojn, sed unu oportuna funkcio kiu eble pasis nerimarkita estas strukturaj indicoj, kiuj aspektas kiel ĉi tio:
Aŭskultu ilin, kaj viaj petoj "fariĝos glataj kaj silkecaj". 🙂
Sed serioze, multaj situacioj, kiuj faras peton malrapida kaj rimedo-malsata estas tipaj kaj povas esti rekonitaj per la strukturo kaj datenoj de la plano.
En ĉi tiu kazo, ĉiu individua programisto ne devas serĉi optimumigan opcion memstare, fidante nur sur sia sperto - ni povas diri al li, kio okazas ĉi tie, kio povus esti la kialo, kaj kiel alproksimigi solvon. Tion ni faris.
Ni rigardu pli detale ĉi tiujn kazojn - kiel ili estas difinitaj kaj al kiaj rekomendoj ili kondukas.
Por pli bone mergi vin en la temo, vi unue povas aŭskulti la respondan blokon de , kaj nur tiam transiru al detala analizo de ĉiu ekzemplo:
numero 1: indekso "subsortado"
Kiam ekestas
Montru la plej novan fakturon por la kliento "LLC Kolokolchik".
Kiel identigi
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
rekomendoj
Indekso uzata vastigi kun ordigaj kampoj.
Ekzemplo:
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;
Vi povas tuj rimarki, ke pli ol 100 registroj estis subtrahitaj de la indekso, kiuj tiam estis ĉiuj ordigitaj, kaj tiam la sola restis.
Korektante:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Eĉ sur tia primitiva specimeno - 8.5 fojojn pli rapide kaj 33 fojojn malpli da legaĵoj. Ju pli da "faktoj" vi havas por ĉiu valoro, des pli evidenta la efiko fk.
Mi rimarkas, ke tia indekso funkcios kiel "prefikso" indekso ne pli malbona ol antaŭe por aliaj demandoj kun fk, kie ordigi pk ne estis kaj ne ekzistas (vi povas legi pli pri tio ). Inkluzive, ĝi provizos normala eksplicita subteno de fremdaj ŝlosiloj sur ĉi tiu kampo.
numero 2: indeksa intersekco (BitmapAnd)
Kiam ekestas
Montru ĉiujn interkonsentojn por la kliento "LLC Kolokolchik", konkludita nome de "NAO Buttercup".
Kiel identigi
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanrekomendoj
krei kunmetita indekso per kampoj el ambaŭ originalaj aŭ pligrandigi unu el la ekzistantaj per kampoj el la dua.
Ekzemplo:
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); -- отбор по конкретной паре
Korektante:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
La rekompenco ĉi tie estas pli malgranda, ĉar Bitmap Heap Scan estas sufiĉe efika per si mem. Sed ĉiuokaze 7 fojojn pli rapide kaj 2.5 fojojn malpli da legaĵoj.
#3: Kunfandi indeksojn (BitmapOr)
Kiam ekestas
Montru la unuajn 20 plej malnovajn "ni" aŭ neasignitajn petojn por prilaborado, kun via prioritato.
Kiel identigi
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanrekomendoj
Uzu UNIO [ĈIUJ] kombini subdemandojn por ĉiu el la OR-blokoj de kondiĉoj.
Ekzemplo:
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;
Korektante:
(
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, больше и не надо
Ni profitis, ke ĉiuj 20 postulataj registroj estis tuj ricevitaj en la unua bloko, do la dua, kun la pli "multekosta" Bitmap Heap Scan, eĉ ne estis efektivigita - finfine 22x pli rapide, 44x malpli da legado!
Pli detala rakonto pri ĉi tiu optimumiga metodo uzante specifajn ekzemplojn legeblas en artikoloj и .
Ĝeneraligita versio ordigita elekto surbaze de pluraj ŝlosiloj (kaj ne nur la paro const/NULL) estas diskutata en la artikolo .
#4: Ni legas multajn nenecesajn aferojn
Kiam ekestas
Kiel regulo, ĝi aperas kiam vi volas "aldoni alian filtrilon" al jam ekzistanta peto.
“Kaj vi ne havas la saman, sed kun perlamo-butonoj? » filmo "La Diamanta Brako"
Ekzemple, modifante la supran taskon, montru la unuajn 20 plej malnovajn "kritikajn" petojn por prilaborado, sendepende de ilia celo.
Kiel identigi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
rekomendoj
Kreu [pli] specialigitan indekso kun WHERE kondiĉo aŭ inkluzivi pliajn kampojn en la indekso.
Se la filtrila kondiĉo estas "senmova" por viaj celoj - tio estas ne implicas vastiĝon listo de valoroj estonte - estas pli bone uzi WHERE-indekson. Diversaj buleaj/enumaj statusoj bone taŭgas en ĉi tiun kategorion.
Se la filtra kondiĉo povas preni malsamajn signifojn, tiam estas pli bone vastigi la indekson kun ĉi tiuj kampoj - kiel en la situacio kun BitmapAnd supre.
Ekzemplo:
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;
Korektante:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kiel vi povas vidi, filtrado tute malaperis de la plano, kaj la peto fariĝis 5 fojojn pli rapide.
#5: malabunda tablo
Kiam ekestas
Diversaj provoj krei vian propran taskopretigan vicon, kiam granda nombro da ĝisdatigoj/forigoj de rekordoj sur la tablo kondukas al situacio de granda nombro da "mortintaj" rekordoj.
Kiel identigi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
rekomendoj
Faru regule regule VAKUO [PLENA] aŭ atingi taŭge oftan trejnadon per fajnagordado de ĝiaj parametroj, inkluzive .
Plejofte, tiaj problemoj estas kaŭzitaj de malbona demanda aranĝo dum vokado de komerca logiko kiel tiuj diskutitaj en .
Sed vi devas kompreni, ke eĉ VACUUM FULL eble ne ĉiam helpas. Por tiaj kazoj, indas konatiĝi kun la algoritmo de la artikolo .
#6: Legado de la "mezo" de la indekso
Kiam ekestas
Ŝajnas, ke ni iomete legis, kaj ĉio estis indeksita, kaj ni ne filtris iun troan - sed tamen ni legas signife pli da paĝoj ol ni ŝatus.
Kiel identigi
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
rekomendoj
Rigardu detale la strukturon de la uzata indekso kaj la ŝlosilaj kampoj specifitaj en la demando - plej verŝajne parto de la indekso ne estas fiksita. Plej verŝajne vi devos krei similan indekson, sed sen la prefiksaj kampoj aŭ .
Ekzemplo:
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;
Ĉio ŝajnas esti bona, eĉ laŭ la indekso, sed estas iel suspektinda - por ĉiu el la 20 legitaj registroj, ni devis subtrahi 4 paĝojn da datumoj, 32KB po rekordo - ĉu ne aŭdaca? Kaj la indeksa nomo tbl_fk_org_fk_cli_idx pensiga.
Korektante:
CREATE INDEX ON tbl(fk_cli);
Subite - 10 fojojn pli rapide, kaj 4 fojojn malpli por legi!
Aliaj ekzemploj de situacioj de neefika uzo de indeksoj videblas en la artikolo .
#7: CTE × CTE
Kiam ekestas
En peto gajnis "dikan" CTE de diversaj tabloj, kaj tiam decidis fari tion inter ili JOIN.
La kazo estas grava por versioj sub v12 aŭ petoj kun WITH MATERIALIZED.
Kiel identigi
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
rekomendoj
Atente analizu la peton — kaj ? Se jes, do apliki "vortaron" en hstore/json laŭ la modelo priskribita en .
#8: interŝanĝi al disko (tempe skribita)
Kiam ekestas
Unufoja prilaborado (ordigo aŭ unikiĝo) de granda nombro da rekordoj ne taŭgas en la memoro asignita por tio.
Kiel identigi
-> *
&& temp written > 0rekomendoj
Se la kvanto de memoro uzata de la operacio ne multe superas la specifitan valoron de la parametro , indas korekti ĝin. Vi povas tuj en la agordo por ĉiuj, aŭ vi povas tra SET [LOCAL] por specifa peto/transakcio.
Ekzemplo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korektante:
SET work_mem = '128MB'; -- перед выполнением запроса
Pro evidentaj kialoj, se nur memoro estas uzata kaj ne disko, tiam la demando estos efektivigita multe pli rapide. Samtempe, parto de la ŝarĝo de la HDD ankaŭ estas forigita.
Sed vi devas kompreni, ke vi ne ĉiam povos asigni multe da memoro - simple ne sufiĉos por ĉiuj.
#9: senrilataj statistikoj
Kiam ekestas
Ili verŝis multe en la datumbazon samtempe, sed ne havis tempon por forpeli ĝin ANALYZE.
Kiel identigi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10rekomendoj
Efektivigu ĝin ANALYZE.
Ĉi tiu situacio estas priskribita pli detale en .
#10: "io misfunkciis"
Kiam ekestas
Ekzistis atendo por seruro trudita de konkuranta peto, aŭ ekzistis nesufiĉaj CPU/hipervisor-hardvarresursoj.
Kiel identigi
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
rekomendoj
Uzu eksteran monitora sistemo servilo por blokado aŭ nenormala konsumo de rimedoj. Ni jam parolis pri nia versio pri organizado de ĉi tiu procezo por centoj da serviloj и .
fonto: www.habr.com