L4. Прискорити повільні запити

середнійспирається на модуль 8, модуль 9

Мета

Повільний запит у продакшні приходить як скарга («сторінка замовлень вантажиться п’ять секунд»), а не як готове рішення. Ви вчитеся рухатися від скарги до індексу: подивитися, скільки сторінок читає запит, вирішити, який індекс це виправить, і переконатися, що він не розорює запис. Після роботи ви вмієте:

• читати в EXPLAIN (ANALYZE, BUFFERS) число прочитаних буферів і знати, який його порядок має бути для точкового запиту;
• вибирати між складеним, покриваючим, за виразом, GIN і BRIN індексом за формою запиту й даних;
• відрізняти запит, який лікується індексом, від запиту, який треба переписати;
• рахувати ціну індексу: місце на диску й WAL, який він додає до кожної вставки.

Завдання

Датасет розміру medium (220 тисяч замовлень, 610 тисяч подій, див. Датасет). У каталозі queries/ лежать шість запитів, кожен із заголовком у коментарі. Ваш результат — у каталозі solution/:

• indexes.sql: команди CREATE INDEX (кілька на файл, в одному файлі, як вам зручно);
• NN.sql: переписаний запит NN, якщо ви вирішили його переписати. Один запит SELECT без інших команд. Запити, яких ви не переписали, перевірка бере з queries/.

№	Скарга
01	Сторінка «Мої замовлення» покупця, у якого 256 замовлень, вантажиться повільно
02	Підтримка шукає покупця за email без урахування регістру, і форма «зависає»
03	Тижневий звіт про події на сайті за днями й типами
04	Фільтр товарів за двома атрибутами з jsonb
05	Останні доставлені замовлення пункту видачі
06	Позиції замовлення за номером, який приходить із листа клієнта текстом

Готово, коли ./labs/l04-indexes/check.sh виводить «усе гаразд: 19 перевірок». Перевіряється, що:

1. indexes.sql виконується на чистій копії й створює лише індекси;
2. результат кожного запиту збігається з еталонним: переписаний запит не має змінити відповідь;
3. число прочитаних буферів (shared hit + read у EXPLAIN (ANALYZE, BUFFERS)) упало щонайменше в 25 разів проти запиту з queries/ на базі без ваших індексів. Міряються буфери, а не мілісекунди: вони не залежать від вашої машини;
4. додані індекси разом займають не більше 25 МіБ;
5. вставка 100 тисяч рядків в orders і в view_events генерує не більше ніж у 3 рази більше WAL, ніж у таблицю без ваших індексів.

Чого робити не треба. Змінювати дані, схему чи таблиці (перевірка відхилить будь-що, крім індексів), правити queries/ (там лежать еталонні запити для порівняння, переписані варіанти йдуть у solution/), чіпати check.sh і lib.sh.

Перед початком

• Прочитайте модуль 8: складені індекси й порядок колонок, індекси за виразом, GIN, BRIN, ціна на запис. З модуля 9 знадобиться читання EXPLAIN, зокрема Buffers, Index Only Scan і причини, з яких індекс не береться до уваги.

• Згенеруйте датасет medium і завантажте його у свою базу для експериментів (команди в setup/README.md):

  ./setup/generate-dataset.sh medium
  docker compose --profile postgres up -d --wait
  ./setup/load-dataset.sh medium --reset
  docker compose exec postgres psql -U shop -d shop

  Перевірка сама бере CSV з dataset/medium/ (вони змонтовані в контейнер) і будує власні бази l04_base і l04_run. Ваша shop потрібна лише вам для досліджень. Якщо ви завантажили medium у shop, то індекси, які ви там створюєте, не враховуються: враховується лише solution/indexes.sql.

• Перший запуск check.sh завантажує l04_base (близько хвилини), далі кожна перевірка триває близько пів хвилини.

Етапи

1. Виміряйте, що є. Запустіть кожен із шести запитів із queries/ у psql як EXPLAIN (ANALYZE, BUFFERS) … і запишіть число буферів на вершині плану. Перевірка: на чистій базі кожен запит читає від сотень до тисяч буферів. Ці числа — «до».

2. Запити 01 і 05: відбір і порядок. Обидва беруть «останні N» за ключем із рівністю. Подивіться, який індекс дає і відбір, і порядок, і як змінюється план, якщо зробити індекс лише за колонкою з WHERE. Окремо подумайте про порядок колонок: запит 05 має дві умови рівності й сортування. Перевірка: ./check.sh показує для 01 і 05 падіння мінімум у 25 разів.

3. Запити 02 і 04: вираз і оператор. Вираз в умові й вираз в індексі мають збігатися. Для jsonb подумайте, який оператор умови підхоплює який тип індексу, і чи можна досягти мети, не переписуючи запит, а лише змінивши індекс. Email у датасеті повторюються з різним регістром, тож унікальний індекс тут не побудується. Перевірка: 02 і 04 проходять за буферами й за результатом.

