PostgreSQL Performance Tuning: Indexes, Query Optimization

PostgreSQL Performance Tuning: Indexes, Query Optimization, and Configuration

Quick answer. Most PostgreSQL slowness isn't Postgres' fault; it's missing indexes, unoptimized queries, or misconfiguration. Never optimize blind. Enable pg_stat_statements first to find the slowest queries by total execution time, then fix them with targeted indexes, query rewrites, and configuration tuning rather than guessing at the bottleneck.

PostgreSQL is fast. Most PostgreSQL performance problems are not PostgreSQL's fault — they're missing indexes, unoptimized queries, or misconfiguration. This guide covers the diagnostics and fixes that consistently move the needle.

---

Step 1: Find the Slow Queries

Never optimize blind. Find what's actually slow first.

-- Enable pg_stat_statements (add to postgresql.conf, then restart)
-- shared_preload_libraries = 'pg_stat_statements'

-- Top 20 slowest queries by total execution time
SELECT
  LEFT(query, 120)          AS query_preview,
  calls,
  ROUND(total_exec_time::numeric / 1000, 2)  AS total_secs,
  ROUND(mean_exec_time::numeric, 2)           AS avg_ms,
  ROUND(stddev_exec_time::numeric, 2)         AS stddev_ms,
  rows / NULLIF(calls, 0)                     AS avg_rows
FROM pg_stat_statements
WHERE calls > 50
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with highest cache miss rate (reads from disk vs memory)
SELECT
  LEFT(query, 100) AS query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE calls > 10
  AND (shared_blks_hit + shared_blks_read) > 1000
ORDER BY cache_hit_pct ASC
LIMIT 20;

-- Current long-running queries (> 30 seconds)
SELECT
  pid,
  now() - query_start AS duration,
  state,
  LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > INTERVAL '30 seconds'
ORDER BY duration DESC;

---

Step 2: Read EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and shows the actual execution plan with real row counts and timing.

-- Always use ANALYZE, BUFFERS to see actual data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  o.id,
  o.total,
  u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

What to look for:

Seq Scan on orders  (cost=0.00..45823.00 rows=123456 width=64)
                    (actual time=0.124..892.431 rows=123456 loops=1)
  Filter: (status = 'pending' AND created_at > ...)
  Rows Removed by Filter: 876544
  Buffers: shared hit=12453 read=21089

Red flags:

• Seq Scan on a large table — almost always means a missing index
• Rows Removed by Filter >> actual rows — index would help
• Buffers: read= is high — data not in memory, disk I/O bound
• actual rows >> estimated rows — stale statistics, run ANALYZE
• Hash Join with large hash batches — may need work_mem increase

Green flags:

• Index Scan or Index Only Scan — using an index
• Buffers: shared hit= dominates over read — data in cache
• Estimated and actual rows are close — good statistics

---

Step 3: Create the Right Indexes

B-tree Indexes (Default — Most Common)

-- Single column: equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);

-- Composite: when you always filter by both columns
-- Order matters: most selective column first, OR the column used in range last
CREATE INDEX CONCURRENTLY idx_orders_status_created 
ON orders(status, created_at DESC);
-- Supports: WHERE status = 'pending' ORDER BY created_at DESC

-- Partial index: only index a subset of rows (smaller, faster)
CREATE INDEX CONCURRENTLY idx_orders_pending 
ON orders(created_at DESC)
WHERE status = 'pending';
-- 10x smaller than full index if 10% of orders are pending

Covering Indexes (Index Only Scan)

When the query only reads columns in the index, PostgreSQL never touches the table:

-- Query: SELECT id, email FROM users WHERE email LIKE 'john%'
-- Covering index: includes all SELECT columns
CREATE INDEX CONCURRENTLY idx_users_email_covering
ON users(email) INCLUDE (id, display_name);
-- Index Only Scan — table never touched

GIN Indexes for JSONB and Arrays

-- JSONB column with GIN index — supports @>, ?, ?| operators
CREATE INDEX CONCURRENTLY idx_products_attributes_gin
ON products USING GIN (attributes);

