Definition
- Index is a data structure, usually a B-tree, that stores a sorted copy of selected column values along with pointers to the actual table rows.
- B-tree index is the default index type in PostgreSQL, MySQL, and most relational databases. It supports equality (=) and range (<, >, BETWEEN) lookups efficiently.
- Composite index covers multiple columns in a defined order. It is effective when queries filter or sort on the leftmost columns of the index.
- Covering index includes all columns a query needs, so the database can answer the query from the index alone without visiting the table (index-only scan).
- EXPLAIN ANALYZE is a PostgreSQL command that runs a query and shows the actual execution plan, including whether an index was used and how long each step took.
Think of the index at the back of a book at National Bank of India's training center. Without the index, you read every page to find the word "compound interest." With the index, you look up "compound interest" alphabetically, find page 47, and go directly there. A composite index is like an index organized first by chapter, then by keyword. If you know the chapter, you find the keyword faster. If you only know the keyword but not the chapter, the composite index is less helpful.
Understanding the Concepts
What is a database index?
An index is a separate data structure that the database maintains alongside the table. It maps column values to the physical location of rows. Without an index, the database must read every row in the table (sequential scan) to find matching rows. With an index, it navigates directly to the matching rows in O(log n) time.
The most common index type is a B-tree, which stores values in sorted order and supports equality, range, and prefix lookups.
-- Create the transactions table (millions of rows in production)
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL,
txn_type VARCHAR(10) NOT NULL,
amount NUMERIC(15, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Without an index on account_id, this query scans every row
SELECT * FROM transactions WHERE account_id = 'SAV2001';
-- EXPLAIN ANALYZE without index:
Seq Scan on transactions (cost=0.00..18334.00 rows=500 width=64)
(actual time=0.015..95.432 rows=500 loops=1)
Filter: (account_id = 'SAV2001')
Rows Removed by Filter: 999500
Planning Time: 0.08 ms
Execution Time: 95.58 ms
How do you create an index and measure the improvement?
Create a B-tree index on the column you filter by, then run EXPLAIN ANALYZE to compare.
-- Create an index on account_id
CREATE INDEX idx_txn_account_id ON transactions (account_id);
-- Same query, now with index
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE account_id = 'SAV2001';
-- EXPLAIN ANALYZE with index:
Index Scan using idx_txn_account_id on transactions
(cost=0.42..52.38 rows=500 width=64)
(actual time=0.025..0.312 rows=500 loops=1)
Index Cond: (account_id = 'SAV2001')
Planning Time: 0.12 ms
Execution Time: 0.45 ms
The query went from 95.58 ms (sequential scan of 1 million rows) to 0.45 ms (index scan of 500 rows). That is a 200x improvement.
What is a composite index?
A composite index covers multiple columns in a defined order. It is useful when queries filter on two or more columns together.
-- Composite index on (account_id, created_at)
CREATE INDEX idx_txn_account_date
ON transactions (account_id, created_at);
-- This query uses the full composite index
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
AND created_at >= '2025-01-01'
AND created_at < '2025-02-01';
Index Scan using idx_txn_account_date on transactions
(cost=0.42..8.55 rows=47 width=64)
(actual time=0.018..0.089 rows=47 loops=1)
Index Cond: ((account_id = 'SAV2001')
AND (created_at >= '2025-01-01')
AND (created_at < '2025-02-01'))
Planning Time: 0.15 ms
Execution Time: 0.12 ms
The composite index narrows the search first by account_id, then by date range within that account. This is much faster than a single-column index on account_id followed by a date filter on the remaining rows.
What is the leftmost prefix rule?
- A composite index on (A, B, C) is effective for queries that filter on:
- A alone
- A and B
- A, B, and C
It is NOT effective for queries that filter on B alone or C alone, because the index is sorted by A first.
| Query Filter | Uses Index (A, B, C)? | Reason |
|---|---|---|
| WHERE A = 1 | Yes | Leftmost column |
| WHERE A = 1 AND B = 2 | Yes | Leftmost prefix |
| WHERE A = 1 AND B = 2 AND C = 3 | Yes | Full index |
| WHERE B = 2 | No | Skips A |
| WHERE C = 3 | No | Skips A and B |
| WHERE A = 1 AND C = 3 | Partial | Uses A, cannot skip B for C |
-- This query does NOT use the composite index (account_id, created_at)
-- because it skips account_id.
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE created_at >= '2025-01-01';
Seq Scan on transactions (cost=0.00..18334.00 rows=250000 width=64)
(actual time=0.012..78.543 rows=250000 loops=1)
Filter: (created_at >= '2025-01-01')
The database fell back to a sequential scan because the query skipped the first column of the composite index.
What is a covering index?
A covering index includes all columns the query needs. The database can answer the query entirely from the index without visiting the table rows. This is called an index-only scan.
-- Covering index: includes account_id, created_at, amount, txn_type
CREATE INDEX idx_txn_covering
ON transactions (account_id, created_at)
INCLUDE (amount, txn_type);
-- Query that only needs columns in the index
EXPLAIN ANALYZE
SELECT txn_type, amount, created_at
FROM transactions
WHERE account_id = 'SAV2001'
AND created_at >= '2025-01-01'
AND created_at < '2025-02-01';
Index Only Scan using idx_txn_covering on transactions
(cost=0.42..8.10 rows=47 width=32)
(actual time=0.015..0.065 rows=47 loops=1)
Index Cond: ((account_id = 'SAV2001')
AND (created_at >= '2025-01-01')
AND (created_at < '2025-02-01'))
Heap Fetches: 0
Planning Time: 0.10 ms
Execution Time: 0.08 ms
"Heap Fetches: 0" confirms the database did not visit the table at all. The entire result came from the index.
What is a partial index?
A partial index covers only rows that match a condition. It is smaller than a full index and faster for targeted queries.
-- Partial index: only ACTIVE accounts
CREATE INDEX idx_active_accounts
ON accounts (customer_id)
WHERE status = 'ACTIVE';
-- This query uses the partial index
EXPLAIN ANALYZE
SELECT * FROM accounts
WHERE customer_id = 1 AND status = 'ACTIVE';
Index Scan using idx_active_accounts on accounts
(cost=0.14..8.16 rows=1 width=64)
(actual time=0.010..0.012 rows=2 loops=1)
Index Cond: (customer_id = 1)
Planning Time: 0.08 ms
Execution Time: 0.03 ms
The partial index is much smaller than a full index because it excludes CLOSED and FROZEN accounts. In a banking system with millions of accounts where 80% are active, this saves significant storage and speeds up queries for active accounts.
What are common mistakes with indexes?
Mistake 1: Indexing every column
WRONG: Creating an index on every column.
-- WRONG: Too many indexes. Every INSERT, UPDATE, DELETE must update
-- all 6 indexes. Write performance suffers.
CREATE INDEX idx_1 ON transactions (txn_id);
CREATE INDEX idx_2 ON transactions (account_id);
CREATE INDEX idx_3 ON transactions (txn_type);
CREATE INDEX idx_4 ON transactions (amount);
CREATE INDEX idx_5 ON transactions (description);
CREATE INDEX idx_6 ON transactions (created_at);
CORRECT: Index only the columns used in WHERE, JOIN, and ORDER BY clauses.
-- CORRECT: Index the columns that queries filter on.
-- txn_id already has a PK index.
-- account_id + created_at are the most common query pattern.
CREATE INDEX idx_txn_account_date
ON transactions (account_id, created_at);
Mistake 2: Wrong column order in composite index
WRONG: Putting the less selective column first.
-- WRONG: txn_type has only 2 values (CREDIT, DEBIT).
-- Putting it first gives poor selectivity.
CREATE INDEX idx_wrong_order
ON transactions (txn_type, account_id);
-- This query cannot use the index efficiently
-- for a specific account.
SELECT * FROM transactions
WHERE account_id = 'SAV2001' AND txn_type = 'CREDIT';
CORRECT: Put the most selective column first.
-- CORRECT: account_id is much more selective.
CREATE INDEX idx_right_order
ON transactions (account_id, txn_type);
How do indexes affect write performance?
Every index adds overhead to write operations because the database must update the index structure on each INSERT, UPDATE, or DELETE.
| Operation | Without Index | With 1 Index | With 5 Indexes |
|---|---|---|---|
| INSERT 1 row | Fast | Slightly slower | Noticeably slower |
| INSERT 100K rows | 200 ms | 350 ms | 1200 ms |
| UPDATE indexed column | Fast | Slower (rebalance tree) | Much slower |
| DELETE | Fast | Slightly slower | Noticeably slower |
| SELECT with filter | Slow (seq scan) | Fast (index scan) | Fast |
In a banking system with high transaction volume, keep indexes focused. Two well-chosen composite indexes are better than ten single-column indexes.
-- For high-volume inserts, consider temporarily dropping indexes
-- and recreating them after bulk loading.
-- Bulk load transactions
ALTER INDEX idx_txn_account_date SET (fastupdate = off);
COPY transactions FROM '/data/daily_transactions.csv' CSV;
ALTER INDEX idx_txn_account_date SET (fastupdate = on);
REINDEX INDEX idx_txn_account_date;
-- Bulk load with index: 45 seconds
-- Bulk load without index + rebuild: 12 seconds
What should a senior or principal engineer know?
- Index types beyond B-tree. PostgreSQL supports Hash (equality only), GiST (geometry, full-text), GIN (arrays, JSONB, full-text), and BRIN (range on physically sorted data). For a banking transactions table sorted by timestamp, a BRIN index on created_at is extremely small and efficient.
- BRIN for time-series data. Block Range Index (BRIN) stores min/max values per block of table pages. For a transactions table where rows are appended in chronological order, BRIN is 100x smaller than B-tree and nearly as fast for range queries.
- Index bloat. Heavy UPDATE and DELETE operations leave dead tuples in the index. Run REINDEX or use pg_repack to rebuild bloated indexes without locking the table.
- Statistics and autovacuum. The query planner uses table statistics (pg_stats) to decide whether to use an index or a sequential scan. Make sure autovacuum runs regularly to keep statistics fresh. Stale statistics lead to bad query plans.
- Monitoring unused indexes. Query pg_stat_user_indexes to find indexes that are never used. Unused indexes waste storage and slow down writes.
-- Find unused indexes
SELECT
indexrelname AS index_name,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
index_name | times_used | index_size
-------------------+------------+-----------
idx_txn_amount | 0 | 21 MB
idx_txn_desc | 0 | 45 MB
These indexes are never used. Drop them to save 66 MB and speed up writes.
What are common interview follow-up questions?
- What is the difference between a clustered and non-clustered index?
- When does the database choose a sequential scan over an index scan?
- What is a covering index and when do you use it?
- How does the leftmost prefix rule affect composite indexes?
- What is index bloat and how do you fix it?
- How do you find and remove unused indexes?
- What is the difference between B-tree and Hash indexes?
- When would you use a BRIN index?
- How does VACUUM affect index performance?
Visual Overview
B-tree Index Structure
======================
Query: WHERE account_id = 'SAV2001'
[CUR3001 | SAV2001 | SAV2002]
/ | \
[CUR3001] [SAV2001] [SAV2002]
-> row 3 -> row 1 -> row 2
-> row 4
-> row 7
Instead of scanning 1,000,000 rows,
the B-tree navigates in 3 steps.
Composite Index: (account_id, created_at)
==========================================
Sorted by account_id first, then by created_at:
CUR3001 | 2025-01-03 -> row 6
CUR3001 | 2025-01-08 -> row 7
SAV2001 | 2025-01-05 -> row 1
SAV2001 | 2025-01-10 -> row 2
SAV2001 | 2025-01-15 -> row 3
SAV2002 | 2025-01-05 -> row 4
SAV2002 | 2025-01-12 -> row 5
Query: WHERE account_id = 'SAV2001'
AND created_at >= '2025-01-10'
Result: Rows 2, 3 (found in 2 steps)
Key Takeaway
An index is the single most important tool for database query performance. Without an index, the database reads every row in the table to find matches. With a B-tree index, it navigates to the matching rows in O(log n) time. For a transactions table with a million rows, that is the difference between 95 ms and 0.45 ms.
Composite indexes cover multiple columns and are essential for queries that filter on two or more columns together. The leftmost prefix rule governs which queries can use the index: a composite index on (account_id, created_at) works for queries filtering on account_id alone or on both columns, but not for queries filtering only on created_at.
The trade-off is write performance. Every index adds overhead to INSERT, UPDATE, and DELETE operations because the database must update the index tree on each write. In a high-volume banking system, two well-chosen composite indexes are better than ten single-column indexes. Use EXPLAIN ANALYZE to verify that your indexes are used, and monitor pg_stat_user_indexes to find and drop unused ones.
For advanced use cases, covering indexes avoid table lookups entirely, partial indexes cover only relevant rows (active accounts), and BRIN indexes are extremely compact for time-series data. Keep statistics fresh with autovacuum, and reindex periodically to eliminate bloat.
Remember one sentence: an index trades write overhead for read speed, and a composite index on the right columns in the right order is the most impactful performance optimization you can make.
---