Back to Indexing
Q3Question 3 of 19

How do indexes and composite indexes improve database query perform

Answer: An index is a separate data structure maintained by the database that maps column values to row locations, allowing the database to find matching rows in O(log n) time instead of scanning every row in the table.

Key Concepts
  • An index is a data structure (typically a B-tree) that the database uses to find rows without scanning the entire table.
  • A primary key and a unique constraint automatically create indexes, but foreign key columns do not get automatic indexes in PostgreSQL.
  • A composite index covers multiple columns and is effective only when queries filter on the leftmost prefix of the indexed columns.
  • EXPLAIN ANALYZE shows the actual query plan and execution time so you can verify whether the database uses an index or falls back to a sequential scan.
  • Too many indexes slow down INSERT, UPDATE, and DELETE operations because the database must update every index on each write.
  • A covering index includes all columns needed by a query, allowing an index-only scan that skips the table entirely.
  • Partial indexes cover only a subset of rows and are smaller and faster for targeted queries.

Definition

An index is a separate data structure maintained by the database that maps column values to row locations, allowing the database to find matching rows in O(log n) time instead of scanning every row in the table.
  • 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.

SQL
-- 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';
TEXT
-- 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.

SQL
-- 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';
TEXT
-- 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.

SQL
-- 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';
TEXT
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 FilterUses Index (A, B, C)?Reason
WHERE A = 1YesLeftmost column
WHERE A = 1 AND B = 2YesLeftmost prefix
WHERE A = 1 AND B = 2 AND C = 3YesFull index
WHERE B = 2NoSkips A
WHERE C = 3NoSkips A and B
WHERE A = 1 AND C = 3PartialUses A, cannot skip B for C
SQL
-- 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';
TEXT
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.

SQL
-- 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';
TEXT
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.

SQL
-- 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';
TEXT
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.

SQL
-- 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.

SQL
-- 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.

SQL
-- 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.

SQL
-- 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.

OperationWithout IndexWith 1 IndexWith 5 Indexes
INSERT 1 rowFastSlightly slowerNoticeably slower
INSERT 100K rows200 ms350 ms1200 ms
UPDATE indexed columnFastSlower (rebalance tree)Much slower
DELETEFastSlightly slowerNoticeably slower
SELECT with filterSlow (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.

SQL
-- 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;
TEXT
-- 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.

SQL
-- 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;
TEXT
 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

TEXT
  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.
TEXT
  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.

---

Share
Use ← → arrow keys to navigate