PostgreSQL slow query basics

Topic: Databases core

Find and fix slow PostgreSQL queries using log_min_duration_statement and pg_stat_statements. Use EXPLAIN ANALYZE to see the plan and add indexes or rewrite queries. Use this when the database is slow or when optimizing after enabling query logging.

Intent: How-to

Quick answer

  • Set log_min_duration_statement = 1s and use pg_stat_statements to see which queries use the most time. Order by total_time or mean_time to find the worst queries.
  • Run EXPLAIN ANALYZE on the slow query. Look for sequential scans on large tables and add indexes on filtered or joined columns. Run ANALYZE after changes.
  • Re-check pg_stat_statements or logs to confirm improvement. Avoid too many indexes; remove unused indexes to speed writes.

Prerequisites

Steps

  1. Enable logging

    Set log_min_duration_statement = 1s in postgresql.conf. Add pg_stat_statements to shared_preload_libraries; CREATE EXTENSION pg_stat_statements; Reload.

  2. Find slow queries

    Query pg_stat_statements for total_time, calls, mean_time. Or check logs. Pick the query that dominates total time or has high mean time.

  3. EXPLAIN and fix

    Run EXPLAIN (ANALYZE, BUFFERS) with the query. Add index on WHERE or JOIN columns; run ANALYZE. Re-run the query and confirm time is lower.

  4. Document

    Document the change and new index. Remove unused indexes if you find them via pg_stat_user_indexes.

Summary

Enable slow-query logging and pg_stat_statements; find slow queries; use EXPLAIN ANALYZE and indexes to improve them. Use this to reduce database latency.

Prerequisites

Steps

Step 1: Enable logging

Set log_min_duration_statement and enable pg_stat_statements; reload.

Step 2: Find slow queries

Use pg_stat_statements or logs to identify the slowest queries.

Step 3: EXPLAIN and fix

Run EXPLAIN ANALYZE; add indexes and run ANALYZE; verify improvement.

Step 4: Document

Document changes and remove unused indexes if needed.

Verification

  • Query time is reduced; metrics or logs confirm.

Troubleshooting

Still slow — Check for locks; consider more RAM or partitioning. Too many indexes — Remove unused indexes to speed writes.

Next steps

Continue to