4. Запит 03: багато рядків, мало потрібних. Подій сотні тисяч, а вибрано тиждень. Порівняйте два індекси, які це прискорюють: розмір кожного й кількість буферів. Якщо обидва дають потрібне падіння, пам’ятайте про бюджет. Перевірка: 03 проходить, а сумарний розмір індексів не перевищує ліміт.

5. Запит 06: індекс чи запит? Подивіться, чи використовує база первинний ключ order_items, і чому. Якщо причина в самому запиті, а не в браку індексу, індекс лікуватиме лише наслідок (і важитиме багато): виправте запит і покладіть його в solution/06.sql. Перевірка: 06 проходить без нового індексу на order_items.

6. Ціна. Запустіть ./labs/l04-indexes/check.sh цілком. Якщо не проходить бюджет чи WAL, знайдіть у indexes.sql індекс, який не потрібен жодному із шести запитів або який можна замінити меншим. Перевірка: «усе гаразд».

Перевірка

./labs/l04-indexes/check.sh                 # solution/ цієї лабораторної
./labs/l04-indexes/check.sh інший/каталог   # будь-який каталог з indexes.sql і NN.sql

Перевірка працює на окремих базах l04_base (датасет medium після VACUUM ANALYZE, створюється один раз) і l04_run (свіжа копія на кожен запуск) у вашому контейнері postgres; база shop не змінюється. Якщо ви запускаєте compose під власною назвою проєкту, передайте її: COMPOSE_PROJECT_NAME=моя-назва ./labs/l04-indexes/check.sh. Перебудувати l04_base після перегенерації датасету: L04_REBUILD=1 ./labs/l04-indexes/check.sh. Після роботи бази видаляються командою DROP DATABASE l04_base від імені postgres.

Порядок перевірки такий. Вона створює копію, виміряє число буферів кожного запиту з queries/ без ваших індексів, виконує indexes.sql, робить VACUUM (ANALYZE) (так, як це зробив би autovacuum: статистика для нових індексів і карта видимості для Index Only Scan) і міряє буфери знову. Результат запиту порівнюється з еталоном із каталогу expected/ (в ньому значення, а не запити). Далі вона зважує додані індекси й вимірює WAL вставки в копії таблиць із вашими індексами й без них. Паралелізм під час вимірів вимкнено, тому число буферів повторюється від запуску до запуску й від машини до машини.

Повідомлення називає, що не так, і підказує напрям: «буферів: 2646 → 260 (у 10.2 раза менше, потрібно у 25)» для запиту, де індекс лише частково допомагає, «додані індекси: 31.5 МіБ, бюджет 25 МіБ» з переліком найбільших, «очікували рядків: N, у вашому результаті: M» із прикладами зайвих і відсутніх рядків.

Як перевірено саму перевірку

Чекер випробувано на розв’язках трьох видів: еталонному, наївних (індекс на кожну колонку окремо замість складеного, індекс на email замість lower(email), B-дерево на всю view_events понад бюджет, GIN без переписування запиту, переписаний запит зі зміною результату, індекси на всі колонки) й альтернативних правильних (покриваючі індекси замість складених, B-дерево за виразами замість GIN, інший склад колонок). Назви індексів, регістр і форматування значення не мають. Якщо ваш розв’язок інший, але дає ті самі рядки й цифри, його приймуть.

Часті помилки

Індекси створено в shop, а перевірка каже «буферів не змінилося». Перевірка не бачить вашу базу. Усі індекси мають бути в solution/indexes.sql.

indexes.sql падає на CREATE UNIQUE INDEX. Унікальності за lower(email) у датасеті немає: є дублікати зі старого імпорту (див. завдання 22 в L1). Для цього запиту потрібен звичайний індекс.

Індекс є, а план його не бере. Вираз в умові не збігається з виразом у визначенні індексу, оператор не той, а ключ інший. Перед тим як шукати помилку в статистиці, порівняйте умову запиту з визначенням індексу буква в букву.

На складеному індексі буферів усе одно багато. Порядок колонок: спершу рівність, потім діапазон чи сортування. Перевірте, чи сортування збігається з напрямом і складом індексу.

Запит 03 проходить на B-дереві, але не проходить бюджет. Індекс такого розміру потрібен не кожній таблиці. Подивіться, як розташовані рядки view_events у таблиці.

solution/04.sql із двома запитами чи з EXPLAIN. Перевірка загортає запит у COPY (...), тож у файлі має бути один SELECT.

Далі, якщо цікаво

Подивіться на pg_stat_user_indexes після кількох прогонів чекера у l04_run: які індекси ваших розв’язків мають idx_scan = 0. Замініть у 04.sql оператор на ->> і знайдіть індекс, що дає той самий виграш без переписування. Побудуйте індекс на orders.status і виміряйте, скільки HOT-оновлень залишилось при масовому UPDATE статусу (модуль 8, розділ про HOT). Вставте 100 тисяч рядків у view_events із випадковим часом і подивіться, що станеться з BRIN.