Проектирование индексов

Ментальная модель: B-tree-индекс - данные, отсортированные по ключу, со ссылками на строки. Отсюда все правила.

Правило префикса

Индекс (a, b, c) сортирует по a, затем b, затем c. Помогает запросам по префиксу:

• WHERE a = 1 - да; a = 1 AND b = 2 - да; полный ключ - да;
• WHERE b = 2 - нет (b не в начале, записи разбросаны).

Порядок колонок: равенство раньше, диапазон/сортировку в конец.

ORDER BY без сортировки

-- индекс (forked_from_id, updated_at DESC) убирает Sort:

SELECT * FROM repos WHERE forked_from_id = 42 ORDER BY updated_at DESC LIMIT 10;

Индекс отдаёт строки уже в нужном порядке - бери первые 10 и стоп.

Покрывающие, частичные, по выражению

CREATE INDEX ON repos (forked_from_id, updated_at DESC) INCLUDE (name);  -- покрытие

CREATE INDEX ON tasks (created_at) WHERE status = 'active';              -- частичный

CREATE INDEX ON users (lower(email));                                    -- по выражению

Цена записи

Каждый индекс обновляется на запись; неиспользуемые - чистый убыток:

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

Слишком много индексов вредит и записи, и планированию. Индекс заводят под доказанную потребность. Условие должно быть sargable (см. sargability); тип под оператор - через operator-classes.