Database Postgres Query Optimization

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

1. Parsing - SQL syntax validation
2. Planning - Query optimizer creates execution plan
3. Execution - Plan executed, data retrieved
4. 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:

1. Traverse B-tree index to find entries
2. Fetch heap tuples (actual table rows) via pointers
3. 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:

1. Traverse index
2. Return data DIRECTLY from index (no heap lookup)
3. 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:

1. Build in-memory bitmap of matching rows
2. Combine bitmaps (OR, AND operations)
3. Sort row locations
4. 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)