Пошук уроків, статей та іншого контенту
Поясніть параметри cost-based optimization, статистику таблиць і причини неточних оцінок.
PostgreSQL не виконує SQL-запит у тому порядку, у якому він записаний. Спочатку планувальник формує можливі плани, оцінює їхню вартість і вибирає план із найменшою оцінкою.
Наприклад, для умови:
SELECT *
FROM orders
WHERE customer_id = 42;планувальник може вибрати:
послідовне читання всієї таблиці (Seq Scan);
сканування індексу (Index Scan);
сканування індексу з подальшим читанням таблиці (Bitmap Heap Scan);
паралельний варіант одного з цих планів.
Вибір залежить не лише від наявності індексу. PostgreSQL оцінює:
скільки рядків потрібно прочитати;
наскільки дані вибіркові;
скільки сторінок таблиці або індексу доведеться прочитати;
чи можуть дані вже перебувати в кеші;
вартість обробки кожного рядка й умови;
витрати на запуск операції;
можливість паралельного виконання.
Це називають cost-based optimization — оптимізацією на основі вартості.
Для аналізу планів використовують EXPLAIN:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;Типовий результат може мати такий вигляд:
Index Scan using orders_customer_id_idx on orders
(cost=0.42..12.50 rows=5 width=64)
Index Cond: (customer_id = 42)Основні частини:
cost=0.42..12.50:
0.42 — вартість запуску вузла;
12.50 — повна очікувана вартість отримання всіх рядків;
rows=5 — очікувана кількість рядків;
width=64 — очікуваний середній розмір одного рядка в байтах.
Значення cost не є мілісекундами. Це умовні одиниці, які дають змогу порівнювати різні плани між собою.
Щоб побачити фактичні результати виконання, використовують EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;У результаті з’являються додаткові дані:
actual time — фактичний час;
rows — фактична кількість рядків;
loops — кількість повторних виконань вузла;
Buffers — інформація про читання сторінок із кешу та диска.
Наприклад:
Index Scan using orders_customer_id_idx on orders
(cost=0.42..12.50 rows=5 width=64)
(actual time=0.030..0.041 rows=120 loops=1)Тут видно суттєву різницю:
оцінка: rows=5;
факт: rows=120.
Така помилка може призвести до вибору невдалого плану.
EXPLAIN ANALYZEфактично виконує запит. ДляINSERT,UPDATEабоDELETEце означає зміну даних, якщо не використати транзакцію з подальшимROLLBACK.
Таблиці та індекси PostgreSQL зберігаються на сторінках. Планувальник оцінює, скільки сторінок потрібно прочитати.
Основні параметри:
SHOW seq_page_cost;
SHOW random_page_cost;Типові значення:
seq_page_cost = 1.0;
random_page_cost = 4.0.
seq_page_cost описує відносну вартість послідовного читання сторінки.
random_page_cost описує відносну вартість випадкового читання сторінки. Історично випадковий доступ до диска був значно дорожчим за послідовний, тому типове значення вище.
На сучасних SSD-системах різниця між послідовним і випадковим доступом може бути меншою. Однак це не означає, що потрібно автоматично зменшувати random_page_cost. Параметр має відображати не лише швидкість диска, а й:
використання кешу операційної системи;
розмір доступного кешу;
характер робочого навантаження;
частку випадкових читань.
Зміна параметра для всього сервера без вимірювань може погіршити інші запити.
Планувальник також враховує процесорні витрати:
SHOW cpu_tuple_cost;
SHOW cpu_index_tuple_cost;
SHOW cpu_operator_cost;Вони описують приблизну відносну вартість:
обробки рядка таблиці;
обробки запису індексу;
перевірки умови або виконання оператора.
Ці параметри важливі, коли потрібно порівняти, наприклад:
послідовне читання великої кількості рядків;
читання через індекс із перевіркою багатьох умов;
виконання складної умови для кожного рядка.
Кожен вузол плану має:
(cost=startup_cost..total_cost)Вартість запуску — це витрати до того, як вузол почне повертати рядки.
Повна вартість — очікувані витрати на отримання всіх рядків вузла.
Наприклад, сортування може мати значну вартість запуску: спочатку потрібно отримати й обробити набір рядків, і лише після цього почати повертати результат.
Це важливо для запитів із LIMIT. План із вищою повною вартістю може бути вигіднішим, якщо він швидше повертає перші рядки.
Для паралельних планів використовуються, зокрема:
SHOW parallel_setup_cost;
SHOW parallel_tuple_cost;parallel_setup_cost — оцінка витрат на запуск паралельної роботи;
parallel_tuple_cost — оцінка витрат на передавання рядка від паралельного виконавця до основного процесу.
Якщо оцінений обсяг роботи малий, витрати на запуск паралельного виконання можуть бути більшими за вигоду. Тому PostgreSQL не використовує паралельний план для кожного великого на вигляд запиту.
effective_cache_sizeПараметр:
SHOW effective_cache_size;не резервує пам’ять і не змінює розмір кешу PostgreSQL. Це лише оцінка обсягу пам’яті, доступної для кешування даних PostgreSQL і операційною системою.
Планувальник використовує цю оцінку під час порівняння планів, особливо планів із доступом через індекс.
Якщо значення занадто мале, планувальник може недооцінювати ймовірність того, що сторінки індексу або таблиці вже є в кеші.
Якщо значення завищене, планувальник може надто охоче вибирати індексні плани.
effective_cache_size не є аналогом:
shared_buffers;
фактично використаної пам’яті;
ліміту пам’яті для одного запиту.
Планувальник не переглядає всі рядки таблиці перед кожним запитом. Для оцінювання він використовує статистику, яку збирає ANALYZE.
Статистику можна оновити вручну:
ANALYZE orders;Також ANALYZE запускається автоматично процесом autovacuum за налаштованими правилами.
Основні відомості доступні в системному поданні pg_stats:
SELECT
tablename,
attname,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds,
correlation
FROM pg_stats
WHERE tablename = 'orders'
AND attname = 'customer_id';Для оцінки загального розміру таблиці планувальник використовує статистику кількості рядків. Вона може бути приблизною, особливо якщо таблиця часто змінюється, а статистика ще не оновилася.
Перевірити оцінку можна в EXPLAIN:
EXPLAIN
SELECT *
FROM orders;Для послідовного сканування значення rows зазвичай близьке до оціненої кількості рядків таблиці.
n_distinctn_distinct описує кількість різних значень у стовпці.
Якщо значень небагато, умова на конкретне значення може повертати значну частину таблиці. У такій ситуації послідовне сканування часто дешевше за індексне.
Якщо значення майже завжди унікальні, умова на одне значення зазвичай дуже вибіркова, і індекс може бути ефективним.
most_common_vals і most_common_freqs містять найчастіші значення та їхні частоти.
Це потрібно, бо розподіл даних часто нерівномірний.
Наприклад, у стовпці status:
active може становити 90% рядків;
cancelled — 5%;
pending — 5%.
Однакова за формою умова:
WHERE status = 'active'і
WHERE status = 'pending'має різну вибірковість. Для першої умови індекс може бути невигідним, а для другої — корисним.
Для значень, які не потрапили до списку найчастіших, PostgreSQL використовує гістограму.
histogram_bounds містить межі діапазонів, за якими планувальник оцінює частку значень у заданому інтервалі.
Наприклад:
WHERE created_at >= DATE '2026-01-01'
AND created_at < DATE '2026-02-01'Для оцінювання такої умови планувальник використовує розподіл значень у стовпці created_at.
Гістограма не зберігає кожне значення. Тому оцінка є наближеною.
correlation описує зв’язок між логічним порядком значень стовпця та фізичним порядком рядків у таблиці.
Висока за модулем кореляція може бути корисною для індексного сканування, оскільки записи з близькими значеннями можуть перебувати на близьких сторінках таблиці.
Низька кореляція означає, що індексний доступ може спричинити багато випадкових читань сторінок. Це впливає на вибір між Index Scan, Bitmap Heap Scan і Seq Scan.
Параметр default_statistics_target визначає деталізацію статистики за замовчуванням:
SHOW default_statistics_target;Збільшення значення може дати точніші оцінки для стовпців зі складним або нерівномірним розподілом, але має ціну:
ANALYZE може працювати довше;
статистика може займати більше місця;
планування запитів може використовувати більше ресурсів.
Зазвичай краще збільшувати статистичну ціль лише для проблемного стовпця:
ALTER TABLE orders
ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;Поточну статистичну ціль можна переглянути так:
SELECT
attname,
attstattarget
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
AND attname = 'customer_id';Значення -1 означає використання default_statistics_target.
Окремі статистики стовпців не завжди достатні. Планувальник може помилятися, якщо умови використовують кілька пов’язаних між собою стовпців.
Наприклад, у таблиці адрес:
country = 'UA';
city = 'Kyiv'.
Ці значення не є незалежними: місто залежить від країни. Якщо планувальник припустить незалежність умов, він може суттєво помилитися в кількості рядків.
Для таких випадків можна створити розширену статистику:
CREATE STATISTICS orders_customer_status_stats
(dependencies, mcv, ndistinct)
ON customer_id, status
FROM orders;
ANALYZE orders;Доступні типи розширеної статистики включають:
dependencies — функціональні залежності між стовпцями;
mcv — спільні найчастіші комбінації значень;
ndistinct — кількість різних комбінацій значень.
Розширена статистика впливає на оцінювання запитів, але не створює індекс і не пришвидшує запит безпосередньо.
Після значних змін даних статистика може не відповідати поточному стану таблиці.
Перевірити час останнього аналізу можна так:
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';Якщо дані сильно змінилися, виконайте:
ANALYZE orders;Середня частота значень не описує ситуацію, коли кілька значень зустрічаються дуже часто, а решта — рідко.
Саме для цього PostgreSQL зберігає список найчастіших значень. Якщо потрібне значення не потрапило до цього списку, його частота може бути оцінена неточно.
У такій ситуації допомагає збільшення статистичної цілі для конкретного стовпця.
Для умов:
WHERE country = 'UA'
AND city = 'Kyiv'планувальник не завжди може точно оцінити результат, використовуючи лише окрему статистику country і city.
Якщо стовпці пов’язані, розгляньте CREATE STATISTICS із типом dependencies або mcv.
Статистика стовпця описує сам стовпець, але не обов’язково результат довільного виразу.
Наприклад:
WHERE lower(email) = 'user@example.com'Розподіл значень email не є повною статистикою для результату lower(email).
Оцінка може бути точнішою, якщо для такого доступу використовується відповідний індекс на вираз, а статистика та структура даних дають планувальнику достатньо інформації:
CREATE INDEX users_lower_email_idx
ON users (lower(email));Однак сам індекс не гарантує точної оцінки кількості рядків. Потрібно перевіряти фактичний план через EXPLAIN (ANALYZE).
ANALYZE працює зі статистичною вибіркою, а не обов’язково з усією таблицею. Для дуже нерівномірного розподілу випадкова вибірка може недостатньо добре відобразити рідкісні або локалізовані значення.
Підвищення STATISTICS збільшує розмір вибірки та деталізацію статистики, але не усуває всі можливі помилки.
Для тимчасових таблиць автоматичне оновлення статистики може не відбутися до виконання запиту. Після заповнення такої таблиці статистику часто потрібно оновити вручну:
ANALYZE temporary_table;Без цього планувальник може використовувати неточні припущення про розмір таблиці.
ANALYZEСтатистика є знімком стану даних на момент аналізу. Якщо після ANALYZE в таблицю додали великий обсяг даних лише для одного діапазону або одного значення, попередня статистика швидко втрачає актуальність.
Наведений приклад створює тимчасову таблицю з нерівномірним розподілом значень і порівнює оцінку планувальника з фактичним результатом:
CREATE TEMP TABLE event_log (
id bigint GENERATED ALWAYS AS IDENTITY,
event_type text NOT NULL,
created_at date NOT NULL
);
INSERT INTO event_log (event_type, created_at)
SELECT
CASE
WHEN g <= 90000 THEN 'view'
WHEN g <= 99000 THEN 'click'
ELSE 'purchase'
END,
DATE '2026-01-01' + ((g - 1) % 30)
FROM generate_series(1, 100000) AS numbers(g);
CREATE INDEX event_log_event_type_idx
ON event_log (event_type);
-- Спочатку статистика для таблиці ще може бути відсутньою або неточною.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM event_log
WHERE event_type = 'purchase';
ANALYZE event_log;
-- Після ANALYZE оцінка має враховувати нерівномірний розподіл значень.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM event_log
WHERE event_type = 'purchase';
-- Для частого значення планувальник може віддати перевагу
-- послідовному скануванню, оскільки потрібно прочитати більшість таблиці.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM event_log
WHERE event_type = 'view';Під час аналізу порівнюйте:
rows у плані — очікувану кількість рядків;
actual rows — фактичну кількість рядків;
вибраний тип сканування;
кількість буферів;
час виконання.
Важливо аналізувати не один вузол, а весь шлях від кореня плану до проблемного вузла. Помилка у кількості рядків на нижньому рівні може збільшуватися на кожному наступному рівні, особливо під час з’єднань.
Змінювати параметри планувальника потрібно після вимірювань, а не як першу спробу виправити повільний запит.
Безпечний спосіб перевірити гіпотезу — змінити параметр лише для поточної сесії:
SET LOCAL random_page_cost = 1.5;SET LOCAL працює в межах транзакції:
BEGIN;
SET LOCAL random_page_cost = 1.5;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;
ROLLBACK;Порівнюйте плани за допомогою однакових умов:
ті самі параметри запиту;
той самий обсяг даних;
прогрітий або непрогрітий кеш — залежно від того, що потрібно виміряти;
кілька запусків для зменшення впливу випадкових коливань.
Зміна random_page_cost може вплинути на багато запитів одночасно. Якщо один запит почав використовувати бажаний індекс, це ще не доводить, що нове глобальне значення краще для всієї системи.
Запустіть запит через EXPLAIN (ANALYZE, BUFFERS).
Знайдіть вузол із найбільшою різницею між rows і actual rows.
Перевірте, чи актуальна статистика:
ANALYZE schema_name.table_name;Перегляньте дані в pg_stats.
Перевірте, чи немає нерівномірного розподілу значень.
Якщо умови залежать одна від одної, розгляньте розширену статистику.
Якщо проблема пов’язана з виразом, перевірте відповідний індекс і форму умови.
Лише після цього тестуйте параметри вартості.
Мета — не змусити PostgreSQL використовувати конкретний тип індексу, а надати планувальнику точнішу інформацію для вибору.
cost часом у мілісекундахcost — це умовна оцінка, а не прогноз часу виконання в мілісекундах. Її використовують для порівняння планів.
Якщо умова повертає значну частину таблиці, послідовне сканування може бути дешевшим. Планувальник не зобов’язаний використовувати індекс.
EXPLAIN ANALYZE для зміни даних без транзакціїEXPLAIN ANALYZE виконує запит. Для модифікацій використовуйте транзакцію та ROLLBACK, якщо потрібно лише перевірити план.
default_statistics_target для всіх таблицьЦе може збільшити витрати на ANALYZE і планування. Часто краще налаштувати статистичну ціль лише для конкретних стовпців.
random_page_cost навманняТаке налаштування може покращити один запит і погіршити багато інших. Спочатку потрібно порівняти плани та фактичний час виконання.
rows і actual rowsВелика різниця часто є основною підказкою про проблему зі статистикою або припущеннями планувальника.
PostgreSQL вибирає план із найменшою оціненою вартістю.
cost складається з витрат запуску та повної вартості й не є часом у мілісекундах.
На оцінку впливають витрати читання сторінок, процесорні витрати, кеш і паралельне виконання.
Статистика ANALYZE описує кількість рядків, найчастіші значення, гістограми та кореляцію.
Застаріла статистика, нерівномірний розподіл і залежність між стовпцями часто спричиняють неточні оцінки.
Для пов’язаних стовпців можна використовувати розширену статистику.
Параметри вартості слід змінювати обережно та перевіряти на реальних планах.
Найважливіше під час діагностики — порівняти rows з actual rows у EXPLAIN (ANALYZE).