Stage 3 · Build
Relational Databases (PostgreSQL)
Indexes & Query Plans
B-tree, hash, GiST indexes — EXPLAIN ANALYZE and eliminating sequential scans.
Why Indexes Matter
Without an index, PostgreSQL performs a sequential scan — reading every row in the table to find matches. For a table with 10 million rows, that means reading all 10 million rows for every query. Indexes create a sorted data structure that lets the database jump directly to matching rows.
-- Without index: sequential scan (~2.5s on 10M rows)
SELECT * FROM orders WHERE user_id = 42;
-- Create index
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- With index: index scan (~0.1ms)
SELECT * FROM orders WHERE user_id = 42;The index creates a B-tree structure that allows logarithmic lookups. Finding a row in 10 million rows requires roughly 23 comparisons instead of 10 million.
Every index must be updated on INSERT, UPDATE, and DELETE. A table with 5 indexes writes 5 index entries per row modification. Too many indexes slow down writes. Choose indexes based on your actual query patterns.
B-tree Indexes
B-tree is the default and most versatile index type. It supports equality checks, range queries, pattern matching with prefixes, and sorting. Most queries benefit from B-tree indexes.
-- Single column
CREATE INDEX idx_users_email ON users(email);
-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Composite index (column order matters)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- The composite index supports these queries:
SELECT * FROM orders WHERE user_id = 42; -- uses index
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped'; -- uses index
SELECT * FROM orders WHERE status = 'shipped'; -- does NOT use index
-- The last query fails because status is the second columnComposite indexes follow the leftmost prefix rule. The index on (user_id, status) can serve queries filtering on user_id alone, or user_id + status, but not status alone.
Other Index Types
| Type | Best For | Example |
|---|---|---|
| Hash | Equality checks only | email = 'x' |
| GiST | Geometric, full-text, ranges | ST_Within(geom, boundary) |
| GIN | Arrays, JSONB, full-text | tags @> ARRAY['python'] |
| BRIN | Large tables with natural ordering | created_at with bulk inserts |
-- GIN index for JSONB columns
CREATE INDEX idx_events_payload ON events USING GIN(payload);
SELECT * FROM events WHERE payload @> '{"type": "click"}';
-- GiST for full-text search
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', body));
SELECT * FROM posts WHERE to_tsvector('english', body) @@ plainto_tsquery('database reliability');
-- BRIN for time-series data with natural insert order
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
SELECT * FROM logs WHERE created_at > NOW() - INTERVAL '1 hour';BRIN indexes are tiny compared to B-tree. On a billion-row table, a B-tree on created_at might be 20GB while a BRIN index is 2MB. The tradeoff is that BRIN only works well when data is physically ordered by the indexed column.
Reading EXPLAIN ANALYZE
EXPLAIN ANALYZE runs the query and shows the actual execution plan with timing and row counts. This is the primary tool for understanding query performance.
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.is_active = true
GROUP BY u.id, u.name;
-- Output (simplified):
-- HashAggregate (cost=1234.56..1234.78 rows=100 width=48) (actual time=12.345..12.400 rows=100 loops=1)
-- Group Key: u.id, u.name
-- -> Hash Join (cost=1.00..1200.00 rows=5000 width=44) (actual time=0.050..10.200 rows=5000 loops=1)
-- Hash Cond: (o.user_id = u.id)
-- -> Seq Scan on orders o (cost=0.00..900.00 rows=50000 width=8) (actual time=0.010..5.100 rows=50000 loops=1)
-- -> Hash (cost=1.00..1.00 rows=100 width=40) (actual time=0.030..0.030 rows=100 loops=1)
-- -> Seq Scan on users u (cost=0.00..1.00 rows=100 width=40) (actual time=0.010..0.020 rows=100 loops=1)
-- Filter: is_active
-- Planning Time: 0.150 ms
-- Execution Time: 12.500 msKey metrics: cost (estimated), actual time (real ms), rows (actual count vs estimated). If actual rows differ greatly from estimated, statistics may be stale — run ANALYZE on the table.
Adding BUFFERS shows shared buffer hits vs disk reads. High disk reads indicate the index or working data does not fit in memory. This is critical for diagnosing memory-related performance issues.
Index Maintenance
Indexes degrade over time due to page splits, dead tuples, and bloated B-tree pages. Regular maintenance keeps them efficient.
-- Check index size and usage
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
idx_scan AS times_used
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexname::regclass) DESC;
-- Rebuild a bloated index
REINDEX INDEX idx_orders_user_id;
-- Rebuild all indexes on a table
REINDEX TABLE orders;
-- Check for unused indexes (candidate for removal)
SELECT indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname LIKE 'idx_%';Unused indexes consume memory and slow writes without providing benefit. Review pg_stat_user_indexes periodically to identify indexes that have never been used since the last statistics reset.
Partial and Expressional Indexes
Partial indexes index only a subset of rows, reducing index size and maintenance cost. Expressional indexes index the result of a function or expression.
-- Partial index: only active users
CREATE INDEX idx_active_users_email ON users(email)
WHERE is_active = true;
-- Expressional index: case-insensitive email lookup
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Partial + expression: recent active orders
CREATE INDEX idx_recent_orders ON orders(created_at, total)
WHERE status = 'shipped' AND created_at > '2024-01-01';
-- Usage: the query planner matches the WHERE clause
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-06-01';A partial index on is_active = true is tiny compared to a full index. If only 5% of users are active, the index is 20x smaller and faster to maintain.
1) Run the slow query with EXPLAIN ANALYZE. 2) Identify sequential scans on large tables. 3) Check if existing indexes match the WHERE clause. 4) Create the missing index. 5) Re-run EXPLAIN ANALYZE to verify. 6) Monitor pg_stat_user_indexes to confirm the index is used.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.