database-postgres-query-optimization

Debugging slow queries in PostgreSQL

PostgreSQL Query Optimization

Scope: Query analysis, EXPLAIN plans, index strategies, query rewriting Lines: ~350 Last Updated: 2025-10-18

When to Use This Skill

Activate this skill when:

Debugging slow queries in PostgreSQL
Analyzing EXPLAIN ANALYZE output
Designing index strategies
Optimizing JOIN operations
Investigating performance degradation
Planning database capacity

Core Concepts

Query Execution Stages

Parsing - SQL syntax validation
Planning - Query optimizer creates execution plan
Execution - Plan executed, data retrieved
Results - Data returned to client

The planner's goal: find the lowest-cost plan.

Cost Model

PostgreSQL uses a cost-based optimizer:

Startup cost: Time before first row returned
Total cost: Time to return all rows
Sequential scan cost: Reading full table
Index scan cost: Reading index + table lookups

-- Cost units are arbitrary (not milliseconds)
-- Lower cost = better plan (usually)
Seq Scan on users  (cost=0.00..1234.56 rows=10000 width=64)
Index Scan using idx_users_email  (cost=0.29..8.31 rows=1 width=64)

Reading EXPLAIN ANALYZE Output

Basic EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Output shows the planned execution (no actual execution).

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Output shows planned + actual execution with real timing.

CRITICAL: EXPLAIN ANALYZE actually runs the query (including writes!). Use with caution on production.

Key Metrics to Watch

Seq Scan on users  (cost=0.00..1234.56 rows=10000 width=64) (actual time=0.012..12.345 rows=1 loops=1)
                    ^^^^^^^^^^^^^^^^^^^^                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                    PLANNED cost/rows                          ACTUAL time/rows/loops

Red flags:

actual rows >> estimated rows - Statistics out of date
actual time >> expected - I/O bottleneck or missing index
Seq Scan on large tables - Usually needs index
loops > 1 on expensive operations - Nested loop inefficiency

Scan Types

Sequential Scan

Seq Scan on orders  (cost=0.00..15234.56 rows=500000 width=128)

When it happens:

No usable index
Table too small (faster to scan than use index)
Query reads >5-10% of table (index overhead not worth it)

Good for:

Small tables (<1000 rows typically)
Reading most/all of table
Analytics queries on warehouses

Bad for:

Large tables with selective filters
OLTP lookups
Real-time queries

Index Scan

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=128)
  Index Cond: (user_id = 12345)

How it works:

Traverse B-tree index to find entries
Fetch heap tuples (actual table rows) via pointers
Return results

Good for:

Selective queries (<5% of rows)
Equality comparisons (=, IN)
Range scans on indexed columns
ORDER BY on indexed columns

Cost factors:

Random I/O to fetch heap tuples (expensive)
Multiple index lookups if fetching many rows

Index Only Scan

Index Only Scan using idx_orders_user_created on orders  (cost=0.42..4.44 rows=1 width=8)
  Index Cond: (user_id = 12345)
  Heap Fetches: 0

How it works:

Traverse index
Return data DIRECTLY from index (no heap lookup)
Check visibility map to verify row visibility

Requirements:

All query columns in index (covering index)
Table must be vacuumed (visibility map current)

BEST PERFORMANCE: No random I/O to heap.

-- Create covering index for this query
CREATE INDEX idx_orders_user_created ON orders(user_id) INCLUDE (created_at);

-- Query can now use Index Only Scan
SELECT created_at FROM orders WHERE user_id = 12345;

Bitmap Index Scan

Bitmap Heap Scan on orders  (cost=123.45..5678.90 rows=5000 width=128)
  Recheck Cond: (status = 'pending' OR status = 'processing')
  ->  BitmapOr  (cost=123.45..123.45 rows=5000 width=0)
        ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..61.00 rows=2500 width=0)
              Index Cond: (status = 'pending')
        ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..61.00 rows=2500 width=0)
              Index Cond: (status = 'processing')

How it works:

Build in-memory bitmap of matching rows
Combine bitmaps (OR, AND operations)
Sort row locations
Fetch heap tuples in sequential order (reduces random I/O)

Good for:

Combining multiple indexes (OR conditions)
Fetching moderate number of rows (5-25% of table)
Reducing random I/O

Better than:

Multiple Index Scans → merge results
Full Seq Scan when filtered set is small enough

Index Strategies

Index Types

B-tree (Default, 95% of use cases)

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_composite ON orders(user_id, created_at);

Good for:

Equality: email = 'foo@example.com'
Range: created_at > '2024-01-01'
Sorting: ORDER BY created_at
Prefix matching: email LIKE 'foo%' (NOT LIKE '%foo')

Multi-column indexes (composite):

Order matters: (user_id, created_at) can be used for:
user_id = X
user_id = X AND created_at > Y
NOT efficient for created_at > Y alone

Hash (Rare, equality only)

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Good for:

Equality ONLY: email = 'foo@example.com'
Slightly smaller than B-tree

Cannot:

Range queries
Sorting
Prefix matching

Usually not needed - B-tree handles equality well.

GiST (Geometric/Full-text)

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);
CREATE INDEX idx_documents_fts ON documents USING GIST (to_tsvector('english', content));

Good for:

Geometric data (PostGIS)
Full-text search
Range types
Custom data types

GIN (Full-text, JSONB, arrays)

CREATE INDEX idx_documents_fts ON documents USING GIN (to_tsvector('english', content));
CREATE INDEX idx_users_tags ON users USING GIN (tags); -- array column
CREATE INDEX idx_metadata_json ON events USING GIN (metadata); -- jsonb column

Good for:

Full-text search (faster than GiST for static data)
JSONB queries: metadata @> '{"status": "active"}'
Array containment: tags @> ARRAY['postgres']

Trade-offs:

Larger than GiST
Slower writes
Faster reads for containment queries

BRIN (Block Range Index)

CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at);

Good for:

Very large tables with natural clustering (e.g., time-series)
Append-only data
Low-selectivity queries acceptable

Extremely small index (1000x smaller than B-tree).

Trade-off: Less precise, may scan extra blocks.

Index Selection Decision Tree

Start: Do I need an index?
│
├─ Table < 1000 rows? → NO (Seq Scan is fine)
├─ Query reads >10% of table? → MAYBE (test both)
└─ Query is selective? → YES
   │
   ├─ What type of query?
   │  ├─ Equality (=, IN) → B-tree
   │  ├─ Range (<, >, BETWEEN) → B-tree
   │  ├─ Sorting (ORDER BY) → B-tree on sort columns
   │  ├─ Full-text search → GIN or GiST
   │  ├─ JSONB queries → GIN
   │  ├─ Geometric queries → GiST
   │  ├─ Time-series append-only → BRIN
   │  └─ Array containment → GIN
   │
   ├─ Multiple columns in WHERE?
   │  ├─ Always used together → Composite index (user_id, created_at)
   │  ├─ Used independently → Separate indexes (or partial indexes)
   │  └─ OR conditions → Bitmap scan or separate indexes
   │
   └─ Can I cover the query? → Add INCLUDE columns for Index Only Scan

Common Query Anti-Patterns

1. N+1 Query Problem

-- Anti-pattern: Load users, then loop and query orders for each
SELECT * FROM users;
-- In application loop:
--   SELECT * FROM orders WHERE user_id = ?

-- Solution: JOIN or batch query
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

2. SELECT * Instead of Specific Columns

-- Anti-pattern
SELECT * FROM orders WHERE user_id = 123;

-- Better: Select only needed columns
SELECT id, total, created_at FROM orders WHERE user_id = 123;

Why it matters:

Enables Index Only Scans (covering indexes)
Reduces network transfer
Lower memory usage

3. Non-Sargable Queries (Can't Use Index)

-- Anti-pattern: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'foo@example.com';

-- Solution: Functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Or store email in lowercase always

-- Anti-pattern: Wildcard at start
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Solution: Full-text search or trigram index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);

