Індекс - це окрема структура даних поруч з таблицею, найчастіше B-tree, у якій значення колонки лежать відсортованими, а поряд з кожним значенням зберігається посилання на рядок. У PostgreSQL це фізична адреса рядка в heap (ctid); вторинний індекс InnoDB зберігає значення первинного ключа, за яким потім іде другий пошук у кластерному індексі. Завдяки сортуванню база спускається деревом за кілька десятків порівнянь замість того, щоб читати всі сторінки таблиці. Ось і весь механізм: замість перегляду п'яти мільйонів рядків база робить кілька звернень до сторінок індексу.
Друга половина питання впирається в селективність, тобто в те, яку частку таблиці відсікає умова. Пошук WHERE user_id = 42 у таблиці замовлень поверне десятки рядків з мільйонів, і тут індекс дає виграш у сотні разів. А WHERE status = 'paid', коли цей статус мають 94% замовлень, через індекс виконається повільніше за повний скан: на кожен знайдений запис індексу потрібне окреме випадкове звернення до сторінки таблиці, а послідовне читання диска набагато дешевше. Планувальник рахує це сам на основі зібраної статистики, тому й показує Seq Scan там, де недосвідчений розробник чекав Index Scan. Груба орієнтація: коли запит повертає більше десятої частини таблиці, індекс перестає окупатись.
Друге застосування, про яке junior зазвичай забуває, - сортування. Записи в індексі вже впорядковані, тож ORDER BY created_at DESC LIMIT 20 за наявності відповідного індексу читає рівно двадцять рядків і зупиняється. Без індексу база прочитає всю таблицю й відсортує мільйони рядків заради двадцяти: у плані PostgreSQL з'явиться вузол Sort, іноді з external merge Disk, а в MySQL Using filesort. На сторінці зі стрічкою останніх записів це різниця між 5 мс і кількома секундами.
Платить за все це запис. Кожен INSERT додає запис у кожен індекс таблиці, DELETE позначає їх мертвими, UPDATE індексованої колонки переписує відповідні гілки дерева. Десять індексів на таблиці, куди активно пишуть, дають десятикратну роботу на кожну вставку плюс місце на диску й у кеші. У PostgreSQL є ще один нюанс: оновлення колонки, яка не входить у жоден індекс, може пройти як HOT-update і взагалі не чіпати індекси, а зайвий індекс саме на цій колонці цю оптимізацію вбиває. Тому індекси створюють під конкретні повільні запити, а не «про всяк випадок», і періодично перевіряють pg_stat_user_indexes.idx_scan чи sys.schema_unused_indexes, щоб прибрати ті, які ніхто жодного разу не використав.
Навіть при чудовій селективності індекс ламає форма самої умови. WHERE DATE(created_at) = '2026-01-15', WHERE lower(email) = ?, порівняння bigint-колонки з рядком або LIKE '%шукане%' змушують базу обчислювати вираз для кожного рядка, і звичайний індекс тут марний. Виходів кілька: переписати умову на діапазон (created_at >= '2026-01-15' AND created_at < '2026-01-16'), створити індекс по виразу або взяти інший тип індексу, наприклад GIN з pg_trgm для пошуку підрядка.
-- Таблиця orders: 5 млн рядків, статусів усього три
SELECT count(*), status FROM orders GROUP BY status;
-- 4 700 000 | paid
-- 250 000 | pending
-- 50 000 | refunded
-- 1. Селективний індекс: user_id має тисячі різних значень,
-- умова відсікає десятки рядків з мільйонів
CREATE INDEX orders_user_id ON orders (user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using orders_user_id, кілька мілісекунд
-- 2. Неселективний індекс: 94% рядків мають status = 'paid',
-- планувальник його не візьме навіть після ANALYZE
CREATE INDEX orders_status ON orders (status);
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';
-- Seq Scan on orders: читати таблицю послідовно дешевше
-- 3. Індекс під сортування: LIMIT читає рівно 20 рядків,
-- вузла Sort у плані немає
CREATE INDEX orders_created_at ON orders (created_at DESC);
EXPLAIN ANALYZE
SELECT id, total FROM orders ORDER BY created_at DESC LIMIT 20;
-- 4. Функція над колонкою вимикає звичайний індекс.
-- Тут потрібен індекс по виразу
CREATE INDEX orders_created_date ON orders ((created_at::date));
SELECT * FROM orders WHERE created_at::date = '2026-01-15';
-- 5. На живій таблиці індекс будують без блокування записів
CREATE INDEX CONCURRENTLY orders_email ON orders (email);Назвіть цифру: індекс окупається, коли повертає одиниці відсотків таблиці. І одразу згадайте зворотний бік - місце на диску й уповільнений запис. Junior, який сам каже «індекс має ціну», виглядає сильніше за того, хто знає лише «індекс = швидко».