Ускорение SQL-запросов в 100 раз: оптимизация PostgreSQL для CRM-системы

К нам обратилась компания СиАрЭм — разработчик CRM-системы для B2B-продаж. Их продукт используют 340 компаний, в базе 28 миллионов контактов и 4.5 миллиона сделок. PostgreSQL 15, один сервер: 32 vCPU, 128 GB RAM, NVMe SSD.

Жалоба была конкретной: «Когда менеджеры запускают отчёт по воронке продаж, база встаёт на 30-40 секунд. В это время CRM тормозит для всех 2 000 пользователей».

Подключились к серверу и первым делом проверили текущую нагрузку:

-- Активные запросы прямо сейчас
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- pid  | duration | state  | wait_event
-- 4521 | 00:00:37 | active | DataFileRead
-- 4522 | 00:00:34 | active | DataFileRead
-- 4530 | 00:00:28 | active | LWLock:BufferMapping

Три запроса висели по 28-37 секунд, все ждали чтения данных с диска. Это означало, что запросы читали гораздо больше данных, чем помещалось в shared_buffers. Мы нашли основного виновника — запрос отчёта по воронке продаж, который выполнялся 42 секунды.

EXPLAIN ANALYZE — главный инструмент оптимизации SQL. Он показывает не только план, который выбрал планировщик, но и реальное время выполнения каждого узла. Запустили проблемный запрос:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    s.stage_name,
    COUNT(d.id) AS deal_count,
    SUM(d.amount) AS total_amount,
    AVG(d.amount) AS avg_amount,
    AVG(EXTRACT(EPOCH FROM (d.moved_at - d.created_at))) AS avg_time_in_stage
FROM deals d
JOIN stages s ON s.id = d.stage_id
JOIN companies c ON c.id = d.company_id
JOIN users u ON u.id = d.assigned_to
WHERE d.created_at >= '2025-01-01'
  AND d.created_at < '2026-01-01'
  AND c.industry = 'IT'
  AND d.is_deleted = false
GROUP BY s.stage_name
ORDER BY s.sort_order;

Результат EXPLAIN ANALYZE (сокращённо):

Sort  (cost=892345.67..892345.72 rows=8 width=64) (actual time=42134.521..42134.525 rows=8 loops=1)
  Sort Key: s.sort_order
  ->  HashAggregate  (cost=892345.10..892345.55 rows=8 width=64) (actual time=42134.489..42134.501 rows=8 loops=1)
        ->  Hash Join  (cost=4521.33..891234.56 rows=148072 width=32) (actual time=89.442..41987.331 rows=156423 loops=1)
              ->  Hash Join  (cost=4123.11..889012.34 rows=312456 width=40) (actual time=78.123..41876.221 rows=312456 loops=1)
                    ->  Seq Scan on deals d  (cost=0.00..884567.89 rows=1245678 width=44) (actual time=0.023..40234.112 rows=1245678 loops=1)
                          Filter: (NOT is_deleted AND created_at >= ... AND created_at < ...)
                          Rows Removed by Filter: 3254322
                          Buffers: shared hit=12345 read=567890
                    ->  Hash  (cost=3456.78..3456.78 rows=45678 width=8) (actual time=67.123..67.123 rows=45678 loops=1)
                          ->  Seq Scan on companies c  (cost=0.00..3456.78 rows=45678 width=8) (actual time=0.012..45.678 rows=45678 loops=1)
                                Filter: (industry = 'IT')
Planning Time: 2.345 ms
Execution Time: 42135.012 ms

Ключевые проблемы, которые мы увидели:

• Seq Scan on deals — полное сканирование таблицы в 4.5 миллиона строк (40 секунд!). Прочитано 567 890 страниц с диска
• Rows Removed by Filter: 3 254 322 — из 4.5M строк PostgreSQL прочитал ВСЕ и отбросил 3.2M. Это 72% бесполезного ввода-вывода
• Seq Scan on companies — ещё одно последовательное сканирование
• Hash Join — вынужден строить хеш-таблицу в памяти на 1.2M строк

