Почему важен порядок колонок в индексах

Что проверяют

Знаете ли вы правило левого префикса и умеете ли подбирать индекс под реальный набор запросов, а не добавлять его на каждую колонку из условия.

Как отвечать

Составной индекс это отсортированный список кортежей: сначала по первой колонке, внутри одинаковых значений по второй, дальше по третьей. Отсюда правило левого префикса: индекс по (a, b, c) работает для условий по a, по a и b, по всем трём, а также для WHERE a = ? ORDER BY b. Для условия только по b он бесполезен, значения b рассыпаны по всему индексу.

Порядок подбирают по форме запросов:

• колонки с равенством идут первыми;
• колонка с диапазоном (>, BETWEEN, LIKE 'x%') ставится последней из ограничивающих: после неё следующие колонки индекса диапазон чтения уже не сужают, они только помогают не ходить в таблицу;
• колонки из ORDER BY идут сразу после равенств, тогда сортировка достаётся бесплатно и не появляется отдельный шаг сортировки в плане.

Пример из вопроса: если запросы фильтруют по стране и сортируют по городу, нужен индекс по паре, где страна первая.

Где спотыкаются

Ставят самую селективную колонку первой как догму. Селективность решает, когда все запросы используют обе колонки одинаково. Если половина запросов ходит только по одной из них, важнее, чтобы именно она была префиксом, иначе для этих запросов индекса как будто нет.

Заводят по индексу на каждую колонку и ждут, что база их склеит. Иногда планировщик действительно объединяет два индекса, но это дороже одного составного, а лишние индексы замедляют вставку и обновление и занимают место.

Забывают, что функция или приведение типа по первой колонке выключает индекс: условие с lower(email) в индекс по email не попадёт, нужен индекс по выражению. Так же ломает префикс LIKE с ведущим символом подстановки: упорядоченность индекса в этом случае ничего не даёт.