Skip to content
Published on

PostgreSQL Performance Tuning Practical Guide — Query Optimization, Index Strategy, EXPLAIN Analysis

🇰🇷KO🇺🇸EN🇯🇵JA
Authors
  • Name
    Twitter
PostgreSQL Performance Tuning

Introduction

"The query is slow" — every backend developer hears this at least once. PostgreSQL is a powerful RDBMS, but without proper tuning, performance degrades rapidly as data grows. This article covers PostgreSQL performance tuning techniques you can apply immediately in production, step by step from EXPLAIN analysis to index strategies and query refactoring.

1. EXPLAIN — Reading Query Execution Plans

EXPLAIN vs EXPLAIN ANALYZE

-- Estimated execution plan (does NOT actually execute)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Actual execution + measured time included (Warning: actually executes the query)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Most detailed analysis (includes buffer usage)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

How to Read EXPLAIN Output

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

Hash Join  (cost=1250.00..5680.50 rows=15000 width=48)
           (actual time=12.5..89.3 rows=14800 loops=1)
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders o  (cost=0.00..3500.00 rows=15000 width=16)
                           (actual time=0.02..45.1 rows=14800 loops=1)
       Filter: (created_at > '2026-01-01')
       Rows Removed by Filter: 85200
  -> Hash  (cost=1000.00..1000.00 rows=50000 width=36)
           (actual time=12.3..12.3 rows=50000 loops=1)
       -> Seq Scan on users u  (cost=0.00..1000.00 rows=50000 width=36)
                               (actual time=0.01..6.8 rows=50000 loops=1)
Planning Time: 0.25 ms
Execution Time: 95.8 ms

Key Metrics Interpretation

MetricMeaningNotes
costEstimated cost (startup..total)Relative units, not absolute values
rowsEstimated row countIf significantly different from actual, update stats
actual timeActual elapsed time (ms)startup..total
loopsNumber of iterationsactual time x loops = real total time
Rows Removed by FilterRows removed by filterLarge numbers indicate an index is needed
Buffers: shared hit/readCache hits/disk readsHigh read count means insufficient memory

Warning Signs

# Seq Scan on large table -> index needed
Seq Scan on orders  (rows=1000000)

# Nested Loop with large rows -> change join strategy
Nested Loop  (actual loops=50000)

# Sort with external disk usage -> insufficient work_mem
Sort Method: external merge  Disk: 128000kB

# Estimated rows vs actual rows mismatch -> ANALYZE needed
(rows=100) ... (actual rows=50000)

2. Index Strategy — Designing the Right Indexes

B-Tree Index (Default)

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Partial index (conditional index)
CREATE INDEX idx_orders_active
ON orders(user_id)
WHERE status = 'active';

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Column Order in Composite Indexes

Column order in composite indexes has a decisive impact on performance:

-- Index: (user_id, created_at, status)

-- Can use index (matches from left)
WHERE user_id = 1
WHERE user_id = 1 AND created_at > '2026-01-01'
WHERE user_id = 1 AND created_at > '2026-01-01' AND status = 'active'

-- Cannot use index (skips middle column)
WHERE user_id = 1 AND status = 'active'  -- skips created_at
WHERE created_at > '2026-01-01'          -- missing user_id
WHERE status = 'active'                  -- missing first column

Principle: Place equality (=) condition columns first, and range (greater than, less than, BETWEEN) conditions last.

GIN Index (Full-Text Search, JSONB, Arrays)

-- JSONB field index
CREATE INDEX idx_products_metadata
ON products USING GIN(metadata);

-- JSONB search is now fast
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Array index
CREATE INDEX idx_posts_tags
ON posts USING GIN(tags);

SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

Cases Where Indexes Are Not Used

-- Index ignored when function is applied
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Create a functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Type mismatch
SELECT * FROM users WHERE id = '123';  -- id is integer but comparing with string
-- Correct type
SELECT * FROM users WHERE id = 123;

-- Leading wildcard in LIKE
SELECT * FROM users WHERE name LIKE '%kim%';  -- Full Scan
-- Fixed prefix
SELECT * FROM users WHERE name LIKE 'kim%';  -- Index can be used

-- OR condition
SELECT * FROM users WHERE email = 'a@b.com' OR name = 'Kim';
-- Split with UNION
SELECT * FROM users WHERE email = 'a@b.com'
UNION ALL
SELECT * FROM users WHERE name = 'Kim';

Monitoring Index Usage

-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index size per table
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(tablename::regclass)) as total_size,
       pg_size_pretty(pg_indexes_size(tablename::regclass)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

3. Query Optimization Patterns

Eliminating N+1 Queries

-- N+1 pattern (common with ORMs)
-- Query 1: SELECT * FROM users LIMIT 100;
-- Query 2-101: SELECT * FROM orders WHERE user_id = ?;

-- Use JOIN to fetch at once
SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
LIMIT 100;

-- Or use subquery + IN
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2026-01-01'
);

Pagination Optimization

-- OFFSET approach (slower as pages get deeper)
SELECT * FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20;

-- Keyset Pagination (cursor-based)
SELECT * FROM orders
WHERE id < 900000  -- last id of the previous page
ORDER BY id DESC
LIMIT 20;

-- Covering index + subquery
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20
) sub ON o.id = sub.id;

EXISTS vs IN

-- EXISTS is more efficient for large datasets (correlated subquery)
-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 10000
);

-- IN (when subquery result is large)
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 10000
);

COUNT Optimization

-- Exact COUNT (causes Full Scan)
SELECT COUNT(*) FROM orders;

-- Approximate value when sufficient
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'orders';

-- Conditional COUNT optimization
-- Index Only Scan possible with an index
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- Required: CREATE INDEX idx_orders_status ON orders(status);

CTE vs Subquery

-- In PostgreSQL 12+, CTEs are inlined by default
-- MATERIALIZED hint forces materialization

-- Auto-inlined (optimizer optimizes)
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'K%';

-- Forced materialization (prevents redundant execution)
WITH active_users AS MATERIALIZED (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users au
JOIN orders o ON au.id = o.user_id;

4. Configuration Tuning — postgresql.conf

Memory Settings

# shared_buffers: 25% of total RAM (most important!)
# Based on 16GB RAM
shared_buffers = 4GB

# effective_cache_size: Expected memory including OS cache (75% of RAM)
effective_cache_size = 12GB

# work_mem: Memory for sort/hash operations (per session x per query)
# Caution: connections x work_mem = total possible memory usage
work_mem = 256MB

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

WAL and Checkpoints

# WAL size (write-heavy workloads)
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB

# Checkpoint interval
checkpoint_completion_target = 0.9

Query Planner

# Enable parallel queries
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Random I/O cost (lower for SSD)
random_page_cost = 1.1  # HDD: 4.0, SSD: 1.1

# Statistics collection precision
default_statistics_target = 200  # Default: 100

5. VACUUM and Statistics Management

Why VACUUM Is Needed

PostgreSQL uses MVCC (Multi-Version Concurrency Control). When rows are UPDATEd or DELETEd, existing rows are not immediately removed but left as "dead tuples." VACUUM cleans them up.

-- Check dead tuples per table
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual VACUUM (normally handled by autovacuum)
VACUUM ANALYZE orders;

-- VACUUM FULL (caution: table lock! — reclaims disk space)
VACUUM FULL orders;  -- Do NOT use in production!

Autovacuum Tuning

# Autovacuum default settings
autovacuum = on
autovacuum_max_workers = 5

# Per-table settings for large tables
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,  -- Run when 1% are dead tuples
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005
);

Updating Statistics

-- When EXPLAIN estimated rows differ significantly from actual
ANALYZE orders;

-- Update statistics for entire DB
ANALYZE;

-- Increase statistics precision for a specific column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

6. Practical Troubleshooting — Finding Slow Queries

Discovering Slow Queries with pg_stat_statements

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries
SELECT query,
       calls,
       ROUND(total_exec_time::numeric, 2) as total_time_ms,
       ROUND(mean_exec_time::numeric, 2) as avg_time_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Most frequently called queries (by total time)
SELECT query, calls,
       ROUND(total_exec_time::numeric, 2) as total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Checking Currently Running Queries

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- Queries waiting for locks
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 bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.relation = bl.relation
  AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

7. Performance Checklist

Items to verify before going to production:

  • Verify execution plans for key queries with EXPLAIN ANALYZE
  • Check that Seq Scan is not occurring on large tables
  • Verify composite index column order matches query patterns
  • Clean up unused indexes (prevent write performance degradation)
  • Set up slow query monitoring with pg_stat_statements
  • Confirm autovacuum is working properly
  • Review memory settings like shared_buffers, work_mem
  • Check connection pooling (PgBouncer, etc.) is in place
  • Keep statistics up to date with regular ANALYZE runs

Quiz

Q1: What is the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN only shows the estimated execution plan, while EXPLAIN ANALYZE actually executes the query and shows measured times and row counts. Since ANALYZE actually executes, caution is needed with INSERT/UPDATE/DELETE.

Q2: Why does a query with WHERE a = 1 AND c = 3 on a composite index (a, b, c) fail to fully utilize the index?

B-Tree composite indexes match from left to right in order. If you skip the middle column (b), the index for column c cannot be used. Only column a uses the index.

Q3: Why is OFFSET-based pagination slow on deep pages? OFFSET N reads and discards N rows. With OFFSET 100000, it reads 100,000 rows, discards them, and returns from the next row onward, so deeper pages require reading more rows.

Q4: Why should random_page_cost be lowered to 1.1 on SSDs? SSDs have almost no difference between random I/O and sequential I/O performance. The default value of 4.0 is based on HDDs, so lowering it to 1.1 on SSDs encourages the planner to choose index scans more aggressively.

Q5: Why do dead tuples occur in PostgreSQL? Due to the MVCC architecture. When UPDATE/DELETE occurs, existing rows are not immediately deleted but left as "dead tuples" so other transactions can still reference them. VACUUM cleans them up.

Q6: What is the solution when WHERE LOWER(email) = 'test@example.com' cannot use an index?

Create a functional index: CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Q7: What is the recommended setting for shared_buffers? Approximately 25% of total system RAM. Example: 16GB RAM -> shared_buffers = 4GB.

Q8: What does a very large "Rows Removed by Filter" value in EXPLAIN output mean?

It means many rows are being read from the table and then most are discarded by the filter. Adding an appropriate index can reduce unnecessary row reads.

Q9: Why should VACUUM FULL not be used in production? VACUUM FULL acquires an ACCESS EXCLUSIVE lock on the table, blocking all reads and writes to that table until the operation completes.

Q10: Between EXISTS and IN, which is more efficient for large subqueries? Generally, EXISTS is more efficient. EXISTS stops immediately when it finds the first matching row, while IN may need to generate the entire subquery result first.

Discuss on Twitter • View on GitHub