Виконання й оптимізація запитів

Навіщо це

Аналітик просить замовлення, дорожчі за середнє свого покупця. Запит із модуля 3 читається як умова задачі: підзапит рахує середнє для кожного замовлення. На medium (220 тисяч замовлень, розміри описано на сторінці Датасет) він іде 2.3 с. Переписаний на JOIN із заздалегідь згрупованою таблицею, він повертає ті самі 63 228 рядків за 0.16 с. Дані й індекси ті самі, різниця в плані.

SQL каже, що треба отримати, а не як. Способів кілька, і між ними вибирає планувальник запитів: оцінює кожен числом і виконує найдешевший. Коли оцінка хибна, запит повільний, хоча в SQL помилки немає.

Передумови. Схема й запити «Крамниці»: модулі 2 і 3. Сторінки й buffer pool: модуль 7. Будову індексів і вибір, який створити, розбирає модуль 8; тут лише те, як планувальник вирішує, чи скористатися ним. PostgreSQL 18.6 у Docker; час залежить від заліза, форма плану ні.

План запиту

Замовлення покупця можна знайти, прочитавши всю таблицю або пішовши за індексом: результат однаковий, час різний. Вибір робить планувальник, що будує план виконання: дерево вузлів, де кожен вузол робить одну дію (читає таблицю, з’єднує дві, сортує, групує). Виконавець обходить дерево: кожен вузол віддає батьківському рядок, коли той попросить.

План показує EXPLAIN, а EXPLAIN ANALYZE ще й виконує запит і додає виміряні числа.

Як планувальник рахує вартість

Вартість вузла виміряно в умовних одиницях, де одиниця дорівнює сторінці, прочитаній у послідовній серії (seq_page_cost = 1). Решта параметрів відраховується від неї:

Параметр	За замовчуванням	Що оцінює
random_page_cost	4	сторінка, прочитана не підряд
cpu_tuple_cost	0.01	обробка одного рядка
cpu_operator_cost	0.0025	один виклик оператора чи функції, наприклад порівняння у фільтрі

Найпростіший вузол, Seq Scan, читає всю таблицю підряд. Його вартість можна порахувати вручну: сторінки плюс рядки, кожен з яких проходить обробку й фільтр. На medium:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
SELECT relpages, reltuples::int,
       relpages + reltuples * 0.01 + reltuples * 0.0025 AS seq_scan_cost
FROM pg_class WHERE relname = 'orders';

 Seq Scan on orders  (cost=0.00..5396.60 rows=10 width=101)
   Filter: (customer_id = 42)

 relpages | reltuples | seq_scan_cost
----------+-----------+---------------
     2640 |    220528 |        5396.6

Два числа 0.00..5396.60 означають вартість до першого рядка й до останнього. У Sort перше майже дорівнює другому: поки вхід не прочитано весь, нічого віддавати.

Четвірка в random_page_cost прийшла з дисків, що обертаються, де випадкове читання було на два порядки дорожчим за послідовне; про це модуль 14 курсу «Операційні системи». На SSD такої різниці немає, тож параметр там зазвичай знижують, але добре значення залежить і від диска, і від обсягу пам’яті. На medium з random_page_cost = 1.1 планувальник узяв Index Scan для половини таблиці, і він пішов 38.5 мс проти 12.6 у Seq Scan, бо дані цілком у кеші. Параметр підбирають вимірюванням.

Способи читати таблицю

На medium у orders лише первинний ключ, тож додамо два індекси:

CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_placed_idx ON orders (placed_at);
ANALYZE orders;

Seq Scan читає всю таблицю підряд. Index Scan за кожним записом індексу лізе в таблицю. Bitmap Heap Scan спершу збирає з індексу адреси в бітову карту сторінок, а потім читає їх у фізичному порядку, кожну один раз.

Який спосіб вибере планувальник, залежить від вибірковості (selectivity): частки рядків, що проходять умову. Запит WHERE customer_id <= N, medium:

