$table->index('status') у Laravel і #[ORM\Index(columns: ['status'])] у Doctrine дають одне й те саме: btree. Schema Builder не вміє ні WHERE, ні INCLUDE, ні вказати клас операторів, тому все цікаве доводиться писати через DB::statement() або addSql(). Нижче зібрано те, що варто туди писати; усе на PostgreSQL 18.
Коли B-tree недостатньо
Btree добре закриває =, діапазони, IN, ORDER BY і префіксний LIKE 'abc%'. Останнє з умовою: у локалі, відмінній від C, префіксний пошук працює лише якщо індекс створено з класом операторів text_pattern_ops (або стовпець має COLLATE "C"). Інакше PostgreSQL не може скористатися порядком індексу для порівняння підрядків і піде сканувати таблицю.
Чотири ситуації, де btree або не допоможе, або допоможе дорого:
- пошук за підрядком (
ILIKE '%київ%'), повнотекстовий пошук, входження в масив абоjsonb @> '{"role":"admin"}': тут працюють інші оператори, яких btree не підтримує; - запити завжди читають вузький зріз таблиці (невиконані джоби, неархівовані замовлення), а індекс при цьому тримає всі 50 мільйонів рядків;
- запит бере дві-три колонки, і хочеться відповіді без походу в heap;
- таблиця подій на сотні гігабайтів, де btree по
created_atсам займає десятки гігабайтів.
На кожну є окремий інструмент.
Частинні індекси
Найчастіший випадок: таблиця з soft deletes. Якщо 90% рядків мають deleted_at IS NOT NULL, індекс по (company_id) тримає історію, яку ніхто не читає:
CREATE INDEX CONCURRENTLY vacancies_company_active_idx
ON vacancies (company_id, published_at DESC)
WHERE deleted_at IS NULL;
Трейт SoftDeletes додає deleted_at is null до кожного запиту сам, тому умова індексу зійдеться без правок у коді. Друге застосування, якого звичайним індексом не досягти: унікальність лише серед живих рядків.
CREATE UNIQUE INDEX CONCURRENTLY vacancies_slug_active_uniq
ON vacancies (slug) WHERE deleted_at IS NULL;
З боку Laravel валідація має бути такою самою: Rule::unique('vacancies', 'slug')->whereNull('deleted_at').
Тепер пастка, через яку частинні індекси іноді «не працюють». Планувальник використає такий індекс лише тоді, коли зможе довести, що умова запиту логічно містить у собі умову індексу. З літералом це тривіально, з параметром prepared statement уже ні. Запит WHERE status = $1 не доводить WHERE status = 'queued', тому за генеричного плану індекс буде проігнорований. PDO готує і знищує statement на кожен виклик, тож зазвичай ви отримуєте custom plan з реальними значеннями і все працює. Але щойно з'являється кешування prepared statements (persistent-з'єднання з повторним використанням того самого запиту, PgBouncer із max_prepared_statements), PostgreSQL після п'ятого виконання може перейти на генеричний план і втратити індекс. Перевірити здогадку просто: SET plan_cache_mode = force_generic_plan; і той самий EXPLAIN.
Друга деталь: CREATE INDEX CONCURRENTLY не виконується всередині транзакції, а Laravel за замовчуванням обертає міграцію в транзакцію на PostgreSQL. Потрібно явно відмовитись:
return new class extends Migration
{
public $withinTransaction = false;
public function up(): void
{
DB::statement('CREATE INDEX CONCURRENTLY ...');
}
};
У Doctrine Migrations те саме робить public function isTransactional(): bool { return false; }.
Покривні індекси й INCLUDE
Про index-only scan говорять тоді, коли всі потрібні колонки лежать в індексі й heap читати не треба. До PostgreSQL 11 для цього колонки доводилось тягнути в ключ індексу, роздуваючи його внутрішні сторінки. INCLUDE кладе їх тільки в листові:
CREATE INDEX CONCURRENTLY applications_vacancy_cover_idx
ON applications (vacancy_id, created_at DESC)
INCLUDE (developer_id, status);
Що про INCLUDE треба знати до того, як ви його додасте:
Колонки з INCLUDE не беруть участі ні в пошуку, ні в сортуванні. Це виключно payload. Умова WHERE status = 'new' за цим індексом не стане index condition, вона перевірятиметься після читання. Вирази там заборонені з тієї ж причини.
Index-only scan залежить від visibility map. Якщо сторінка не позначена як «усі версії видимі», PostgreSQL однаково піде в heap. Тому після масового апдейту свіжозбудований покривний індекс дає Heap Fetches у тисячах, і виглядає це як «індекс не допоміг». Лікується VACUUM (звичайним, не FULL), а на такі таблиці варто подивитись на autovacuum_vacuum_scale_factor окремо.
Кожна колонка в індексі, включно з INCLUDE, ламає HOT-апдейт. Якщо ви покрили status, то кожна зміна статусу тепер породжує новий запис в індексі. Для таблиці, де статус змінюється по кілька разів на рядок, це погана угода.
Окремий корисний випадок: унікальний індекс із payload. Унікальність перевіряється лише по ключових колонках, тож CREATE UNIQUE INDEX ... (email) INCLUDE (id, name) залишається унікальним по email і при цьому закриває запит логіну без heap.
Індекси за виразом
Запит WHERE lower(email) = ? не скористається індексом по email. Потрібен індекс по тому самому виразу, символ у символ:
CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));
Вираз має бути IMMUTABLE. Найчастіше на цьому спотикаються з датами: CREATE INDEX ON orders ((created_at::date)) для стовпця timestamptz відвалиться з functions in index expression must be marked IMMUTABLE, бо результат залежить від поточного TimeZone. Виручає зона, зафіксована літералом:
CREATE INDEX CONCURRENTLY orders_created_day_idx
ON orders (((created_at AT TIME ZONE 'Europe/Kyiv')::date));
І запит має містити рівно той самий вираз, інакше індекс не підійде. Часто простіше індексувати сам created_at і писати діапазон created_at >= ? AND created_at < ?: тоді індекс звичайний, а планувальник має нормальну оцінку селективності.
Для jsonb btree по виразу теж працює, якщо ви знаєте конкретний ключ:
CREATE INDEX CONCURRENTLY events_user_idx ON events (((payload ->> 'user_id')::bigint));
Приведення типу обов'язково повторити в запиті. У Eloquent це whereRaw("(payload ->> 'user_id')::bigint = ?", [$id]), бо where('payload->user_id', $id) згенерує порівняння тексту і промахнеться.
Побічний бонус індексів за виразом: ANALYZE збирає статистику по виразу, не лише по стовпцю. Без індексу планувальник оцінює lower(email) = ? як фіксовану частку рядків і будує плани наосліп.
GIN: jsonb, масиви, пошук за підрядком
Для jsonb без фіксованого набору ключів потрібен GIN. Клас операторів за замовчуванням, jsonb_ops, індексує і ключі, і значення, та підтримує ?, ?|, ?&, @>, @?, @@. Якщо з усього цього вам потрібне лише входження @>, беріть jsonb_path_ops: він індексує хеші шляхів разом зі значеннями, виходить відчутно менший і точніший, але перестає відповідати на «чи є такий ключ».
CREATE INDEX CONCURRENTLY events_payload_idx
ON events USING gin (payload jsonb_path_ops);
ILIKE '%...%' закриває pg_trgm:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY companies_name_trgm_idx
ON companies USING gin (name gin_trgm_ops);
Триграмний індекс працює з трьох символів: шаблон %ab% нічого в ньому не знайде і запит піде в seq scan. Для автокомпліту з двох літер це треба врахувати на рівні UI.
Повнотекстовий пошук найзручніше тримати в генерованому стовпці. Тут теж потрібна immutable-функція, тобто дволанкова форма to_tsvector(regconfig, text) з явною конфігурацією; однойменна функція з одним аргументом залежить від default_text_search_config і в генерованому стовпці не приймається. Вбудованої конфігурації для української немає, тому це або simple (плюс unaccent), або підключений ispell-словник:
ALTER TABLE articles ADD COLUMN search tsvector
GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || body)) STORED;
CREATE INDEX CONCURRENTLY articles_search_idx ON articles USING gin (search);
Ціна GIN платиться на записі. За замовчуванням fastupdate = on: нові записи складаються в pending list (до gin_pending_list_limit, типово 4 МБ), а потім вливаються в індекс. Вставки від цього швидкі, але запит, якому не пощастило, доплачує за послідовне читання всього pending list. Якщо у вас жорсткі вимоги до латентності читань, fastupdate = off робить затримки рівнішими за рахунок повільніших вставок.
Як знайти індекси, які ніхто не використовує
Зайвий індекс гальмує кожен INSERT і UPDATE, займає місце в кеші, збільшує час VACUUM і на додачу їсть диск.
SELECT s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
s.last_idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
AND NOT i.indisprimary
AND s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;
last_idx_scan доступний з PostgreSQL 16 і відповідає на головне питання: нуль сканувань означає «ніколи не треба» чи «статистику скинули минулого тижня». Дату скидання видно в pg_stat_database.stats_reset.
Чотири речі, які варто перевірити до DROP:
На репліках своя статистика. Індекс, непотрібний на primary, може обслуговувати всю аналітику на read-only репліці. Запитайте там окремо.
Квартальний звіт теж запит. idx_scan = 4 за рік ще не привід видаляти індекс, без якого звіт ітиме годину.
Індекси за унікальними обмеженнями й зовнішніми ключами фільтр вище відкидає свідомо. Дублікати шукайте інакше: індекс, чий список колонок є префіксом іншого індексу тієї ж таблиці, майже завжди зайвий.
Рядки з pg_index.indisvalid = false залишає по собі обірваний CREATE INDEX CONCURRENTLY. Вони не використовуються в запитах, але оновлюються при записі. Їх треба видаляти, а індекс будувати заново.
Перевірити, чи запит справді зламається, можна без видалення: DDL у PostgreSQL транзакційний, тому BEGIN; DROP INDEX ...; EXPLAIN <запит>; ROLLBACK; показує план без індексу. На проді так робити не варто: DROP INDEX бере ACCESS EXCLUSIVE на таблицю до кінця транзакції. Для реального видалення є DROP INDEX CONCURRENTLY.
BRIN для великих таблиць із часом
BRIN не зберігає посилань на рядки. Він тримає зведення (для minmax це мінімум і максимум) на кожен діапазон блоків, типово 128 сторінок по 8 КБ, тобто 1 МБ heap на один запис індексу. Для таблиці на 100 ГБ це близько 102 тисяч записів, тобто індекс розміром у кілька мегабайтів замість гігабайтів у btree.
Працює це лише за фізичної впорядкованості даних. Перевірка одним запитом:
SELECT attname, correlation FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
Значення біля 1 або -1 означає, що BRIN має сенс. Приблизно 0 означає, що індекс читатиме всю таблицю. Для append-only логів, подій і таблиць з uuidv7() як ключем (функція є в PostgreSQL 18) кореляція висока природним чином. Для uuidv4() або для таблиці, яку активно перемішують апдейтами, BRIN безглуздий.
CREATE INDEX events_created_brin_idx ON events
USING brin (created_at) WITH (pages_per_range = 32, autosummarize = on);
Менший pages_per_range дає точніше зведення і більший індекс. autosummarize виключений за замовчуванням: без нього нові сторінки залишаються незведеними до наступного VACUUM, і запити по найсвіжіших даних (а це зазвичай найчастіші запити) індексом не скористаються. Або ввімкніть його, або викликайте brin_summarize_new_values('events_created_brin_idx') за розкладом.
Якщо дані майже впорядковані, але трапляються викиди (дані надійшли із запізненням, бекфіл старого періоду), один такий рядок розтягує min/max діапазону і робить його безкорисним. На це є timestamptz_minmax_multi_ops: клас операторів тримає кілька інтервалів на діапазон замість одного. Для рівномірно розкиданих значень, за якими потрібна лише рівність, є bloom-опклас.
Чого BRIN не вміє: index-only scan, сортування, видачу рядків у порядку індексу. Він завжди lossy, тобто дає Bitmap Heap Scan із перепровіркою умови. Якщо запиту потрібен ORDER BY created_at DESC LIMIT 50, BRIN не замінить btree. А ось у парі «btree по id для точкових запитів плюс BRIN по created_at для діапазонів за місяць» вони живуть добре, і друга половина цієї пари коштує майже нічого.