Database Indexing

◆Databases◆Intermediate to Advanced🎯Free Tools

Introduction

Database indexes are data structures that speed up data retrieval at the cost of additional storage and write overhead. Without proper indexes, every query scans the entire table (full table scan) — catastrophic at scale.

Understanding index types, when to use them, and how to verify they're working is essential for any developer working with databases beyond toy projects.

B-Tree Indexes

B-tree is the default index type in PostgreSQL and MySQL. It maintains a sorted tree structure, enabling efficient equality and range queries with O(log n) lookup time.

btree.sql

SQL

1	-- Basic B-tree index
2	CREATE INDEX idx_users_email ON users (email);
3
4	-- Unique index (enforces uniqueness)
5	CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
6
7	-- Multi-column index (order matters!)
8	CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
9
10	-- The above index efficiently serves:
11	SELECT * FROM orders WHERE user_id = 42;
12	SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';
13	-- But NOT: SELECT * FROM orders WHERE created_at > '2026-01-01';
14	-- (leftmost prefix rule)
15
16	-- Partial index (only index rows matching a condition)
17	CREATE INDEX idx_orders_pending ON orders (created_at)
18	WHERE status = 'pending';
19
20	-- This index is tiny compared to a full index on created_at
21	-- and perfect for queries that filter by pending status

✓

best practice

Place the most selective (high-cardinality) column first in composite indexes. The leftmost prefix rule means (user_id, created_at) serves queries on user_id alone, but not created_at alone.

Hash & Covering Indexes

hash-covering.sql

SQL

1	-- Hash index: O(1) equality lookups, no range support
2	CREATE INDEX idx_sessions_token ON sessions USING hash (token);
3
4	-- Covering index: includes all columns needed by a query
5	-- Avoids going back to the table (index-only scan)
6	CREATE INDEX idx_orders_covering ON orders (user_id, created_at)
7	INCLUDE (total, status);
8
9	-- This query is served entirely from the index:
10	SELECT created_at, total, status
11	FROM orders
12	WHERE user_id = 42;
13
14	-- PostgreSQL EXPLAIN shows:
15	-- Index Only Scan using idx_orders_covering
16	-- (cost=0.29..8.31 rows=1 width=20)
17
18	-- Expression index (index on a computed value)
19	CREATE INDEX idx_users_lower_email ON users (LOWER(email));
20	-- Serves: SELECT * FROM users WHERE LOWER(email) = 'test@example.com'
21
22	-- GIN index for full-text search
23	CREATE INDEX idx_articles_search ON articles USING gin(
24	to_tsvector('english', title \|\| ' ' \|\| content)
25	);

EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and shows the actual execution plan with timing and row counts. This is how you verify indexes are being used.

explain.sql

SQL

1	-- Basic EXPLAIN
2	EXPLAIN SELECT * FROM orders WHERE user_id = 42;
3	-- Seq Scan on orders (cost=0.00..1234.00 rows=500 width=40)
4	-- Filter: (user_id = 42)
5	-- Rows Removed by Filter: 99500
6	-- ^^^ BAD: full table scan, checking 100K rows
7
8	-- EXPLAIN ANALYZE (runs the query for real timing)
9	EXPLAIN ANALYZE
10	SELECT * FROM orders WHERE user_id = 42;
11	-- Index Scan using idx_orders_user_id on orders (cost=0.29..8.31 rows=500 width=40)
12	-- Index Cond: (user_id = 42)
13	-- Planning Time: 0.1 ms
14	-- Execution Time: 0.5 ms
15	-- ^^^ GOOD: uses index, sub-millisecond
16
17	-- EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
18	EXPLAIN (ANALYZE, BUFFERS)
19	SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';
20	-- Shows buffer hits vs reads — indicates cache effectiveness
21
22	-- Check if index is actually used
23	EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
24	-- If it shows Seq Scan, you need an expression index

⚠

warning

Always use EXPLAIN ANALYZE, not just EXPLAIN. EXPLAIN shows the estimated plan; ANALYZE shows the actual plan with real timing. Stale statistics can make EXPLAIN misleading.

Index Optimization

optimization.sql

SQL

1	-- Find unused indexes (PostgreSQL)
2	SELECT schemaname, tablename, indexname, idx_scan
3	FROM pg_stat_user_indexes
4	WHERE idx_scan = 0
5	ORDER BY pg_relation_size(indexrelid) DESC;
6	-- Drop unused indexes to save storage and write overhead
7
8	-- Find missing indexes (queries doing sequential scans)
9	SELECT relname, seq_scan, seq_tup_read
10	FROM pg_stat_user_tables
11	WHERE seq_scan > 100
12	ORDER BY seq_tup_read DESC;
13
14	-- Rebuild bloated indexes
15	REINDEX INDEX idx_orders_user_date;
16
17	-- Analyze table statistics (after bulk inserts)
18	ANALYZE orders;
19
20	-- Index maintenance: check bloat
21	SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid))
22	FROM pg_stat_user_indexes
23	ORDER BY pg_relation_size(indexrelid) DESC;

ℹ

info

Run ANALYZE after bulk data changes. PostgreSQL uses table statistics to choose between index scan and sequential scan. Stale statistics cause bad query plans.

$Blueprint — Engineering Documentation·Section ID: DB-IDX-01·Revision: 1.0

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor