В качестве затравки
При наличии двух отдельных индексов и двух условий WHERE запросы работают медленно. Анализ EXPLAIN ANALYSE демонстрирует применение фильтра вместо полного использования индекса.
Для этого существуют составные индексы
Решение — создание индекса на несколько полей одновременно:
CREATE INDEX ci_f1_f2 ON my_table USING btree("field1", "field2");Производительность значительно улучшается. Однако при добавлении сортировки поле ORDER BY также нужно включить в индекс.
Как это работает
Составной индекс имеет иерархическую структуру — для каждого из элементов на следующем уровне формируется частичный индекс в его пределах. Поэтому порядок полей критически важен.
Индекс (field1, field2, id) работает намного быстрее, чем (id, field1, field2).
Как это использовать
Рекомендуемый порядок полей:
- Поля из
WHERE(с большей селективностью вперёд). - Поля из
ORDER BY(в порядке сортировки). - Уникальные поля в конце.
Планировщик выбирает индекс на основе количества вариантов значений (селективности) — фильтрует сначала по более избирательным полям.
Как это можно оптимизировать
Частичные индексы сокращают размер, фильтруя данные на уровне условия индекса:
CREATE INDEX comp_index ON news USING btree("rubric_id", "ndate") WHERE status = 1;Итоги
- Составные индексы ускоряют сложные запросы.
- Уникальные поля — в конце.
- Порядок:
WHERE(по селективности) →ORDER BY. - Использовать частичные индексы при возможности.
P.S.
Составные условия могут применяться в предложениях WHERE для выборки рядов с использованием кортежных операций сравнения.