Identify PostgreSQL Query Performance Problems

1. Identify workload candidates

Where approved and installed, pg_stat_statements groups normalized query activity. Enabling it may require a configuration change and restart; coordinate that change with the service owner. Statistics are cumulative since reset, so compare measurement windows and do not confuse total time with per-call latency.

SELECT queryid, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows, shared_blks_read, shared_blks_hit,
       left(query, 180) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Also inspect application latency, error rate, CPU, I/O, memory pressure, lock waits, and recent deploys. Choose a query tied to a measured user impact rather than optimizing an arbitrary statement.

2. Read a plan safely

Start with a non-executing plan and representative parameter values. Look for sequential scans on large selective inputs, inaccurate row estimates, repeated loops, expensive sorts, spills, and join order. A sequential scan can be correct for a small table or when most rows are needed.

EXPLAIN (COSTS, VERBOSE, BUFFERS)
SELECT order_id, order_date, total
FROM orders
WHERE customer_id = 4201
  AND order_date >= DATE '2026-01-01'
ORDER BY order_date DESC
LIMIT 50;

3. Form and test a tuning hypothesis

If a recurring query filters by customer and date, a composite B-tree index may help; column order depends on predicate and ordering patterns. Create candidate indexes in a test environment and compare the plan, elapsed time, rows, buffers, and write/storage costs.

-- Example candidate: test the workload and index order first.
CREATE INDEX CONCURRENTLY orders_customer_date_idx
ON orders (customer_id, order_date DESC);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Check for invalid indexes after an interrupted build, monitor I/O and disk space, and confirm the planner uses the index. Remove the index only through the approved change process if it does not help.

Performance change record

1. Capture baseline plan and workload period.
2. Write the expected bottleneck and proposed change.
3. Test with representative data and parameters.
4. Deploy one change with a rollback path.
5. Compare latency, throughput, waits, buffers, and write cost after the change.