Provision
—— XP

Индекс и EXPLAIN: почему запрос медленный

теория~7 минут+180 XP

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

Индекс — это шпаргалка, которую база держит рядом с таблицей, вроде алфавитного указателя в конце толстой книги: вместо того чтобы листать восемьсот страниц, открываешь указатель и сразу видишь нужные номера. В Postgres основной вид индекса — B-дерево: отсортированный список значений столбца, где у каждого значения записано, в каких строках оно встречается. Индексы были в базах задолго до SQL и есть в любой из них. Чего индекс не делает: не ускоряет всё подряд и не создаётся сам — кроме того, что база заводит под первичный ключ.

Как база ищет без индекса

Возьмём простой вопрос к учебной базе: какие заказы на сумму ровно 4321 рубль. Таких сорок штук из двухсот тысяч. Индекса по столбцу amount нет, поэтому у базы один путь: прочитать каждую строку и сравнить сумму.

Такой просмотр называется последовательным, в планах он пишется Seq Scan. На учебной таблице он занимает пару миллисекунд, и разницы ты не почувствуешь. На таблице в сто миллионов строк тот же запрос идёт десятки секунд, и сотня таких запросов в минуту кладёт базу.

Узнать, каким путём база собирается идти, можно не гадая — она сама расскажет.

EXPLAIN: спросить у базы план

Перед выполнением запроса в Postgres работает планировщик: он прикидывает несколько способов найти строки и выбирает самый дешёвый. Слово EXPLAIN перед запросом просит показать выбранный план, а EXPLAIN ANALYZE — ещё и выполнить запрос и замерить, как всё прошло на самом деле.

SQL
shop=# EXPLAIN ANALYZE SELECT * FROM orders WHERE amount = 4321;
                              QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..3909.00 rows=40 width=24)
                     (actual time=0.095..3.919 rows=40 loops=1)
   Filter: (amount = 4321)
   Rows Removed by Filter: 199960
 Planning Time: 0.078 ms
 Execution Time: 3.925 ms

Читать план начинающему достаточно по четырём местам. Первое слово строки — способ доступа: здесь Seq Scan on orders, полный просмотр таблицы. rows=40 в скобках actual — сколько строк реально нашлось. Rows Removed by Filter: 199960 — сколько прочитано зря: почти вся таблица. Execution Time — сколько ушло времени.

Числа cost — это оценка планировщика в условных единицах, не в миллисекундах; сравнивать их имеет смысл только между планами одного запроса. Если оценка rows= в первых скобках сильно расходится с реальной во вторых, у базы устарела статистика, и лечится это командой ANALYZE orders;.

Вывод выше слегка сокращён: в терминале первая строка длиннее, её здесь перенесли надвое. Когда освоишься, добавляй ещё слово BUFFERS — EXPLAIN (ANALYZE, BUFFERS) покажет, сколько страниц прочитано из памяти, а сколько с диска.

Ещё важное: EXPLAIN ANALYZE выполняет запрос по-настоящему. Для SELECT это безопасно, а UPDATE и DELETE так проверяют только внутри BEGIN … ROLLBACK, иначе изменения запишутся.

Причина медленного поиска видна: база читает всю таблицу ради нескольких строк. Чинится это одной командой.

Что меняет индекс

Индекс создаётся одной командой: имя индекса, таблица и столбец в скобках.

SQL
shop=# CREATE INDEX orders_amount_idx ON orders (amount);
CREATE INDEX

Тот же запрос после этого:

SQL
shop=# EXPLAIN ANALYZE SELECT * FROM orders WHERE amount = 4321;
                              QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on orders  (actual time=0.015..0.076 rows=40 loops=1)
   Recheck Cond: (amount = 4321)
   ->  Bitmap Index Scan on orders_amount_idx
             (actual time=0.009..0.009 rows=40 loops=1)
         Index Cond: (amount = 4321)
 Execution Time: 0.103 ms

