12 battle-tested AI prompts to fix slow PostgreSQL JOINs and decode EXPLAIN ANALYZE plans

Запросы, которые не падают: 12 промтов для SQL и PostgreSQL — от оптимизации JOIN до анализа EXPLAIN ANALYZE

Медленный запрос — это не просто раздражающий Seq Scan в логах. Это ночные дежурства, деградация API, раздутые счета за облако и тихая ненависть аналитиков к дашбордам, которые грузятся дольше, чем варится кофе. PostgreSQL — одна из самых мощных реляционных СУБД, но именно поэтому она умеет наказывать за невнимательность: неверный JOIN, отсутствующий индекс или N+1 в ORM превращают миллисекунды в минуты.

AI-ассистент здесь работает как опытный DBA, который всегда под рукой: он не «думает за вас», но резко сокращает путь от «почему это тормозит?» до «вот рабочий план и индекс». По данным Stack Overflow Developer Survey, PostgreSQL много лет подряд удерживает статус самой используемой и самой любимой реляционной СУБД среди разработчиков — а значит, спрос на грамотную работу с ней только растёт. Ниже — 12 промтов, проверенных на реальных проектах: генерация сложных запросов, разбор планов EXPLAIN ANALYZE, миграции без даунтайма, партиционирование и индексы. Каждый — с задачей, примером «до/после» и пояснением, когда его применять.

Эта подборка не про «магию» — она про конкретику. Все примеры синтаксиса соответствуют официальной документации PostgreSQL (postgresql.org/docs).

1. Генерация сложного аналитического запроса из бизнес-требования

Задача: превратить описание на человеческом языке (или ТЗ от аналитика) в корректный SQL с CTE и оконными функциями.

Промт:

Ты — senior PostgreSQL-разработчик. Напиши один SQL-запрос (PostgreSQL 15+) для следующей задачи:
«Для каждого клиента найти сумму заказов за последние 12 месяцев, средний чек, и его место в рейтинге по выручке внутри своего региона. Показать только тех, у кого больше 3 заказов».
Требования: используй CTE (WITH), оконные функции, явные JOIN, алиасы без однобуквенных имён. Объясни каждую часть запроса. Учти NULL в суммах.

До: аналитик три часа собирает запрос вручную, забывает про NULL и получает завышенные средние.

После:

WITH recent_orders AS (
  SELECT o.customer_id,
         SUM(o.total_amount) AS revenue,
         AVG(o.total_amount) AS avg_check,
         COUNT(*) AS orders_count
  FROM orders o
  WHERE o.created_at >= NOW() - INTERVAL '12 months'
  GROUP BY o.customer_id
  HAVING COUNT(*) > 3
)
SELECT c.id, c.region, ro.revenue, ro.avg_check,
       RANK() OVER (PARTITION BY c.region ORDER BY ro.revenue DESC) AS region_rank
FROM recent_orders ro
JOIN customers c ON c.id = ro.customer_id;

Когда применять: построение отчётов, метрик, ad-hoc аналитики. Всегда просите объяснение — это ловит логические ошибки до запуска.

2. Оптимизация медленного JOIN: от 40 секунд до 200 мс

Задача: разобрать тяжёлый JOIN и предложить переписывание.

Промт:

Вот запрос, который выполняется 40 секунд на таблицах orders (12 млн строк) и customers (800 тыс. строк).
Предложи 3 варианта оптимизации: (1) переписать JOIN, (2) индексы, (3) изменить структуру.
Для каждого — ожидаемый эффект и почему.

SELECT * FROM orders o, customers c
WHERE o.customer_id = c.id AND c.country = 'DE' AND o.status = 'paid';

Ключевая проблема: неявный CROSS JOIN-синтаксис, SELECT *, отсутствие индекса на orders(status, customer_id).

После:

CREATE INDEX CONCURRENTLY idx_orders_status_customer ON orders (status, customer_id) INCLUDE (total_amount);

SELECT o.id, o.total_amount, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid';

Результат: планировщик уходит от Nested Loop на 12 млн строк к Hash Join с индексным сканированием — типичный выигрыш в десятки раз. Когда применять: любой запрос дольше SLA.

3. Чтение EXPLAIN ANALYZE: перевод плана на человеческий

Задача: понять, где именно теряется время.

Промт:

Вот вывод EXPLAIN (ANALYZE, BUFFERS). Объясни простыми словами: где узкое место, что означает каждый узел, какие строки «оценены vs фактические», и что делать.

