Что хотят услышать интервьюеры:

Индексы нужны там, где таблица часто читается по конкретным условиям, а полное сканирование становится дорогим. При этом учитывают не только ускорение SELECT, но и цену на INSERT, UPDATE, DELETE, размер данных и тип запросов. Важно понимать, что индекс полезен не всегда: если данных мало или фильтрация невыборочная, PostgreSQL может выбрать последовательное сканирование.

Определение:

Индекс в PostgreSQL — это дополнительная структура данных, которая помогает быстрее находить строки по значениям одного или нескольких столбцов. Необходимость его создания определяется профилем запросов: какие поля участвуют в WHERE, JOIN, ORDER BY, GROUP BY, насколько часто выполняются запросы и насколько выборочны условия фильтрации.
При проектировании индексов учитывают ограничения: индекс занимает место на диске, замедляет операции изменения данных, требует обслуживания, а также не всегда используется планировщиком, если он считает полный проход таблицы дешевле. Дополнительно важно учитывать тип данных, кардинальность столбца и порядок колонок в составном индексе.

Пример использования:

Например, в таблице заказов часто ищут заказы по customer_id и дате создания. В этом случае может быть полезен составной индекс.

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at);

Такой индекс ускорит запросы вида:

SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= '2024-01-01';

Пояснение кода:

Индекс создается по двум колонкам: сначала customer_id, затем created_at. Это важно, потому что порядок столбцов в составном индексе влияет на то, какие запросы он сможет эффективно обслуживать.
Если запрос фильтрует по customer_id, индекс обычно помогает. Если же фильтрация идет только по created_at, такой индекс может быть менее полезен, потому что ведущая колонка не используется.
В результате PostgreSQL сможет быстрее находить нужные строки без полного просмотра всей таблицы.

Ключевые моменты:

Индекс создают под реальные частые запросы, а не “на всякий случай”.
Основной критерий — высокая стоимость полного сканирования таблицы.
Важны селективность условий, кардинальность колонок и тип запросов.
Индексы ускоряют чтение, но замедляют запись и занимают место.
Составной индекс зависит от порядка колонок.
Планировщик PostgreSQL может не использовать индекс, если последовательное сканирование выгоднее.

Что определяет необходимость создания индексов в PostgreSQL, и какие ограничения при этом учитываются?