Первое и самое эффективное — создать индексы, которые позволят PostgreSQL читать только нужные строки вместо полного сканирования.

Проверили существующие индексы:

-- Какие индексы есть на таблице deals?
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'deals';

-- indexname        | indexdef
-- deals_pkey       | CREATE UNIQUE INDEX deals_pkey ON deals (id)
-- deals_company_id | CREATE INDEX deals_company_id ON deals (company_id)
-- Всего 2 индекса на таблицу с 4.5M строк! Индекса по created_at нет.

Создали составной индекс, покрывающий условия фильтрации запроса:

-- Составной индекс: created_at + is_deleted + включение нужных колонок
CREATE INDEX CONCURRENTLY idx_deals_created_active
    ON deals (created_at, is_deleted)
    INCLUDE (stage_id, company_id, assigned_to, amount, moved_at)
    WHERE is_deleted = false;

-- CONCURRENTLY — создание без блокировки таблицы (production!)
-- INCLUDE — covering index, все нужные колонки в индексе (Index-Only Scan)
-- WHERE is_deleted = false — partial index, только активные записи

-- Индекс для фильтра по industry
CREATE INDEX CONCURRENTLY idx_companies_industry
    ON companies (industry)
    INCLUDE (id);

Проверяем размер новых индексов:

SELECT pg_size_pretty(pg_relation_size('idx_deals_created_active'));
-- 312 MB (вместо 2.1 GB полной таблицы)

SELECT pg_size_pretty(pg_relation_size('idx_companies_industry'));
-- 14 MB

Обновили статистику и запустили запрос снова:

ANALYZE deals;
ANALYZE companies;

-- Повторный EXPLAIN ANALYZE
Index Scan using idx_deals_created_active on deals d
    (cost=0.56..45678.90 rows=156423 width=44)
    (actual time=0.034..1245.678 rows=156423 loops=1)
      Index Cond: (created_at >= ... AND created_at < ...)
      Buffers: shared hit=34567 read=1234

-- Время: 42 сек → 4.2 сек (10x ускорение)

Уже 10x ускорение, но 4.2 секунды для отчёта — всё ещё много. Идём дальше.

Оригинальный запрос джойнил 4 таблицы, хотя данные из users вообще не использовались в выводе (JOIN был лишний — наследие рефакторинга). Кроме того, фильтр по companies.industry можно перенести в подзапрос:

-- Оптимизированная версия: убрали лишний JOIN, переписали фильтрацию
SELECT 
    s.stage_name,
    COUNT(d.id) AS deal_count,
    SUM(d.amount) AS total_amount,
    AVG(d.amount) AS avg_amount,
    AVG(EXTRACT(EPOCH FROM (d.moved_at - d.created_at))) AS avg_time_in_stage
FROM deals d
JOIN stages s ON s.id = d.stage_id
WHERE d.created_at >= '2025-01-01'
  AND d.created_at < '2026-01-01'
  AND d.is_deleted = false
  AND d.company_id IN (
      SELECT id FROM companies WHERE industry = 'IT'
  )
GROUP BY s.stage_name, s.sort_order
ORDER BY s.sort_order;

Подзапрос с IN вместо JOIN позволил планировщику использовать Semi Join, который останавливается при первом совпадении. Но ещё лучше — использовать EXISTS:

-- EXISTS эффективнее IN для больших подзапросов
WHERE d.company_id IN (SELECT id FROM companies WHERE industry = 'IT')
-- заменяем на:
WHERE EXISTS (
    SELECT 1 FROM companies c 
    WHERE c.id = d.company_id AND c.industry = 'IT'
)

Для отчётов, которые агрегируют данные по нескольким измерениям, мы переписали вложенные подзапросы в CTE с LATERAL JOIN:

-- Было: вложенные подзапросы (выполняются для каждой строки)
SELECT d.*,
    (SELECT COUNT(*) FROM activities a WHERE a.deal_id = d.id) AS activity_count,
    (SELECT MAX(a.created_at) FROM activities a WHERE a.deal_id = d.id) AS last_activity
