Медленный запрос в PostgreSQL — это не всегда проблема железа. Чаще всего это неверный план, лишний seq scan, отсутствующий индекс или JOIN, который тянет миллион строк вместо двух тысяч. И вот здесь AI-ассистент превращается в полноценного напарника по оптимизации, если задавать ему правильные промты — с реальным планом EXPLAIN, схемой таблиц и контекстом нагрузки.
Я собрал 12 промтов, которые сам использую в работе: для генерации сложных запросов, разбора планов EXPLAIN ANALYZE, работы с индексами, партиционированием и миграциями без даунтайма. Каждый промт — с примером «до/после» и пояснением, для какой задачи он подходит. Всё проверено на PostgreSQL 16, синтаксис соответствует официальной документации postgresql.org.
Важный принцип: чем больше контекста вы даёте модели (DDL таблиц, реальный план, размеры таблиц, кардинальность), тем точнее она отвечает. Промт вида «оптимизируй запрос» без данных — это гадание. Промт с планом и схемой — это инженерная работа.
1. Промт для генерации сложного запроса из бизнес-требования
Подходит, когда нужно быстро собрать аналитический запрос с CTE, оконными функциями и агрегатами.
Промт:
Ты — senior PostgreSQL-разработчик. Сгенерируй SQL-запрос для PostgreSQL 16.
Схема:
CREATE TABLE orders (id bigserial PK, user_id bigint, status text, total numeric, created_at timestamptz);
CREATE TABLE order_items (id bigserial PK, order_id bigint, product_id bigint, qty int, price numeric);
Задача: для каждого пользователя получить топ-3 товара по сумме покупок за последние 90 дней,
а также общую выручку пользователя за этот период.
Требования: только ANSI JOIN, оконная функция ROW_NUMBER, параметризация через $1 (интервал дней).
Объясни план выполнения и какие индексы нужны.
Модель вернёт запрос с ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY revenue DESC) и подскажет индексы по orders(created_at) и order_items(order_id). Пример «до»: три запроса в приложении + агрегация в Python. «После»: один запрос, одно обращение к БД.
2. Промт для чтения EXPLAIN (ANALYZE, BUFFERS)
Самый рабочий промт в моём арсенале. Всегда добавляйте BUFFERS — без него не видно I/O.
Вот план EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) для PostgreSQL 16:
<вставьте план>
Схема: <DDL>
Размеры таблиц: orders — 12 млн строк, order_items — 60 млн строк.
Задача: найди узкие места. Для каждого:
1) какой узел (Seq Scan, Nested Loop, Hash Join) и почему он дорогой;
2) оценка rows vs actual rows — где ошибка планировщика;
3) конкретное действие: индекс, переписывание запроса, ANALYZE, настройка work_mem.
Не предлагай мифических параметров — только реальные из postgresql.conf.
Ключевой сигнал в плане — расхождение rows=100 и actual rows=850000. Это значит, что статистика устарела или оценка селективности неверна. Часто лечится ANALYZE или расширенной статистикой CREATE STATISTICS.
3. Промт для оптимизации медленного JOIN
Запрос выполняется 8 секунд:
SELECT u.email, COUNT(o.id) FROM users u JOIN orders o ON o.user_id = u.id WHERE o.created_at > now() - interval '30 days' GROUP BY u.email;
План: <EXPLAIN ANALYZE>
Схема индексов: <\d users>, <\d orders>
Что делать? Предложи 3 варианта: индекс, переписывание, партиционирование.
Для каждого — ожидаемый эффект и цена (замедление вставки, место на диске).
Типичный ответ: заменить JOIN на EXISTS или LATERAL, добавить покрывающий индекс orders(user_id, created_at) INCLUDE (id). «До»: 8 сек, seq scan по orders. «После»: index-only scan, ~120 мс.
4. Промт для проектирования индексов под набор запросов
Вот 10 самых частых запросов к таблице events (200 млн строк):
<список SQL>
Предложи минимальный набор индексов (B-tree, GIN, BRIN), который покрывает их.
Для каждого индекса: определение, какие запросы он ускоряет, стоимость записи.
Отдельно укажи, где уместен BRIN вместо B-tree и почему.
BRIN хорош для append-only таблиц с корреляцией по времени: занимает в разы меньше места, чем B-tree. Для events(created_at) при монотонном росте BRIN часто выигрывает.
5. Промт для анализа блокировок и deadlock
В логах PostgreSQL: "deadlock detected".
Вот два запроса из разных транзакций:
<SQL 1>
<SQL 2>
Объясни, из-за какого порядка блокировок возникает deadlock.
Предложи исправление: единый порядок захвата, advisory lock или изменение уровня изоляции.
Покажи, как воспроизвести минимальный кейс.
Классика: транзакция A блокирует строку 1, затем 2; транзакция B — наоборот. Лечится сортировкой UPDATE ... ORDER BY id или SELECT ... FOR UPDATE в одном порядке.
6. Промт для партиционирования большой таблицы
Таблица logs (1.2 млрд строк, 400 ГБ) партиционирована по RANGE(created_at) помесячно.
Запросы фильтруют по created_at и service_name.
1) Проверь, срабатывает ли partition pruning для такого запроса: <SQL>.
2) Нужны ли локальные индексы на каждой партиции?
3) Как автоматизировать создание партиций (pg_partman vs cron)?
4) Как безопасно detach старых партиций без долгих блокировок?
Важно помнить: DETACH PARTITION в PostgreSQL 14+ может работать CONCURRENTLY, что снимает блокировку на таблицу-родителя.
7. Промт для миграции без даунтайма
Нужно добавить NOT NULL колонку tenant_id в таблицу users (50 млн строк) без блокировки записи.
PostgreSQL 16. Предложи пошаговый план миграции:
DDL, backfill батчами, добавление constraint через NOT VALID + VALIDATE, финальный шаг.
Укажи, какие шаги берут ACCESS EXCLUSIVE lock и как их минимизировать.
Схема: ADD COLUMN ... NULL → backfill батчами по 10k → ADD CONSTRAINT ... NOT VALID → VALIDATE CONSTRAINT (берёт SHARE UPDATE EXCLUSIVE, не блокирует запись) → SET NOT NULL.
8. Промт для переписывания запроса с подзапросами
Перепиши запрос, убрав коррелированные подзапросы:
SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt FROM users u;
Целевая версия: один проход по orders, GROUP BY, LEFT JOIN.
Покажи план до и после, объясни разницу в сложности (O(N*M) → O(N+M)).
Коррелированный подзапрос в SELECT часто превращается в Nested Loop с миллионами итераций. LEFT JOIN (SELECT user_id, COUNT(*) FROM orders GROUP BY user_id) даёт Hash Join и один проход.
9. Промт для работы с JSONB
Таблица events: payload jsonb. 50 млн строк.
Запрос: SELECT * FROM events WHERE payload->>'type' = 'click' AND (payload->>'ts')::timestamptz > now() - interval '1 day';
1) Почему это медленно?
2) Предложи GIN-индекс jsonb_path_ops vs jsonb_ops — что выбрать для @> и для ->>?
3) Как переписать на оператор @> и почему это быстрее?
jsonb_path_ops компактнее и быстрее для @>, но не поддерживает ?, ?|, ?&. Для фильтрации по ->> GIN не поможет — нужен expression-индекс ((payload->>'type')).
10. Промт для настройки autovacuum на горячей таблице
Таблица orders: 30 млн строк, 200k UPDATE/час, autovacuum не успевает, растёт bloat.
Предложи настройки per-table: autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay.
Объясни, как проверить эффект через pg_stat_user_tables и pgstattuple.
Для горячих таблиц: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000). Проверка: SELECT n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname='orders';
11. Промт для анализа pg_stat_statements
Вот топ-10 запросов из pg_stat_statements по total_exec_time:
<выгрузка>
Для каждого: объясни, почему он дорогой (calls × mean_time), предложи конкретное действие.
Отдельно выдели запросы с высоким stddev_exec_time — там планы нестабильны.
Высокий stddev_exec_time — сигнал, что планировщик иногда выбирает плохой план из-за параметров. Лечится plan_cache_mode = force_custom_plan или переписыванием.
12. Промт для code review SQL в пул-реквесте
Проверь SQL из PR на антипаттерны PostgreSQL:
<SQL>
Ищи: SELECT *, отсутствие LIMIT, NOT IN с NULL, функции над индексируемой колонкой,
неявные приведения типов, N+1 в ORM-стиле, отсутствие параметризации.
Для каждого — почему плохо и как исправить.
WHERE date(created_at) = '2026-09-30' убивает индекс — нужно WHERE created_at >= '2026-09-30' AND created_at < '2026-10-01'. Это одна из самых частых ошибок в проде.
Как получить максимум от этих промтов
Во-первых, всегда давайте модели DDL и реальный план. Без EXPLAIN (ANALYZE, BUFFERS) ответы будут поверхностными. Во-вторых, проверяйте рекомендации на staging — планировщик чувствителен к объёму данных, и то, что работает на 10k строк, может не работать на 10 млн. В-третьих, используйте официальную документацию PostgreSQL (postgresql.org/docs) как источник истины: параметры вроде work_mem, effective_cache_size, random_page_cost реальны и настраиваются, а вот выдуманных «магических» параметров не существует.
Эти 12 промтов закрывают 90% задач, с которыми я сталкиваюсь ежедневно: от генерации сложных запросов до разбора deadlock и миграций без даунтайма. Начните с промта №2 (чтение EXPLAIN) — он даёт самый быстрый прирост в понимании, что происходит с вашей базой. А если хотите системно прокачать работу с данными и AI-инструментами — загляните в материалы asibiont.com/blog, там есть разборы по автоматизации и инженерным практикам.
Комментарии