Middle: питання на співбесіді з теми «Індекси»

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

WHERE lower(email) = 'ivan@x.com'        -- індекс на email не використається
WHERE DATE(created_at) = '2026-10-04'    -- те саме
WHERE name LIKE '%олег%'                 -- невідомий початок рядка

Як виправити:

• Переписати умову, щоб колонка лишалася «голою»:

WHERE created_at >= '2026-10-04' AND created_at < '2026-10-05'

• Індекс за виразом (PostgreSQL; функціональний індекс у MySQL 8.0.13+):

CREATE INDEX users_email_lower_idx ON users (lower(email));

• LIKE 'олег%' (відомий початок) працює з B-деревом. У PostgreSQL з не-C локаллю для цього потрібен індекс з text_pattern_ops.
• LIKE '%олег%' - потрібен інший тип індексу: триграмний (pg_trgm + GIN у PostgreSQL) або повнотекстовий пошук (tsvector, FULLTEXT у MySQL), або окремий пошуковий рушій - Meilisearch, Elasticsearch.

Ще одна пастка - неявне приведення типів: порівняння рядкової колонки з числом (WHERE phone = 380501234567) змушує MySQL перетворювати кожне значення колонки, і індекс не працює.

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

Звичайно база знаходить у індексі посилання на рядок, а потім іде в саму таблицю по решту колонок. Якщо всі колонки, потрібні запиту, вже є в індексі, звертатися до таблиці не треба - це index-only scan, а індекс називають покривним.

-- Запит
SELECT status, total FROM orders WHERE user_id = 5;

-- Покривний індекс: user_id для пошуку, status і total - щоб не йти в таблицю
CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (status, total);

INCLUDE (PostgreSQL 11+) додає колонки лише в листки індексу: за ними не шукають, зате їх можна прочитати. У MySQL INCLUDE немає - колонки просто додають у кінець складеного індексу.

Нюанси:

• PostgreSQL через MVCC мусить перевірити, чи рядок видимий транзакції. Index-only scan справді не читає таблицю лише для сторінок, позначених у visibility map як «усі рядки видимі». Її оновлює VACUUM, тож на таблиці з частими змінами без вакууму виграш менший. У EXPLAIN ANALYZE це видно як Heap Fetches.
• InnoDB усі вторинні індекси вже містять первинний ключ, тож SELECT id ... WHERE email = ? за індексом на email - покривний автоматично.
• Широкий покривний індекс - це копія частини таблиці: більше місця, повільніший запис. Його роблять під конкретний частий запит, а не «про всяк випадок».

Докладніше в документації: Index-only scan і покривні індекси

Планувальник обирає план з найменшою оцінною вартістю. Seq Scan часто справді дешевший, ніж здається.

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

• Умова не відсіює більшість рядків. Якщо запит поверне 30% таблиці, послідовне читання сторінок підряд дешевше, ніж тисячі випадкових переходів індекс → таблиця. Так задумано.
• Маленька таблиця. Кілька сторінок простіше прочитати повністю.
• Застаріла статистика. Після масового імпорту чи видалення планувальник думає, що в таблиці 100 рядків, а їх мільйон. Рішення - ANALYZE orders (autovacuum робить це сам, але із запізненням).
• Умова не підходить до індексу: функція над колонкою (lower(email)), неявне приведення типів, LIKE '%текст', умова на другу колонку складеного індексу без першої.
• Параметризований запит з generic-планом, оптимальним «в середньому», але не для конкретного значення.
• Налаштування вартості: random_page_cost = 4 за замовчуванням розраховане на HDD. На SSD його зазвичай знижують до 1.1-1.5, і індекси стають «дешевшими» для планувальника.

Як розібратися:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Порівняти rows (оцінку) з actual rows. Якщо вони розходяться в рази - проблема в статистиці. Для перевірки гіпотези в сесії можна тимчасово заборонити послідовне читання: SET enable_seqscan = off; - і подивитися, наскільки «індексний» план насправді швидший. На проді так не залишають.

Нерівномірні дані: для колонок з перекосом (99% status = 'done') допомагає збільшити деталізацію статистики: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; і повторний ANALYZE.

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

Обмеження UNIQUE - логічне правило схеми. PostgreSQL реалізує його унікальним індексом, який створює автоматично. Тому для простого випадку різниці в поведінці немає:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
-- майже те саме, що:
CREATE UNIQUE INDEX users_email_unique ON users (email);

Коли потрібен саме індекс:

• Частковий: унікальність серед частини рядків - CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL. Обмеження з WHERE не буває.
• За виразом: унікальність без урахування регістру - ON users (lower(email)).
• CONCURRENTLY: створити без блокування запису на великій таблиці.

Коли потрібне саме обмеження:

• Зовнішні ключі посилаються на PRIMARY KEY чи UNIQUE-обмеження (або унікальний індекс без умови).
• Відкладена перевірка DEFERRABLE INITIALLY DEFERRED - перевіряти унікальність у кінці транзакції, а не після кожного рядка (наприклад, при перестановці позицій).
• ON CONFLICT ON CONSTRAINT за іменем.

NULL і унікальність. За стандартом SQL NULL не дорівнює NULL, тож у колонці з UNIQUE може бути скільки завгодно рядків з NULL. Часто це і потрібно (необов'язковий email). Але інколи - ні: «у користувача може бути лише одна адреса без типу».

NULLS NOT DISTINCT (PostgreSQL 15+) змінює правило - NULL вважаються однаковими:

CREATE UNIQUE INDEX one_default_address ON addresses (user_id, type) NULLS NOT DISTINCT;

Тепер для одного user_id дозволено лише один рядок з type IS NULL.

Докладніше в документації: Унікальні індекси

Роздування (bloat) - індекс займає значно більше місця, ніж потрібно для живих даних. Причина - MVCC: UPDATE і DELETE залишають мертві версії рядків, а записи індексу, що на них вказували, звільняються вакуумом не завжди повністю. Сторінки індексу стають напівпорожніми.

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

Особливо схильні:

• індекси на колонках, які часто змінюються;
• таблиці-черги, де рядки постійно вставляють і видаляють;
• індекси на монотонно зростаючих значеннях з видаленням старих рядків (ліва частина дерева порожніє).

Як оцінити: розширення pgstattuple (pgstatindex('orders_created_idx') показує щільність листків) або запити-оцінки з PostgreSQL Wiki. Простий сигнал - індекс помітно більший за свіжостворений аналог.

Як виправити без простою:

REINDEX INDEX CONCURRENTLY orders_created_idx;   -- PostgreSQL 12+
REINDEX TABLE CONCURRENTLY orders;

CONCURRENTLY будує новий індекс поруч і підміняє старий, не блокуючи запис. Звичайний REINDEX блокує запис у таблицю на весь час перебудови.

Підводні камені: як і з CREATE INDEX CONCURRENTLY, команду не можна виконати в транзакції, а при збої може лишитися недійсний індекс (_ccnew), який треба видалити вручну.

Профілактика:

• Налаштований autovacuum для таблиць з частими змінами.
• fillfactor менше 100 для таблиць з частими оновленнями - щоб нові версії рядків вміщалися на ту саму сторінку (HOT-оновлення не чіпають індекси взагалі).
• Не індексувати колонки, які часто змінюються, без потреби.

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

B-tree зберігає значення відсортованими. Тому запит «перші N за порядком» може взяти рядки прямо з індексу й зупинитися, не сортуючи всю таблицю:

CREATE INDEX posts_published_idx ON posts (published_at);

SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;
-- Index Scan Backward: 20 рядків з кінця індексу, без сортування

B-tree можна читати в обох напрямках, тож для однієї колонки DESC в індексі не обов'язковий.

Коли напрямок у індексі важливий - змішане сортування кількох колонок:

SELECT * FROM products ORDER BY category_id ASC, price DESC LIMIT 50;

CREATE INDEX products_cat_price_idx ON products (category_id ASC, price DESC);

Індекс (category_id, price) для такого запиту не підійде: обхід у будь-якому напрямку дасть price в тому ж напрямку, що й category_id.

NULLS FIRST / NULLS LAST. За замовчуванням у PostgreSQL NULL більші за всі значення: при ASC вони в кінці, при DESC - на початку. Якщо запит пише ORDER BY published_at DESC NULLS LAST, індекс має збігатися:

CREATE INDEX posts_pub_idx ON posts (published_at DESC NULLS LAST);

Інакше планувальник змушений сортувати.

Разом з фільтром: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10 найкраще обслуговує індекс (user_id, created_at): спершу рівність, потім колонка сортування.

Як перевірити: у EXPLAIN не має бути вузла Sort над великою кількістю рядків - лише Index Scan з Limit. Саме на цьому тримається keyset-пагінація.

Докладніше в документації: Індекси й ORDER BY