EXPLAIN для PHP-розробника: як читати план запиту в PostgreSQL

Найчастіша реакція на повільний запит — «додати індекс на колонку з WHERE». Іноді спрацьовує, частіше ні: індекс створено, а Postgres його не бере, або бере й швидше не стало. Розібратися можна тільки одним способом — подивитись, що планувальник насправді робить із запитом. Нижче — мінімум, якого вистачає, щоб читати плани щодня.

Як зняти план

Дві команди. EXPLAIN показує намір планувальника і нічого не виконує. EXPLAIN ANALYZE виконує запит і додає фактичні цифри — саме вона потрібна в 95% випадків.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id, title, published_at
FROM articles
WHERE status = 'published' AND published_at > '2026-01-01'
ORDER BY published_at DESC
LIMIT 20;

BUFFERS показує, скільки сторінок прочитано з shared buffers і скільки з диска — у PostgreSQL 18 він увімкнений разом з ANALYZE за замовчуванням, у попередніх версіях його треба вказувати руками. SETTINGS виводить нестандартні GUC-параметри, які вплинули на план: колеги потім не питатимуть, чому у вас інший результат.

ANALYZE реально виконує запит, тож для UPDATE/DELETE загортайте у транзакцію:

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM sessions WHERE last_activity < 1735689600;
ROLLBACK;

З Laravel беріть готовий SQL із підставленими значеннями і несіть у psql:

$sql = Article::query()
    ->where('status', 'published')
    ->where('published_at', '>', now()->subYear())
    ->orderByDesc('published_at')
    ->limit(20)
    ->toRawSql();          // Laravel 10.15+

У білдера є ще ->explain(), але він виконує голий EXPLAIN без ANALYZE — тобто без фактичних чисел, а це половина цінності. Використовуйте його хіба що для швидкої перевірки, який індекс обраний.

Один нюанс, який пояснює «в psql швидко, з застосунку повільно»: Laravel шле prepared statements. Після кількох виконань Postgres може перейти на generic plan — план, побудований без знання конкретних значень параметрів. Якщо дані сильно перекошені, generic plan буває помітно гіршим. Перевірити гіпотезу можна через SET plan_cache_mode = force_generic_plan; перед EXPLAIN з PREPARE/EXECUTE.

Seq Scan, Index Scan і Bitmap: три способи дістати рядки

Дерево плану читається знизу вгору і зсередини назовні: найглибший вузол виконується першим, кожен батьківський споживає рядки дітей. Рядок вузла виглядає так:

Index Scan using articles_status_published_at_idx on articles
  (cost=0.42..18.71 rows=20 width=48)
  (actual time=0.031..0.094 rows=20 loops=1)

cost — умовні одиниці, а не мілісекунди: перше число — вартість до першого рядка, друге — до останнього. rows і width — оцінка кількості рядків і середнього розміру рядка в байтах. У дужках з actual — те саме, але виміряне, плюс loops: скільки разів вузол виконався. Важливо: actual time і rows подані на одну ітерацію, тож реальна вартість вузла — це actual time × loops. Саме тут найчастіше губиться справжній винуватець.

Три способи дістати рядки з таблиці:

Seq Scan — послідовне читання всієї таблиці. Це не діагноз. Для таблиці на кілька тисяч рядків або для запиту, який повертає більшу частину таблиці, послідовне читання дешевше за випадкові звернення до heap через індекс. Погано, коли поруч стоїть Rows Removed by Filter з великим числом: прочитали мільйон, віддали десять.

Index Scan — прохід по індексу з походом у heap за кожним знайденим рядком. Оптимальний, коли рядків мало і вони потрібні у порядку індексу (наприклад, разом із ORDER BY і LIMIT).

Bitmap Heap Scan над Bitmap Index Scan — компроміс для «середньої» кількості рядків. Спочатку будується бітова мапа потрібних сторінок, потім heap читається один раз у фізичному порядку. Побічний ефект: втрачається порядок індексу, тому нагорі часто з'являється Sort.

Bitmap Heap Scan on articles  (actual rows=4210 loops=1)
  Recheck Cond: (category_id = 3)
  Heap Blocks: exact=812 lossy=0
  ->  Bitmap Index Scan on articles_category_id_idx  (actual rows=4210 loops=1)
        Index Cond: (category_id = 3)

lossy більше нуля означає, що мапа не влізла у work_mem і Postgres запам'ятав сторінки цілком замість окремих рядків — звідси Recheck Cond на кожному рядку сторінки. Bitmap-вузли також вміють об'єднуватись через BitmapAnd/BitmapOr: це той випадок, коли два окремі однколонкові індекси працюють разом.

Різниця між Index Cond і Filter — ключова. Index Cond звужує пошук усередині індексу. Filter застосовується вже до прочитаних рядків. Умова, яка потрапила у Filter замість Index Cond, індексом не прискорена.

Оцінка проти факту

Планувальник обирає план за оцінками. Якщо оцінка бреше, план буде поганим, навіть коли всі потрібні індекси на місці. Тому перше, що шукаєш у виводі EXPLAIN ANALYZE, — вузол, де rows= (оцінка) і actual rows= розходяться на порядок і більше. Далі це розходження множиться вгору по дереву: недооцінка на нижньому вузлі перетворює нормальний Nested Loop на мільйон ітерацій.

Типові причини:

• Застаріла статистика. ANALYZE articles; вручну, і подивитись last_autoanalyze у pg_stat_user_tables. Після масового імпорту чи великої міграції автовакуум ще не встиг добігти.
• Замало кошиків у гістограмі. default_statistics_target за замовчуванням 100. Для колонки з нерівномірним розподілом підняти точково: ALTER TABLE articles ALTER COLUMN status SET STATISTICS 500; і потім ANALYZE.
• Корельовані колонки. Postgres за замовчуванням вважає умови незалежними: селективність WHERE city = ? AND country = ? він порахує як добуток, хоча місто однозначно визначає країну. Лікується розширеною статистикою:

CREATE STATISTICS articles_cat_status (dependencies, ndistinct)
  ON category_id, status FROM articles;
ANALYZE articles;

• Вирази. WHERE lower(email) = ? не має статистики взагалі, поки ви не створите індекс за виразом — тоді Postgres збиратиме статистику й по ньому.

Ще одна річ, на яку варто дивитись, — Sort Method. quicksort Memory: 25kB — добре. external merge Disk: 84MB — сортування пішло на диск, бо не влізло у work_mem. Це не привід глобально піднімати work_mem: він виділяється на кожен сортувальний вузол кожного бекенда, тож множиться на конкурентність.

Складені індекси і порядок колонок