4. Unnecessary DISTINCT

-- Anti-pattern: DISTINCT when not needed
SELECT DISTINCT user_id FROM orders WHERE status = 'pending';

-- Better if user_id is already unique per status:
SELECT user_id FROM orders WHERE status = 'pending';

5. OR Conditions Across Tables

-- Anti-pattern: OR across tables
SELECT * FROM orders WHERE user_id = 123 OR vendor_id = 456;

-- Better: UNION
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE vendor_id = 456;

Query Rewriting Patterns

Pattern 1: Subquery → JOIN

-- Slower: Correlated subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Faster: JOIN with DISTINCT
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON o.user_id = u.id WHERE o.total > 100;

-- Or EXISTS (often better for large datasets)
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100);

Pattern 2: JOIN → Semi-Join (EXISTS)

-- When you only need to check existence:
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT:
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON o.user_id = u.id;

EXISTS is faster when you don't need order data, just existence check.

Pattern 3: Partial Index for Filtered Queries

-- Common query:
SELECT * FROM orders WHERE status = 'pending';

-- Create partial index
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

Benefits:

Smaller index (only pending orders)
Faster writes (only updated when status = pending)
Perfect for skewed data (e.g., 1% pending, 99% completed)

Pattern 4: Materialized View for Complex Aggregations

-- Slow query run frequently:
SELECT user_id, COUNT(*), SUM(total) FROM orders GROUP BY user_id;

-- Create materialized view
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
FROM orders
GROUP BY user_id;

CREATE UNIQUE INDEX idx_user_order_stats_user ON user_order_stats(user_id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_stats;

Index Maintenance

Statistics

PostgreSQL uses table statistics to estimate row counts and plan queries.

-- Outdated statistics cause bad plans
-- Fix: Analyze the table
ANALYZE users;
ANALYZE orders;

-- Auto-vacuum should handle this, but manual ANALYZE helps after bulk changes

Reindexing

Indexes can become bloated over time.

-- Check index bloat
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild index
REINDEX INDEX idx_users_email;
REINDEX TABLE users; -- All indexes on table

-- Or recreate index (allows CONCURRENTLY, less locking)
CREATE INDEX CONCURRENTLY idx_users_email_new ON users(email);
DROP INDEX CONCURRENTLY idx_users_email;
ALTER INDEX idx_users_email_new RENAME TO idx_users_email;

Monitoring Index Usage

-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop unused indexes to improve write performance
DROP INDEX idx_never_used;

Optimization Workflow

Step 1: Identify Slow Query

-- Enable slow query logging in postgresql.conf
log_min_duration_statement = 1000  -- Log queries > 1 second

-- Or use pg_stat_statements extension
CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 2: Analyze with EXPLAIN ANALYZE

EXPLAIN ANALYZE <your slow query>;

Look for:

Seq Scan on large tables
Nested Loop with high actual rows
Hash Join with large temp spills
Sort operations with large work_mem usage

Step 3: Check Statistics

-- When was table last analyzed?
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'orders';

-- If stale, analyze
ANALYZE orders;

Step 4: Add/Modify Index

-- Based on WHERE, JOIN, ORDER BY clauses
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

Step 5: Re-run EXPLAIN ANALYZE

EXPLAIN ANALYZE <your slow query>;

Compare before/after:

Total cost reduced?
Seq Scan → Index Scan?
Actual time improved?

Step 6: Test in Production-like Data Volume

CRITICAL: Optimizer chooses plans based on table size.

1K rows → Seq Scan might be optimal
1M rows → Index Scan needed

Test with realistic data volume.

Quick Reference

EXPLAIN Options

EXPLAIN SELECT ...;                    -- Plan only, no execution
EXPLAIN ANALYZE SELECT ...;            -- Plan + actual execution
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- Include I/O stats
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...; -- Full details

Index Syntax

-- B-tree (default)
CREATE INDEX idx_name ON table(column);

-- Composite
CREATE INDEX idx_name ON table(col1, col2);

-- Covering (Index Only Scan)
CREATE INDEX idx_name ON table(col1) INCLUDE (col2, col3);

-- Partial
CREATE INDEX idx_name ON table(col) WHERE condition;

-- Functional
CREATE INDEX idx_name ON table(LOWER(email));

-- Concurrent (no table lock)
CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- Other types
CREATE INDEX idx_name ON table USING HASH (column);
CREATE INDEX idx_name ON table USING GIN (jsonb_column);
CREATE INDEX idx_name ON table USING GIST (geometry_column);
CREATE INDEX idx_name ON table USING BRIN (timestamp_column);

Optimization Checklist

Query Performance Issues:
[ ] Run EXPLAIN ANALYZE to see actual execution
[ ] Check for Seq Scan on large tables
[ ] Verify statistics are current (ANALYZE table)
[ ] Look for missing indexes on WHERE/JOIN columns
[ ] Check if index is being used (Index Cond vs Filter)
[ ] Consider composite index for multi-column queries
[ ] Use covering index (INCLUDE) for Index Only Scan
[ ] Check for N+1 queries (use JOIN or batch)
[ ] Verify query is sargable (no functions on indexed columns)
[ ] Consider partial index for filtered queries
[ ] Test with production-like data volume
[ ] Monitor index usage (drop unused indexes)

Level 3 Resources

This skill includes Level 3 Resources (executable tools, reference materials, examples):

Reference Materials

REFERENCE.md - Deep dive into EXPLAIN output, query planner internals, all index types, and optimization patterns

Executable Scripts

Located in ./postgres-query-optimization/resources/scripts/:

analyze_query.py - Parses EXPLAIN ANALYZE output, detects issues (seq scans, stale statistics, inefficient filters), suggests optimizations
suggest_indexes.py - Recommends indexes based on query patterns (WHERE, JOIN, ORDER BY), supports covering indexes and workload analysis
benchmark_queries.sh - Benchmarks query performance with statistical analysis, compares before/after optimization

See scripts/README.md for usage examples.

Examples

slow-queries/ - Real-world slow query examples with fixes (N+1 problem, missing indexes, non-sargable queries)
docker/ - Pre-configured PostgreSQL test environment with sample data (10K users, 100K orders, 500K events)

Quick Start:

# Analyze EXPLAIN output
python postgres-query-optimization/resources/scripts/analyze_query.py --explain-file explain.txt

# Get index recommendations
python postgres-query-optimization/resources/scripts/suggest_indexes.py --query "SELECT * FROM orders WHERE user_id = 123"

# Benchmark query
./postgres-query-optimization/resources/scripts/benchmark_queries.sh --query "SELECT ..." --iterations 20

# Start test environment
cd postgres-query-optimization/resources/examples/docker && docker-compose up -d

Related Skills

postgres-migrations.md - Safe schema changes, adding indexes without downtime
postgres-schema-design.md - Table design affects query performance
orm-patterns.md - ORM-specific N+1 prevention, eager loading
database-connection-pooling.md - Connection limits affect query concurrency
database-selection.md - When to use Postgres vs other databases

Common Pitfalls

❌ Running EXPLAIN ANALYZE on writes in production - It executes the query (including DELETE!) ✅ Use EXPLAIN (no ANALYZE) for writes, or test on staging

❌ Creating too many indexes - Slows down writes, wastes space ✅ Monitor index usage, drop unused indexes

❌ Ignoring statistics - Planner makes bad decisions with stale stats ✅ Run ANALYZE after bulk changes, ensure auto-vacuum is working

❌ Not testing with realistic data volume - Plans change with table size ✅ Test on production-like dataset

❌ Using DISTINCT when not needed - Adds expensive sort/dedup ✅ Only use when actually needed

❌ Assuming index always helps - Small tables are faster with Seq Scan ✅ Test before/after, trust the planner for small tables

Last Updated: 2025-10-27 Format Version: 1.0 (Atomic)