N	Рядків (оцінка / факт)	Частка таблиці	План	Час
2	10 / 1	0.0005 %	Index Scan	0.02 мс
3000	17 453 / 17 312	8 %	Bitmap Heap Scan	4.1 мс
20000	109 734 / 109 917	50 %	Seq Scan	12.6 мс

Рядки orders лежать у файлі приблизно за датою, а не за покупцем: кореляція customer_id з фізичним порядком у pg_stats дорівнює 0.22. Сусідні записи індексу ведуть на довільні сторінки. Тому Index Scan на 110 тисяч рядків зробив 109 559 звернень до буфера при 2640 сторінках таблиці.

Bitmap Heap Scan бере кожну сторінку один раз, тому виграє в Index Scan, а згодом програє Seq Scan, коли читати доводиться майже все.

placed_at має кореляцію 0.9999983: сусідні записи індексу ведуть на сусідні сторінки. Тому діапазон дат лишається на Index Scan і для 100 днів (22 285 рядків, 2.3 мс), і для 500 (137 435 рядків, 62 % таблиці, 13.2 мс). Seq Scan виграє лише на 600 днях (75 %). Отже, правило «індекс не допоможе при 10 % рядків» хибне: поріг залежить від кореляції, розміру рядка й кешу.

У пісочниці small, тож пороги там інші:

СпробуйPostgreSQLCtrl+Enter — виконати

EXPLAIN SELECT * FROM orders WHERE customer_id <= 50

ВиконатиСкинути запитСкинути базу

Index Only Scan бере значення з індексу, не заходячи в таблицю. Для цього мало, щоб індекс містив усі колонки запиту (модуль 8): індекс не знає, чи видима версія рядка вашому знімку (модуль 10). Це знає карта видимості: біт на сторінку, що всі рядки на ній видимі всім. Для сторінок без біта вузол іде в таблицю, що видно в Heap Fetches.

Спочатку запит, потім оновлення 2 % рядків і VACUUM (medium):

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE customer_id BETWEEN 100 AND 200;
UPDATE orders SET updated_at = updated_at WHERE order_id % 50 = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE customer_id BETWEEN 100 AND 200;

VACUUM orders;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE customer_id BETWEEN 100 AND 200;

Вузол лишається Index Only Scan, а Heap Fetches стає 0 (0.089 мс). Після оновлення їх 638 (1.349 мс, у 15 разів повільніше): оновлені рядки розкидані по всій таблиці, і біти зникли майже скрізь. Після VACUUM знову 0 (0.053 мс). Коли autovacuum не встигає, Index Only Scan тихо стає Index Scan.

JOIN: три алгоритми

Три алгоритми `JOIN`. Вибір залежить від кількості рядків на вході, наявності індексу всередині й потреби в порядку на виході.

Nested Loop (вкладений цикл) для кожного рядка зовнішнього входу шукає пару у внутрішньому. Якщо всередині індекс, вартість дорівнює кількості зовнішніх рядків, помноженій на вартість одного пошуку. На medium замовлення п’ятьох покупців: внутрішній вузол виконано п’ять разів, по 6.6 рядка, разом 33.

EXPLAIN (ANALYZE) SELECT c.customer_id, o.order_id, o.total_amount
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id BETWEEN 3750 AND 3754;

 Nested Loop  (cost=0.58..242.31 rows=22 width=21) (actual time=0.024..0.056 rows=33.00 loops=1)
   ->  Index Only Scan using customers_pkey on customers c  (actual rows=5.00 loops=1)
   ->  Index Scan using orders_customer_idx on orders o  (actual time=0.003..0.008 rows=6.60 loops=5)
 Execution Time: 0.064 ms

Лише він працює з довільною умовою, не тільки з рівністю.

Hash Join будує хеш-таблицю з меншого входу, а другий вхід проходить один раз і шукає в ній пари. Індекси й порядок не потрібні, але таблиця має вміститися в пам’ять. Усі позиції з усіма замовленнями на medium:

EXPLAIN (ANALYZE) SELECT o.order_id, i.product_id
FROM orders o JOIN order_items i ON i.order_id = o.order_id;

 Hash Join  (cost=8514.88..21201.60 rows=396570 width=12) (actual time=31.677..118.488 rows=396570.00 loops=1)
   ->  Hash  (actual time=31.024..31.025 rows=220528.00 loops=1)
         Buckets: 262144  Batches: 2  Memory Usage: 6366kB

Batches: 2 і temp written означають, що хеш-таблиця не вмістилася. Її ліміт дорівнює work_mem (4 МБ) × hash_mem_multiplier (2), а потрібно близько 10.6 МБ. Виконавець розклав входи на дві порції за хешем ключа й з’єднав по одній. З work_mem = '64MB' той самий план має Batches: 1 і йде близько 100 мс.

Merge Join проходить два впорядковані входи одночасно, як дві відсортовані колоди. Вхід беруть з індексу або сортують, тож алгоритм виграє, коли порядок уже є або потрібен на виході.

Додамо до попереднього запиту ORDER BY o.order_id (medium). Обидва входи приходять із первинних ключів (Index Scan using orders_pkey, Index Only Scan using order_items_pkey) уже впорядкованими, і вузол Merge Join виконується за 88.6 мс. Якщо заборонити алгоритм (SET enable_mergejoin = off, лише для демонстрації), планувальник бере Hash Join і ставить зверху Sort, що скидає на диск 10 МБ: 209 мс.

Планувальник помиляється й у бік «розумнішого» алгоритму. На medium замовлення покупців із customer_id <= 3000 (17 312 рядків) він з’єднав із позиціями хешем за 97.6 мс. Примусовий Nested Loop упорався за 31.7 мс: вартість пошуку виходить із random_page_cost = 4, а сторінки лежать у кеші.

Сортування й агрегація

На medium. Sort працює в пам’яті, поки вхід вміщається в work_mem, інакше скидає відсортовані порції на диск і зливає їх. SELECT * FROM order_items ORDER BY unit_price дає Sort Method: external merge Disk: 14952kB і 115 мс. З work_mem = '64MB' метод стає quicksort Memory: 30878kB, вартість у плані падає з 53 273 до 43 785, а час не зменшується (144 мс): тимчасові файли лежали в кеші ОС.

HashAggregate тримає хеш-таблицю груп і читає вхід один раз. GroupAggregate вимагає входу, впорядкованого за ключем групування. Групування позицій за product_id (30 016 груп) іде HashAggregate за 44.5 мс. Із забороненим хешем планувальник ставить Sort (4.6 МБ скинуто на диск) перед GroupAggregate: 88 мс. За order_id впорядкований вхід дає сам первинний ключ, і GroupAggregate працює без сортування за 63 мс.

HashAggregate теж скидає дані на диск. Подій за session_id планувальник очікував 138 461 групу, а їх 215 010, і вийшло Batches: 5 Disk Usage: 11872kB.

Статистика і чому планувальник помиляється

Оцінку rows планувальник бере зі статистики, яку збирає ANALYZE. Вона лежить у pg_stats: частка NULL, n_distinct, найчастіші значення з частотами й гістограма решти. Вивід нижче з medium, у пісочниці числа менші.

СпробуйPostgreSQLCtrl+Enter — виконати

SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'orders' AND attname = 'status'

ВиконатиСкинути запитСкинути базу

 n_distinct |                 most_common_vals                 |                     most_common_freqs
------------+--------------------------------------------------+-----------------------------------------------------------
          5 | {delivered,cancelled,returned,shipped,confirmed} | {0.868,0.095233336,0.028533334,0.0065666665,0.0016666667}

У даних шість статусів, а n_distinct = 5: восьми рядків new у вибірку не потрапило. ANALYZE читає не всю таблицю, а 300 × default_statistics_target рядків: 30 000 із 220 528. Для customer_id така вибірка дала оцінку 20 740 різних значень проти справжніх 31 517, звідси й хибна кількість груп вище. Після ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000 оцінка точна, але ANALYZE триває довше.

Більші помилки дають залежні колонки: планувальник множить вибірковості, ніби вони незалежні. Продавці спеціалізуються, тож пара «продавець і категорія» трапляється частіше за добуток (medium):

EXPLAIN (ANALYZE) SELECT * FROM products WHERE seller_id = 118 AND category_id = 100;
CREATE STATISTICS products_seller_category (dependencies, mcv) ON seller_id, category_id FROM products;
ANALYZE products;
EXPLAIN (ANALYZE) SELECT * FROM products WHERE seller_id = 118 AND category_id = 100;

 Seq Scan on products  (cost=0.00..3285.00 rows=39 width=572) (actual time=0.345..6.522 rows=407.00 loops=1)
 Seq Scan on products  (cost=0.00..3285.00 rows=400 width=571) (actual time=0.383..5.414 rows=407.00 loops=1)

Оцінка 39 проти 407 після CREATE STATISTICS стала 400. У JOIN такі помилки множаться.

dependencies шукає функціональну залежність, коли одна колонка визначає іншу. Тут вона 0.0015, тобто залежності немає, і допомогла mcv, що зберігає найчастіші пари цілком. dependencies застосовується лише до умов рівності, а mcv працює й з діапазонами.

Пара «статус і дата» в orders теж корельована (shipped лише в останні тижні). Умову status = 'shipped' AND placed_at < '2025-06-01' оцінено в 943 рядки при нулі фактичних, а CREATE STATISTICS не допоміг (1 024): placed_at майже в кожному рядку своє, і спільних пар, які mcv міг би запам’ятати, немає.

Паралельні плани і JIT

Для великої таблиці планувальник додає Gather: провідний процес запускає робітників (workers), кожен читає свою частину, результати збираються нагорі. На medium групування подій view_events (53 МБ) за типом з WHERE occurred_at >= '2025-06-01' має Gather Merge, Workers Planned: 2, Workers Launched: 2 і Parallel Seq Scan з loops=3: провідний процес і двоє робітників, rows=67778.67 — середнє на одного. Запит іде 21.3 мс проти 38.7 без паралельності. Таблиці менші за min_parallel_table_scan_size (8 МБ) паралельно не скануються, а запуск робітників коштує parallel_setup_cost (1000).

JIT-компіляція перекомпільовує вирази запиту в машинний код, коли вартість плану перевищує jit_above_cost (100 000). Корельований підзапит із початку модуля коштує понад 10 мільйонів (medium). JIT витратив 166 мс на компіляцію без виграшу: із jit = off запит іде 2141 мс проти 2276.

Що відбувається до плану

Як план з’являється? Запит проходить чотири кроки. Парсер перевіряє синтаксис і з’ясовує за каталогом, що таке orders. Переписувач замінює представлення на їхні запити. Планувальник будує дерево вузлів і вибирає найдешевше. Виконавець його обходить.

Переписувач видно на представленні: воно зникає ще до планувальника, а його умова зливається із зовнішньою. Перший рядок вимикає паралельні плани.

SET max_parallel_workers_per_gather = 0;
CREATE VIEW delivered_orders AS SELECT * FROM orders WHERE status = 'delivered';
EXPLAIN SELECT order_id FROM delivered_orders WHERE customer_id = 3750;
DROP VIEW delivered_orders;

 Seq Scan on orders  (cost=0.00..5947.92 rows=9 width=8)
   Filter: ((status = 'delivered'::text) AND (customer_id = 3750))

Логічний порядок із модуля 3 лише описує результат, а фізично планувальник переставляє кроки: умову опускає до таблиці, як і в модулі 2. Корельований EXISTS стає semi-join (у плані Hash Right Semi Join).

Як це насправді

