Пре неколико месеци - јавно на ПостгреСКЛ.
Већ сте га користили више од 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 није било и нема (више о овоме можете прочитати ). Укључујући, обезбедиће нормално експлицитна подршка страном кључу на овом терену.
#2: пресек индекса (БитмапАнд)
Када ради
Прикажи све уговоре за клијента „ЛЛЦ Колоколцхик“, закључене у име „НАО Буттерцуп“.
Како препознати
-> 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);
Исплата је овде мања, пошто је скенирање битмап хеап-а прилично ефикасно само по себи. Али у сваком случају 7 пута брже и 2.5 пута мање читања.
#3: Спајање индекса (БитмапОр)
Када ради
Прикажите првих 20 најстаријих „нас“ или недодељених захтева за обраду, са вашим приоритетом.
Како препознати
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanПрепоруке
Употреба УНИОН [СВИ] да се комбинују потупити за сваки од ОР-блокова услова.
Пример:
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 потребних записа одмах примљено у првом блоку, тако да други, са „скупљим” Битмап Хеап Сцан, није ни извршен – на крају 22к брже, 44к мање читања!
Детаљнија прича о овој методи оптимизације на конкретним примерима може се прочитати у чланцима и .
Генерализована верзија наређени избор на основу неколико кључева (а не само пар цонст/НУЛЛ) се разматра у чланку .
#4: Читамо много непотребних ствари
Када ради
По правилу се јавља када желите да „прикачите још један филтер“ на већ постојећи захтев.
„И немате исти, али са бисерним дугмадима? " филм "Дијамантска рука"
На пример, модификујући горњи задатак, прикажите првих 20 најстаријих „критичних“ захтева за обраду, без обзира на њихову сврху.
Како препознати
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Препоруке
Направите [више] специјализоване индекс са ВХЕРЕ условом или укључити додатна поља у индекс.
Ако је услов филтера "статичан" за ваше потребе - тј не подразумева експанзију списак вредности у будућности - боље је користити индекс ВХЕРЕ. Различити боолеан/енум статуси се добро уклапају у ову категорију.
Ако је услов филтрирања може попримити различита значења, онда је боље проширити индекс овим пољима - као у ситуацији са БитмапАнд изнад.
Пример:
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
Препоруке
Редовно изводите ручно ВАКУУМ [ПУН] или остварити адекватно учесталу обуку финим подешавањем његових параметара, укључујући .
У већини случајева, такви проблеми су узроковани лошом композицијом упита при позивању из пословне логике попут оних о којима се говори у .
Али морате схватити да чак ни ВАКУУМ ПУНИ можда неће увек помоћи. У таквим случајевима, вреди се упознати са алгоритмом из чланка .
#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 странице података, 32 КБ по запису - зар није подебљано? И назив индекса tbl_fk_org_fk_cli_idx подстичући на размишљање.
Исправљање:
CREATE INDEX ON tbl(fk_cli);
изненада - 10 пута брже, а 4 пута мање за читање!
Други примери ситуација неефикасне употребе индекса се могу видети у чланку .
#7: ЦТЕ × ЦТЕ
Када ради
На захтев постигао „дебели” ЦТЕ из различитих столова, а затим одлучио да то уради између њих JOIN.
Случај је релевантан за верзије испод в12 или захтеве са WITH MATERIALIZED.
Како препознати
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Препоруке
Пажљиво анализирајте захтев - и ? Ако да, онда примени "речник" у хсторе/јсон према моделу описаном у .
#8: пребаците на диск (температура записана)
Када ради
Једнократна обрада (сортирање или јединственост) великог броја записа не уклапа се у меморију која је за то додељена.
Како препознати
-> *
&& temp written > 0Препоруке
Ако количина меморије коју користи операција не прелази много наведену вредност параметра , вреди то исправити. Можете одмах у конфигурацији за све, а можете и преко SET [LOCAL] за одређени захтев/трансакцију.
Пример:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Исправљање:
SET work_mem = '128MB'; -- перед выполнением запроса
Из очигледних разлога, ако се користи само меморија, а не диск, онда ће се упит извршити много брже. Истовремено, део оптерећења са ХДД-а се такође уклања.
Али морате да схватите да нећете увек моћи да доделите много и пуно меморије - једноставно неће бити довољно за све.
#9: небитна статистика
Када ради
Убацили су много у базу података одједном, али нису имали времена да је отерају ANALYZE.
Како препознати
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Препоруке
Изведите то ANALYZE.
Ова ситуација је детаљније описана у .
#10: „нешто је пошло наопако“
Када ради
Дошло је до чекања на закључавање наметнуто конкурентским захтевом или није било довољно хардверских ресурса ЦПУ/хипервизора.
Како препознати
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Препоруке
Користите екстерно Мониторинг систем сервер за блокирање или абнормалну потрошњу ресурса. Већ смо говорили о нашој верзији организовања овог процеса за стотине сервера и .
Извор: ввв.хабр.цом