Junior: питання на співбесіді з теми «Продуктивність запитів»

EXPLAIN показує, як база збирається виконати запит: у якому порядку читати таблиці, чи використовувати індекси, як з'єднувати. Сам запит при цьому не виконується.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Index Scan using orders_user_id_idx on orders  (cost=0.43..8.45 rows=3 width=64)
  Index Cond: (user_id = 5)

Що шукати новачку:

• Seq Scan (PostgreSQL) / type: ALL (MySQL) на великій таблиці - повний перегляд таблиці. Часто ознака відсутнього індексу.
• Index Scan / Index Only Scan (PostgreSQL), type: ref, range (MySQL) - використано індекс.
• rows - скільки рядків база очікує. Якщо оцінка сильно відрізняється від реальності, план може бути поганим.
• Sort з великою кількістю рядків чи Using filesort у MySQL - сортування, яке не взяли з індексу.

cost - умовні одиниці вартості, не мілісекунди. Перше число - вартість до першого рядка, друге - до останнього.

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

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

Докладніше в документації: Використання EXPLAIN

SELECT * повертає всі колонки, навіть ті, що коду не потрібні. На маленьких таблицях різниці немає, але проблеми з'являються з ростом:

• Зайві дані через мережу й у пам'ять. Колонка body TEXT чи payload JSON на сотні кілобайтів, вибрана для списку із 50 заголовків, множить обсяг у рази.
• Не працює покривний індекс. Якщо запиту потрібні лише id і status, які є в індексі, база могла б не звертатися до таблиці. З * мусить.
• Крихкість. Додали колонку - і запит раптом повертає більше даних; у INSERT ... SELECT * чи UNION зміна схеми ламає запит.
• Читабельність. З коду не видно, які дані реально використовуються.

-- Для списку статей
SELECT id, title, published_at FROM posts ORDER BY published_at DESC LIMIT 20;

У Laravel Eloquent за замовчуванням робить select *. Для важких таблиць варто явно обмежувати колонки: Post::select(['id', 'title', 'published_at']), а для зв'язків - with('author:id,name').

Коли * нормальний: разові запити в консолі, EXISTS (SELECT * ...) (там колонки не читаються взагалі), і COUNT(*), який з вибором колонок узагалі не пов'язаний.

Докладніше в документації: Списки вибірки

У PostgreSQL немає збереженого лічильника рядків таблиці. Через MVCC різні транзакції одночасно бачать різну кількість рядків, тож SELECT count(*) FROM orders мусить переглянути рядки (або індекс) і перевірити видимість кожного. На сотнях мільйонів рядків це секунди чи хвилини.

Що можна зробити:

1. Приблизна кількість зі статистики - миттєво:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'public.orders'::regclass;

Значення оновлює ANALYZE/autovacuum. Для лічильника «близько 2,4 млн записів» на сторінці адмінки - цілком достатньо.

2. Оцінка для запиту з умовою - з плану:

EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- rows=183000 у плані - оцінка планувальника

3. Точна кількість швидше: index-only scan по невеликому індексу, якщо visibility map свіжа (після вакууму), - помітно швидше за читання всієї таблиці.

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

Практичні висновки:

• Пагінація з «сторінка 1 з 48 213» - дорога на великих таблицях: кожна сторінка - ще й count(*). Laravel simplePaginate() чи cursorPaginate() обходяться без підрахунку.
• «Чи є хоч один рядок» - не count(*) > 0, а EXISTS (SELECT 1 ... ): зупиняється на першому знайденому.
• count(column) не швидший за count(*) - він ще й перевіряє NULL.

Докладніше в документації: Оцінка кількості рядків

ANALYZE збирає статистику про дані таблиці: кількість рядків, частку NULL, кількість унікальних значень, найчастіші значення, гістограму розподілу. Планувальник використовує її, щоб оцінити, скільки рядків поверне кожна частина запиту, - і обрати план: індекс чи повний перегляд, який вид JOIN, у якому порядку з'єднувати таблиці.

ANALYZE orders;                   -- одна таблиця
ANALYZE orders (status, user_id); -- окремі колонки
ANALYZE;                          -- уся база

ANALYZE читає не всю таблицю, а вибірку рядків, тож виконується швидко навіть на великих таблицях.

Чому після імпорту все повільно: статистика стала застарілою. Таблиця, у якій учора було 1000 рядків, сьогодні має 10 мільйонів, а планувальник досі думає, що їх тисяча, - і обирає план, оптимальний для маленької таблиці (наприклад, Nested Loop там, де потрібен Hash Join).

Autovacuum запускає ANALYZE автоматично, коли змінилася помітна частка таблиці, але не миттєво. Тому після масових операцій - імпорту, видалення великої частини даних, відновлення з дампу, pg_upgrade - ANALYZE варто запустити вручну одразу.

Як помітити проблему: у EXPLAIN ANALYZE оцінка rows сильно розходиться з actual rows. Коли статистика застаріла - розходження величезне; ANALYZE усе виправляє.

Коли статистики недостатньо навіть свіжої:

• Перекошений розподіл значень - збільшити деталізацію: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000.
• Корельовані колонки - розширена статистика (CREATE STATISTICS).

Час останнього аналізу видно в pg_stat_user_tables (last_analyze, last_autoanalyze).

Докладніше в документації: ANALYZE