-- Query: products where attributes contains "color": "red"
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Array column
CREATE INDEX CONCURRENTLY idx_posts_tags_gin ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

Text Search Indexes

-- Full-text search with tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX CONCURRENTLY idx_articles_search ON articles USING GIN (search_vector);

-- Query
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

---

Step 4: Rewrite Problematic Query Patterns

N+1 Queries — Fix with JOINs or CTEs

-- ❌ N+1: For each order, fetch user separately
-- Application code doing this in a loop is a common ORM problem

-- ✅ Single query with JOIN
SELECT
  o.id,
  o.total,
  o.created_at,
  u.email,
  u.display_name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

Avoid Functions on Indexed Columns

-- ❌ Function on indexed column → index unusable
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- ✅ Option 1: Functional index
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users(LOWER(email));

-- ✅ Option 2: Store pre-lowercased
ALTER TABLE users ADD CONSTRAINT email_lowercase CHECK (email = LOWER(email));

Efficient Pagination

-- ❌ OFFSET pagination: scans all skipped rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- PostgreSQL scans 10,020 rows, returns 20

-- ✅ Cursor-based: always O(1) for index scan
SELECT * FROM orders
WHERE id > $last_seen_id   -- Pass last ID from previous page
ORDER BY id
LIMIT 20;

Window Functions Instead of Correlated Subqueries

-- ❌ Correlated subquery: executes once per row
SELECT
  o.id,
  o.user_id,
  o.total,
  (SELECT SUM(total) FROM orders WHERE user_id = o.user_id) AS user_lifetime_value
FROM orders o
WHERE o.status = 'completed';

-- ✅ Window function: single pass
SELECT
  id,
  user_id,
  total,
  SUM(total) OVER (PARTITION BY user_id) AS user_lifetime_value
FROM orders
WHERE status = 'completed';

---

Step 5: Configuration Tuning

The PostgreSQL defaults are conservative. These settings have the biggest impact:

# postgresql.conf — production settings for a 16GB RAM server

# Memory
shared_buffers = 4GB              # 25% of total RAM
effective_cache_size = 12GB       # 75% of total RAM (hint to planner)
work_mem = 64MB                   # Per-operation memory (sorts, hash joins)
maintenance_work_mem = 1GB        # For VACUUM, index builds

# WAL / Write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Connection settings (use PgBouncer for connection pooling)
max_connections = 200

# Statistics — helps planner make better decisions
default_statistics_target = 200   # Default 100; increase for complex queries

# Logging — identify slow queries
log_min_duration_statement = 1000 # Log queries > 1 second
log_checkpoints = on
log_lock_waits = on

# Apply PGTune recommendations for your server size
# https://pgtune.leopard.in.ua/

# Check current settings
SELECT name, setting, unit FROM pg_settings
WHERE name IN (
  'shared_buffers', 'effective_cache_size', 'work_mem',
  'max_connections', 'default_statistics_target'
);

---

Step 6: Monitor Table Bloat and Autovacuum

PostgreSQL uses MVCC — deleted/updated rows leave dead tuples that accumulate and slow queries.

-- Tables with highest bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manual vacuum on high-bloat tables
VACUUM ANALYZE orders;

-- Force vacuum (resets dead tuple counter even if autovacuum recently ran)
VACUUM (ANALYZE, VERBOSE) orders;

# Tune autovacuum for high-write tables
# (add to postgresql.conf or ALTER TABLE SET)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- Vacuum when 1% of rows are dead (default 20%)
  autovacuum_analyze_scale_factor = 0.005
);

---

Quick Performance Wins Checklist

---

The Viprasol Method

We run PostgreSQL performance audits — finding slow queries, identifying missing indexes, tuning configuration, and implementing schema changes that improve performance by 5–50×.

→ Database performance audit →
→ Software Development Services →

---

Continue Learning

• Database Schema Migration
• Software Scalability
• Multi-Tenant Architecture
• Data Engineering Pipeline

---

Sources

• PostgreSQL Documentation — Performance Tips
• pg_stat_statements
• Use the Index, Luke
• PGTune