Indexes
B-tree, EXPLAIN ANALYZE, partial indexes, and when not to index
Skills taught: pg.btree-index, pg.explain-analyze, pg.partial-index, pg.composite-index
Creating indexes
-- Basic index on a single column
CREATE INDEX idx_users_email ON users(email);
-- Concurrent index creation (does not lock the table)
CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts(user_id);
-- Composite index (order matters)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Useful for: WHERE user_id = 1 AND status = 'pending'
-- Also useful for: WHERE user_id = 1 (uses the leftmost column)
-- NOT useful for: WHERE status = 'pending' only (skips leftmost column)
-- Partial index — index only a subset of rows
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Much smaller, much faster for: WHERE status = 'pending' ORDER BY created_at
-- Index on expression
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Supports: WHERE LOWER(email) = 'alice@example.com'
EXPLAIN ANALYZE — reading query plans
-- See the query plan without running
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Run the query and show actual timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Show buffer usage (cache hits vs disk reads)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'alice@example.com';
-- Reading the output:
-- Seq Scan → reading every row (bad for large tables + selective filter)
-- Index Scan → using an index to find rows (fast for selective queries)
-- Index Only Scan → all needed data is in the index itself (fastest)
-- Bitmap Heap Scan → fetches blocks of matching rows (batch of index lookups)
-- Cost format: (startup cost..total cost) (rows=estimate) (width=bytes per row)
-- Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=136)
-- (actual time=0.045..0.046 rows=1 loops=1)
Tip: The most impactful indexes: (1) Foreign key columns, (2) columns in WHERE clauses on large tables, (3) columns in ORDER BY when used with LIMIT. Adding all three will fix the majority of slow queries in a typical web application.
Frequently Asked Questions
How do I find which queries are slow?
Enable pg_stat_statements (CREATE EXTENSION pg_stat_statements) to track query performance statistics. Query: SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; — shows the slowest average queries. Then run EXPLAIN ANALYZE on those queries to see what plan they are using.