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

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

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

Составной индекс в PostgreSQL — это индекс, построенный сразу по нескольким колонкам в заданном порядке. Он эффективен, когда запросы используют первый столбец индекса, а также комбинацию первого и второго столбца. Порядок колонок важен: индекс по (a, b) не эквивалентен индексу по (b, a).

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

Допустим, в таблице orders часто ищут заказы конкретного пользователя в рамках статуса.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Хорошо использует индекс
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'PAID';

-- Тоже может использовать индекс, потому что фильтр по левому столбцу есть
SELECT *
FROM orders
WHERE user_id = 42;

-- Обычно плохо использует этот индекс
SELECT *
FROM orders
WHERE status = 'PAID';

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

В первом запросе PostgreSQL может быстро найти строки по user_id, а затем сузить результат по status. Во втором запросе используется только первый столбец индекса, поэтому индекс тоже полезен. В третьем запросе фильтрация идет только по status, а так как это не первый столбец составного индекса, пользы обычно мало.

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

Составной индекс ускоряет запросы, которые используют первый столбец или весь набор столбцов индекса.
Порядок столбцов в индексе критичен для эффективности.
Такой индекс особенно полезен для WHERE, JOIN, иногда ORDER BY.
Индекс занимает место на диске и замедляет INSERT, UPDATE, DELETE.
Если запросы часто фильтруют только по второму столбцу, может понадобиться отдельный индекс или другой порядок столбцов.

Какое влияние оказывает создание индекса по двум столбцам на скорость выполнения запросов в PostgreSQL?