Hash Join  (cost=12000.00..45000.00 rows=1000 width=64) (actual time=800.5..3200.2 rows=98000 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=1200 read=45000
  -> Seq Scan on orders o (actual time=0.02..2500.0 rows=98000 loops=1)

Что вы увидите: расхождение rows=1000 (оценка) и rows=98000 (факт) — классический сигнал устаревшей статистики.

Действие:

ANALYZE orders;
-- или для точечной настройки:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

Когда применять: первым делом при любом расследовании производительности. Всегда используйте EXPLAIN (ANALYZE, BUFFERS) — BUFFERS показывает I/O.

4. Поиск и устранение N+1 в ORM-коде

Задача: обнаружить проблему в коде приложения, а не в SQL.

Промт:

Это Django ORM код. Найди N+1 проблему, покажи, сколько запросов он генерирует, и перепиши с select_related/prefetch_related.

for order in Order.objects.all():
    print(order.customer.name, order.items.count())

После:

Order.objects.select_related('customer').prefetch_related('items')

Результат: вместо 1 + N + N запросов — 3. Когда применять: профайлинг API, жалобы на «медленный список заказов».

5. Миграция без даунтайма: добавление колонки и индекса

Задача: безопасно менять схему на живой базе.

Промт:

Составь план миграции без блокировок для PostgreSQL 15: добавить NOT NULL колонку region в таблицу customers (5 млн строк) и индекс на неё. Учти блокировки и откат.

Правильная последовательность:

ALTER TABLE customers ADD COLUMN region text;
ALTER TABLE customers ADD COLUMN region text DEFAULT 'unknown';
-- backfill батчами, затем:
ALTER TABLE customers ALTER COLUMN region SET NOT NULL;
CREATE INDEX CONCURRENTLY idx_customers_region ON customers (region);

Важно: CREATE INDEX CONCURRENTLY не блокирует запись, но не работает внутри транзакции. Когда применять: любая миграция на проде.

6. Партиционирование большой таблицы

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

Промт:

Предложи схему декларативного партиционирования (RANGE по created_at) для таблицы events (200 млн строк, растёт на 5 млн/мес). Покажи DDL, создание партиций и автоматизацию.

Пример:

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Когда применять: таблицы > 100 млн строк, ретеншн-политики, где старые партиции можно просто DETACH.

7. Индексы: подбор вместо «на всякий случай»

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

Промт:

По этим 5 запросам предложи минимальный набор индексов. Для каждого индекса укажи, какой запрос он ускоряет, и предупреди о цене записи.

Практика: частичные и covering-индексы:

CREATE INDEX idx_orders_active ON orders (customer_id) WHERE status = 'pending';
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total_amount, created_at);

Когда применять: когда pg_stat_user_indexes показывает индексы с idx_scan = 0 — их стоит удалить.

8. Диагностика блокировок и deadlock

Задача: найти, кто кого блокирует.

Промт:

Объясни, как найти блокирующие транзакции в PostgreSQL, и напиши запрос к pg_locks + pg_stat_activity, который покажет цепочку ожиданий.

Запрос:

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.relation = bl.relation AND kl.pid <> bl.pid AND NOT kl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;

Когда применять: приложения «зависают» без ошибок.

9. Оптимизация запросов с JSONB

Задача: ускорить фильтрацию по JSON-полю.

Промт:

Запрос фильтрует по payload->>'status' в jsonb-колонке. Как ускорить: GIN-индекс, expression-индекс или извлечь в колонку? Обоснуй.

Решение:

CREATE INDEX idx_payload_status ON events ((payload->>'status'));
-- или для containment:
CREATE INDEX idx_payload_gin ON events USING GIN (payload jsonb_path_ops);

Когда применять: события, метаданные, гибкие схемы.

10. Рефакторинг SQL: от подзапросов к оконным функциям

Промт:

Перепиши коррелированный подзапрос с оконной функцией. Объясни, почему оконная функция быстрее (один проход vs N проходов).

До: SELECT *, (SELECT MAX(x) FROM t2 WHERE t2.id = t1.id) FROM t1;

После: SELECT *, MAX(x) OVER (PARTITION BY id) FROM t1 JOIN t2 USING (id);

Когда применять: отчёты с повторяющейся агрегацией.

11. Настройка autovacuum и bloat

Промт:

Таблица сильно «распухла» (bloat). Объясни, как проверить через pg_stat_user_tables, и предложи настройки autovacuum для таблицы с интенсивной записью.

Практика:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);

Когда применять: рост размера таблицы при стабильном числе строк.

12. Генерация тестовых данных и бенчмарков

Промт:

Сгенерируй SQL для 1 млн реалистичных строк в таблицу orders (PostgreSQL), используя generate_series, random() и коррелированные customer_id. Затем — запрос для сравнительного бенчмарка до/после индекса.

Пример:

INSERT INTO orders (customer_id, total_amount, status, created_at)
SELECT (random()*800000)::int, (random()*1000)::numeric(10,2),
       (ARRAY['paid','pending','cancelled'])[floor(random()*3+1)],
       NOW() - (random()*INTERVAL '365 days')
FROM generate_series(1, 1000000);

Когда применять: проверка гипотез оптимизации до продакшена.

Как пользоваться этой подборкой

Главное правило: AI не заменяет EXPLAIN ANALYZE и здравый смысл — он ускоряет итерации. Всегда проверяйте предложенный SQL на тестовой копии, сверяйтесь с официальной документацией PostgreSQL, и не забывайте про ANALYZE после массовых изменений. Начните с промта №3 — умение читать план запроса окупается быстрее всего остального.