PostgreSQL Query Tuning with EXPLAIN ANALYZE

In this tutorial, you'll learn about PostgreSQL Query Tuning with EXPLAIN ANALYZE. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

PostgreSQL query tuning is the systematic Process of analyzing query execution plans with EXPLAIN ANALYZE, identifying performance bottlenecks like sequential scans and nested loops, and applying optimizations such as index creation, configuration tuning, and query rewriting.

What You'll Learn

You will read EXPLAIN ANALYZE output fluently, identify sequential scans, nested loop abuses, and sort operations, configure PostgreSQL parameters for workload-specific optimization, and apply advanced tuning techniques for production queries.

Why PostgreSQL Query Tuning Matters

PostgreSQL handles complex queries gracefully, but default configuration is designed for a desktop, not a production server. Durga Antivirus Pro queries 2 billion threat signatures daily. Tuning PostgreSQL configuration and optimizing queries reduced average scan time from 340ms to 22ms.

Query Tuning Learning Path

flowchart LR
  A[SQL Basics] --> B[Query Optimization]
  B --> C[PostgreSQL Query Tuning]
  C --> D[Database Indexing]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Reading EXPLAIN ANALYZE Output

EXPLAIN ANALYZE executes the query and shows actual execution times and row counts.

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'shipped'
  AND o.created_at > now() - interval '7 days'
ORDER BY o.total DESC
LIMIT 20;

Output:

Limit  (cost=145.23..178.90 rows=20 width=42)
  (actual time=12.345..12.890 rows=20 loops=1)
  Buffers: shared hit=45 read=12
  ->  Sort  (cost=145.23..152.45 rows=2890 width=42)
        (actual time=12.344..12.456 rows=20 loops=1)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 32kB
        Buffers: shared hit=45 read=12
        ->  Hash Join  (cost=34.56..98.23 rows=2890 width=42)
              (actual time=4.567..9.234 rows=2890 loops=1)
              Hash Cond: (o.user_id = u.id)
              Buffers: shared hit=45 read=12
              ->  Index Scan using idx_orders_status_date on orders o
                    Index Cond: ((status = 'shipped'::text)
                      AND (created_at > (now() - '7 days'::interval)))
                    (actual time=1.234..3.456 rows=2890 loops=1)
                    Buffers: shared hit=30 read=8
              ->  Hash  (cost=22.22..22.22 rows=1022 width=22)
                    (actual time=2.345..2.345 rows=1022 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 68kB
                    Buffers: shared hit=15 read=4
                    ->  Seq Scan on users u
                          (actual time=0.567..1.234 rows=1022 loops=1)
                          Buffers: shared hit=15 read=4

What to Look For

Identifying Slow Patterns

Pattern 1: Sequential Scan on Large Table

-- Problem: Full table scan on 10 million row table
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

Output shows Seq Scan on orders with high actual time. Fix by adding an index:

CREATE INDEX idx_orders_status ON orders (status);

Pattern 2: Bad Row Estimates

When rows estimate differs from actual rows by 10x or more, the planner makes poor decisions.

-- Update table statistics
ANALYZE orders;

-- Check statistics
SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

Expected output:

 relname | n_live_tup | n_dead_tup |         last_analyze
---------+------------+------------+-------------------------------
 orders  |   10000000 |      54321 | 2026-06-22 03:15:00+00

Pattern 3: Nested Loop on Large Dataset

A nested loop join of two large tables without indexes causes millions of inner table scans.

-- Problem: No index on orders.user_id
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01';

Fix by adding the missing index:

CREATE INDEX idx_orders_user_id ON orders (user_id);

PostgreSQL Configuration Tuning

The default <a href="/databases/postgresql/">PostgreSQL</a>.conf is tuned for a small desktop. Production servers need different settings.

Memory Configuration

# postgresql.conf — tune for server with 32GB RAM

# shared_buffers: 25% of total RAM
shared_buffers = 8GB

# effective_cache_size: 50-75% of total RAM
effective_cache_size = 20GB

# work_mem: per-operation sort memory
# Total: work_mem * max_connections = 64MB * 100 = 6.4GB
work_mem = 64MB

# maintenance_work_mem: for VACUUM, CREATE INDEX
maintenance_work_mem = 1GB

Workload Tuning

# For OLTP (many small queries)
max_connections = 100
random_page_cost = 1.1        # SSD-optimized (default 4.0)
effective_io_concurrency = 200 # SSD can handle concurrent I/O

# For OLAP (large analytical queries)
# max_connections = 20
# work_mem = 512MB
# random_page_cost = 1.0

Auto-Vacuum Tuning

# Prevent transaction wraparound and table bloat
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.01     # 1% of table dead tuples triggers vacuum
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_scale_factor = 0.005   # 0.5% changes triggers analyze
autovacuum_vacuum_cost_limit = 2000       # Higher = faster vacuum, more I/O

Advanced EXPLAIN Features

EXPLAIN (BUFFERS)

Shows buffer cache usage (shared hit vs shared read) to identify disk I/O.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE status = 'completed';

EXPLAIN (TIMING OFF)

Disables timing when you only need row estimates and plan shape.

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

EXPLAIN with JSON Format

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders WHERE id = 12345;

Useful for programmatic analysis or loading into visualization tools.

Common Queries for Performance Diagnosis

Find Current Running Queries

SELECT pid, now() - pg_stat_activity.query_start AS duration,
       state, wait_event_type, wait_event,
       LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid <> pg_backend_pid()
ORDER BY duration DESC
LIMIT 10;

Find Blocked Queries

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked_locks.pid = blocked.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database = blocked_locks.database
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Find Index Usage

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

Using pg_stat_statements for Query Tuning

-- Find top 5 queries by total time
SELECT queryid,
       LEFT(query, 60) AS query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 5;

Expected output:

  queryid  |                     query                      | calls | total_ms | avg_ms |  pct
-----------+--------------------------------------------------+-------+----------+--------+-------
 123456789 | SELECT * FROM orders WHERE status = $1 ORDER...  | 54321 | 123456.7 |   2.27 | 34.21
 234567891 | INSERT INTO audit_log (event, user_id, time)...   | 99999 | 98765.4  |   0.99 | 27.34

Common PostgreSQL Tuning Errors

1. Leaving Default Configuration in Production

PostgreSQL defaults are for a 512MB desktop. A production server with 32GB RAM running defaults wastes 95% of available memory.

2. Setting shared_buffers Too High

shared_buffers over 40% of RAM causes the OS cache to compete with PostgreSQL for memory, reducing overall performance.

3. Not Running ANALYZE After Bulk Operations

After bulk INSERT, UPDATE, or DELETE, run ANALYZE to update statistics. PostgreSQL auto-analyze is conservative and may not trigger in time.

4. Ignoring random_page_cost for SSDs

The default random_page_cost = 4.0 assumes HDD seek costs. For SSDs, set random_page_cost = 1.1 to make index scans cheaper.

5. Using Default work_mem with Many Connections

work_mem * max_connections can exceed RAM. Each sort or hash operation uses work_mem. Monitor external merge in EXPLAIN output.

6. Not Enabling pg_stat_statements

Without pg_stat_statements, you are guessing which queries are slow. Enable it in every PostgreSQL 10+ installation.

7. Ignoring Connection Pooling

Each PostgreSQL connection uses ~10MB of RAM. With 200 direct connections, that is 2GB before any query runs. Use PgBouncer.

Practice Questions

1. What does a Seq Scan on a 10GB table indicate?

A missing index. The database is reading every row sequentially. Add an appropriate index on the WHERE clause columns.

2. How do you interpret the rows vs actual rows mismatch in EXPLAIN ANALYZE?

If row estimates are off by 10x or more, table statistics are stale. Run ANALYZE on the table to update statistics.

3. What is the recommended shared_buffers setting for a 32GB server?

Approximately 8GB (25% of RAM). The OS cache handles the rest. Above 40% causes performance degradation.

4. How do you find blocked queries in PostgreSQL?

Query pg_stat_activity joined with pg_locks to find queries waiting for locks held by other sessions.

5. Challenge: Tune a slow query.

Given this query that takes 12 seconds:

SELECT c.name, SUM(oi.quantity * oi.unit_price) as revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= '2026-01-01'
  AND o.status IN ('completed', 'shipped')
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 10;

List your tuning steps. Answer: (1) Run EXPLAIN ANALYZE to find the bottleneck. (2) Add indexes on orders(created_at, status), orders(customer_id), and order_items(order_id). (3) Ensure full coverage. (4) Check if work_mem is sufficient (avoid disk sorts). (5) Consider a materialized view if this is a frequent report query.

FAQ

Try It Yourself

Tune a PostgreSQL query step by step:

1. Write a query that JOINs three tables with a WHERE clause and ORDER BY
2. Run EXPLAIN (ANALYZE, BUFFERS) and note execution time
3. Add appropriate indexes for the WHERE and JOIN conditions
4. Re-run EXPLAIN ANALYZE and compare
5. Tune work_mem if disk sorts appear
6. Run ANALYZE on all involved tables
7. Document the improvement factor

What's Next

You have learned to read EXPLAIN ANALYZE output, identify performance bottlenecks, and tune PostgreSQL configuration. Start by enabling pg_stat_statements and running a top-5 slow query report on your production database today.