How to Diagnose Slow Queries in PostgreSQL

Why is my PostgreSQL query slow?

Queries become slow when they scan large tables, miss indexes, fetch more rows than needed, or wait on locks. Measuring execution plans pinpoints the exact cause.

How do I measure a query’s cost with EXPLAIN ANALYZE?

Run EXPLAIN ANALYZE before your statement. PostgreSQL returns the execution plan with actual timings, row counts, and I/O statistics, showing where the time is spent.

Step-by-step EXPLAIN ANALYZE

1️⃣ Prefix the statement with EXPLAIN (ANALYZE, BUFFERS).
2️⃣ Inspect high Actual Total Time nodes.
3️⃣ Create or adjust indexes on filter and join columns.
4️⃣ Re-run to verify improvement.

How can I list the worst-offending queries globally?

Enable the pg_stat_statements extension. It records execution counts, total time, and I/O per normalized query, letting you rank statements by cost.

Enabling pg_stat_statements

1️⃣ Add shared_preload_libraries = 'pg_stat_statements' in postgresql.conf.
2️⃣ Restart PostgreSQL.
3️⃣ CREATE EXTENSION pg_stat_statements;

Querying the statistics view

Use SELECT on pg_stat_statements ordered by total_exec_time or mean_exec_time to surface slow SQL.

How do I auto-log slow plans?

Load the auto_explain module and set auto_explain.log_min_duration (ms). PostgreSQL logs plans for statements exceeding that threshold, even in production.

How do I rewrite a slow ecommerce query?

Replace SELECT * with explicit columns, add LIMIT, and push filters early. Combine EXISTS instead of IN for large sub-queries. Always create composite indexes matching your WHERE and JOIN predicates.

Refactor example

Original: SELECT * FROM Orders WHERE customer_id IN (SELECT id FROM Customers WHERE created_at < now()-interval '1 year');
Optimized: SELECT o.id, o.order_date, o.total_amount FROM Orders o JOIN Customers c ON c.id = o.customer_id WHERE c.created_at < now()-interval '1 year';

Best practices to avoid slow queries?

Analyze tables regularly, keep statistics up-to-date, index selectively, retrieve only needed rows, and monitor pg_stat_statements weekly.

Common Mistakes

Missing composite indexes: Indexing only one column of a multi-column predicate forces sequential scans. Create indexes that match the full filter.

Selecting unnecessary columns: Using SELECT * increases I/O and memory usage. Specify only required fields.

FAQs

Does EXPLAIN change data?

No. EXPLAIN reads metadata and never modifies rows.

Is pg_stat_statements safe in production?

Yes. It adds minimal overhead (<2%) and is widely used in live systems.

What threshold should I set for auto_explain.log_min_duration?

Start at 500 ms. Lower it gradually after you fix the worst offenders.