Почему запрос медленный: индексы на пальцах

← назад к разделу

Два одинаковых с виду запроса: один отвечает мгновенно, другой — висит и кладёт стенд. Разница почти всегда в одном слове: индекс. Понимать индексы «на пальцах» полезно каждому, кто пишет запросы: это объясняет и медленный поиск в приложении, и то, почему ваш собственный невинный SELECT вдруг стал проблемой.

Полный перебор: как база ищет без индекса

Выполняя SELECT * FROM orders WHERE customer_id = 'cus-01', база по умолчанию делает ровно то, что делали бы вы с бумажной книгой без оглавления: читает всё подряд и откладывает подходящее. Это называется полным перебором (sequential scan). На тысяче строк он незаметен, на ста миллионах — это минуты работы дисков.

Важное следствие: на маленьком тестовом стенде всё летает всегда. Медленные запросы — болезнь больших объёмов, поэтому проблемы производительности не видны на стенде с сотней записей и внезапно расцветают на проде.

Индекс — оглавление таблицыспросят на собеседовании

Индекс — дополнительная структура рядом с таблицей: отсортированный список значений столбца со ссылками на строки. Точная аналогия — предметный указатель в конце книги: не листать все страницы, а найти слово в алфавитном списке и перейти сразу на нужную.

Есть индекс по customer_id — и база идёт к нужным строкам через него, почти не замечая роста таблицы: от удвоения строк поиск прибавляет один шаг, а не вдвое работы. Слово «почти» тут не для красоты: если под условие подходит заметная доля таблицы, прыгать по ссылкам дороже, чем прочитать всё подряд, и база сознательно вернётся к перебору — про этот случай чуть ниже. Индекса нет — перебор всегда.

Почему бы не проиндексировать всё? У индекса есть цена: он занимает место и замедляет запись — каждый INSERT и UPDATE должен обновить не только таблицу, но и все её индексы. Поэтому индексы заводят точечно. На первичном ключе индекс есть всегда — его база создаёт сама. А вот под внешний ключ PostgreSQL индекс не создаёт, хотя многие уверены в обратном: его заводят руками, и именно его отсутствие даёт медленный JOIN и долгое удаление родительской строки — чтобы выяснить, ссылается ли кто-нибудь на удаляемую строку, база каждый раз перебирает дочернюю таблицу целиком. Остальное — поля частого поиска — по решению разработчиков.

Цену лишнего индекса полезно представлять в числах. Индекс — отдельная структура, которую база обновляет вместе со строкой, поэтому INSERT в таблицу с пятью индексами делает шесть записей вместо одной, а UPDATE колонки, входящей в индекс, ещё и убирает из него старую запись. Плюс место: индекс по текстовой колонке нередко занимает столько же, сколько сама колонка, и таблица вместе с индексами на диске бывает вдвое-втрое больше «чистых» данных.

Отсюда вывод, который редко произносят вслух: лишний индекс — это дефект, а не запас. Индекс, которым не пользуется ни один запрос, только замедляет запись и занимает место. Найти такие можно: в PostgreSQL счётчики обращений лежат в pg_stat_user_indexes, и idx_scan = 0 на живой базе означает «за всё время никто им не воспользовался».

Составной индекс и правило левого префиксаспросят на собеседовании

В песочнице лежит индекс orders (customer_id, created_at DESC), и он работает в половине примеров курса. Индекс по двум колонкам устроен как телефонная книга: сначала по фамилии, внутри фамилии по имени. Поэтому он помогает запросу «заказы покупателя за период» (WHERE customer_id = ? AND created_at > ?) и запросу «все заказы покупателя, свежие первыми» (WHERE customer_id = ? ORDER BY created_at DESC): база находит покупателя и читает его заказы подряд, уже в нужном порядке.

Правило левого префикса говорит, чего он не умеет. Искать только по created_at через этот индекс нельзя: это как искать в телефонной книге по имени без фамилии. Порядок колонок в индексе задаёт, какие запросы он обслуживает: первой ставят колонку, по которой ищут с равенством всегда, второй ту, по которой ограничивают диапазон или сортируют. Два индекса по одной колонке каждый не заменяют один составной: базе придётся склеивать результаты, а сортировку она сделает сама.

Когда индекс есть, но не работаетспросят на собеседовании

Коварный класс ситуаций: индекс существует, а запрос всё равно перебирает всю таблицу, и причины три.

Первая: над столбцом стоит функция. WHERE lower(email) = 'anna@...' не использует индекс по email, потому что сравнивается не email, а lower(email), для базы это другое выражение, и в оглавлении его нет. Тот же эффект у арифметики: WHERE total_amount * 100 > 5000. Лечится либо индексом по выражению, либо переносом функции на другую сторону сравнения.

Вторая: LIKE с процентом в начале. LIKE 'anna%' индексом пользуется, как поиск по первым буквам в алфавитном указателе, а LIKE '%@gmail.com' нет: по окончанию слова оглавление не работает, и это отдельная задача со своими инструментами.

Третья не ошибка вовсе: слабая избирательность. WHERE status = 'PAID', когда оплачены 90 % заказов, читает всю таблицу, потому что прыгать по ссылкам на почти каждую строку дольше, чем прочитать её подряд. База сама выбирает перебор, и это правильное решение.

EXPLAIN: спросить базу, как она будет искатьспросят на собеседовании

Гадать не нужно — базу можно спросить. Добавьте перед запросом слово EXPLAIN:

живой пример

EXPLAIN SELECT * FROM orders WHERE customer_id = 'cus-01';

Вместо данных база покажет план: как она собирается выполнять запрос. Читать планы глубоко — отдельное искусство, но первый уровень доступен сразу: увидели Seq Scan (последовательный перебор) на большой таблице — вот причина тормозов; Index Scan — работает индекс. Этого достаточно, чтобы разговор с разработчиком начался не с «что-то тормозит», а с «поиск заказов по номеру телефона идёт полным перебором, таблица 40 миллионов строк».

Два предупреждения, чтобы первый опыт не сбил с толку.

Первое: сам по себе EXPLAIN запрос не выполняет. Он показывает план и оценку — сколько строк база ожидает найти. Оценка бывает мимо: на нашей песочнице этот самый запрос даёт rows=1, а заказов у cus-01 семь. Настоящие числа печатает EXPLAIN (ANALYZE, BUFFERS) — он запрос выполняет и рядом с оценкой показывает факт: rows=1 … (actual … rows=7). С изменяющими запросами тут осторожнее: ANALYZE у UPDATE или DELETE данные и правда изменит.

Второе: «на маленькой таблице индексы не нужны» — не закон, а привычка ожидания. В песочнице двадцать три заказа, а EXPLAIN SELECT * FROM orders WHERE customer_id = 'cus-01' показывает Index Scan using orders_customer_created: индекс по (customer_id, created_at) в схеме есть, и база решила, что через него дешевле. План выбирает она сама и каждый раз заново — ваше дело не угадать его, а прочитать.

Какие индексы уже есть и что EXPLAIN ANALYZE знает, чего не знает EXPLAIN

Прежде чем создавать индекс, смотрят, что есть. В psql команда \d orders печатает колонки таблицы и все её индексы с определениями; из любого клиента то же даёт запрос к pg_indexes:

живой пример

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

Обычный EXPLAIN показывает план, который база собирается выполнить, и оценки: сколько строк она ожидает. Это догадка по статистике. EXPLAIN (ANALYZE, BUFFERS) выполняет запрос по-настоящему и рядом с оценкой печатает факт: сколько строк вышло на каждом шаге, сколько времени он занял и сколько страниц прочитано с диска и из кеша.

живой пример

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 'cus-01' ORDER BY created_at DESC LIMIT 20;

Первое, на что смотрят: rows= в оценке против rows= в факте. Если база ожидала 10 строк, а вышло 100 000, она выбрала план для маленькой выдачи, и виновата устаревшая статистика, лечится ANALYZE orders. Второе: Buffers: shared read= против hit=, чтение с диска против кеша; запрос, который «то быстрый, то медленный», обычно упирается сюда. Осторожность одна: ANALYZE выполняет команду, и для UPDATE или DELETE его запускают внутри транзакции с ROLLBACK.

Соединения: почему JOIN больших таблиц медленный

Соединяя две таблицы, база выбирает один из трёх способов, и все три видны в плане по именам.

Nested Loop — вложенный цикл: для каждой строки левой таблицы ищутся пары в правой. Хорош, когда слева строк мало, а справа есть индекс по колонке соединения: тысяча строк слева — тысяча быстрых поисков. Без индекса справа это тысяча полных перечитываний правой таблицы, и именно так выглядит «запрос работает минутами».

Hash Join — по меньшей стороне строится хеш-таблица в памяти, и по ней проверяется каждая строка большей. Обычный выбор для двух больших таблиц без индексов. Смотреть здесь надо на память: если хеш не поместился, база разбивает работу на порции (Batches: 4 в плане вместо 1) и часть пишет на диск, а время растёт скачком.

Merge Join — обе стороны читаются по порядку и сливаются, как два отсортированных списка. Дёшев, когда порядок уже есть, например обе стороны читаются по индексу; иначе перед ним встанет Sort, и он окажется главной ценой запроса.

Что с этим делать. Первое: индекс по колонке соединения — это обычно вложенный цикл вместо хеша и минуты вместо десятков минут; под внешний ключ такой индекс приходится создавать руками. Второе: фильтровать до соединения, а не после — условие, которое база может применить к одной таблице заранее, уменьшает обе стороны. Третье: не соединять лишнее; таблица в FROM, из которой в SELECT ничего не взято, — частая находка в унаследованных отчётах, и убрать её дешевле, чем ускорять.

Где это применяется

Это знание работает в обе стороны. Внутрь — про ваши запросы: тяжёлый SELECT без ограничений по огромной таблице на общем стенде мешает всем; добавьте LIMIT и условия по индексированным полям. Наружу — про продукт: «поиск в приложении отвечает 40 секунд» — это дефект производительности, и после EXPLAIN вы опишете его на уровне причины, а не симптома. А фраза разработчика «там нет индекса» перестанет быть заклинанием — вы понимаете, что она значит и чем грозит.

Где спотыкаются начинающие:

• Меряют скорость на пустом стенде. Сто строк летают без всяких индексов; выводы о производительности с маленькой базы не переносятся на большую.
• Считают, что индекс ускоряет всё. Индекс помогает конкретным условиям поиска — и берёт плату замедлением записи. «Добавьте индексов» — не универсальный рецепт.
• Пишут условия, убивающие индекс, — функции над столбцом, LIKE с процентом в начале — и удивляются перебору.

Что учить дальше. Цикл «SQL с нуля» пройден. Практическое применение в работе тестировщика — в статье SQL для тестировщика. А когда захочется глубины — раздел PostgreSQL: транзакции и изоляция, устройство индексов, EXPLAIN всерьёз.

Глубже: поиск по подстроке: триграммы и полнотекстовый индексрасширенное

Статья про строки обещала, что обе дороги описаны здесь. LIKE '%кофе%' обычный индекс не использует: B-дерево ищет по началу значения, а начало здесь неизвестно. Первая дорога, расширение pg_trgm: индекс по триграммам, трёхбуквенным кусочкам слова, отвечает на любое LIKE и ILIKE с процентом с обеих сторон и на поиск с опечатками.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_products_title_trgm ON products USING gin (title gin_trgm_ops);
SELECT title FROM products WHERE title ILIKE '%кофе%';

Вторая дорога, полнотекстовый поиск: to_tsvector('russian', title) разбирает текст на слова и приводит их к основе, поэтому «кофемолка» и «кофемолки» совпадают, а искать можно по нескольким словам сразу; индекс тоже GIN. Триграммы берут для коротких полей и подстрок (артикул, имя, адрес), полнотекст для описаний и статей, где важны слова, а не буквы. Обе дороги подробно, с весами и подсветкой, разобраны в статье про полнотекстовый поиск.

Коротко

• Без индекса база читает таблицу целиком (Seq Scan); индекс это отсортированное оглавление, по которому она прыгает к нужным строкам.
• Индекс не работает, когда колонку завернули в функцию, LIKE начинается с %, тип не совпал или выборка слишком большая.
• Составной индекс работает слева направо: первой колонка равенства, второй диапазон или сортировка; поиск только по второй индекс не использует.
• EXPLAIN показывает догадку, EXPLAIN (ANALYZE, BUFFERS) факт: расхождение rows лечит ANALYZE, read против hit объясняет «то быстро, то медленно».
• Подстроку ищут индексом по триграммам (pg_trgm), слова полнотекстовым поиском.
• У соединения три плана: вложенный цикл (мало строк слева и индекс справа), хеш (две большие таблицы) и слияние (порядок уже есть); лишний индекс — дефект, а не запас.

Что почитать дальше

• EXPLAIN: как читать план — как читать план целиком.
• Составные индексы — почему порядок колонок решает.
• Полнотекстовый поиск — веса, подсветка и стемминг.