Декілька місяців тому - публічний до 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Рекомендації
використовувати СОЮЗ [ВСЕ] для об'єднання підзапитів щодо кожного з 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