What is a covering index, and how does it enable an index-only scan

-- National Bank of India: mini-statement shows date and amount
-- Query needs: account_id (filter), created_at (sort), amount (return)

-- Non-covering index: only has account_id and created_at
CREATE INDEX ix_txn_acct_date
    ON transactions(account_id, created_at);

-- The query also needs 'amount', which is not in the index
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
ORDER BY created_at DESC
LIMIT 10;
-- Plan: Index Scan + Heap Fetches (must read the table for 'amount')

-- Without covering: each matching row needs a heap fetch
EXPLAIN ANALYZE
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
  AND  created_at >= '2026-01-01'
ORDER BY created_at DESC;

Index Scan using ix_txn_acct_date on transactions
  Index Cond: (account_id = 'SAV2001'
    AND created_at >= '2026-01-01')
  Heap Fetches: 3500  <-- 3500 random I/O operations
  Execution Time: 45 ms

-- Add amount as a third key column
CREATE INDEX ix_txn_cover_key
    ON transactions(account_id, created_at, amount);

-- Now all three columns are in the index
EXPLAIN ANALYZE
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
  AND  created_at >= '2026-01-01'
ORDER BY created_at DESC;

Index Only Scan using ix_txn_cover_key on transactions
  Index Cond: (account_id = 'SAV2001'
    AND created_at >= '2026-01-01')
  Heap Fetches: 0  <-- zero! No table access needed
  Execution Time: 8 ms  (5x faster)

-- INCLUDE attaches amount as a non-key payload column
CREATE INDEX ix_txn_cover_include
    ON transactions(account_id, created_at)
    INCLUDE (amount);

-- The key stays narrow (account_id, created_at)
-- but amount is available in the leaf for retrieval
EXPLAIN ANALYZE
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
  AND  created_at >= '2026-01-01'
ORDER BY created_at DESC;

Index Only Scan using ix_txn_cover_include on transactions
  Index Cond: (account_id = 'SAV2001'
    AND created_at >= '2026-01-01')
  Heap Fetches: 0
  Execution Time: 8 ms

-- INCLUDE columns do NOT help filtering or ordering
-- They ONLY help avoid the heap fetch

-- This CANNOT use the INCLUDE column for filtering:
SELECT * FROM transactions
WHERE amount > 50000.00;  -- amount is in INCLUDE, not the key
-- Plan: Seq Scan (amount is not in the B-tree key)

-- National Bank of India: top 3 queries by frequency
-- 1. Mini-statement (account + date + amount) - 5M queries/day
-- 2. Balance check (account + balance) - 3M queries/day
-- 3. Branch report (branch + status + count) - 10K queries/day

-- Cover #1: mini-statement
CREATE INDEX ix_txn_cover_statement
    ON transactions(account_id, created_at)
    INCLUDE (amount, txn_type);

-- Cover #2: balance check is already covered by the clustered PK
-- (all columns are in the clustered leaf)

-- Do NOT cover #3: too infrequent to justify the extra index width

// com.bank.service.MiniStatementService
package com.bank.service;

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

public class MiniStatementService {

    /**
     * Uses the covering index ix_txn_cover_statement.
     * All needed columns (account_id, created_at, amount, txn_type)
     * are in the index, so the query produces an index-only scan.
     */
    public List<String> getMiniStatement(
            Connection conn, String accountId) throws SQLException {

        String sql = """
            SELECT amount, txn_type, created_at
            FROM   transactions
            WHERE  account_id = ?
            ORDER BY created_at DESC
            LIMIT 10
            """;

        List<String> rows = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    BigDecimal amt = rs.getBigDecimal("amount");
                    String type = rs.getString("txn_type");
                    Timestamp ts = rs.getTimestamp("created_at");
                    rows.add(type + " | " + amt.toPlainString()
                        + " | " + ts);
                }
            }
        }
        return rows;
    }
}

-- Check heap fetches in the plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
ORDER BY created_at DESC
LIMIT 10;

-- If Heap Fetches > 0, the table needs vacuuming
VACUUM transactions;

-- After vacuum, Heap Fetches should drop to 0