FROM deals d;
-- Время: 18 секунд (correlated subquery для каждой из 4.5M строк)

-- Стало: LATERAL JOIN (выполняется один раз)
SELECT d.*, act.activity_count, act.last_activity
FROM deals d
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS activity_count,
           MAX(created_at) AS last_activity
    FROM activities a
    WHERE a.deal_id = d.id
) act ON true;
-- Время: 2.1 секунды

-- Ещё лучше: предварительная агрегация через CTE
WITH deal_activities AS (
    SELECT deal_id,
           COUNT(*) AS activity_count,
           MAX(created_at) AS last_activity
    FROM activities
    GROUP BY deal_id
)
SELECT d.*, da.activity_count, da.last_activity
FROM deals d
LEFT JOIN deal_activities da ON da.deal_id = d.id;
-- Время: 0.8 секунды

Результат после оптимизации JOIN: 4.2 сек → 0.9 сек. Уже 47x ускорение от исходных 42 секунд.

Для часто используемых срезов данных мы создали partial indexes — индексы, которые покрывают только подмножество строк:

-- Partial index: только активные сделки текущего года
-- Размер: 45 MB вместо 312 MB для полного индекса
CREATE INDEX CONCURRENTLY idx_deals_2025_active
    ON deals (created_at, stage_id, company_id)
    INCLUDE (amount, moved_at)
    WHERE is_deleted = false
      AND created_at >= '2025-01-01';

-- Partial index: только открытые сделки (hot data)
CREATE INDEX CONCURRENTLY idx_deals_open
    ON deals (assigned_to, stage_id)
    INCLUDE (amount, company_id, created_at)
    WHERE is_deleted = false
      AND closed_at IS NULL;

-- Partial index для поиска дубликатов контактов
CREATE INDEX CONCURRENTLY idx_contacts_email_active
    ON contacts (lower(email))
    WHERE is_deleted = false
      AND email IS NOT NULL;

Для тяжёлых аналитических отчётов, которые запускают 50+ раз в день, создали материализованные представления:

-- Материализованное представление: воронка продаж по месяцам
CREATE MATERIALIZED VIEW mv_sales_funnel AS
SELECT 
    date_trunc('month', d.created_at) AS month,
    s.stage_name,
    s.sort_order,
    c.industry,
    COUNT(d.id) AS deal_count,
    SUM(d.amount) AS total_amount,
    AVG(d.amount) AS avg_amount,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.amount) AS median_amount,
    AVG(EXTRACT(EPOCH FROM (d.moved_at - d.created_at)) / 86400) AS avg_days_in_stage
FROM deals d
JOIN stages s ON s.id = d.stage_id
JOIN companies c ON c.id = d.company_id
WHERE d.is_deleted = false
GROUP BY 1, 2, 3, 4
WITH DATA;

-- Индекс на materialized view
CREATE UNIQUE INDEX idx_mv_funnel
    ON mv_sales_funnel (month, stage_name, industry);

