Некалькі месяцаў таму - публічны да PostgreSQL.
За мінулы час вы ўжо скарысталіся ім больш за 6000 разоў, але адна з зручных функцый магла застацца незаўважанай - гэта структурныя падказкі, якія выглядаюць прыкладна так:
Прыслухоўвайцеся да іх, і вашыя запыты "стануць гладкімі і шаўкавістымі". 🙂
А калі сур'ёзна, то многія сітуацыі, якія робяць запыт павольным і «пражэрлівым» па рэсурсах, тыповыя і могуць быць распазнаны па структуры і дадзеным плана.
У гэтым выпадку кожнаму асобнаму распрацоўніку не давядзецца шукаць варыянт аптымізацыі самастойна, абапіраючыся выключна на свой вопыт - мы можам яму падказаць, што тут адбываецца, у чым можа быць прычына, і як можна падысці да рашэння. Што мы і зрабілі.
Давайце крыху падрабязней разгледзім гэтыя кейсы - як яны вызначаюцца і да якіх рэкамендацый прыводзяць.
Для лепшага апускання ў тэму спачатку можна паслухаць адпаведны блок з , а ўжо потым перайсці да дэталёвага разбору кожнага прыкладу:
#1: індэксная «недасартоўка»
Калі ўзнікае
Паказаць апошні рахунак па кліенту «ТАА Званочак».
Як апазнаць
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Рэкамендацыі
Выкарыстоўваны індэкс пашырыць палямі сартавання.
Прыклад:
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;
Адразу можна заўважыць, што па індэксе адымалася больш за 100 запісаў, якія потым усё сартаваліся, а потым была пакінута адзіная.
Выпраўляемы:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Нават на такой прымітыўнай выбарцы - у 8.5 разоў хутчэй і ў 33 разы менш чытанняў. Эфект будзе тым навочней, чым больш у вас "фактаў" па кожным значэнні fk.
Заўважу, што такі індэкс будзе працаваць як "прэфіксны" не горш за ранейшы і па іншых запытах з fk, дзе сартавання па pk не было і не (падрабязней пра гэта можна прачытаць ). У тым ліку, ён забяспечыць і нармальную падтрымку відавочнага foreign key па гэтым полі.
#2: скрыжаванне індэксаў (BitmapAnd)
Калі ўзнікае
Паказаць усе дагаворы па кліенту «ТАА Званочак», заключаныя ад імя «НАО Люцік».
Як апазнаць
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanРэкамендацыі
Стварыць састаўны індэкс па палях з абодвух зыходных або пашырыць адзін з існуючых палямі з другога.
Прыклад:
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); -- отбор по конкретной паре
Выпраўляемы:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Тут выйгрыш менш, паколькі Bitmap Heap Scan дастаткова эфектыўны сам па сабе. Але ўсё ж у 7 разоў хутчэй і ў 2.5 разы менш чытанняў.
#3: аб'яднанне індэксаў (BitmapOr)
Калі ўзнікае
Паказаць першыя 20 самых старых «сваіх» ці непрызначаных заявак для апрацоўкі, прычым свае ў прыярытэце.
Як апазнаць
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanРэкамендацыі
выкарыстоўваць UNION [ALL] для аб'яднання подзапросов па кожным з OR-блокаў умоў.
Прыклад:
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;
Выпраўляемы:
(
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, больше и не надо
Мы скарысталіся тым, што ўсе 20 патрэбных запісаў былі адразу атрыманы ўжо ў першым блоку, таму другі, з больш "дарагім" Bitmap Heap Scan, нават не выконваўся - у выніку у 22 разы хутчэй, у 44 разы менш чытанняў!
Больш дэталёвы аповяд пра гэты спосаб аптымізацыі. на канкрэтных прыкладах можна прачытаць у артыкулах и .
Абагульнены варыянт спарадкаванага адбору па некалькіх ключах (а не толькі па пары const/NULL) разгледжаны ў артыкуле .
#4: чытэльны шмат лішняга
Калі ўзнікае
Як правіла, узнікае пры жаданні "прыкруціць яшчэ адзін фільтр" да ўжо існуючага запыту.
«А ў вас няма такога ж, але з перламутравымі гузікамі? » х/ф «Дыяментавая рука»
Напрыклад, мадыфікуючы задачу вышэй, паказаць першыя 20 самых старых "крытычных" заявак для апрацоўкі, незалежна ад іх прызначанасці.
Як апазнаць
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Рэкамендацыі
Стварыць [больш] спецыялізаваны індэкс з WHERE-умовай ці ўключыць у індэкс дадатковыя палі.
Калі ўмова фільтравання з'яўляецца "статычным" для вашых задач - гэта значыць не прадугледжвае пашырэння пераліку значэнняў у будучыні - лепш выкарыстоўваць WHERE-індэкс. У гэтую катэгорыю добра ўкладваюцца розныя boolean/enum-статусы.
Калі ж ўмова фільтрацыі можа прымаць розныя значэння, то лепш пашырыць індэкс гэтымі палямі - як у сітуацыі з BitmapAnd вышэй.
Прыклад:
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;
Выпраўляемы:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Як бачым, фільтраванне з плана цалкам сышло, а запыт стаў у 5 разоў хутчэй.
#5: разрэджаная табліца
Калі ўзнікае
Разнастайныя спробы зрабіць уласную чаргу апрацоўкі задач, калі вялікая колькасць абнаўленняў/выдаленняў запісаў на табліцы прыводзяць да сітуацыі вялікай колькасці "мёртвых" запісаў.
Як апазнаць
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Рэкамендацыі
Рэгулярна ўручную праводзіць VACUUM [FULL] ці дамагчыся адэкватна частай адпрацоўкі з дапамогай тонкай наладкі яго параметраў, у тым ліку .
У большасці выпадкаў падобныя праблемы аказваюцца выкліканыя дрэннай кампаноўкай запытаў пры выкліках з бізнес-логікі накшталт тых, якія былі разгледжаны ў .
Але трэба разумець, што нават VACUUM FULL можа памагчы не заўсёды. Для такіх выпадкаў варта азнаёміцца з алгарытмам з артыкула .
#6: чытанне з "сярэдзіны" індэкса
Калі ўзнікае
Накшталт і прачыталі трохі, і ўсё па азначніку, і нікога лішняга не фільтравалі - а ўсё роўна прачытана істотна больш старонак, чым жадалася бы.
Як апазнаць
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Рэкамендацыі
Уважліва паглядзець на структуру які выкарыстоўваўся азначніка і ключавыя палі, зададзеныя ў запыце — хутчэй за ўсё, частка індэкса не зададзена. Хутчэй за ўсё, вам давядзецца стварыць падобны індэкс, але без прэфіксных палёў або .
Прыклад:
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;
Накшталт бы ўсё добра, нават па азначніку, але неяк падазрона — на кожны з 20 прачытаных запісаў прыйшлося адымаць па 4 старонкі дадзеных, 32KB на запіс — ці не тоўста? Ды і імя азначніка tbl_fk_org_fk_cli_idx наводзіць на разважанні.
Выпраўляемы:
CREATE INDEX ON tbl(fk_cli);
Раптам - у 10 разоў хутчэй, і ў 4 разы менш чытаць!
Іншыя прыклады сітуацый неэфектыўнага выкарыстання індэксаў можна ўбачыць у артыкуле .
#7: CTE × CTE
Калі ўзнікае
У запыце набралі "тлустых" CTE з розных табліц, а потым вырашылі зрабіць паміж імі JOIN.
Кейс актуальны для версій ніжэй v12 або запытаў з WITH MATERIALIZED.
Як апазнаць
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Рэкамендацыі
Уважліва прааналізаваць запыт - а ? Калі ўсё ж так, то прымяніць «аслоўванне» у hstore/json па мадэлі, апісанай у .
#8: swap на дыск (temp written)
Калі ўзнікае
Разавая апрацоўка (сартаванне ці ўнікалізацыя) вялікай колькасці запісаў не залазіць у вылучаную для гэтага памяць.
Як апазнаць
-> *
&& temp written > 0Рэкамендацыі
Калі выкарыстанае аперацыяй колькасць памяці не моцна перавышае ўсталяванае значэнне параметру , варта яго скарэктаваць. Можна адразу ў канфігу для ўсіх, а можна праз SET [LOCAL] для канкрэтнага запыту/транзакцыі.
Прыклад:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Выпраўляемы:
SET work_mem = '128MB'; -- перед выполнением запроса
Па зразумелых прычынах, калі выкарыстоўваецца толькі памяць, а не дыск, то і запыт будзе выконвацца нашмат хутчэй. Пры гэтым яшчэ і частка нагрузкі з HDD здымаецца.
Але трэба разумець, што вылучаць шматліка-шмат памяці заўсёды таксама не атрымаецца - яе банальна не хопіць на ўсіх.
#9: неактуальная статыстыка
Калі ўзнікае
У базу ўлілі адразу шмат, але не паспелі прагнаць ANALYZE.
Як апазнаць
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Рэкамендацыі
Правесці-такі ANALYZE.
Падрабязней дадзеная сітуацыя распісана ў .
#10: "нешта пайшло не так"
Калі ўзнікае
Адбылося чаканне блакавання, накладзенай канкуруючым запытам, ці не хапіла апаратных рэсурсаў CPU/гіпервізара.
Як апазнаць
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Рэкамендацыі
Выкарыстоўвайце знешнюю сістэму для маніторынгу сервера на прадмет наяўнасці блакіровак або няштатнага спажывання рэсурсаў. Пра наш варыянт арганізацыі гэтага працэсу для сотняў сервераў мы ўжо расказвалі и .
Крыніца: habr.com