Каков основной эффект добавления индекса по столбцу?

Как работает. Без индекса база читает всю таблицу (sequential scan) и проверяет каждую строку. Индекс хранит значения колонки в отсортированном виде со ссылками на строки; поиск значения — спуск по B-дереву за несколько чтений страниц. На таблице в 100 млн строк разница — миллисекунды против минут.

Что ускоряет: точный поиск и диапазоны в WHERE, соединения по ключу, сортировку (если порядок совпадает с индексом), MIN/MAX, проверку уникальности.

Цена:

• каждая вставка, изменение индексированной колонки и удаление обновляют все индексы таблицы — запись медленнее, больше WAL;
• место на диске и в памяти (индексы конкурируют с данными за кэш);
• лишние индексы мешают HOT-обновлениям в PostgreSQL и замедляют VACUUM.

Когда индекс не поможет: запрос выбирает большую долю таблицы (дешевле прочитать всё подряд), функция от колонки без функционального индекса, LIKE '%abc', несоответствие типов, низкая селективность (колонка пола).

Практика: создавать индексы под реальные запросы, проверять по EXPLAIN, удалять неиспользуемые (pg_stat_user_indexes), на больших таблицах — CREATE INDEX CONCURRENTLY.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..180000.00 rows=12)
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders(customer_id);
-- Index Scan using orders_customer_idx (cost=0.43..50.12 rows=12)