Читати EXPLAIN (ANALYZE, BUFFERS)

Повний план запиту «скільки замовлень у покупців кожного населеного пункту, топ-5» на medium. EXPLAIN ANALYZE запит виконує, тож UPDATE чи DELETE змінять дані.

Той самий план деревом. Виконавець тягне рядки згори, дані рухаються вгору; числа на стрілках — actual rows з виводу нижче.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.settlement_id, count(*) AS orders
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.settlement_id ORDER BY orders DESC LIMIT 5;

 Limit  (cost=8134.48..8134.50 rows=5 width=12) (actual time=56.594..56.597 rows=5.00 loops=1)
   ->  Sort  (cost=8134.48..8134.83 rows=139 width=12) (actual time=56.593..56.595 rows=5.00 loops=1)
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=8130.79..8132.18 rows=139 width=12) (actual time=56.565..56.575 rows=140.00 loops=1)
               ->  Hash Join  (cost=2037.30..7028.15 rows=220528 width=4) (actual time=11.703..41.966 rows=220528.00 loops=1)
                     ->  Index Only Scan using orders_customer_idx on orders o  (actual rows=220528.00 loops=1)
                           Buffers: shared hit=276
                     ->  Hash  (actual time=11.324..11.325 rows=50000.00 loops=1)
                           ->  Seq Scan on customers c  (actual rows=50000.00 loops=1)
                                 Buffers: shared hit=912
 Execution Time: 56.752 ms

План читають знизу вгору: листки, потім вузли, що їх споживають, нагорі результат; відступ показує підпорядкованість. Hash під Hash Join будується першим. Листки взяли 276 + 912 = 1188 сторінок з buffer pool (модуль 7). shared read означав би читання з диска, temp read і written — скидання на диск.

Починають з порівняння rows в дужках з actual rows: тут 139 проти 140. У вузла з loops > 1 actual rows і actual time середні на один цикл: rows=6.60 loops=5 дає 33 рядки, час множать на loops. Хибна в тисячу разів оцінка — уже діагноз. Далі дивляться, де накопичився час, скільки Rows Removed by Filter (стільки рядків прочитано даремно) і чи є temp.

Підзапити з модуля 3

Корельований підзапит із початку модуля і його переписана версія (medium):

EXPLAIN (ANALYZE) SELECT order_id FROM orders o
WHERE total_amount > (SELECT avg(x.total_amount) FROM orders x WHERE x.customer_id = o.customer_id);
EXPLAIN (ANALYZE) SELECT o.order_id FROM orders o
JOIN (SELECT customer_id, avg(total_amount) AS a FROM orders GROUP BY customer_id) g ON g.customer_id = o.customer_id
WHERE o.total_amount > g.a;

У першому плані Seq Scan має Filter: (total_amount > (SubPlan 1)), а SubPlan 1 виконано loops=220528 разів, по разу на рядок: 4.6 мільйона звернень до буфера. Другий будує хеш-таблицю середніх.

NOT IN лишається фільтром із хешованим підзапитом, а NOT EXISTS стає anti-join (medium):

EXPLAIN (ANALYZE) SELECT count(*) FROM categories WHERE category_id NOT IN (SELECT category_id FROM products);
EXPLAIN (ANALYZE) SELECT count(*) FROM categories c WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id);

 Seq Scan on categories  (actual time=9.960..9.960 rows=0.00 loops=1)
   Filter: (NOT (ANY (category_id = (hashed SubPlan 1).col1)))
 Hash Right Anti Join  (actual time=5.000..5.004 rows=38.00 loops=1)

Перший запит повертає 0 через NULL у products.category_id, другий знаходить 38 категорій. Коли підзапит не вміщається в work_mem, лишається SubPlan із Materialize, що перечитує список для кожного рядка: 110 мс проти 10 на 105 категоріях. Навіть коли обидві колонки NOT NULL, PostgreSQL 18.6 не перетворює NOT IN на JOIN.

