EXPLAIN ANALYZE по-новому: как ИИ-промты превращают медленные SQL-запросы в быстрые и безопасные миграции

EXPLAIN ANALYZE по-новому: как ИИ-промты превращают медленные SQL-запросы в быстрые и безопасные миграции

Почти каждый разработчик хотя бы раз сидел над запросом, который «вдруг» начал тормозить после роста таблицы до десятков миллионов строк. И почти каждый хотя бы раз катил миграцию в пятницу вечером, молясь, чтобы ALTER TABLE не заблокировал таблицу на несколько минут. SQL и PostgreSQL — это области, где опыт решает всё: планировщик запросов, статистика, типы блокировок, MVCC, VACUUM — дьявол прячется в деталях.

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

Ниже — 12 сценариев, разбитых на три уровня: базовые, продвинутые и экспертные. Для каждого промта указано, какую задачу он решает, как он выглядит и что примерно вы получите. Все примеры основаны на реальных возможностях PostgreSQL: EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pg_locks, CREATE INDEX CONCURRENTLY, ALTER TABLE ... ADD CONSTRAINT ... NOT VALID и других документированных механизмах.

Базовый уровень: разобраться, что вообще происходит

Промт 1. Чтение плана выполнения через EXPLAIN ANALYZE

Задача: у вас есть план запроса, но вы не понимаете, где узкое место.

Промт:

Ты — эксперт по PostgreSQL 16. Ниже — вывод EXPLAIN (ANALYZE, BUFFERS, VERBOSE) для запроса.
Разбери план по шагам: какой узел самый дорогой по actual time и rows,
где оценка rows сильно расходится с фактом (>10x), какие буферы читаются с диска,
а какие из shared hit. Предложи 2-3 гипотезы причины и что проверить дальше
(статистика, индексы, настройки work_mem). Не выдумывай индексы, которых нет в схеме.

Схема: <вставьте \d+ таблиц>
Запрос: <вставьте SQL>
План: <вставьте EXPLAIN>

Пример результата: модель укажет, что Nested Loop с rows=1 в оценке и rows=15000 по факту — классический признак устаревшей статистики, и посоветует ANALYZE table_name; и проверку pg_stats по колонке в условии JOIN.

Промт 2. Переписывание медленного JOIN

Задача: запрос с несколькими JOIN тормозит, и вы подозреваете, что дело в структуре, а не в индексах.

Промт:

Вот запрос с 4 JOIN и его план. Перепиши его так, чтобы уменьшить объём промежуточных
строк: рассмотри порядок соединений, вынос фильтров в CTE с MATERIALIZED,
замену LEFT JOIN на INNER, если это допустимо по логике.
Для каждого варианта покажи полный SQL и объясни, что меняется в плане.
Укажи, какие индексы поддерживают новый вариант.

Пример результата: модель предложит вынести агрегацию в подзапрос с MATERIALIZED и покажет, что оценка кардинальности упадёт, а hash join заменится на более дешёвый merge join.

Промт 3. Генерация индексов под конкретные WHERE-условия

Задача: нужен индекс, который реально используется, а не «на всякий случай».

Промт:

Дана таблица и типичные запросы с WHERE/ORDER BY. Предложи индексы:
одиночные, составные и частичные. Для каждого укажи точный CREATE INDEX,
объясни порядок колонок (селективность, порядок в ORDER BY),
и почему частичный индекс здесь уместен или нет.
Учти, что таблица часто обновляется — предупреди о стоимости записи.

Пример результата: для WHERE status = 'active' AND created_at > now() - interval '7 days' ORDER BY created_at DESC вы получите частичный индекс CREATE INDEX ... ON orders (created_at DESC) WHERE status = 'active'; с пояснением, почему он меньше полного.

Продвинутый уровень: миграции, блокировки, тестовые данные

Промт 4. Безопасная миграция без даунтайма

Задача: добавить NOT NULL-колонку или индекс на большую таблицу, не блокируя запись.

Промт:

Нужно добавить колонку с NOT NULL и DEFAULT на таблицу 200 млн строк в PostgreSQL 16.
Напиши пошаговую миграцию без долгих блокировок: отдельные шаги ALTER TABLE,
использование ADD COLUMN с DEFAULT (в PG 11+ это метаданные),
создание индекса через CONCURRENTLY, добавление CHECK NOT VALID,
затем VALIDATE CONSTRAINT. Для каждого шага укажи, какую блокировку он берёт
(по документации pg_locks и Locking) и сколько примерно длится.

Пример результата: последовательность из 4-5 шагов с пояснением, что CREATE INDEX CONCURRENTLY не берёт ACCESS EXCLUSIVE, а VALIDATE CONSTRAINT берёт SHARE UPDATE EXCLUSIVE и не блокирует чтение/запись.

Промт 5. Отладка блокировок и deadlock

Задача: приложение ловит deadlock detected в логах.

Промт:

Вот лог deadlock из PostgreSQL и вывод pg_locks в момент проблемы.
Определи цикл ожидания, какие транзакции и какие объекты в нём участвуют.
Предложи, как переупорядочить операции в приложении (порядок блокировок),
и как воспроизвести ситуацию в тесте. Если нужно — предложи уровень изоляции
или advisory lock. Не советуй отключать deadlock detection.

Пример результата: модель покажет, что транзакция A держит строку 1 и ждёт строку 2, а B — наоборот, и предложит сортировать обновления по первичному ключу.

