PostgreSQL Performance Tuning in Practice
1. What You'll Learn
- Read EXPLAIN ANALYZE execution plans in depth
- Understand Seq Scan / Index Scan / Bitmap Scan / Nest Loop / Hash Join / Merge Join
- Use pg_stat_statements to locate slow queries
- Configure a connection pool (PgBouncer)
- Tune key parameters: shared_buffers, work_mem, effective_cache_size
- Understand and tune autovacuum
2. The Story
Charlie took over an e-commerce system whose homepage loaded in 8 seconds and whose monthly report timed out. He analyzed each query with EXPLAIN ANALYZE and found three problems: (1) the orders table lacked an index, causing a Seq Scan; (2) work_mem was only 4MB, so complex sorts spilled to disk; (3) autovacuum couldn't keep up with the write rate, and the table was badly bloated. After fixing each one — adding an index so queries used Index Scan, raising work_mem to 64MB so sorts stayed in memory, and doubling autovacuum frequency to control bloat — overall performance improved 10x and the homepage loaded in 0.8 seconds.
3. Concept: EXPLAIN ANALYZE In Depth
(1) Core Execution-Plan Operators
| Operator | Meaning | Good for |
|---|---|---|
| Seq Scan | Full sequential table scan | Small tables, no usable index, many rows returned |
| Index Scan | B-Tree index scan | High-selectivity query (returns < 5% of rows) |
| Bitmap Heap Scan | Bitmap heap scan | Medium selectivity (5%-15%); collect TIDs then fetch rows |
| Bitmap Index Scan | Bitmap index scan | Pairs with Bitmap Heap Scan |
| Nest Loop | Nested-loop join | Small outer table + indexed inner table |
| Hash Join | Hash join | Equality join, inner table fits in memory |
| Merge Join | Merge join | Both tables sorted, equality join |
(2) Key EXPLAIN Output Fields
SQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001;
TEXT
Index Scan using idx_orders_user_id on orders (cost=0.42..8.44 rows=1 width=72) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (user_id = 1001)
Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.032 ms
| Field | Meaning |
|---|---|
| cost=X..Y | Startup cost .. total cost (estimated) |
| rows=N | Estimated number of rows returned |
| actual time | Actual time taken (ms) |
| rows (actual) | Actual number of rows returned |
| loops | Number of executions |
| Buffers: shared hit | Number of shared-buffer hits |
| Planning Time | Planning time |
| Execution Time | Execution time |
(3) Execution-Plan Reading Flow
flowchart TD
A["EXPLAIN ANALYZE output"] --> B{"Top node type?"}
B -->|"Seq Scan"| C{"Rows vs estimated?"}
C -->|"Estimate off"| D["RUN ANALYZE<br/>Update statistics"]
C -->|"Estimate OK"| E{"Filter selectivity?"}
E -->|"Low (< 5%)"| F["Add index on filter column"]
E -->|"High (> 15%)"| G["Seq Scan is OK"]
B -->|"Index Scan"| H["✅ Good for low selectivity"]
B -->|"Hash Join"| I{"Hash table spill?"}
I -->|"Yes (work_mem low)"| J["Increase work_mem"]
I -->|"No"| K["✅ Good"]
B -->|"Nest Loop"| L{"Outer rows × inner cost?"}
L -->|"Too high"| M["Consider Hash Join<br/>or add inner index"]
L -->|"Reasonable"| N["✅ Good"]
4. Operation: Scan-Type Comparison
▶ Example: Seq Scan Full Table Scan
SQL
-- No index on status, planner chooses Seq Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM orders WHERE status = 'pending';
TEXT
Aggregate (actual time=45.123..45.124 rows=1 loops=1)
-> Seq Scan on orders (actual time=0.012..42.890 rows=50000 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 950000
▶ Example: Index Scan Precise Lookup
SQL
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- High selectivity: planner uses Index Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;
TEXT
Index Scan using idx_orders_user_id on orders (actual time=0.015..0.018 rows=3 loops=1)
Index Cond: (user_id = 1001)
▶ Example: Bitmap Scan Medium Selectivity
SQL
-- Moderate selectivity: planner chooses Bitmap
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id BETWEEN 1000 AND 1100;
TEXT
Bitmap Heap Scan on orders (actual time=0.523..2.145 rows=523 loops=1)
Recheck Cond: (user_id >= 1000 AND user_id <= 1100)
-> Bitmap Index Scan on idx_orders_user_id (actual time=0.412..0.412 rows=523 loops=1)
Index Cond: (user_id >= 1000 AND user_id <= 1100)
| Scan type | Selectivity | I/O pattern | Best when |
|---|---|---|---|
| Seq Scan | Whole table or > 15% | Sequential read | Small table / large result |
| Index Scan | < 5% | Random read | Precise lookup |
| Bitmap Scan | 5%-15% | Random then sequential | Range + sorting |
▶ Example: Nest Loop vs Hash Join
SQL
-- Small outer table + indexed inner = Nest Loop
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1001;
-- Large outer + unsorted inner = Hash Join
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date > '2024-01-01';
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: Find and Fix a Missing Index
SQL
-- Slow query: Seq Scan on large table
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan, cost=0.00..15432.00, actual time=120ms
-- Add index
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Re-analyze for accurate statistics
ANALYZE orders;
-- Same query now uses Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Index Scan, cost=0.42..890.00, actual time=2ms
Output:
TEXT
CREATE TABLE
5. Concept: pg_stat_statements Slow Queries
(1) Enable and Configure
BASH
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
SQL
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
(2) Key Metric Interpretation
| Column | Meaning | Optimization direction |
|---|---|---|
| total_exec_time | Total execution time | Optimize the most time-consuming queries |
| mean_exec_time | Average execution time | Single slow query |
| calls | Call count | Prioritize high-frequency queries |
| rows | Total rows returned | Check whether too many rows are returned |
| shared_blks_hit | Buffer hits | Low hit rate → increase shared_buffers |
| shared_blks_read | Disk reads | High disk reads → add index / increase cache |
▶ Example: Find Top N Slow Queries
SQL
-- Top 10 by total time
SELECT left(query, 80) AS query_preview,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 1) AS avg_ms,
rows,
round((100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Output:
TEXT
result
----------
42.50
(1 row)
▶ Example: Find I/O-Intensive Queries
SQL
-- Queries with most disk reads
SELECT left(query, 80) AS query_preview,
shared_blks_read AS disk_reads,
shared_blks_hit AS cache_hits,
calls,
round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;
Output:
TEXT
result
----------
42.50
(1 row)
6. Concept: Connection Pool and Configuration Tuning
(1) PgBouncer Connection Pool
| Mode | Description | Best for |
|---|---|---|
| Session pooling | Connection bound to client | Needs session variables / temp tables |
| Transaction pooling | Connection returned after transaction | Most web applications |
| Statement pooling | Connection returned after statement | Simple queries without transactions |
BASH
# pgbouncer.ini
[databases]
shop_db = host=127.0.0.1 port=5432 dbname=shop
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
| Parameter | Recommended | Description |
|---|---|---|
| max_client_conn | 500-1000 | Max client connections |
| default_pool_size | 20-50 | Pool size per database/user |
| reserve_pool_size | 5-10 | Burst-traffic buffer pool |
| pool_mode | transaction | Recommended for web apps |
(2) PostgreSQL Core Parameters
BASH
# postgresql.conf tuning for 16GB RAM server
shared_buffers = 4GB # 25% of RAM
work_mem = 64MB # per-sort-operation memory
effective_cache_size = 12GB # 75% of RAM
maintenance_work_mem = 1GB # for VACUUM, CREATE INDEX
effective_io_concurrency = 200 # SSD; 2 for HDD
random_page_cost = 1.1 # SSD; 4.0 for HDD
| Parameter | Default | Recommended (16GB RAM) | Description |
|---|---|---|---|
| shared_buffers | 128MB | 25% RAM | Shared buffers |
| work_mem | 4MB | 32-128MB | Sort/hash memory |
| effective_cache_size | 4GB | 75% RAM | Planner cache estimate |
| maintenance_work_mem | 64MB | 512MB-1GB | Maintenance operation memory |
| max_parallel_workers | 8 | CPU cores | Parallel worker processes |
▶ Example: Verify Parameters Take Effect
SQL
-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
-- Change at runtime (no restart needed for some)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();
-- Must-restart parameters
SELECT name, setting, boot_val, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');
Output:
TEXT
ALTER TABLE
7. Concept: Autovacuum Principle and Tuning
(1) Why Autovacuum Is Needed
PostgreSQL's MVCC mechanism: UPDATE/DELETE produce dead tuples, which need VACUUM to reclaim space — otherwise tables bloat and queries slow down.
SQL
-- Check dead tuple ratio
SELECT relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
(2) Autovacuum Key Parameters
| Parameter | Default | Tuning suggestion |
|---|---|---|
| autovacuum | on | Must be enabled |
| autovacuum_vacuum_threshold | 50 | Base dead-tuple count to trigger vacuum |
| autovacuum_vacuum_scale_factor | 0.2 | Trigger vacuum at 20% of rows as dead tuples |
| autovacuum_analyze_scale_factor | 0.1 | Trigger analyze at 10% change |
| autovacuum_vacuum_cost_delay | 2ms | Vacuum throttling delay |
| autovacuum_vacuum_cost_limit | 200 | Vacuum I/O limit per round |
▶ Example: Tune autovacuum for a Large Table
SQL
-- For high-write tables, lower scale factor for more frequent vacuum
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_vacuum_cost_delay = '1ms',
autovacuum_vacuum_cost_limit = 1000
);
-- For orders with 10M rows, vacuum triggers at:
-- 10M × 0.05 = 500K dead tuples (vs default 2M)
Output:
TEXT
-- SQL statement executed successfully
▶ Example: Monitor Vacuum Progress
SQL
-- PG 12+: track vacuum progress
SELECT pid,
relid::regclass AS table_name,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count
FROM pg_stat_progress_vacuum;
Output:
TEXT
count
-------
5
(1 row)
8. Operation: Parallel Query and Monitoring
▶ Example: Enable Parallel Query
SQL
-- Enable parallel query (PG 10+)
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET parallel_tuple_cost = 0.001;
SET min_parallel_table_scan_size = '8MB';
-- Verify parallel plan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*), category
FROM products
GROUP BY category;
TEXT
Finalize Aggregate (actual time=12.3..12.4 rows=5 loops=1)
-> Gather (actual time=12.1..12.3 rows=15 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual time=8.5..8.6 rows=5 loops=3)
-> Parallel Seq Scan on products (actual time=0.2..6.1 rows=33333 loops=3)
▶ Example: pg_stat_user_tables Monitoring
SQL
SELECT relname AS table_name,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
round(100.0 * idx_scan / nullif(idx_scan + seq_scan, 0), 2) AS idx_scan_pct,
n_live_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
| Monitoring metric | Alert threshold | Action |
|---|---|---|
| seq_tup_read >> idx_tup_fetch | High seq_scan | Add index |
| dead_pct > 20% | Severe bloat | Tune autovacuum |
| cache_hit_pct < 95% | Insufficient buffer | Increase shared_buffers |
| last_autovacuum > 7 days | Vacuum not timely | Lower scale_factor |
▶ Example: Identify and Fix Performance Anti-Patterns
SQL
-- Anti-pattern 1: OR condition preventing index use
-- BAD
SELECT * FROM orders WHERE user_id = 1001 OR status = 'pending';
-- GOOD: use UNION ALL
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1001;
-- Anti-pattern 2: function wrapping indexed column
-- BAD
SELECT * FROM orders WHERE lower(status) = 'pending';
-- GOOD
SELECT * FROM orders WHERE status = lower('PENDING');
-- Anti-pattern 3: LIKE with leading wildcard
-- BAD
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD: pg_trgm or full-text search
SELECT * FROM products WHERE name % 'phone';
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
9. Comprehensive Example
SQL
-- Full performance optimization workflow for Charlie's e-commerce system
-- Step 1: Identify slow queries
SELECT left(query, 60) AS q, calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;
-- Step 2: Analyze worst query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
AND o.status = 'completed';
-- Step 3: Add missing indexes
CREATE INDEX idx_orders_date_status ON orders (order_date, status);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Step 4: Update statistics
ANALYZE orders;
ANALYZE order_items;
ANALYZE products;
-- Step 5: Tune autovacuum for high-write tables
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_vacuum_cost_limit = 1000
);
-- Step 6: Increase work_mem for complex sorts
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();
-- Step 7: Verify improvement
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
AND o.status = 'completed';
-- Step 8: Monitor table health
SELECT relname,
n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'products')
ORDER BY n_dead_tup DESC;
❓ FAQ
Q What is the difference between EXPLAIN and EXPLAIN ANALYZE?
A EXPLAIN shows only the estimated plan and does not execute the query; EXPLAIN ANALYZE actually runs it and returns real timing and row counts. Note that ANALYZE executes INSERT/UPDATE/DELETE, so when analyzing DML, wrap it in a transaction and ROLLBACK afterward.
Q The cost value is large but it's actually fast — why?
A cost is a unit value estimated by the planner (not milliseconds), influenced by statistics. If the ANALYZE statistics are stale, the cost may be inaccurate. Run ANALYZE regularly to keep statistics fresh.
Q How much should shared_buffers be set to?
A Generally 25% of physical RAM, not exceeding 40%. On Linux, above 40% it may conflict with the OS page cache. On Windows, keep it within 512MB-1GB.
Q Can a large work_mem cause OOM?
A work_mem is the memory limit per sort/hash operation, and one complex query may have several such operations running at once. Setting it too large can indeed cause OOM. Recommended 32-128MB, tuned by monitoring actual usage.
Q What if autovacuum can't keep up with the write rate?
A Lower scale_factor (e.g., 0.02), raise cost_limit (e.g., 2000), reduce cost_delay (e.g., 1ms), and set these per large table. In extreme cases, run VACUUM manually in parallel.
Q When does parallel query take effect?
A When the table size exceeds min_parallel_table_scan_size (default 8MB), the query cost exceeds the parallel startup threshold, and max_parallel_workers has free capacity. Small tables or simple queries won't go parallel.
Q Is Seq Scan always worse than Index Scan?
A Not necessarily. When most of a table's rows must be returned, Seq Scan's sequential reads are more efficient than Index Scan's random reads. The PG planner automatically picks the lower-cost option.
📖 Summary
- EXPLAIN ANALYZE is the first tool for performance tuning; reading operators and cost is the key
- Seq Scan / Index Scan / Bitmap Scan suit different selectivity scenarios
- pg_stat_statements locates slow queries: watch total_time, calls, cache_hit
- PgBouncer transaction mode is the standard connection pool for web apps
- shared_buffers 25% RAM, work_mem by query complexity, effective_cache_size 75% RAM
- Autovacuum on high-write tables needs a lower scale_factor and higher cost_limit
- Avoid common anti-patterns: OR conditions, function-wrapped index columns, LIKE leading wildcards
📝 Exercises
-
⭐ For a 1-million-row test table, observe the actual-time difference between Seq Scan and Index Scan with EXPLAIN ANALYZE, and record the gap between the cost estimate and the actual time.
-
⭐⭐ Configure pg_stat_statements, collect 24 hours of query statistics, and write a slow-query report: list the Top 5 by each of total_exec_time, mean_exec_time, and shared_blks_read, and give optimization suggestions.
-
⭐⭐⭐ Simulate Charlie's scenario: create 5 tables (orders/order_items/users/products/payments), insert test data, use EXPLAIN ANALYZE to find 3 performance problems (missing index, insufficient work_mem, lagging autovacuum), fix each one, then use EXPLAIN ANALYZE to verify the performance improvement multiple for each fix.
