Пошук уроків, статей та іншого контенту
Проаналізуйте операції Sort, GroupAggregate і HashAggregate та їхній вплив на пам’ять і час.
Запит із ORDER BY або GROUP BY не обов’язково виконується одним і тим самим способом. PostgreSQL аналізує статистику, оцінює кількість рядків і вартість операцій, а потім обирає план виконання.
Для цієї теми важливі три вузли плану:
Sort — сортує рядки;
GroupAggregate — обчислює агрегати над уже відсортованими групами;
HashAggregate — формує групи в хеш-таблиці без попереднього сортування.
Подивитися обраний план можна за допомогою:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...EXPLAIN показує план, який PostgreSQL планує використати, а EXPLAIN ANALYZE додатково виконує запит і показує фактичні значення.
Не запускайте
EXPLAIN ANALYZEдля запитів, які змінюють дані, без розуміння наслідків: такий запит справді виконується.
Sort впорядковує рядки перед поверненням результату або перед передачею їх іншому вузлу плану.
Наприклад:
SELECT customer_id, amount
FROM orders
ORDER BY amount DESC;Можливий фрагмент плану:
Sort
Sort Key: amount DESC
Sort Method: quicksort Memory: 1930kB
-> Seq Scan on ordersУ плані можна побачити:
Sort Key — стовпці та напрямок сортування;
Sort Method — алгоритм або спосіб виконання;
Memory — обсяг пам’яті, використаний для сортування;
Disk — обсяг тимчасових даних на диску, якщо сортування не помістилося в пам’ять.
Основні варіанти Sort Method:
quicksort — звичайне сортування в пам’яті;
top-N heapsort — оптимізація для запитів із LIMIT;
external merge — сортування з використанням тимчасових файлів на диску.
Наприклад, для такого запиту PostgreSQL може застосувати сортування лише частини даних:
SELECT customer_id, amount
FROM orders
ORDER BY amount DESC
LIMIT 10;Якщо потрібно повернути тільки перші рядки, повне сортування всіх результатів часто не потрібне.
Пам’ять для окремих операцій сортування обмежується параметром work_mem.
SHOW work_mem;Якщо результат не вміщується в доступну пам’ять, PostgreSQL створює тимчасові файли та виконує зовнішнє сортування. Це зазвичай повільніше через додаткові операції введення-виведення.
Порівняйте два можливі фрагменти плану:
Sort Method: quicksort Memory: 8192kBі:
Sort Method: external merge Disk: 24576kBДругий варіант означає, що частина роботи виконувалася на диску.
Збільшення work_mem може прискорити сортування, але параметр застосовується не до всього запиту загалом. Кілька операцій або паралельних процесів можуть одночасно використовувати власний обсяг пам’яті. Тому безконтрольне глобальне збільшення work_mem може призвести до надмірного споживання пам’яті.
GroupAggregate обробляє рядки групами. Для цього рядки мають надходити у порядку ключів групування.
Приклад:
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;Типовий план може мати такий вигляд:
GroupAggregate
Group Key: customer_id
-> Sort
Sort Key: customer_id
-> Seq Scan on ordersСпочатку Sort розташовує рядки з однаковим customer_id поруч. Потім GroupAggregate проходить результат послідовно й обчислює агрегат для кожної групи.
Умовно виконання має такий вигляд:
отримати рядки;
відсортувати їх за ключем групування;
прочитати послідовність рядків;
накопичувати значення поточної групи;
видати результат після переходу до наступної групи.
Для агрегатів на кшталт SUM, COUNT, AVG, MIN і MAX PostgreSQL зберігає проміжний стан поточної групи, а не всі рядки групи.
GroupAggregate може бути ефективним, якщо дані вже впорядковані за ключем групування. Наприклад, порядок може забезпечувати індекс:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);За відповідного плану PostgreSQL може отримати рядки в потрібному порядку й уникнути окремого вузла Sort.
Однак наявність індексу не гарантує його використання. Планувальник порівнює вартість різних варіантів. Для великої частини таблиці послідовне читання може бути дешевшим за сканування індексу з численними зверненнями до таблиці.
HashAggregate групує рядки за допомогою хеш-таблиці.
Для кожного рядка PostgreSQL:
обчислює хеш ключа групування;
знаходить відповідну групу;
оновлює стан агрегатів цієї групи;
створює групу, якщо її ще немає.
Можливий план:
HashAggregate
Group Key: customer_id
-> Seq Scan on ordersПопереднє сортування для такого плану не потрібне.
HashAggregate часто добре працює, коли:
вхідні дані не відсортовані;
кількість груп помірна;
хеш-таблиця поміщається в пам’ять;
результат не має додаткової вимоги щодо порядку.
У такому випадку PostgreSQL може виконати агрегацію за один прохід по вхідних рядках.
Хеш-таблиця має зберігати інформацію про групи та проміжні значення агрегатів. Якщо груп дуже багато або ключі великі, споживання пам’яті зростає.
Коли операція не вміщується в доступну пам’ять, PostgreSQL може розділити дані на batches і обробляти їх частинами. У плані це може бути видно за додатковими полями, наприклад:
Batches: 8 Memory Usage: 4096kB Disk Usage: 32768kBПоява Disk Usage означає, що для агрегації знадобилися тимчасові дані на диску.
Для хешових операцій PostgreSQL також використовує параметр hash_mem_multiplier разом із work_mem у версіях, де цей параметр підтримується. Він визначає допустимий обсяг пам’яті для деяких хешових операцій. Перевірити поточні значення можна так:
SHOW work_mem;
SHOW hash_mem_multiplier;Обидва вузли можуть обчислити один і той самий GROUP BY, але використовують різні ресурси.
потребує відсортованих вхідних даних;
може вимагати окремий Sort;
зручно працює з даними, які вже впорядковані індексом;
часто використовує менше пам’яті для самої агрегації;
може бути природним вибором, якщо результат також потрібно повернути в порядку ключа.
не потребує попереднього сортування;
використовує пам’ять для хеш-таблиці груп;
часто швидкий для неупорядкованого входу;
може створювати batches і тимчасові файли за нестачі пам’яті;
не гарантує порядок рядків у результаті.
Вибір залежить не лише від кількості вхідних рядків. Важливі також:
кількість унікальних груп;
розмір ключа групування;
доступна пам’ять;
наявні індекси;
оцінки планувальника;
вимога до порядку результату.
Наведений приклад створює тимчасову таблицю, заповнює її тестовими даними та показує плани для сортування й агрегації.
DROP TABLE IF EXISTS orders_demo;
CREATE TEMP TABLE orders_demo (
order_id integer,
customer_id integer,
amount numeric(10, 2),
created_at date
);
INSERT INTO orders_demo (order_id, customer_id, amount, created_at)
SELECT
order_id,
((order_id - 1) % 10000) + 1,
round((random() * 1000)::numeric, 2),
DATE '2024-01-01' + ((order_id - 1) % 365)
FROM generate_series(1, 500000) AS order_id;
ANALYZE orders_demo;
-- Перевірка сортування всіх рядків за сумою
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT order_id, customer_id, amount
FROM orders_demo
ORDER BY amount DESC;
-- Агрегація за клієнтами
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders_demo
GROUP BY customer_id;
-- Та сама агрегація з явним сортуванням результату
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders_demo
GROUP BY customer_id
ORDER BY customer_id;Планувальник може вибрати HashAggregate для другого запиту, а для третього — GroupAggregate із сортуванням або інший план. Точний результат залежить від версії PostgreSQL, статистики, параметрів і характеристик середовища.
ORDER BY після GROUP BY має важливе значення. Навіть якщо агрегування виконувалося через HashAggregate, для впорядкування готових груп може знадобитися додатковий Sort.
work_memЗначення параметрів можна тимчасово змінити в межах транзакції:
BEGIN;
SET LOCAL work_mem = '1MB';
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT order_id, customer_id, amount
FROM orders_demo
ORDER BY amount DESC;
ROLLBACK;Після ROLLBACK початкове значення буде відновлено для цієї транзакції.
Для порівняння можна виконати запит із більшим значенням:
BEGIN;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT order_id, customer_id, amount
FROM orders_demo
ORDER BY amount DESC;
ROLLBACK;Порівнюйте:
Execution Time;
Sort Method;
наявність Disk;
Buffers, зокрема тимчасові буфери;
фактичну кількість рядків;
оцінку кількості рядків.
Зміна work_mem не гарантує прискорення. Якщо сортування вже виконується в пам’яті, збільшення ліміту може не дати помітного ефекту.
Розглянемо умовний план:
GroupAggregate (actual time=420.100..470.300 rows=10000 loops=1)
Group Key: customer_id
-> Sort (actual time=410.000..438.000 rows=500000 loops=1)
Sort Key: customer_id
Sort Method: external merge Disk: 18432kB
-> Seq Scan on orders_demoЙого можна прочитати так:
таблиця прочитана послідовно;
500 000 рядків відсортовано за customer_id;
сортування вийшло на диск;
після цього GroupAggregate сформував 10 000 груп;
найбільша потенційна проблема в цьому плані — дискове сортування.
Інший умовний план:
HashAggregate (actual time=300.000..345.000 rows=10000 loops=1)
Group Key: customer_id
Batches: 8 Memory Usage: 4096kB Disk Usage: 12000kB
-> Seq Scan on orders_demoТут немає окремого сортування, але хеш-агрегація не помістилася в пам’ять і використовувала диск.
Важливо оцінювати не окрему назву вузла, а весь ланцюжок:
чи читається надто багато рядків;
чи є дорогий Sort;
чи використовується диск;
чи відповідають оцінки фактичним значенням;
який вузол витрачає найбільше часу.
Планувальник обирає між GroupAggregate і HashAggregate на основі оцінок, зокрема очікуваної кількості груп. Якщо статистика застаріла, PostgreSQL може неправильно оцінити розмір результату.
Після значних змін у таблиці корисно оновити статистику:
ANALYZE orders_demo;Без актуальної статистики планувальник може:
недооцінити кількість груп;
обрати хеш-агрегацію для результату, який не поміститься в пам’ять;
вибрати сортування, коли інший спосіб був би дешевшим;
неправильно оцінити вартість читання таблиці.
ORDER BY із гарантованим порядкомHashAggregate не гарантує порядок груп. Так само порядок результату без ORDER BY не є контрактом запиту.
Якщо потрібен конкретний порядок, його потрібно вказати явно:
SELECT customer_id, SUM(amount) AS total_amount
FROM orders_demo
GROUP BY customer_id
ORDER BY customer_id;HashAggregate завжди швидшимХеш-агрегація може бути швидкою, поки хеш-таблиця поміщається в пам’ять. Велика кількість груп може призвести до batches і дискового введення-виведення.
work_mem глобально без вимірюваньwork_mem застосовується до окремих вузлів і може використовуватися одночасно кількома операціями та сесіями. Велике значення для всіх підключень може спричинити нестачу пам’яті.
Спочатку варто підтвердити проблему через EXPLAIN (ANALYZE, BUFFERS), а потім протестувати локальну зміну параметра.
Disk у планіDisk у Sort або HashAggregate не завжди означає катастрофічну проблему, але це сигнал перевірити операцію. Потрібно оцінити її частоту, тривалість і вплив на навантаження.
Індекс може допомогти отримати дані у потрібному порядку, але PostgreSQL може вибрати послідовне читання та сортування, якщо такий план має нижчу оцінену вартість.
ANALYZEОцінки планувальника (rows, cost) — це прогноз. Для вимірювання фактичної поведінки використовуйте:
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)Sort упорядковує рядки та може працювати в пам’яті або з тимчасовими файлами на диску.
GroupAggregate очікує відсортований вхід і обробляє групи послідовно.
HashAggregate створює хеш-таблицю груп і зазвичай не потребує попереднього сортування.
work_mem впливає на сортування та інші операції, але виділяється для окремих операцій, а не для запиту загалом.
Велика кількість груп може зробити HashAggregate пам’яттєво витратним.
Дискове сортування або batches у хеш-агрегації вказують на використання тимчасових даних.
Вибір між GroupAggregate і HashAggregate залежить від статистики, кількості груп, порядку даних, індексів і доступної пам’яті.
Для аналізу потрібно дивитися на весь план і фактичні значення EXPLAIN (ANALYZE, BUFFERS), а не лише на назву окремого вузла.