Provision
—— XP

Практика: ускорить запрос индексом

практика~20 минут+450 XP

В уроке ты видел планы чужими глазами. Теперь повторишь всё на своей базе и сам убедишься, что индекс работает — и что иногда база им сознательно не пользуется. Это навык, который пригодится в первый же раз, когда разработчик скажет «база тормозит».

Все запросы набирай в интерактивном psql и в конце выйди через \q: так сохранится история, по которой проверяется сдача.

Шаг 1. Подключиться

Флаг -U — имя пользователя, база по умолчанию называется так же.

ubuntu@stand:~$
cd ~/infra/db
docker compose exec db psql -U shop
shop=#

Шаг 2. Включить секундомер

У psql есть встроенный замер времени. После этой команды он будет печатать под каждым ответом, сколько шёл запрос.

ubuntu@stand:~$
shop=# \timing on
Timing is on.

Шаг 3. Замерить медленный поиск

Ищем заказы на сумму ровно 4321 рубль.

ubuntu@stand:~$
shop=# SELECT count(*) FROM orders WHERE amount = 4321;
 count
-------
    40
(1 row)

Time: 15.963 ms

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

Шаг 4. Спросить план

Добавь перед запросом EXPLAIN ANALYZE. База выполнит его и расскажет, каким путём шла.

ubuntu@stand:~$
shop=# EXPLAIN ANALYZE SELECT * FROM orders WHERE amount = 4321;
 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
 Execution Time: 3.925 ms

Вывод здесь сокращён: длинная первая строка перенесена надвое, а строки с рамкой QUERY PLAN и Planning Time убраны. У тебя будут и они, и свои числа cost и времени. Найди глазами три вещи: Seq Scan — полный просмотр, Rows Removed by Filter: 199960 — прочитано зря, Execution Time — итоговое время. Запиши его, чтобы сравнить в шаге 7.

Шаг 5. Какие индексы уже есть

Команда \di перечисляет индексы базы.

ubuntu@stand:~$
shop=# \di
 Schema |      Name      | Type  | Owner |   Table
--------+----------------+-------+-------+-----------
 public | customers_pkey | index | shop  | customers
 public | orders_pkey    | index | shop  | orders

Два индекса, оба под первичные ключи — их база создала сама. По amount ничего нет, поэтому поиск по сумме идёт перебором.

Шаг 6. Построить индекс

Имя индекса составляют из таблицы, столбца и окончания _idx — так его потом легко узнать в списке.

ubuntu@stand:~$
shop=# CREATE INDEX orders_amount_idx ON orders (amount);
CREATE INDEX
Time: 36.881 ms

На двухстах тысячах строк — доли секунды. На ста миллионах это минуты, и всё это время обычный CREATE INDEX не пускает запись в таблицу. В рабочей базе поэтому пишут CREATE INDEX CONCURRENTLY.

Шаг 7. Тот же план после индекса

Повтори запрос из шага 4:

ubuntu@stand:~$
shop=# EXPLAIN ANALYZE SELECT * FROM orders WHERE amount = 4321;
 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 — забрала из таблицы только найденные строки. Строки про выброшенные фильтром больше нет. Сравни Execution Time с записанным в шаге 4 — разница должна быть в десятки раз.

Шаг 8. Когда индекс не нужен

Теперь условие, которому подходят почти все заказы:

ubuntu@stand:~$
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

Индекс есть, а база снова читает таблицу подряд. Это не поломка: нужны 199960 строк из 200000, и прыгать за каждой по указателю дороже. Если у тебя здесь всё же Bitmap Index Scan — у базы ещё нет свежей статистики; выполни ANALYZE orders; и повтори.

Шаг 9. Сколько стоит индекс

Индекс — не бесплатный. Функция pg_relation_size возвращает его размер в байтах, pg_size_pretty переводит в понятные единицы.

ubuntu@stand:~$
shop=# SELECT pg_size_pretty(pg_relation_size('orders_amount_idx'));
 pg_size_pretty
----------------
 1400 kB
(1 row)

Почти полтора мегабайта на одну колонку небольшой таблицы. И при каждой новой строке в orders базе теперь нужно обновить ещё и этот указатель.

Шаг 10. Выйти

Выключи секундомер и выйди — история сохранится при выходе.

ubuntu@stand:~$
shop=# \timing off
Timing is off.
shop=# \q

Если что-то пошло не так

Если после шага 6 план всё равно показывает Seq Scan на поиске по = 4321, проверь \di: индекс мог создаться не на ту таблицу или не по тому столбцу. Ошибка relation "orders_amount_idx" already exists значит, что индекс уже есть — например, остался с прошлой попытки; удалить его можно DROP INDEX orders_amount_idx;.

Самый частый вопрос на этом месте — «почему время так скачет». Первое чтение идёт с диска, следующие — из памяти. Сравнивай не первый запуск, а второй или третий, и смотри прежде всего на способ доступа в плане: он не скачет.

Что дальше

Данные ты читаешь, меняешь и ускоряешь. Следующий урок — о том, что делать, когда данные потеряны: резервная копия и восстановление.

Практика 04+450 XP

Находить по плану запроса полный просмотр таблицы, лечить его индексом и проверять результат через EXPLAIN ANALYZE.

  • План смотрели с замером
  • Индекс по сумме создан
  • База пользуется индексом
Команда сдачи — выполни на своей машине
команда
cd ~/infra/db 2>/dev/null || echo NODB; echo ANALYZE=$(docker compose exec -T db grep -ci 'explain.*analyze' /root/.psql_history); echo IDX=$(docker compose exec -T db psql -U shop -Atc "SELECT count(*) FROM pg_indexes WHERE tablename = 'orders' AND indexdef LIKE '%(amount)%'"); docker compose exec -T db psql -U shop -Atc 'EXPLAIN SELECT * FROM orders WHERE amount = 4321'

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