Оптимизация базы данных

PostgreSQL: индексы, EXPLAIN и медленные запросы

Алексей В. • 24 Октября 2024 PostgreSQL • 8 мин чтения
Консоль терминала с запросами PostgreSQL

Медленные запросы — это боль каждого разработчика, работающего с реляционными базами данных. В этой статье мы разберём, почему PostgreSQL может тормозить, и как превратить запрос за 8 секунд в запрос за 40 мс с помощью правильных индексов и анализа плана выполнения.

1. Чтение EXPLAIN ANALYZE

Не доверяйте только EXPLAIN

Команда EXPLAIN ANALYZE показывает не только теоретический план, но и реальное время выполнения каждой операции. Это ключевой инструмент для профилирования производительности. 

EXPLAIN ANALYZE SELECT * FROM users WHERE created_at < '2023-01-01' AND status = 'active';

Обратите внимание на строки Execution Time и Planning Time. Если Execution Time огромное, значит, движок действительно много работает. Если Planning Time огромное, проблема в статистике или планировщике. Всегда запускайте именно ANALYZE, чтобы получить реальные цифры.

2. Типы индексов

B-tree, GIN, BRIN — когда что использовать

Индексы — это ускорители. Но не все индексы подходят для всех задач. Выбор неправильного типа может даже замедлить работу базы данных.

B-tree

Стандарт для равенства и диапазонов. Работает для операторов =, >, <. Идеален для даты и ID.

GIN

Для неупорядоченных наборов данных: массивы, JSONB, полнотекстовый поиск. Отлично подходит для поиска по вложенным объектам.

BRIN

Блок-ридж индексы. Очень маленькие, но работают только если данные монотонны (например, временные метки или последовательные ID).

GiST

Обобщённый поиск. Используется для геометрии, полнотекстового поиска и операторов, не поддерживаемых GIN.

3. Мониторинг

pg_stat_statements

Чтобы найти "самого виновного" запроса, нужно включить расширение pg_stat_statements в конфигурации.

  • Шаг 1: CREATE EXTENSION pg_stat_statements;
  • Шаг 2: SELECT query, calls, total_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Это покажет, какие запросы выполняются чаще всего и сколько времени на них уходит в сумме. Часто оказывается, что один запрос с большим rows тормозит всё приложение, хотя выполняется редко.

4. Connection Pooling

PgBouncer vs Встроенный пул

Поддержка множества подключений может съесть CPU и RAM сервера. В PostgreSQL есть встроенный механизм (пулер), но профессионалы предпочитают PgBouncer.

PgBouncer держит в памяти пул активных соединений и пересоздаёт их при необходимости. Это снижает нагрузку на БД в десятки раз. Встроенный пул в PostgreSQL (параметр max_connections) имеет свои ограничения по производительности при высокой нагрузке.

Узнать больше о PgBouncer
5. Автовакуум

Когда он мешает

Автовакуум — это необходимость. Но если он работает слишком агрессивно, он может блокировать запись.

Посмотрите на системные логи. Если видите ошибки lock contention или autovacuum: unable to obtain lock, значит, нагрузка высокая, а таблица большая. Настройте параметры autovacuum_vacuum_scale_factor и autovacuum_vacuum_cost_delay, чтобы распределить нагрузку на вакуумирование равномернее.

6. Реальный кейс

8 секунд → 40 мс

Клиент столкнулся с зависанием API. Запрос на выборку логов с фильтрами по дате и статусу работал 8 секунд.

$ SELECT * FROM logs WHERE event_time > '2023-01-01' AND user_id = 102;
Seq Scan on logs (cost=0.00..125000.00 rows=10000 width=200) (actual time=0.015..8000.425 rows=10000 loops=1)
Filter: (event_time > '2023-01-01'::date) AND (user_id = 102)
Execution Time: 8000.450 ms

Решение: Добавили составной индекс CREATE INDEX idx_logs_user_time ON logs(user_id, event_time DESC);.

$ SELECT * FROM logs WHERE event_time > '2023-01-01' AND user_id = 102;
Index Scan using idx_logs_user_time on logs (cost=0.43..845.45 rows=10000 width=200) (actual time=0.012..40.123 rows=10000 loops=1)
Index Cond: ((user_id = 102) AND (event_time > '2023-01-01'::date))
Execution Time: 40.150 ms

Разница в 200 раз. Составной индекс позволил отсеять ненужные строки ещё на этапе чтения индекса.

Хотите, чтобы ваша база данных работала быстрее?

GlyphKit предоставляет выделенные серверы с NVMe-дисками и предустановленными PostgreSQL для быстрого старта.

Выбрать сервер →