Creating Indexes
IntermediateIndexes speed up read queries by creating an ordered data structure; CREATE INDEX, UNIQUE INDEX, and composite indexes must be chosen deliberately to avoid write overhead.
Overview
An index is a separate on-disk structure (typically a B-Tree) that maps column values to the physical row location (ctid in PostgreSQL, row pointer in MySQL InnoDB). The query planner checks available indexes and decides whether using one is cheaper than a sequential scan. In PostgreSQL, CREATE INDEX CONCURRENTLY builds the index without locking writes. Composite indexes use the leftmost-prefix rule: an index on (a, b, c) can serve queries filtering on a, (a,b), or (a,b,c) but not on b alone. Over-indexing hurts INSERT/UPDATE/DELETE performance because every write must update all affected indexes.
Creating Single and Composite Indexes
Name indexes explicitly for easy management. Single-column indexes suit high-cardinality columns used in WHERE and JOIN. Composite indexes should list the most selective column (or the equality column) first.
-- Single-column index on a foreign key (always index FK columns)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Unique index (also enforces uniqueness — same as UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Composite index: equality first, then range
-- Serves: WHERE status = 'pending' AND created_at > '2024-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Partial index: only index rows matching a condition (smaller, faster)
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';
-- Expression index: index the result of a function
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Enables: WHERE LOWER(email) = 'alice@example.com' to use the index
-- Concurrent index build (PostgreSQL) — no write lock on the table
CREATE INDEX CONCURRENTLY idx_orders_product_id ON orders(product_id);When NOT to Add an Index
Indexes have write amplification costs. Low-cardinality columns (boolean, status with 3 values), very small tables, and columns rarely used in WHERE/JOIN predicates are poor index candidates.
-- Anti-pattern: indexing a boolean column (only 2 distinct values)
-- The planner will choose a seq scan for > ~5% of rows anyway
CREATE INDEX idx_users_is_active ON users(is_active); -- rarely useful
-- Anti-pattern: redundant index (PK index already covers id)
CREATE INDEX idx_users_id ON users(id); -- waste
-- Anti-pattern: index on a column only used with a function (index not used!)
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Fix: create an expression index: CREATE INDEX ON users(UPPER(email));
-- Check existing indexes in PostgreSQL
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- Find unused indexes (PostgreSQL)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY relname;Key Points to Remember
- 1B-Tree indexes support =, <, >, BETWEEN, and LIKE 'prefix%' predicates.
- 2Composite index column order matters: equality columns first, range column last.
- 3CREATE INDEX CONCURRENTLY avoids write-blocking in PostgreSQL.
- 4Partial indexes are smaller and faster for queries with a fixed WHERE condition.
- 5Expression indexes (on LOWER(col)) are required for case-insensitive lookups to use an index.
- 6Every index adds overhead to INSERT/UPDATE/DELETE — audit and drop unused indexes regularly.
Interview Questions
Sign in to ask AriaWhat is the leftmost-prefix rule for composite indexes?
How does CREATE INDEX CONCURRENTLY differ from CREATE INDEX?
When would a query planner ignore an available index and do a sequential scan?
What is a partial index and when would you use one?
How do you find and remove unused indexes in PostgreSQL?
Ask Aria about Creating Indexes
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.