Пошук уроків, статей та іншого контенту
Пройдіть повний цикл діагностики й оптимізації запиту: від EXPLAIN ANALYZE до перевірки результату.
Оптимізація повільного запиту — це не випадковий підбір індексів. Повний цикл має вигляд:
Відтворити повільний запит на реальних або репрезентативних даних.
Зафіксувати базові показники.
Проаналізувати план виконання.
Сформулювати гіпотезу про причину повільної роботи.
Змінити запит, індекси або статистику.
Повторно виміряти результат.
Перевірити, що результат запиту не змінився.
Перевірити поведінку на інших параметрах і після оновлення даних.
Головний інструмент для цього циклу — EXPLAIN ANALYZE.
Розглянемо запит, який шукає останні оплачені замовлення клієнтів із певного регіону за конкретний день.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
region text NOT NULL
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
status text NOT NULL,
created_at timestamp NOT NULL,
total_amount numeric(12, 2) NOT NULL
);
INSERT INTO customers (region)
SELECT CASE
WHEN g % 10 = 0 THEN 'west'
WHEN g % 10 = 1 THEN 'east'
WHEN g % 10 = 2 THEN 'north'
ELSE 'south'
END
FROM generate_series(1, 10000) AS s(g);
INSERT INTO orders (
customer_id,
status,
created_at,
total_amount
)
SELECT
(random() * 9999 + 1)::bigint,
CASE
WHEN random() < 0.70 THEN 'paid'
WHEN random() < 0.90 THEN 'pending'
ELSE 'cancelled'
END,
timestamp '2026-01-01'
+ random() * interval '240 days',
round((10 + random() * 990)::numeric, 2)
FROM generate_series(1, 500000);
ANALYZE customers;
ANALYZE orders;Початковий запит:
SELECT
o.id,
o.customer_id,
o.created_at,
o.total_amount
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE c.region = 'west'
AND o.status = 'paid'
AND date_trunc('day', o.created_at) = timestamp '2026-08-01'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;Умова
date_trunc('day', o.created_at) = timestamp '2026-08-01'застосовує функцію до кожного значення o.created_at. Звичайний індекс на created_at не зможе безпосередньо використати таку умову як діапазон.
Спочатку потрібно отримати план без змін:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT
o.id,
o.customer_id,
o.created_at,
o.total_amount
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE c.region = 'west'
AND o.status = 'paid'
AND date_trunc('day', o.created_at) = timestamp '2026-08-01'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;EXPLAINANALYZE фактично виконує запит і показує реальні вимірювання.
BUFFERS показує роботу з буферами shared buffer та диском.
VERBOSE додає деталі вузлів плану.
SETTINGS показує важливі параметри, які вплинули на план.
План може мати приблизно таку форму:
Limit
-> Sort
Sort Key: o.created_at DESC, o.id DESC
-> Hash Join
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o
Filter: ((status = 'paid') AND
(date_trunc('day', created_at) = '2026-08-01'::timestamp))
-> Hash
-> Seq Scan on customers c
Filter: (region = 'west'::text)Конкретні числа залежать від версії PostgreSQL, розміру таблиць, статистики та випадково згенерованих даних.
costНаприклад:
Seq Scan on orders (cost=0.00..15000.00 rows=500 width=40)cost — це внутрішня оцінка PostgreSQL, а не час у мілісекундах. Вона потрібна планувальнику для порівняння альтернативних планів.
Не можна робити висновок, що план із cost=100 обов’язково вдвічі швидший за план із cost=200.
actual timeНаприклад:
(actual time=0.020..180.500 rows=350 loops=1)Це фактичний час виконання вузла:
перше число — час отримання першого рядка;
друге — час отримання всіх рядків;
rows — кількість рядків, отриманих вузлом;
loops — кількість виконань вузла.
Приблизний час вузла з урахуванням повторень — це його час, помножений на loops.
rows і actual rowsПорівнюйте оцінку:
rows=500із фактом:
actual rows=25000Велика різниця означає, що планувальник неправильно оцінив вибірковість умови. Це може призвести до невдалого вибору між:
послідовним скануванням;
індексним скануванням;
nested loop;
hash join;
merge join.
У нашому прикладі особливо важливо перевірити, скільки рядків PostgreSQL переглядає в orders, перш ніж залишити лише рядки потрібного дня.
BuffersПриклад:
Buffers: shared hit=12000 read=8000shared hit — сторінки знайдено в кеші PostgreSQL;
shared read — сторінки довелося прочитати;
temp read і temp written — використано тимчасові файли, наприклад для великого сортування або хешування.
Якщо запит повільний через велику кількість shared read, проблема може бути пов’язана з дисковим введенням-виведенням. Якщо переважають shared hit, запит все одно може бути повільним через обробку великої кількості рядків у пам’яті.
У запиті є дві проблеми:
date_trunc застосовується до колонки created_at.
Для цього сценарію потрібні лише оплачені замовлення.
Для timestamp-значення конкретний календарний день краще описати напіввідкритим інтервалом:
o.created_at >= timestamp '2026-08-01'
AND o.created_at < timestamp '2026-08-02'Такий запис:
не залежить від результату функції над колонкою;
однозначно описує межі дня;
може використовувати індекс на created_at.
Для цього запиту також доречний частковий індекс лише для оплачених замовлень.
CREATE INDEX orders_paid_created_at_idx
ON orders (created_at DESC, id DESC)
INCLUDE (customer_id, total_amount)
WHERE status = 'paid';
ANALYZE orders;Предикат індексу:
WHERE status = 'paid'означає, що в індекс потрапляють лише оплачені замовлення.
Переваги:
індекс менший за індекс усіх замовлень;
пошук для status = 'paid' може бути дешевшим;
записи зі статусами pending і cancelled не збільшують цей індекс.
PostgreSQL може використати частковий індекс, якщо умова запиту логічно передбачає його предикат. У нашому випадку запит містить:
o.status = 'paid'INCLUDEКолонки customer_id і total_amount потрібні запиту, але не використовуються для впорядкування індексу.
INCLUDE зберігає їх у листових сторінках індексу, не додаючи до ключа сортування. Це може дати змогу виконати index-only scan або зменшити кількість звернень до таблиці.
Index-only scan не гарантований: PostgreSQL також перевіряє visibility map, тому після великої кількості змін можуть знадобитися звернення до основної таблиці.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT
o.id,
o.customer_id,
o.created_at,
o.total_amount
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE c.region = 'west'
AND o.status = 'paid'
AND o.created_at >= timestamp '2026-08-01'
AND o.created_at < timestamp '2026-08-02'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;Потенційно план тепер може містити:
Limit
-> Nested Loop
-> Index Scan using orders_paid_created_at_idx on orders o
Index Cond: ((created_at >= '2026-08-01'::timestamp)
AND (created_at < '2026-08-02'::timestamp))
-> Index Scan using customers_pkey on customers c
Index Cond: (id = o.customer_id)
Filter: (region = 'west'::text)Або PostgreSQL може вибрати іншу структуру, наприклад hash join. Це нормально: правильний план залежить від кількості рядків, розподілу регіонів та актуальної статистики.
Оптимізація не полягає в тому, щоб домогтися конкретного типу вузла. Мета — зменшити фактичний час, кількість оброблених рядків і зайві операції введення-виведення.
Потрібно порівняти щонайменше:
загальний Execution Time;
кількість прочитаних буферів;
кількість рядків на основних вузлах;
наявність сортування;
відповідність rows і actual rows;
поведінку для різних параметрів.
Не варто порівнювати лише значення cost. Наприклад, після створення індексу оцінка може змінитися, але реальний час — майже ні, якщо всі потрібні сторінки вже знаходяться в кеші.
Під час тестування корисно виконати кожен варіант кілька разів. Перше виконання може включати читання з диска, а наступні — працювати з кешем.
Оптимізація не повинна непомітно змінити бізнес-логіку. Перевіримо, що обидва запити повертають однакові ідентифікатори.
WITH old_result AS (
SELECT
o.id
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE c.region = 'west'
AND o.status = 'paid'
AND date_trunc('day', o.created_at) = timestamp '2026-08-01'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50
),
new_result AS (
SELECT
o.id
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE c.region = 'west'
AND o.status = 'paid'
AND o.created_at >= timestamp '2026-08-01'
AND o.created_at < timestamp '2026-08-02'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50
),
differences AS (
(
SELECT id
FROM old_result
EXCEPT
SELECT id
FROM new_result
)
UNION ALL
(
SELECT id
FROM new_result
EXCEPT
SELECT id
FROM old_result
)
)
SELECT count(*) AS different_rows
FROM differences;Очікуваний результат:
different_rows
----------------
0Додавання o.id DESC до ORDER BY важливе. Якщо кілька замовлень мають однаковий created_at, сортування лише за цією колонкою не гарантує стабільний порядок рядків. Через це два коректні запити можуть повернути різні рядки на межі LIMIT 50.
Для заміни date_trunc на діапазон особливо важливі межі:
2026-08-01 00:00:00 має потрапити до результату;
2026-08-01 23:59:59.999999 має потрапити до результату;
2026-08-02 00:00:00 не має потрапити до результату.
Саме тому використовується умова:
created_at >= початок_дня
AND created_at < початок_наступного_дняа не умова з включеною верхньою межею:
created_at <= timestamp '2026-08-01 23:59:59'Останній варіант може втратити значення з дробовою частиною секунди.
Індекс не замінює статистику. Після значних змін у таблиці виконайте:
ANALYZE orders;
ANALYZE customers;ANALYZE збирає статистику, яку планувальник використовує для оцінки кількості рядків.
Якщо оцінки систематично неточні для конкретної колонки, можна збільшити статистичну ціль:
ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 1000;
ALTER TABLE orders
ALTER COLUMN created_at SET STATISTICS 1000;
ANALYZE orders;Збільшення статистики не є універсальним способом прискорення. Воно збільшує обсяг статистичних даних і час ANALYZE, тому його слід застосовувати після вимірювання проблеми з оцінками.
EXPLAIN ANALYZE виконує запит. Для SELECT це зазвичай безпечно, але для INSERT, UPDATE і DELETE він реально змінює дані.
Модифікуючий запит можна перевіряти в транзакції:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET total_amount = total_amount
WHERE status = 'paid';
ROLLBACK;Створення індексу в робочій базі часто виконують так:
CREATE INDEX CONCURRENTLY orders_paid_created_at_idx
ON orders (created_at DESC, id DESC)
INCLUDE (customer_id, total_amount)
WHERE status = 'paid';CREATE INDEX CONCURRENTLY зменшує блокування звичайних операцій із таблицею, але має додаткові витрати та не може виконуватися всередині звичайної транзакції. Для локального навчального прикладу достатньо звичайного CREATE INDEX.
Оптимізація для одного дня або одного регіону не гарантує однаково добрий результат для всіх значень.
Перевірте запит для:
дня з великою кількістю замовлень;
дня з малою кількістю замовлень;
регіону з високою та низькою кількістю клієнтів;
періоду, для якого LIMIT швидко знаходить 50 рядків;
періоду, де потрібно переглянути багато рядків, перш ніж знайти 50 відповідних клієнтів.
Планувальник може вибрати різні плани для різних параметрів. Це не обов’язково проблема. Важливо, щоб кожен план був виправданим фактичними даними.
costcost — не час виконання. Для оцінки результату використовуйте actual time, Execution Time та BUFFERS.
Кожен індекс:
займає місце;
уповільнює INSERT, UPDATE і DELETE;
потребує обслуговування під час vacuum;
може взагалі не використовуватися.
Спочатку зафіксуйте проблему через EXPLAIN ANALYZE, потім перевірте гіпотезу.
Якщо планувальник очікує 10 рядків, а отримує 100 000, він може вибрати неправильний тип join або порядок виконання.
Умова <= '23:59:59' може втратити значення з мікросекундами. Використовуйте напіввідкритий інтервал із < для наступного дня.
Результат залежить від кешу, навантаження, стану таблиці та параметрів. Порівнюйте кілька запусків і, за можливості, перевіряйте на даних, близьких до продуктивного середовища.
LIMIT без повного детермінованого ORDER BY може повертати різні рядки при однакових значеннях сортування. Додавайте унікальний ключ як додатковий критерій.
Для великої частини таблиці послідовне сканування може бути швидшим за індексне. Важливий не сам тип вузла, а фактична продуктивність плану.
Повний цикл оптимізації запиту в PostgreSQL:
Запустити початковий запит через EXPLAIN (ANALYZE, BUFFERS).
Знайти дорогі вузли та порівняти оцінені й фактичні кількості рядків.
Перевірити, чи не заважають індексу функції над колонками.
Переписати умови у формі, придатній для пошуку за діапазоном.
Створити індекс, який відповідає реальному предикату запиту.
Оновити статистику через ANALYZE.
Повторно виміряти план і фактичний час.
Переконатися, що старий і новий запити повертають однаковий результат.
Перевірити граничні значення та інші параметри.
Оцінити вартість індексу для операцій запису й подальшого обслуговування.