СпробуйPostgreSQLCtrl+Enter — виконати

EXPLAIN SELECT count(*) FROM categories WHERE category_id NOT IN (SELECT category_id FROM products)

ВиконатиСкинути запитСкинути базу

Типові причини повільного запиту

Усе на medium з двома індексами orders.

Функція над індексованою колонкою. Індекс упорядкований за колонкою, а не за функцією від неї. Умова (placed_at AT TIME ZONE 'Europe/Kyiv')::date = DATE '2025-11-28' із модуля 3 дає Seq Scan, Rows Removed by Filter: 219187, 2691 буфер і 42 мс. Діапазон placed_at >= TIMESTAMP '2025-11-28' AT TIME ZONE 'Europe/Kyiv' AND placed_at < TIMESTAMP '2025-11-29' AT TIME ZONE 'Europe/Kyiv' іде Index Only Scan: 8 буферів, 0.18 мс. Без діапазону допоможе індекс за виразом (модуль 8).

Неявне приведення типів. WHERE customer_id = 3750.0 порівнює bigint із numeric. База приводить колонку, у плані стає Filter: ((customer_id)::numeric = 3750.0), а замість Index Scan за 0.04 мс виходить Seq Scan за 11 мс. Так буває, коли драйвер шле параметр іншого типу.

OR по різних колонках. customer_id = 3750 OR promo_code = 'NY20' без індексу по promo_code читає всю таблицю (8.2 мс). З індексом це BitmapOr із двох Bitmap Index Scan за 1.1 мс.

LIMIT із сортуванням. Без індексу ORDER BY total_amount DESC LIMIT 10 читає всю таблицю заради десяти рядків (23 мс). Підступніше WHERE status = 'shipped' ORDER BY order_id LIMIT 10. Планувальник бачить індекс по order_id, думає, що shipped розкидані рівномірно, і оцінює кілька сторінок (вартість 63.71). Але shipped лише в останніх замовленнях: Index Scan відкинув 212 653 рядки, зробив 11 537 звернень до буфера і знайшов десятий за 16.8 мс. З індексом (status, order_id) це 13 буферів і 0.04 мс.

Застаріла статистика і промах у тисячі разів. Після масового завантаження, доки не виконано ANALYZE, умову по двох колонках оцінюють типові константи (autovacuum вимкнено для демонстрації):

CREATE TABLE orders_fresh WITH (autovacuum_enabled = false) AS SELECT * FROM orders;
ALTER TABLE orders_fresh ADD PRIMARY KEY (order_id);
EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_id, i.product_id
FROM orders_fresh o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'delivered' AND o.promo_code IS NULL;
ANALYZE orders_fresh;

 Nested Loop  (cost=0.42..4269.61 rows=8 width=12) (actual time=0.034..156.432 rows=285853.00 loops=1)
   Buffers: shared hit=639310 read=3
   ->  Seq Scan on orders_fresh o  (cost=0.00..4233.60 rows=3 width=8) (actual rows=158930.00 loops=1)
   ->  Index Scan using order_items_pkey on order_items i  (actual rows=1.80 loops=158930)

Оцінка 3 рядки проти 158 930: планувальник вирішив, що зовнішній вхід крихітний, і вибрав Nested Loop на 158 930 пошуків. Після ANALYZE виходить Hash Join із 5632 буферами замість 639 310 і 101 мс замість 162: дані в кеші, тож час змінився менше за буфери. Після великого завантаження виконують ANALYZE вручну.

Знайти запит, який варто оптимізувати

Важить сумарний час, а не найдовший запит. Розширення pg_stat_statements накопичує виклики й час по кожному нормалізованому запиту. Навантаження pgbench за 15 секунд на medium: 95 % пошуків за покупцем, 5 % добових звітів із функцією над placed_at.

SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 2;

 calls | total_ms | mean_ms | query