-- Обновление по расписанию (каждые 15 минут)
-- В cron или pg_cron:
SELECT cron.schedule('refresh_funnel', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_funnel');

Запрос к materialized view вместо исходных таблиц:

-- Было: 42 секунды (исходный запрос)
-- Стало: 3 мс (из materialized view)
SELECT stage_name, deal_count, total_amount, avg_amount, avg_days_in_stage
FROM mv_sales_funnel
WHERE month >= '2025-01-01'
  AND month < '2026-01-01'
  AND industry = 'IT'
ORDER BY sort_order;

-- Время: 0.003 секунды. Ускорение: 14 000x

Оптимизировать один запрос — полдела. Нужно найти все проблемные запросы. Для этого есть расширение pg_stat_statements:

-- Включаем расширение (требует перезагрузки PostgreSQL)
-- postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.track_io_timing = on  # Важно для анализа I/O

-- После перезагрузки:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Сбрасываем статистику для чистого замера
SELECT pg_stat_statements_reset();

Через 24 часа собрали статистику и нашли Top-10 проблемных запросов:

-- Top-10 по суммарному времени выполнения
SELECT 
    substring(query, 1, 80) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows,
    round((shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- short_query                                        | calls  | total_ms    | avg_ms  | max_ms    | cache_hit_pct
-- SELECT d.*, (SELECT COUNT(*) FROM activities...     | 12456  | 224208000   | 18000   | 45000     | 34.5
-- SELECT s.stage_name, COUNT(d.id)...                 | 8934   | 375726000   | 42067   | 68000     | 28.1
-- SELECT c.*, array_agg(t.name) FROM contacts c...    | 45678  | 182712000   | 4000    | 12000     | 41.2
-- UPDATE deals SET updated_at = NOW() WHERE id...     | 234567 | 117283500   | 500     | 8000      | 89.3

Колонка cache_hit_pct — процент попаданий в shared_buffers. Значение 28-34% для top-запросов означало, что 66-72% данных читались с диска — катастрофа для NVMe SSD, не говоря о HDD.

Мы также проверили настройки PostgreSQL:

-- Текущие настройки (было)
SHOW shared_buffers;        -- 128MB  (!!!) при 128GB RAM
SHOW work_mem;              -- 4MB    (дефолт)
SHOW effective_cache_size;  -- 4GB    (дефолт)
SHOW random_page_cost;      -- 4      (для HDD, не SSD!)

-- Исправленные настройки
ALTER SYSTEM SET shared_buffers = '32GB';          -- 25% от RAM
ALTER SYSTEM SET effective_cache_size = '96GB';    -- 75% от RAM
ALTER SYSTEM SET work_mem = '256MB';               -- для сортировок
ALTER SYSTEM SET maintenance_work_mem = '2GB';     -- для VACUUM/CREATE INDEX
ALTER SYSTEM SET random_page_cost = 1.1;           -- NVMe SSD
ALTER SYSTEM SET effective_io_concurrency = 200;   -- NVMe SSD
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_worker_processes = 16;

-- Перезагрузка
SELECT pg_reload_conf();  -- для параметров без restart
-- shared_buffers требует restart:
sudo systemctl restart postgresql

После настройки shared_buffers cache hit ratio вырос с 34% до 97% для основных запросов.

Финальный EXPLAIN ANALYZE оптимизированного запроса:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT stage_name, deal_count, total_amount, avg_amount, avg_days_in_stage
FROM mv_sales_funnel
WHERE month >= '2025-01-01' AND month < '2026-01-01' AND industry = 'IT'
ORDER BY sort_order;

-- Index Scan using idx_mv_funnel on mv_sales_funnel
--   (cost=0.28..8.45 rows=8 width=64)
--   (actual time=0.021..0.034 rows=8 loops=1)
--   Index Cond: (month >= ... AND month < ... AND industry = 'IT')
--   Buffers: shared hit=4
-- Planning Time: 0.089 ms
-- Execution Time: 0.051 ms

Сводная таблица оптимизаций:

Помимо конкретного запроса, мы оптимизировали все 10 запросов из top pg_stat_statements. Общий эффект: среднее время ответа CRM сократилось с 1.8 секунды до 0.12 секунды, а жалобы на «зависания при отчётах» полностью прекратились.

Рекомендации для оптимизации PostgreSQL-запросов от инженеров itfresh.ru:

• Всегда начинайте с EXPLAIN (ANALYZE, BUFFERS) — это рентген вашего запроса
• Seq Scan на таблице > 100K строк — почти всегда проблема. Добавьте индекс
• Проверяйте random_page_cost — если у вас SSD, а там стоит 4 (дефолт для HDD), планировщик избегает индексов
• pg_stat_statements должен быть включён на каждом production-сервере
• Materialized views — отличный способ ускорить аналитические запросы без изменения кода приложения