Как исправить медленные запросы PostgreSQL
Slow Postgres queries — most common bottleneck веб-приложений. Diagnostic tools: pg_stat_statements (топ по total time), EXPLAIN ANALYZE (query plan), auto_explain (log plans automatically). Fix path: identify slow query → add index → rewrite (avoid N+1, SELECT *) → partitioning (для >100M rows). Always measure: p50 vs p99 — p99 matters для user experience.
Ниже: пошаговая инструкция, рабочие примеры, типичные ошибки, FAQ.
Пошаговая настройка
- Enable
pg_stat_statements:CREATE EXTENSION pg_stat_statements;+ restart Postgres - Find slow queries:
SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; - Для каждой query:
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; - Seq Scan на большой table → add index:
CREATE INDEX CONCURRENTLY idx_name ON table(col); - N+1 problem: ORM loads parent + loops fetch child. Fix: eager loading (
JOIN,WITH) - SELECT * → SELECT только нужные columns (network + memory savings)
- Connection pooling: PgBouncer перед Postgres (10-100× reduce idle conns)
- Partition large tables (>100M rows): PARTITION BY RANGE(date)
Рабочие примеры
| Сценарий | Конфиг |
|---|---|
| EXPLAIN ANALYZE | EXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM users WHERE email = $1;\n# Seq Scan → bad, Index Scan → good |
| Top slow queries | SELECT
substring(query, 1, 50) AS query_short,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
calls,
ROUND(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10; |
| Add concurrent index | CREATE INDEX CONCURRENTLY idx_users_email ON users(email);\n# CONCURRENTLY — no table lock, safe for prod |
| Composite index | CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);\n# Для query: WHERE user_id = ? ORDER BY created_at DESC |
| auto_explain (logs slow queries) | # postgresql.conf:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000 # ms
auto_explain.log_analyze = true
auto_explain.log_buffers = true |
Типичные ошибки
- CREATE INDEX без CONCURRENTLY — table-level lock на production, blocks writes
- Too many indexes — writes slow down (каждый INSERT updates все indexes)
- Index не используется → check: is column в WHERE/JOIN? Cost vs Seq Scan? Stats up-to-date (ANALYZE)?
- EXPLAIN vs EXPLAIN ANALYZE: первый — estimate, второй — actual. Always ANALYZE для final decision
- pg_stat_statements не ловит queries < auto_explain threshold. Lower threshold когда dig deeper
Больше по теме
Часто задаваемые вопросы
Как увидеть queries real-time?
<code>SELECT * FROM pg_stat_activity WHERE state = 'active';</code> — live queries. С PID можно canсel: <code>SELECT pg_cancel_backend(pid);</code>
VACUUM ANALYZE — когда?
Autovacuum делает автоматически. Manual нужен после bulk insert/update — refresh stats для query planner.
Connection pooling — PgBouncer или application-level?
Application pool (node-pg-pool) — per-app. PgBouncer — cluster-wide, shared. Production — PgBouncer (1000 app connections → 25 Postgres).
Slow на 10 GB DB — индексы или scale?
Сначала indexes (часто 100× improvement). Scale (replicas, shards) — после exhausting query optimization.