-------+----------+---------+--------------------------------------------------------------
  2010 |    55017 |  27.372 | SELECT count(*) FROM orders WHERE (placed_at AT TIME ZONE $1
 41284 |      487 |   0.012 | SELECT count(*), sum(total_amount) FROM orders WHERE custome

Звіти становлять менше 5 % викликів і 99 % часу; константи нормалізовано до $1. Розширення потребує shared_preload_libraries і CREATE EXTENSION, у пісочниці його немає. Далі беруть запит із верху й читають його план.

Типові помилки розуміння

«Seq Scan у плані — це погано». Для великої частки таблиці він найдешевший: на medium планувальник перейшов на нього при 50 % рядків по customer_id.

«Планувальник завжди знаходить найкращий план». Він знаходить найдешевший за своєю оцінкою, а оцінка спирається на вибіркову статистику й припущення про незалежність колонок. Nested Loop на 158 930 пошуків вибрано за хибною оцінкою.

«Вартість у плані — це мілісекунди». Це умовні одиниці, зіставні лише в межах одних налаштувань; час дає actual time, і лише ANALYZE його вимірює.

«Створив індекс, і запит його використає». Планувальник бере індекс, лише коли той дешевший. Функція над колонкою, неявне приведення, невибіркова умова чи застаріла статистика роблять його марним.

Перевір себе

1. Діапазон по `placed_at` на 62 % таблиці йде `Index Scan`, а `customer_id <= N` із тією самою часткою рядків — `Seq Scan`. Чому?

Стовпець `placed_at` має менше унікальних значеньУ `placed_at` висока кореляція з фізичним порядком, сусідні записи індексу ведуть на сусідні сторінкиПланувальник завжди вибирає індекс для датІндекс по `customer_id` створено неправильно

2. У `Nested Loop` внутрішній `Index Scan` має `actual time=0.004..0.010 rows=6.60 loops=5000`. Скільки рядків він повернув і скільки часу витратив?

6.6 рядка і 0.01 мс5000 рядків і 0.004 мс33 000 рядків і близько 50 мс: значення середні на один цикл33 000 рядків і 0.01 мс

3. Запит із `NOT IN (SELECT ...)` іде секундами й дедалі довше зі зростанням таблиці. У плані `SubPlan 1` із `Materialize`, а не `hashed SubPlan`. Що сталося?

Потрібен `VACUUM FULL` таблиціПотрібно збільшити `seq_page_cost`Запит правильний, треба почекатиПідзапит не вміщається в `work_mem`, хешувати його не можна; `NOT EXISTS` дасть анти-з’єднання

4. Після завантаження 200 тисяч рядків запит іде повільно: у плані `rows=3`, `actual rows=158930`, `Nested Loop`, `loops=158930`. Що найімовірніше?

Відсутня статистика: виконати `ANALYZE` на таблиціЗамало `max_connections`Треба зменшити `random_page_cost` до 1Треба вимкнути `Nested Loop` для всієї бази

5. `WHERE seller_id = 118 AND category_id = 100` оцінено в 39 рядків при 407 фактичних. Яка розширена статистика виправить це для цієї пари?

`dependencies`: одна колонка визначає іншу`mcv`: він зберігає найчастіші пари значень; `dependencies` тут лише 0.0015, залежності немаєЖодна: `CREATE STATISTICS` не працює для двох колонок`ndistinct`: він виправляє оцінки умов рівності

Лабораторна

L4. Прискорити повільні запити: шість повільних запитів до «Крамниці» на medium: прискорити індексами й переписуванням так, щоб кількість прочитаних буферів упала щонайменше в 25 разів, не перевищивши бюджету на розмір індексів.

Джерела

• PostgreSQL 18, документація: Using EXPLAIN, Statistics Used by the Planner, Parallel Query, Planner Cost Constants, pg_stat_statements, JIT.
• PostgreSQL 18, Release Notes.
• H. Suzuki, The Internals of PostgreSQL, розділ 3 «Query Processing».
• M. Winand, SQL Performance Explained і use-the-index-luke.com.