Разработчик пишет в чат: «база тормозит, список заказов открывается целую вечность». В половине таких случаев причина одна — у таблицы нет индекса, и база перебирает все строки подряд, чтобы найти несколько нужных. В этом уроке ты научишься видеть это своими глазами командой EXPLAIN и чинить одной строкой.
Индекс — это шпаргалка, которую база держит рядом с таблицей, вроде алфавитного указателя в конце толстой книги: вместо того чтобы листать восемьсот страниц, открываешь указатель и сразу видишь нужные номера. В Postgres основной вид индекса — B-дерево: отсортированный список значений столбца, где у каждого значения записано, в каких строках оно встречается. Индексы были в базах задолго до SQL и есть в любой из них. Чего индекс не делает: не ускоряет всё подряд и не создаётся сам — кроме того, что база заводит под первичный ключ.
Как база ищет без индекса
Возьмём простой вопрос к учебной базе: какие заказы на сумму ровно 4321 рубль. Таких сорок штук из двухсот тысяч. Индекса по столбцу amount нет, поэтому у базы один путь: прочитать каждую строку и сравнить сумму.
Такой просмотр называется последовательным, в планах он пишется Seq Scan. На учебной таблице он занимает пару миллисекунд, и разницы ты не почувствуешь. На таблице в сто миллионов строк тот же запрос идёт десятки секунд, и сотня таких запросов в минуту кладёт базу.
Узнать, каким путём база собирается идти, можно не гадая — она сама расскажет.
EXPLAIN: спросить у базы план
Перед выполнением запроса в Postgres работает планировщик: он прикидывает несколько способов найти строки и выбирает самый дешёвый. Слово EXPLAIN перед запросом просит показать выбранный план, а EXPLAIN ANALYZE — ещё и выполнить запрос и замерить, как всё прошло на самом деле.
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, иначе изменения запишутся.
Причина медленного поиска видна: база читает всю таблицу ради нескольких строк. Чинится это одной командой.
Что меняет индекс
Индекс создаётся одной командой: имя индекса, таблица и столбец в скобках.
shop=# CREATE INDEX orders_amount_idx ON orders (amount); CREATE INDEX
Тот же запрос после этого:
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 теперь есть, но план другой:
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 — оно копит статистику по всем запросам, и с него начинается разбор «база тормозит». Оба понадобятся, когда будешь разбирать медленную базу вместе с разработчиком; сейчас достаточно знать, что они есть.
Словарь урока
Что спросят на собеседовании
- Запрос стал медленным. С чего начнёте?С EXPLAIN ANALYZE: посмотреть, есть ли Seq Scan по большой таблице и сколько строк отброшено фильтром. Затем сверить оценку строк с реальной — расхождение говорит об устаревшей статистике.
- Почему не стоит индексировать все столбцы?Индексы занимают место и замедляют каждую запись: база обновляет их все. Индекс оправдан под частый запрос, отбирающий малую долю строк.
- Как создать индекс на большой таблице в проде?CREATE INDEX CONCURRENTLY: строится дольше, но не блокирует запись.
Что дальше
В практике ты проделаешь это на своей базе: найдёшь медленный запрос, построишь индекс и сравнишь планы до и после.