Складений індекс — це відсортований список кортежів. Звідси єдине правило, з якого випливає решта: індекс корисний зліва направо. (a, b, c) обслуговує умови по a, по a, b, по a, b, c. Для умови лише по b він у загальному випадку не годиться — Postgres або візьме інший індекс, або зробить повний прохід по цьому. (У PostgreSQL 18 з'явився skip scan, який частково рятує ситуацію, коли у ведучої колонки дуже мало різних значень; розраховувати на це як на стратегію не варто.)

Практичний порядок побудови для типового запиту «фільтр + діапазон + сортування»:

1. Спочатку колонки з рівністю (=, IN).
2. Потім одна колонка з діапазоном (>, <, BETWEEN).
3. Потім колонки з ORDER BY.

Колонки після діапазонної вже не звужують пошук — діапазон «розмазує» позицію в індексі, далі йде фільтрація.

Для запиту з початку статті правильний індекс:

CREATE INDEX articles_status_published_at_idx
  ON articles (status, published_at DESC);

Напрямок DESC тут здебільшого косметика: B-tree Postgres сканується в обидва боки, і ORDER BY published_at DESC дасть Index Scan Backward. Напрямок починає важити тільки для змішаного сортування на кшталт ORDER BY status ASC, published_at DESC.

Якщо статус має два-три значення і 90% рядків — published, тримати його в індексі зайве. Кращий варіант — частковий індекс:

CREATE INDEX articles_published_at_idx
  ON articles (published_at DESC)
  WHERE status = 'published';

Він менший, тримається в пам'яті і не оновлюється для чернеток. Умова в запиті має збігатися з умовою індексу дослівно, інакше планувальник його не застосує.

І окремо про операційну частину: CREATE INDEX блокує запис у таблицю. У production завжди CREATE INDEX CONCURRENTLY, а в Laravel-міграції — з вимкненою транзакцією, бо CONCURRENTLY всередині транзакції працювати не буде:

final class AddArticlesPublishedIndex extends Migration
{
    public $withinTransaction = false;

    public function up(): void
    {
        DB::statement(
            'CREATE INDEX CONCURRENTLY IF NOT EXISTS articles_published_at_idx
             ON articles (published_at DESC) WHERE status = \'published\''
        );
    }
}

Покривні індекси та Index Only Scan

Index Scan завжди йде в heap за рештою колонок. Якщо всі потрібні запиту колонки є в самому індексі, Postgres обирає Index Only Scan і heap не чіпає взагалі.

CREATE INDEX articles_feed_idx
  ON articles (status, published_at DESC)
  INCLUDE (id, title, slug);

INCLUDE (з PostgreSQL 11) додає колонки лише в листові сторінки: вони доступні для видачі, але не можуть використовуватись як умови пошуку. Тобто в INCLUDE кладуть те, що йде в SELECT, а в основний список — те, що йде у WHERE і ORDER BY.

У плані з'явиться рядок, за яким усе й перевіряється:

Index Only Scan using articles_feed_idx on articles
  Heap Fetches: 0

Heap Fetches: 0 — індекс справді покриває запит. Якщо число велике, Index Only Scan лише називається «only»: Postgres мусить ходити в heap, щоб перевірити видимість рядків, бо відповідні сторінки не позначені у visibility map. Причина майже завжди одна — таблиця активно змінюється, а автовакуум за нею не встигає. Ліками є налаштування autovacuum для конкретної таблиці, а не переписування запиту.

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

Типові плани з Laravel-запитів

whereHas компілюється в WHERE EXISTS (SELECT ...). У плані це Nested Loop Semi Join або Hash Semi Join. Semi Join — нормально: Postgres зупиняється на першому збігу. Погано, коли внутрішній бік — Seq Scan з великим loops: бракує індексу на зовнішньому ключі. Laravel не створює індекси для FK автоматично — foreignId()->constrained() дає обмеження, а індекс треба додавати самому.

paginate() — це два запити. Перший, select count(*), часто дорожчий за другий, бо мусить обійти всі рядки, що підпадають під умову. Другий страждає від OFFSET: Postgres усе одно читає й відкидає всі пропущені рядки, тож у плані бачите Limit над вузлом з actual rows, що дорівнює offset + limit. Для глибокої пагінації використовуйте cursorPaginate() — він генерує keyset-пагінацію (WHERE (published_at, id) < (?, ?)), яка лягає прямо на складений індекс. Якщо точна кількість сторінок не потрібна — simplePaginate() прибирає count(*) зовсім.

withCount() додає корелований підзапит у SELECT. У плані це SubPlan, який виконується для кожного рядка сторінки. Для 15 рядків це прийнятно, якщо всередині є індекс; для експорту в кілька тисяч рядків — ні, там краще окремий агрегатний запит із GROUP BY і JOIN.

whereDate('published_at', $date) у PostgresGrammar перетворюється на published_at::date = ?. Це вираз, і звичайний індекс на published_at для нього не працює — умова опиниться у Filter. Замініть на діапазон:

Article::whereBetween('published_at', [
    $date->copy()->startOfDay(),
    $date->copy()->endOfDay(),
])->get();

Пошук по підрядку. where('title', 'like', 'laravel%') може використати B-tree, але в базі з не-C collation для цього потрібен клас операторів text_pattern_ops. where('title', 'ilike', '%laravel%') індексом за префіксом не прискорити взагалі — тут потрібен GIN на триграмах:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm_idx ON articles USING gin (title gin_trgm_ops);

У плані з'явиться Bitmap Index Scan по GIN-індексу замість Seq Scan із фільтром.

JSON-колонки. where('meta->locale', 'uk') компілюється у meta->>'locale' = ?. Індексувати треба саме вираз: CREATE INDEX ON articles ((meta->>'locale')); — або GIN по всьому JSONB, якщо умови різноманітні.

Чекліст

Коли перед вами незнайомий повільний запит:

1. Зніміть EXPLAIN (ANALYZE, BUFFERS) з реальними значеннями параметрів.
2. Знайдіть вузол з найбільшим actual time × loops — це і є ціль, решта дерева зазвичай шум.
3. Порівняйте rows і actual rows на цьому вузлі. Розходження на порядок — спершу лікуйте статистику, а не додавайте індекси.
4. Подивіться на Rows Removed by Filter. Велике число означає умову, яка не потрапила в Index Cond.
5. Перевірте Sort Method і Heap Fetches — обидва вказують на проблеми, які не вирішуються переписуванням SQL.
6. Тільки після цього проєктуйте індекс: рівність → діапазон → сортування, INCLUDE для колонок з SELECT, CONCURRENTLY при створенні.