The Practical Developer

Postgres Query Performance Tuning: Finding and Fixing the Slow Queries Your Users Feel

July 2, 2026 by The Practical Developer

Your Postgres CPU is at 60%, users are complaining about slow pages, and EXPLAIN ANALYZE on the query in psql shows a fast index scan. The real problem is the other 99% of your queries. Here is how to use pg_stat_statements, auto_explain, and planner cost tuning to find the actual bottlenecks and fix them systematically.

database

postgres

performance

devops

Abstract data visualization of network nodes and performance metrics, representing database query analysis and optimization

Your Postgres CPU is at 60% during business hours. The slow query log catches a few queries that run for three seconds, but when you copy them into psql and run EXPLAIN ANALYZE, they finish in 12 milliseconds. You add an index. Nothing changes. You bump the instance size. The CPU drops for a week, then creeps back up.

This cycle repeats because you are fixing the symptoms, not the cause. The queries that show up in the slow query log are the outliers. The real damage is done by the queries that run 10,000 times per second, each taking 5-15 milliseconds. Those queries are too fast to trip a slow-query threshold, but collectively they consume all your CPU and I/O.

This post is the workflow I use to break that cycle. It covers three Postgres features that work together: pg_stat_statements for finding the expensive queries, auto_explain for capturing actual plans from production, and cost constant tuning for getting the planner to make better decisions without changing a single query.

Step 1: Install and configure pg_stat_statements

pg_stat_statements is the single most useful tool for Postgres performance work, and most installations do not have it turned on. It tracks every query executed, aggregates execution statistics (total time, rows, shared buffer hits, I/O timings), and normalizes parameter values so you can see the pattern behind 100,000 queries with different WHERE clause values.

Enable it in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

A restart is required because shared_preload_libraries changes need a full Postgres restart. On RDS and other managed Postgres services, you can set these through the parameter group.

Once the extension is loaded, create the view in each database you want to monitor:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

That is it. From this point forward, every query executed against the database is being tracked.

Step 2: Find the queries that cost you the most

The pg_stat_statements view exposes 30+ columns of per-query metrics. These are the ones I look at first:

SELECT
  queryid,
  LEFT(query, 120) AS query_preview,
  calls,
  ROUND(total_exec_time::numeric, 1) AS total_ms,
  ROUND(mean_exec_time::numeric, 1) AS mean_ms,
  ROUND((total_exec_time / GREATEST(calls, 1))::numeric, 1) AS avg_ms,
  ROUND(100.0 * total_exec_time / NULLIF(SUM(total_exec_time) OVER (), 0), 1) AS pct_total_time,
  rows,
  ROUND(shared_blks_hit::numeric / GREATEST(shared_blks_hit + shared_blks_read, 1), 3) AS cache_hit_ratio,
  shared_blks_read,
  shared_blks_written,
  local_blks_written,
  temp_blks_written,
  wal_bytes
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

This query gives you the top 20 queries by total execution time. The key columns:

  • total_exec_time: The total milliseconds spent executing this query across all calls. A query that runs 2 million times at 1.5 ms each has a total of 3,000,000 ms. That is three CPU-seconds per call, or more than three thousand CPU-seconds in total. That is the one to fix, not the query that runs 3 times at 2000 ms each.

  • calls: How many times this query was executed since the last reset. Use this to distinguish between a frequently called fast query (likely the real problem) and a rarely called slow query (a candidate for a simple index fix).

  • cache_hit_ratio: The ratio of shared buffer hits to total reads. Anything below 0.99 (99%) means the query is reading from disk instead of memory. A query with 50 million calls and a 0.85 hit ratio is reading 7.5 million pages from disk and spending most of its time waiting on I/O.

  • temp_blks_written: The number of temporary blocks written. If this is non-zero, the query is spilling to disk (sort, hash join, or aggregate that does not fit in work_mem). This is a red flag.

  • wal_bytes: If this is high, the query is writing a lot of WAL. That means it is modifying many rows and causing I/O on the write side. Often overlooked when chasing read performance.

Reset the counters periodically so you can see what happens after you deploy changes:

SELECT pg_stat_statements_reset();

I reset this after every major deployment and every index change, so I can compare before/after metrics with confidence.

Step 3: Capture actual query plans with auto_explain

The biggest lie in performance work is “I ran EXPLAIN ANALYZE in psql and the query was fast.” The plan Postgres generates in psql (with your current session parameters, your current data distribution, and no concurrent load) is almost never the plan the same query uses in production.

auto_explain solves this. It is a Postgres contrib module that logs the query plan for queries that exceed a configurable duration threshold. It captures the plan from the actual production execution, with real parameters, real data distribution, and real concurrency.

shared_preload_libraries = 'pg_stat_statements, auto_explain'
auto_explain.log_min_duration = 200
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.5

With these settings, any query taking longer than 200 ms gets its EXPLAIN ANALYZE output written to the Postgres log. The sample_rate = 0.5 means half of all qualifying queries are logged, which prevents log flooding during high-traffic periods while still giving you enough data.

The output looks like this in your Postgres logs:

LOG:  duration: 345.123 ms  plan:
  Query Text: SELECT o.*, oi.* FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.user_id = $1 AND o.status = $2
    ORDER BY o.created_at DESC LIMIT 50
  Limit  (cost=0.43..1534.22 rows=50 width=142)
    ->  Nested Loop  (cost=0.43..24567.34 rows=801 rows=50 width=142)
          ->  Index Scan Backward using orders_created_at_idx on orders o
                (cost=0.43..12893.21 rows=801 width=88)
                Filter: ((user_id = 12345) AND (status = 'shipped'::text))
                Rows Removed by Filter: 89211
          ->  Index Scan using order_items_order_id_idx on order_items oi
                (cost=0.29..14.31 rows=23 width=54)
                Index Cond: (order_id = o.id)

The important detail is the Rows Removed by Filter: 89211. The planner chose to scan the orders_created_at_idx index backwards (sorting by created_at descending) and then filter by user_id and status. It read 89,211 rows from the index, fetched each one from the heap, checked the filter, and discarded all but the matching ones. An index on (user_id, status, created_at) would let it seek directly to the matching rows and avoid the scan entirely.

Without auto_explain, you might never have seen this plan because the query only runs at 345 ms and might not hit your slow-query threshold. But when it runs 5,000 times per hour, those 345 ms add up to 1,725 seconds of CPU time per hour.

Step 4: Tune the planner cost constants for your hardware

Postgres does not know what hardware it is running on. The planner uses a set of cost constants to estimate the relative expense of sequential reads, random reads, processing rows, and other operations. The defaults were set in the early 2000s, when spinning disks were the norm and a random I/O was about four times more expensive than a sequential I/O.

On modern hardware (NVMe SSDs or cloud block storage like gp3/EBS), random I/O is much closer to sequential I/O in cost. The default random_page_cost = 4 makes the planner overestimate the cost of index scans and underestimate the cost of sequential scans, causing it to choose sequential scans for queries that would be faster with an index scan.

Start here:

# For NVMe SSD or local SSD storage
random_page_cost = 1.1
seq_page_cost = 1.0
effective_cache_size = '8GB'   # 50-75% of available RAM
effective_io_concurrency = 200  # NVMe SSDs can handle hundreds of concurrent I/Os

  • random_page_cost: Set this to 1.1 for NVMe SSDs. For cloud EBS volumes (gp3), 1.5 is a safer starting point. For iops-provisioned volumes, go as low as 1.1. The key insight: if random_page_cost equals seq_page_cost, the planner treats random and sequential I/O identically, which is close to reality on modern SSDs.

  • effective_cache_size: This is Postgres’s estimate of how much cache is available for data files. Set it to 50-75% of total system RAM. On a 16 GB server with 4 GB allocated to shared_buffers, set effective_cache_size to around 10 GB. The planner uses this to estimate whether data will be found in the OS page cache.

  • effective_io_concurrency: Controls how many concurrent I/O operations Postgres assumes it can issue. For a single NVMe drive, 200 is reasonable. For a RAID of SSDs, 300-500. For a single spinning disk, leave it at 1.

These changes alone frequently reduce query times by 20-40% on workloads with many index scans. I have seen a production system where changing random_page_cost from 4 to 1.5 cut the p95 query latency in half because the planner finally chose index-only scans instead of sequential scans for half the query patterns.

After changing these, always verify with pg_stat_statements:

-- Before and after comparison
SELECT
  queryid,
  LEFT(query, 80) AS query,
  calls,
  ROUND(mean_exec_time::numeric, 1) AS mean_ms,
  ROUND(total_exec_time::numeric, 1) AS total_ms
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

Reset the stats before the change, let the system run for a day, then compare the top queries. If the mean times dropped, the cost constants are better. If they went up, you went too far.

Step 5: Find unused and missing indexes with statistics

Postgres tracks index usage in pg_stat_user_indexes. This view shows how many times each index has been scanned, how many rows were fetched from the table via the index, and how many rows were fetched from the index alone (index-only scans).

Find indexes that are never used:

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Every index with idx_scan = 0 is dead weight. It wastes storage, slows down writes (every INSERT, UPDATE, and DELETE must update every index on the table), and inflates your backup size. Drop them:

DROP INDEX CONCURRENTLY IF EXISTS unused_index_name;

Use CONCURRENTLY to avoid taking a write lock on the table during the drop.

Now find tables that are frequently scanned sequentially, which suggests a missing index:

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  COALESCE(seq_tup_read / NULLIF(seq_scan, 0), 0) AS avg_tuples_per_seq_scan,
  n_live_tup AS estimated_row_count
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND COALESCE(seq_tup_read / NULLIF(seq_scan, 0), 0) > 100
ORDER BY seq_scan DESC;

A table with 50,000 sequential scans and 10,000 index scans is a strong candidate for a new index. Combine this with the auto_explain output to understand which WHERE clauses are driving the sequential scans.

Step 6: Set up a performance regression alert

The goal is to catch regressions before users do. Combine pg_stat_statements with a simple monitoring query that runs every few minutes:

CREATE OR REPLACE VIEW query_performance_regression AS
SELECT
  queryid,
  LEFT(query, 200) AS query_text,
  calls,
  ROUND(total_exec_time::numeric, 1) AS total_exec_time_ms,
  ROUND(mean_exec_time::numeric, 1) AS mean_exec_time_ms,
  ROUND(stddev_exec_time::numeric, 1) AS stddev_exec_time_ms,
  ROUND(min_exec_time::numeric, 1) AS min_exec_time_ms,
  ROUND(max_exec_time::numeric, 1) AS max_exec_time_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  wal_bytes
FROM pg_stat_statements
WHERE mean_exec_time > 50
  AND calls > 100
ORDER BY total_exec_time DESC;

Pipe this into your monitoring system (Datadog, Grafana, Prometheus with postgres_exporter) and set an alert when any query’s mean_exec_time increases by more than 20% compared to the previous day’s baseline. pg_stat_statements resets its stats when the Postgres instance restarts, so store the historical data in your monitoring system, not in the view itself.

The tuning workflow in practice

Here is exactly what I do when I get a “Postgres is slow” alert:

  1. Run the top-20 query from Step 2 against pg_stat_statements. Identify which query pattern dominates the total time.

  2. Check temp_blks_written for that query. If it is non-zero, increase work_mem until the spilling stops. Start with 4 MB, go up to 64 MB for queries with sorts or hash joins. Watch for the trap: work_mem is per-operation, per-query, per-session. Setting it to 256 MB with 200 concurrent connections can eat 50 GB of RAM.

  3. Check cache_hit_ratio for that query. If below 0.99, check whether shared_buffers is sized correctly (typically 25% of RAM on dedicated DB servers, up to the 8 GB practical ceiling where the law of diminishing returns kicks in).

  4. Check auto_explain logs for the query’s actual plan. Look for Rows Removed by Filter, sequential scans on large tables, and hash or merge joins where nested loops would be faster.

  5. Add the missing index (or change the existing one to a covering index). Deploy. Reset pg_stat_statements.

  6. Wait 24 hours. Compare before/after from the monitoring system. If the top query by total time dropped from 35% of total DB time to 5%, you fixed it. If it did not change, the index is not being used. Check pg_stat_user_indexes for idx_scan on the new index. If it is zero, your query is not matching the index conditions.

  7. Repeat with the next most expensive query.

The takeaway

Postgres performance work is not about running EXPLAIN ANALYZE on one query in isolation. It is about understanding the aggregate behavior of every query on the system, capturing real plans from real production executions, and setting the planner cost constants to match the actual hardware.

The three tools in this post (pg_stat_statements, auto_explain, cost constant tuning) cover the read side. For write performance, add WAL monitoring and check pg_stat_bgwriter for checkpoint behavior, but that is another post.

Start with pg_stat_statements today. If it is not enabled, restart Postgres with it enabled, wait a week, and look at the top-20 list. You will almost certainly find a query pattern that is costing you more than you realize, and the fix will be simpler than you expect.

A note from Yojji

Production database performance tuning is one of those engineering disciplines that only gets attention during an incident, but the best work happens before the incident ever starts. Setting up pg_stat_statements, capturing real query plans with auto_explain, and tuning the planner cost constants to match your hardware is infrastructure work that pays for itself every single day in lower latency and fewer pages.

Yojji is an international custom software development company, founded in 2016, with offices in Europe, the US, and the UK. Their teams specialize in the JavaScript stack (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and full-cycle delivery from architecture design through production performance tuning. If your team needs a Postgres-backed Node.js service that stays fast as it grows, Yojji builds and operates the data layer for you.

Keep Reading