Вы когда-нибудь смотрели на вывод EXPLAIN ANALYZE и чувствовали, что смотрите в глаза древнему божеству? Сотни строк, непонятные аббревиатуры, цифры... А запрос всё равно выполняется 30 секунд. А теперь представьте, что у вас есть ассистент, который мгновенно переводит этот «птичий язык» на человеческий, находит узкое место и предлагает конкретный план лечения. И этот ассистент — бесплатный ИИ, который понимает вашу схему данных и историю запросов. В этой статье я собрал 11 проверенных промтов, которые превратят ChatGPT, Claude или любого другого LLM в вашего персонального DBA. Никакой воды — только готовые шаблоны, которые вы можете скопировать и вставить прямо сейчас.
Почему ИИ — лучший друг DBA
Оптимизация PostgreSQL — это не магия, а системный подход. По данным официальной документации PostgreSQL, неправильный план выполнения может замедлить запрос в десятки и сотни раз. Но чтобы исправить план, нужно сначала его понять. Именно здесь ИИ проявляет себя лучше всего: он молниеносно анализирует текстовый вывод EXPLAIN, знает типовые паттерны медленных запросов (например, Seq Scan на большой таблице вместо Index Scan) и может предложить решение, опираясь на многолетний опыт сообщества. Но есть нюанс: ИИ не знает вашей конкретной схемы и данных. Поэтому каждый промт должен быть максимально конкретным — с реальными названиями таблиц, типов данных и полным выводом EXPLAIN. Тогда ответ будет не абстрактным, а прикладным.
1. Экспресс-диагностика: «Что здесь не так?»
Когда использовать: Вы скопировали вывод EXPLAIN ANALYZE в консоль, но не хотите разбираться в нём вручную. Идеально для первого знакомства с проблемой.
Промт:
Ты — эксперт по оптимизации PostgreSQL. Вот вывод EXPLAIN ANALYZE для запроса [вставьте ваш вывод].
Проанализируй его и ответь на вопросы:
1. Какие операции занимают больше всего времени (в порядке убывания)?
2. Есть ли признаки проблем: Seq Scan на больших таблицах, Nested Loop вместо Hash Join, большие числа в rows и actual rows?
3. Оцени общее состояние запроса по шкале от 1 до 10, где 10 — оптимально.
Дай краткий ответ (не более 200 слов) в формате:
- Проблемы: ...
- Причины: ...
- Рекомендации: ...
Пример: Вы вставляете вывод для запроса, который выбирает все заказы пользователя за месяц. ИИ отвечает: «Проблема: Seq Scan по таблице orders (10 млн строк), actual rows=1, но оценка=1000. Причина: устаревшая статистика. Рекомендация: выполните ANALYZE, затем добавьте индекс (user_id, created_at)». Это займёт 30 секунд вместо часа анализа.
2. Перевод EXPLAIN на человеческий язык
Когда использовать: Когда вам нужно объяснить план выполнения коллеге, который не знаком с PostgreSQL, или просто систематизировать знания.
Промт:
Ты — технический писатель. Перепиши следующий вывод EXPLAIN ANALYZE так, чтобы его понял разработчик, знающий SQL, но не разбирающийся в планах выполнения.
[вставьте вывод]
Используй аналогии: например, «Seq Scan — это чтение всей книги подряд, а Index Scan — поиск по оглавлению». Опиши, что делает каждая операция, в каком порядке и почему именно так.
Пример: Для запроса с Hash Join вы получите объяснение: «Сначала PostgreSQL читает всю таблицу customers (Seq Scan) и строит хеш-таблицу в памяти. Затем для каждой строки из orders ищет соответствие в этой таблице. Если бы таблица customers была меньше, PostgreSQL, возможно, выбрал бы Nested Loop — это как поиск в маленьком блокноте, а не в библиотеке».
3. Поиск пропущенных индексов
Когда использовать: Вы знаете, что запрос медленный, но не уверены, какой индекс создать. Этот промт анализирует EXPLAIN и предлагает конкретные CREATE INDEX.
Промт:
Проанализируй вывод EXPLAIN ANALYZE для запроса [вставьте вывод] и предложи, какие индексы ускорят его.
Учитывай:
- Типы данных в WHERE, JOIN и ORDER BY.
- Сортировку и группировку.
- Если индекс уже используется, но всё равно медленно — возможно, нужен покрывающий индекс (INCLUDE).
Для каждого индекса напиши:
- Точный синтаксис CREATE INDEX.
- Почему он поможет.
- Ожидаемый эффект (примерно, в процентах).
Пример: Запрос фильтрует по status = 'active' и сортирует по created_at. ИИ предложит: CREATE INDEX idx_orders_status_created ON orders (status, created_at); и пояснит: «Этот индекс покроет и фильтрацию, и сортировку, что позволит избежать Sort step».
4. Оптимизация сложных JOIN
Когда использовать: Когда несколько таблиц соединяются, и план показывает Nested Loop с большим количеством итераций.
Промт:
У меня есть запрос, который соединяет таблицы [перечислите таблицы и ключи] и работает медленно. Вот вывод EXPLAIN ANALYZE: [вставьте вывод].
Какие стратегии соединения (Nested Loop, Hash Join, Merge Join) здесь используются? Какие из них неэффективны для данного объёма данных? Предложи, как переписать запрос или изменить схему (индексы, партиционирование), чтобы оптимизировать соединение. Приведи конкретный SQL.
Пример: Для трёх таблиц, где две из них содержат миллионы строк, ИИ может предложить заменить Nested Loop на Hash Join, написав: SET enable_nestloop = off; (временное решение) или добавив индекс на столбец соединения во внутренней таблице.
5. Создание оптимального индекса с учётом селективности
Когда использовать: Когда вы не уверены, какой тип индекса (B-tree, GIN, BRIN) подойдёт для ваших данных.
Промт:
Для таблицы [имя] с колонками [перечислите] и типичными запросами (например, WHERE ... AND ...) какой тип индекса лучше выбрать: B-tree, GIN, BRIN, GiST? Учитывай:
- Кардинальность (количество уникальных значений).
- Тип данных (текст, число, JSON, геометрия).
- Характер запросов (равенство, диапазон, полнотекстовый поиск).
Дай рекомендацию с обоснованием и примером CREATE INDEX.
Пример: Для колонки с датами и запросами по диапазону ИИ посоветует BRIN, если данные физически упорядочены по дате, или B-tree, если нет. Это важное различие, которое может ускорить запрос в 10 раз.
6. Рефакторинг медленного запроса
Когда использовать: Когда запрос работает, но его план ужасен, и вы хотите переписать его в более эффективную форму.
Промт:
Вот текущий запрос: [вставьте SQL]. Он выполняется [время], а вот его EXPLAIN ANALYZE: [вставьте].
Перепиши запрос так, чтобы он стал быстрее, сохраняя ту же логику. Предложи 2-3 варианта с объяснением, что именно вы изменили (например, заменил подзапрос на JOIN, добавил агрегацию на стороне сервера). Укажи, какой вариант предпочтительнее и почему.
Пример: Запрос с NOT IN (SELECT ...) ИИ перепишет в LEFT JOIN ... WHERE ... IS NULL, потому что NOT IN часто приводит к полному сканированию. В ответе будет объяснение: «NOT IN не может использовать индекс, если подзапрос возвращает NULL, поэтому лучше использовать LEFT JOIN».
7. Настройка параметров конфигурации
Когда использовать: Когда проблема не в конкретном запросе, а в глобальной конфигурации PostgreSQL.
Промт:
На основе следующих параметров моего сервера PostgreSQL: [перечислите key параметры, например, shared_buffers, work_mem, effective_cache_size], и типичной нагрузки (OLTP/OLAP, размер БД), посоветуй, какие значения стоит изменить и на что.
Для каждого параметра дай:
- Текущее значение (если есть).
- Рекомендуемое значение.
- Почему это поможет.
- Риски (например, нехватка памяти).
Пример: Если work_mem слишком мал, PostgreSQL будет использовать временные файлы на диске для сортировки, что резко замедляет запрос. ИИ подскажет: «Увеличьте work_mem до 64MB, но следите, чтобы общее потребление памяти не превысило 25% от RAM».
8. Партиционирование больших таблиц
Когда использовать: Когда таблица разрослась до сотен миллионов строк, и даже индексы не помогают.
Промт:
У меня таблица [имя] содержит [кол-во] строк, и запросы по [дата/ключ] стали медленными. Я рассматриваю партиционирование.
Опиши:
- Подходит ли партиционирование для моего случая.
- Какой тип: RANGE, LIST, HASH.
- Конкретный SQL для создания партиционированной таблицы с переносом данных.
- Как изменятся запросы и индексы.
- Какие подводные камни (например, update партиционирования, глобальные индексы).
Пример: Для таблицы логов по месяцам ИИ предложит RANGE-партиционирование по created_at, создаст партиции на каждый месяц и покажет, как создать индекс на каждой партиции отдельно.
9. Анализ и оптимизация запросов с JSONB
Когда использовать: Когда вы используете JSONB-колонки и замечаете, что запросы с @> или -> работают медленно.
Промт:
У меня есть таблица [имя] с колонкой JSONB. Вот типичный запрос: [вставьте SQL]. Он выполняется [время]. Вот EXPLAIN ANALYZE: [вставьте].
Какой тип индекса (GIN, GiST) лучше использовать для ускорения? С учётом того, что данные содержат [например, массив тегов], предложи конкретный CREATE INDEX. Также посоветуй, как переписать запрос (например, использовать jsonb_path_ops).
Пример: Для запроса WHERE data @> '{"tags": ["postgres"]}' ИИ порекомендует CREATE INDEX ON table USING GIN (data jsonb_path_ops); и объяснит, что jsonb_path_ops уменьшает размер индекса и ускоряет поиск по ключам.
10. Оптимизация запросов с LIKE и полнотекстовым поиском
Когда использовать: Когда вы используете LIKE '%text%', который не использует обычный B-tree индекс.
Промт:
У меня есть запрос: [вставьте SQL] с оператором LIKE. Он медленный, потому что не использует индекс. Что мне сделать?
Рассмотри варианты:
- pg_trgm для GIN-индекса.
- Переписать запрос на полнотекстовый поиск (tsvector).
- Использовать COLLATE "C" для B-tree.
Дай конкретные SQL-команды для каждого варианта и объясни, когда какой применять.
Пример: Для LIKE '%postgres%' ИИ предложит создать расширение pg_trgm и индекс CREATE INDEX ON table USING GIN (column gin_trgm_ops);, что ускорит поиск в разы.
11. Диагностика блокировок и взаимоблокировок
Когда использовать: Когда ваши запросы зависают из-за блокировок (locks) или вы получаете ошибки deadlock.
Промт:
У меня возникают блокировки в PostgreSQL. Вот список активных запросов из pg_stat_activity: [вставьте вывод].
Определи:
- Какие запросы блокируют друг друга.
- Какие типы блокировок (RowExclusiveLock, ShareLock и т.д.) участвуют.
- Как решить проблему: добавить индекс, изменить порядок операций, использовать SELECT ... FOR UPDATE SKIP LOCKED.
- Дай SQL-запросы для мониторинга блокировок.
Пример: ИИ увидит, что два запроса обновляют строки в разном порядке, и посоветует всегда обновлять в одном порядке или использовать SKIP LOCKED для очередей.
12. Мониторинг и алерты с помощью pg_stat_statements
Когда использовать: Когда нужно выявить самые медленные запросы за период времени.
Промт:
Используя pg_stat_statements, напиши запрос, который покажет топ-10 самых медленных запросов (по среднему времени) за последний день. Также включи количество вызовов, общее время, долю от общего времени. Дай пояснение, как интерпретировать результаты.
Пример: ИИ выдаст SQL:
SELECT query, calls, total_exec_time::numeric(10,2) as total_ms, mean_exec_time::numeric(10,2) as mean_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
И посоветует периодически очищать статистику, чтобы видеть свежие данные.
13. Оптимизация запросов с оконными функциями
Когда использовать: Когда запрос с ROW_NUMBER(), LAG() и другими оконными функциями работает медленно.
Промт:
Вот мой запрос с оконной функцией: [вставьте SQL]. Он обрабатывает [кол-во] строк и работает [время]. Вот EXPLAIN ANALYZE: [вставьте].
Почему используется Seq Scan, и как добавить индекс, чтобы ускорить окно (PARTITION BY, ORDER BY)? Есть ли альтернативы, например, использование LATERAL JOIN или материализованного представления?
Пример: Для ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) ИИ посоветует индекс (user_id, created_at), чтобы оконная функция могла читать данные уже отсортированными.
14. Визуализация плана выполнения
Когда использовать: Когда текстовый EXPLAIN сложно читать, и вы хотите визуальную схему.
Промт:
Сгенерируй ASCII-диаграмму (или описание в Mermaid) для следующего плана выполнения: [вставьте вывод]. Покажи иерархию операций, стрелки, и подпиши время выполнения каждой операции. Это поможет мне увидеть узкое место.
Пример: ИИ нарисует дерево с корнем Hash Join, от которого идут ветви Seq Scan и Index Scan, и подпишет время. Это наглядно показывает, что основной вклад вносит Seq Scan.
Итоги
Эти 14 промтов — ваш арсенал для борьбы с медленными запросами. Начните с первого, чтобы быстро понять проблему, а затем используйте остальные для глубокой оптимизации. Помните: ИИ — это инструмент, который усиливает ваши знания, но не заменяет их. Всегда проверяйте предложенные решения на тестовой среде, прежде чем применять в проде. И не забывайте про EXPLAIN (ANALYZE, BUFFERS) — это даст ещё больше информации. А если вы хотите поделиться своими промтами или получить обратную связь от сообщества, загляните в наш блог — там мы обсуждаем практические кейсы. Удачной оптимизации!
Комментарии