Промт 6. Генерация тестовых данных и стресс-нагрузки

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

Промт:

Сгенерируй SQL для наполнения схемы <schema> реалистичными данными:
10 млн строк в orders, 1 млн в users, распределение статусов и дат — как в проде.
Используй generate_series и random(), но без внешних расширений.
Затем напиши pgbench-скрипт для нагрузки на 3 типовых запроса.
Предупреди, где random() даст равномерное распределение, а где нужно
нормальное (например, через box-muller в SQL).

Пример результата: вы получите INSERT ... SELECT FROM generate_series с реалистичными связями и скрипт pgbench -f load.sql -c 20 -T 300.

Экспертный уровень: производительность, схема, регрессии

Промт 7. Аудит схемы и нормализации

Задача: есть подозрение, что схема страдает от избыточности и аномалий.

Промт:

Проанализируй DDL схемы. Найди: дублирующиеся данные, нарушения 3НФ,
отсутствующие FK, колонки, которые стоило бы вынести в справочники.
Для каждой проблемы предложи конкретную миграцию и оцени риск.
Отдельно отметь места, где денормализация оправдана (например, для чтения).

Пример результата: модель укажет, что customer_city дублируется в orders, предложит справочник cities и объяснит, когда лучше оставить денормализацию.

Промт 8. Поиск регрессий через pg_stat_statements

Задача: после релиза p95 вырос, но непонятно, какой запрос виноват.

Промт:

Вот выгрузка pg_stat_statements за два периода (до и после релиза).
Сравни по total_exec_time и mean_exec_time, найди топ-5 регрессий.
Для каждой предположи причину: изменение плана, потеря индекса, рост таблицы.
Предложи, что проверить: pg_stat_user_indexes, pg_stat_user_tables, EXPLAIN.

Пример результата: таблица сравнения с топ-5 запросами и гипотезами, например: «mean вырос в 8 раз — вероятно, изменился план из-за устаревшей статистики».

Промт 9. Настройка autovacuum под нагрузку

Задача: таблица пухнет, bloat растёт, autovacuum не успевает.

Промт:

Дана таблица с частыми UPDATE. Предложи настройки autovacuum для неё:
scale_factor, threshold, cost_delay, cost_limit, autovacuum_vacuum_scale_factor.
Объясни, как это влияет на bloat и на нагрузку. Покажи ALTER TABLE ... SET (...).

Пример результата: рекомендация снизить autovacuum_vacuum_scale_factor до 0.01 для горячей таблицы и команда ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);.

Промт 10. Разбор оконных функций и CTE

Задача: запрос с WINDOW и WITH читается тяжело и работает медленно.

Промт:

Вот запрос с оконными функциями и CTE. Объясни, что делает каждая часть.
Затем предложи переписать без лишних сортировок: заменить WINDOW на агрегаты,
где возможно, вынести фильтры до оконной функции, использовать MATERIALIZED
только если CTE используется несколько раз.

Пример результата: модель покажет, что ROW_NUMBER() внутри CTE пересортировывает данные дважды, и предложит один проход с PARTITION BY.

Промт 11. Безопасное изменение типов и переименование

Задача: изменить тип колонки на большой таблице без долгой блокировки.

Промт:

Нужно переименовать колонку и сменить тип с integer на bigint на таблице 100 млн строк.
Напиши миграцию с нулевым даунтаймом: переименование через ALTER TABLE RENAME,
смена типа через новую колонку + backfill батчами + триггер на синхронизацию,
затем переключение. Укажи, где взять lock_timeout и statement_timeout.

Пример результата: пошаговый план с батчами по 10 000 строк и рекомендацией выставлять SET lock_timeout = '2s'; перед каждым DDL-шагом.

Промт 12. Проверка запроса на SQL-инъекции и параметризацию

Задача: убедиться, что динамический SQL безопасен.

Промт:

Вот код, который формирует SQL строкой. Найди места, уязвимые к инъекциям.
Перепиши на параметризованные запросы (например, для psycopg или JDBC).
Если динамический SQL неизбежен — покажи использование format() с %I и %L
в PostgreSQL и quote_ident/quote_literal.

Пример результата: модель заменит конкатенацию на cur.execute('SELECT * FROM t WHERE id = %s', (user_id,)) и покажет format('SELECT %I FROM %I', col, tbl).

Как использовать эти промты на практике

Ключевое правило: чем больше контекста вы дадите, тем точнее ответ. Всегда прикладывайте DDL таблиц, версию PostgreSQL (SELECT version();), вывод EXPLAIN (ANALYZE, BUFFERS) и, если есть, строки из pg_stat_statements или pg_locks. Модель не видит вашу базу — она видит только то, что вы вставили в промт.

Второе правило — проверяйте всё на стенде. Даже хороший совет про CREATE INDEX CONCURRENTLY может не подойти, если у вас репликация с логическим слотом или ограничения по памяти. И третье: просите модель объяснять, а не только давать SQL. Понимание, почему план изменился, ценнее готового индекса.

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

Официальные источники, на которые стоит опираться при проверке советов модели: документация PostgreSQL по EXPLAIN (postgresql.org/docs/current/using-explain.html), раздел про явные блокировки (explicit-locking.html), CREATE INDEX (sql-createindex.html) и pg_stat_statements (pgstatstatements.html). Держите их под рукой — и ни один промт не заведёт вас в тупик.