PostgreSQL Slow Query Optimization: Diagnose & Fix in 2 Days

Optimizing Slow PostgreSQL SQL Queries

You wait 20 seconds for a report to load. Users leave, the database is the bottleneck. Typical scenario: one query takes 5 seconds, and there are hundreds like it per minute. We diagnose why and fix it: reduce database response time by 3–5x under load. Diagnostics take a day, optimization a couple more. All changes documented, before/after measurements mandatory.

Slow queries are the main cause of poor UX. 95% of database performance problems are solved by one of four methods: adding an index, rewriting the query, denormalization, or caching. Experience shows that properly optimizing the 10–15 heaviest queries can free up to 40% of server resources. Let's see how to diagnose and fix slow queries in PostgreSQL.

How to Diagnose Slow Queries?

pg_stat_statements is the first extension to enable on production. It collects per-query statistics: total and average time, call count, standard deviation. The coefficient of variation (coeff_var) helps spot queries with unstable plans.

Five-step diagnostic process:

1. Enable pg_stat_statements (if disabled) and collect statistics for a few hours.
2. Run a query for the top 20 by total_exec_time.
3. For each suspicious query, get a plan via EXPLAIN (ANALYZE, BUFFERS).
4. Identify plan nodes: Seq Scan, Nested Loop, Hash Join with Batches > 1.
5. Apply the appropriate optimization: add index, rewrite query, tune work_mem.

-- Enable pg_stat_statements shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all -- Top 20 by total time SELECT round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, calls, round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct, left(query, 120) AS query FROM pg_stat_statements WHERE calls > 100 ORDER BY total_exec_time DESC LIMIT 20; 

coeff_var_pct — coefficient of variation: a high percentage indicates an unstable plan (different parameters yield drastically different times). Then run each suspicious query through EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT p.*, c.name AS category_name FROM products p JOIN categories c ON c.id = p.category_id WHERE p.status = 'published' AND p.created_at > NOW() - INTERVAL '30 days' ORDER BY p.created_at DESC LIMIT 50; 

Key nodes to watch in the plan:

• Seq Scan on a large table — no index or planner thinks index isn't beneficial.
• Nested Loop with many iterations — N+1 at the SQL level.
• Hash Join with Batches > 1 — insufficient work_mem.
• Sort without Index Scan on the ORDER BY column — no suitable index.

Why Aren't Indexes Used?

Even with an index, the planner may ignore it. Main reasons:

• Function on column in WHERE (e.g., DATE(created_at))
• Low selectivity (index on boolean column with skewed distribution)
• Sort order not matching the index order

Solution: rewrite the query to remove function wrappers and create composite indexes for specific patterns. Column order in composite index: equality conditions first, then range and sort.

Which Anti-Patterns Are Most Common?

-- Bad: SELECT * pulls unnecessary columns; OFFSET increases load; OR doesn't use index; function on column; NOT IN with NULL SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000; SELECT * FROM users WHERE email = $1 OR phone = $1; SELECT * FROM orders WHERE DATE(created_at) = $1; SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items); -- Good: only needed columns; keyset pagination; UNION ALL; range condition; NOT EXISTS SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50; SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1; SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2; SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id); 

Compare pagination methods:

Optimizing JOINs: Composite Indexes

-- Add composite index for typical filter CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC); -- Query uses index scan without Sort SELECT id, total, status, created_at FROM orders WHERE user_id = $1 AND status = 'completed' ORDER BY created_at DESC LIMIT 10; 

Column order in index: equality conditions first (user_id = $1, status = 'completed'), then range/sort (created_at DESC).

Tuning work_mem and Using LATERAL

If EXPLAIN ANALYZE shows external merge (Disk: ...) during Sort — increase work_mem for the session:

SET work_mem = '64MB'; -- Run the heavy analytical query 

In postgresql.conf it's better to keep work_mem low (default 4–8MB) and raise it for specific queries via SET LOCAL work_mem.

-- LATERAL: for row-dependent subqueries SELECT u.id, u.email, recent.total FROM users u CROSS JOIN LATERAL ( SELECT SUM(total) AS total FROM orders o WHERE o.user_id = u.id AND o.created_at > NOW() - INTERVAL '30 days' ) AS recent; 

LATERAL often yields a better plan than JOIN on an aggregated CTE.

Scope of Work for Optimization

• Audit 10–15 heaviest queries via pg_stat_statements and EXPLAIN ANALYZE
• Rewrite queries to eliminate anti-patterns
• Add and tune indexes (including composite and partial)
• Tune PostgreSQL parameters (shared_buffers, work_mem, effective_cache_size)
• Deliver a report with before/after measurements and recommendations for the team
• Optional: train developers on reading query plans

In one project, we cut query execution time from 4 seconds to 200 ms — that reduced server load and allowed us to avoid an infrastructure upgrade. Optimizing 15 slow queries can free a significant portion of server resources.

Timeline and Pricing

Diagnostics and optimization of 10–15 slow queries — 2–3 days. Deep schema and query audit for a high-load application — 3–5 days. Pricing is calculated individually after scope assessment.

To start a project, contact us via Telegram or email — we'll do a free analysis of the first two queries. Order diagnostics and get a report with before/after measurements. We guarantee a measurable reduction in query time of at least 30% — we fix results before and after optimization.