How does a composite index work and what is the left-prefix rule: why put equality columns before range columns

-- National Bank of India: composite index on (account_id, created_at)
CREATE INDEX ix_txn_acct_date
    ON transactions(account_id, created_at);

-- The B-tree sorts entries like this:
-- SAV1001 | 2026-01-15
-- SAV1001 | 2026-02-20
-- SAV1001 | 2026-03-10
-- SAV2001 | 2026-01-05
-- SAV2001 | 2026-01-18
-- SAV2001 | 2026-02-25
-- SAV3001 | 2026-01-02

-- Index on (account_id, created_at)

-- Uses the index: filters on account_id (leftmost column)
SELECT * FROM transactions WHERE account_id = 'SAV2001';
-- Plan: Index Scan on ix_txn_acct_date

-- Uses the index: filters on both columns (full prefix)
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
  AND created_at >= '2026-01-01';
-- Plan: Index Scan on ix_txn_acct_date

-- CANNOT use the index: skips the leftmost column
SELECT * FROM transactions
WHERE created_at >= '2026-01-01';
-- Plan: Seq Scan (created_at values scattered across all accounts)

Index entries (sorted by account_id, then created_at):
  SAV1001 | 2026-01-15  <-- date range scattered
  SAV1001 | 2026-02-20
  SAV2001 | 2026-01-05  <-- same date range, different account
  SAV2001 | 2026-01-18
  SAV3001 | 2026-01-02  <-- same date range, yet another account

A query for created_at >= '2026-01-01' must visit every account.
There is no contiguous block to seek to.

-- Good: equality first, range last
-- Index on (account_id, created_at)
SELECT * FROM transactions
WHERE account_id = 'SAV2001'            -- equality: pins one value
  AND created_at BETWEEN '2026-01-01'   -- range: walks dates within SAV2001
                   AND '2026-03-31';
-- Plan: Index Scan (seek to SAV2001, walk date range)

-- Bad: range first, equality last
-- Index on (created_at, account_id) -- wrong order!
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
  AND created_at BETWEEN '2026-01-01' AND '2026-03-31';
-- Plan: scans the entire date range, then filters by account
-- Much slower: reads all accounts' transactions in that date range

// com.bank.service.StatementService
package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class StatementService {

    /**
     * Uses the composite index (account_id, created_at).
     * Equality on account_id, range on created_at.
     * The index serves this perfectly.
     */
    public List<String> getStatement(
            Connection conn, String accountId,
            LocalDate from, LocalDate to)
            throws SQLException {

        String sql = """
            SELECT txn_id, amount, created_at
            FROM   transactions
            WHERE  account_id = ?
              AND  created_at >= ?
              AND  created_at <  ?
            ORDER BY created_at DESC
            """;

        List<String> rows = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            ps.setDate(2, Date.valueOf(from));
            ps.setDate(3, Date.valueOf(to));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    BigDecimal amt = rs.getBigDecimal("amount");
                    rows.add(rs.getLong("txn_id")
                        + " | " + amt.toPlainString()
                        + " | " + rs.getTimestamp("created_at"));
                }
            }
        }
        return rows;
    }
}

-- Index on (account_id, created_at)
-- ORDER BY created_at is already sorted within each account
EXPLAIN ANALYZE
SELECT txn_id, amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
ORDER BY created_at DESC
LIMIT 10;

Limit
  -> Index Scan Backward using ix_txn_acct_date on transactions
       Index Cond: (account_id = 'SAV2001')
  -- No Sort node: the index provides the order

-- Index on (account_id, created_at) already serves:
-- 1. WHERE account_id = ?                    (left prefix)
-- 2. WHERE account_id = ? AND created_at > ? (full prefix)
-- 3. WHERE account_id = ? ORDER BY created_at (prefix + sort)

-- A separate index on just account_id is redundant
-- DROP INDEX ix_accounts_only_acctid;  -- not needed

-- Index on (branch_code, status, created_at)
CREATE INDEX ix_accounts_branch_status_date
    ON accounts(branch_code, status, created_at);

-- Valid prefixes:
WHERE branch_code = 'MUM001'                           -- a
WHERE branch_code = 'MUM001' AND status = 'ACTIVE'     -- a, b
WHERE branch_code = 'MUM001' AND status = 'ACTIVE'
  AND created_at > '2026-01-01'                         -- a, b, c

-- NOT valid (skips the leftmost):
WHERE status = 'ACTIVE'                                 -- b alone
WHERE created_at > '2026-01-01'                         -- c alone
WHERE status = 'ACTIVE' AND created_at > '2026-01-01'  -- b, c

-- Skip scan: only works when leading column has few distinct values
-- PostgreSQL does not support skip scan (as of v16)
-- Oracle and MySQL 8.0.13+ support it for very low cardinality

-- Example: status has only 3 values (ACTIVE, FROZEN, CLOSED)
-- Index on (status, created_at)
-- A skip scan on WHERE created_at > ? would scan 3 ranges
-- But this is unreliable – design the index order properly