PostgreSQL Performance & Indexing Cheat Sheet

Query optimization recipes, EXPLAIN ANALYZE interpretation, B-Tree vs GIN vs BRIN indexes, and VACUUM tuning

EXPLAIN ANALYZE Interpretation

Diagnose slow queries by reading execution plans

Run detailed query analysis

EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT * FROM orders WHERE user_id = 42;

Sequential Scan (Seq Scan)⚠️ Scans every table block. Indicates missing index on filtered column.

Filter: (user_id = 42)
Rows Removed by Filter: 1250000

Index Scan vs Index Only Scan✅ Best performance. Data satisfied directly from index memory without table lookup.

Index Only Scan using idx_orders_user_created

Bitmap Heap ScanGathers matching row pointers before fetching table pages.

Bitmap Index Scan on idx_status
Bitmap Heap Scan on orders

Select the optimal index structure for query patterns

B-Tree Index (Default)Best for equality (=) and range (<, >, BETWEEN, ORDER BY)

CREATE INDEX idx_users_email ON users(email);

Composite Index (Leftmost prefix rule)Optimizes queries filtering by (user_id) or (user_id, status)

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

GIN Index (JSONB & Full-Text Search)Fast search on JSONB @> containment operators and tsvector text

CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

BRIN Index (Append-only / Time-series)Tiny index footprint (99% smaller) for naturally ordered timestamp tables

CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

Partial IndexIndexes only relevant subsets, saving memory and write overhead

CREATE INDEX idx_active_users ON users(id) WHERE is_active = true;

Rebuild bloated index without locking table

REINDEX TABLE CONCURRENTLY orders;

Update query planner statistics

ANALYZE VERBOSE orders;

Find unused indexes wasting write I/O

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Key postgresql.conf Memory Settings

shared_buffers = '25% of RAM'
work_mem = '32MB'
maintenance_work_mem = '512MB'
effective_cache_size = '75% of RAM'