keepcalculator

06 Numbers and Powers

PostgreSQL Troubleshooting: Queries & Logs Guide

Card No. 06·32134 · Updated · Standard formula, shown below

This guide explains how to diagnose slow PostgreSQL queries and use the database’s built-in logging to find root causes. You’ll learn the key configuration settings, the essential queries to run, and a practical step-by-step method to fix performance issues.

Why PostgreSQL Performance Degrades

PostgreSQL performance problems usually stem from one of a few common areas: inefficient query plans, missing or unused indexes, lock contention, insufficient memory configuration, or hardware limits. The database itself is rarely the culprit; more often, the way it’s configured or the way queries are written creates bottlenecks. Understanding these categories helps you know where to look first.

Essential Logging Configuration

PostgreSQL’s logging is controlled by settings in postgresql.conf. To capture useful performance data, enable the following:

  • log_min_duration_statement = 1000 — logs any query taking longer than 1000 ms (adjust to your needs).
  • log_checkpoints = on — records checkpoint activity, which can reveal I/O pressure.
  • log_connections = on and log_disconnections = on — tracks session starts and ends.
  • log_lock_waits = on — logs when a query waits longer than deadlock_timeout for a lock.
  • log_temp_files = 0 — logs when temporary files are created (sign of sort/hash spills).

After changing these, reload the configuration with SELECT pg_reload_conf(); — no restart needed for most settings.

Reading the Query Log

The log entries will show the query text, duration, and sometimes the connection details. A typical slow query log line looks like:

2025-01-15 10:32:11 UTC [12345] LOG:  duration: 2500.123 ms  statement: SELECT * FROM orders WHERE customer_id = 42;

Use the duration to identify the worst offenders. Then, for each slow query, run EXPLAIN ANALYZE to see the execution plan. The plan shows which steps take the most time, whether indexes are used, and if rows are being scanned unnecessarily.

Key Diagnostic Queries

Beyond the logs, you can query PostgreSQL’s system catalogs to find performance issues in real time:

  • Currently running queries: SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active';
  • Longest-running transactions: SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC;
  • Index usage per table: SELECT relname, idx_scan, seq_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC;
  • Lock waits: SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;

These queries give you a live view of what’s happening right now.

Common Problems and Fixes

Once you’ve identified a slow query, the usual suspects are:

  • Missing indexes: If EXPLAIN shows a sequential scan on a large table, consider adding an index on the columns used in WHERE or JOIN.
  • Outdated statistics: Run ANALYZE to help the planner make better decisions.
  • Bad query plans: Sometimes the planner chooses a nested loop when a hash join would be better. You can force a different join type with SET enable_nestloop = off; (temporarily) to test.
  • Lock contention: If queries are waiting on locks, look for long transactions that hold locks and aren’t committing.
  • Memory settings: shared_buffers and work_mem are often too low. Increase work_mem to avoid disk sorts.

Using EXPLAIN ANALYZE Effectively

To get the most from EXPLAIN ANALYZE, run it on the actual query and pay attention to the following:

  • Actual vs. estimated rows: Large discrepancies indicate stale statistics.
  • Time per step: The step with the highest time is your bottleneck.
  • Buffers: Add BUFFERS to the command (EXPLAIN (ANALYZE, BUFFERS)) to see how much data was read from disk vs. memory.

Frequently Asked Questions

How do I find the slowest queries in PostgreSQL?

Enable log_min_duration_statement to log slow queries, then check the log file. Alternatively, query pg_stat_statements (if enabled) to get aggregated statistics on all queries.

What does ‘seq scan’ mean in EXPLAIN output?

A sequential scan means PostgreSQL reads the entire table row by row. For large tables, this is slow. An index scan is usually faster, so consider adding an index.

Why is my query not using an index?

Possible reasons: the column has low cardinality (many duplicates), the query uses a function on the column, or the planner thinks a sequential scan is cheaper. Run ANALYZE to update statistics, and check if the index is actually created.

How do I check for lock contention?

Query pg_locks for un-granted locks, or enable log_lock_waits to see when waits occur. Long-running transactions are the usual cause.

What is the best way to tune shared_buffers?

A common starting point is 25% of your server’s RAM, but not more than 8 GB for most systems. Monitor cache hit ratio with SELECT * FROM pg_stat_database; — if it’s below 99%, you may need more memory.

Should I use pg_stat_statements?

Yes, it’s one of the most useful extensions for performance troubleshooting. It tracks execution statistics for every query, letting you identify the most frequent and most time-consuming ones.

Final Thoughts

Performance troubleshooting in PostgreSQL is a systematic process: enable logging, find the slow queries, analyze the plan, and fix the root cause. Start with the logging settings, then use the diagnostic queries to narrow down the issue. With practice, you’ll be able to resolve most performance problems without guessing.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →