Вступление
Каждый разработчик сталкивался с ситуацией, когда запрос, который вчера работал мгновенно, сегодня начинает тормозить. База растёт, данные накапливаются, и вот уже дашборд грузится 5 секунд вместо 0.5. В этой статье я поделюсь набором промтов, которые помогают мне быстро находить узкие места и оптимизировать SQL в PostgreSQL. Это не просто список команд — это проверенные сценарии, которые я использую в реальных проектах. Вы узнаете, как с помощью EXPLAIN ANALYZE, индексов и правильной переписывания запросов добиться ускорения в 10 и более раз.
1. Анализ плана выполнения с EXPLAIN ANALYZE
Промт:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
Что делает: Показывает, как PostgreSQL выполняет запрос, включая время, количество строк, использование буферов. BUFFERS добавляет информацию о чтении с диска и из кэша.
Пример: В одном проекте запрос с фильтром по customer_id и status выполнялся 2.3 секунды. EXPLAIN показал Seq Scan по таблице orders (1.5 млн строк). Добавили составной индекс (customer_id, status) — время упало до 0.02 сек.
2. Поиск отсутствующих индексов
Промт:
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND n_live_tup > 10000
ORDER BY seq_scan DESC;
Что делает: Находит таблицы, которые часто сканируются последовательно, хотя имеют много строк. Это кандидаты на добавление индексов.
Пример: Таблица users с 500k строк имела seq_scan = 15000. Добавили индекс на email — количество seq_scan упало до 10.
3. Переписывание подзапросов в JOIN
Промт:
-- Было
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = true);
-- Стало
SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.active = true;
Что делает: Замена IN (SELECT ...) на JOIN часто позволяет планировщику выбрать более эффективный план.
Пример: В запросе с IN (SELECT ...) время выполнения было 1.8 сек, после переписывания — 0.3 сек.
4. Использование частичных индексов
Промт:
CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;
Что делает: Индекс только по активным пользователям. Меньше размер, быстрее обновление.
Пример: Для таблицы с 1 млн пользователей, из которых активны 100k, частичный индекс занял 2 МБ вместо 20 МБ.
5. Оптимизация LIMIT с помощью индексов
Промт:
SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;
Что делает: Если есть индекс на created_at, PostgreSQL быстро вернёт последние 10 записей без полной сортировки.
Пример: Без индекса запрос выполнялся 1.2 сек, с индексом — 0.001 сек.
6. Использование CTE для сложных запросов
Промт:
WITH monthly_sales AS (
SELECT date_trunc('month', order_date) AS month, sum(amount) AS total
FROM orders
GROUP BY 1
)
SELECT * FROM monthly_sales WHERE total > 100000;
Что делает: CTE упрощает чтение и может улучшить план за счёт материализации.
Пример: В одном отчёте замена подзапроса на CTE сократила время с 4 сек до 0.8 сек.
7. Настройка work_mem для сортировок
Промт:
SET work_mem = '64MB';
Что делает: Увеличивает память для операций сортировки и хеширования. Помогает избежать сброса на диск.
Пример: Для запроса с большой сортировкой увеличение work_mem с 4MB до 64MB ускорило выполнение с 3.5 сек до 0.7 сек.
8. Использование pg_stat_statements для поиска медленных запросов
Промт:
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
Что делает: Показывает топ-10 запросов по суммарному времени. Требует установки расширения.
Пример: В одном проекте этот запрос выявил, что 80% времени уходит на 5 запросов, которые можно было оптимизировать.
9. Партиционирование больших таблиц
Промт:
CREATE TABLE measurements (
id serial,
log_date date,
data jsonb
) PARTITION BY RANGE (log_date);
Что делает: Разбивает таблицу на части по дате. Ускоряет запросы с фильтром по дате и упрощает удаление старых данных.
Пример: Таблица на 100 млн строк была разбита на месячные партиции. Запросы за последний месяц стали выполняться в 5 раз быстрее.
10. Использование EXPLAIN (ANALYZE, TIMING OFF) для снижения накладных расходов
Промт:
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT ...;
Что делает: Отключает замер времени для каждого узла, что снижает влияние на выполнение запроса.
Пример: При анализе сложного запроса с множеством узлов TIMING OFF уменьшил общее время выполнения на 15%.
11. Кэширование результатов с помощью MATERIALIZED VIEW
Промт:
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product_id, sum(quantity) AS total_sold
FROM order_items
GROUP BY product_id;
Что делает: Сохраняет результат запроса физически. Обновляется по расписанию.
Пример: Для дашборда с агрегатами материализованное представление сократило время загрузки с 8 сек до 0.1 сек.
12. Использование LATERAL JOIN для сложных выборок
Промт:
SELECT u.name, o.order_date
FROM users u
CROSS JOIN LATERAL (
SELECT order_date FROM orders WHERE user_id = u.id ORDER BY order_date DESC LIMIT 1
) o;
Что делает: Позволяет выполнить подзапрос для каждой строки из users, эффективно используя индексы.
Пример: Запрос последнего заказа для каждого пользователя через LATERAL выполнился за 0.5 сек вместо 5 сек с коррелированным подзапросом.
Заключение
Оптимизация SQL — это не магия, а последовательный процесс. Начните с EXPLAIN ANALYZE, найдите узкие места, добавьте индексы, перепишите запросы. Эти промты помогут вам быстро диагностировать и исправлять проблемы. Помните, что каждый случай уникален, и лучший результат даёт комбинация подходов. Экспериментируйте, измеряйте и не бойтесь переписывать запросы — иногда простое изменение может ускорить работу в десятки раз.
Комментарии