План стал из двух шагов, и читается он снизу вверх, от самой глубокой стрелки. Bitmap Index Scan on orders_amount_idx — база заглянула в индекс и узнала, где лежат сорок нужных строк. Bitmap Heap Scan — забрала из таблицы только их. Строки Rows Removed by Filter больше нет: лишнего не читали. Время упало с 3,9 до 0,1 миллисекунды, почти в сорок раз.

Встречается и Index Scan — то же самое, только строки забираются по одной. Для начинающего разница неважна: и то и другое значит «индекс используется». Если ты пришёл из MS SQL, не перепутай: там Index Scan — проход по всему индексу, а точечный поиск называется Index Seek. В Postgres Index Scan — как раз точечный поиск.

Значит ли это, что индексы надо завести на все столбцы? Нет — и вот почему.

Когда индекс не помогает

Попросим заказы дороже ста рублей. Индекс по amount теперь есть, но план другой:

SQL
shop=# EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 100;
 Seq Scan on orders  (actual time=0.005..8.450 rows=199960 loops=1)
   Filter: (amount > 100)
   Rows Removed by Filter: 40

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

У индекса есть и цена. Он занимает место — у нашего вышло 1,4 мегабайта на двести тысяч строк. И каждый INSERT или UPDATE теперь обновляет не только таблицу, но и все её индексы. Десять индексов «на всякий случай» замедляют запись в разы. Поэтому индекс заводят под конкретный частый запрос, который действительно тормозит.

Есть ещё

В рабочей базе индекс создают командой CREATE INDEX CONCURRENTLY: обычная блокирует запись в таблицу на всё время построения, а на большой таблице это минуты простоя. Чтобы найти, какие запросы вообще стоит ускорять, в Postgres есть расширение pg_stat_statements — оно копит статистику по всем запросам, и с него начинается разбор «база тормозит». Оба понадобятся, когда будешь разбирать медленную базу вместе с разработчиком; сейчас достаточно знать, что они есть.

Словарь урока

Индекс
Отсортированный указатель по столбцу: значение и где лежат строки с ним. ↩ к тексту
Seq Scan
Последовательный просмотр всей таблицы. Нормален для маленьких таблиц и запросов на большую долю строк.
Index Scan
Поиск через индекс. Bitmap Index Scan — его разновидность для нескольких строк сразу.
Планировщик
Часть базы, которая выбирает самый дешёвый способ выполнить запрос. ↩ к тексту
EXPLAIN ANALYZE
Выполняет запрос и показывает план с реальным временем каждого шага.

Что спросят на собеседовании

  • Запрос стал медленным. С чего начнёте?
    С EXPLAIN ANALYZE: посмотреть, есть ли Seq Scan по большой таблице и сколько строк отброшено фильтром. Затем сверить оценку строк с реальной — расхождение говорит об устаревшей статистике.
  • Почему не стоит индексировать все столбцы?
    Индексы занимают место и замедляют каждую запись: база обновляет их все. Индекс оправдан под частый запрос, отбирающий малую долю строк.
  • Как создать индекс на большой таблице в проде?
    CREATE INDEX CONCURRENTLY: строится дольше, но не блокирует запись.

Что дальше

В практике ты проделаешь это на своей базе: найдёшь медленный запрос, построишь индекс и сравнишь планы до и после.

Проверь себя+180 XPдве попытки
1
Без индекса
Что означает Seq Scan в плане запроса?
2
Индекс
Что такое индекс?
3
EXPLAIN ANALYZE
Чем EXPLAIN ANALYZE отличается от EXPLAIN?
4
Когда не помогает
Почему для условия amount > 100 база выбрала Seq Scan, хотя индекс есть?
5
Цена индекса
Чем платят за каждый лишний индекс?
ответь на все вопросы, чтобы проверить

Нашёл ошибку или непонятное место? Наведи на абзац — слева появится «+», нажми и напиши, что не так. Замечание увидит автор курса.Нашёл ошибку или непонятное место? Нажми «+» справа от заголовка раздела и напиши, что не так. Замечание увидит автор курса.