When does a database actually use an index: WHERE, JOIN, ORDER BY, GROUP BY

-- National Bank of India: accounts table with 10 million rows
-- Index on branch_code exists

-- High selectivity: only 50 accounts in branch 'AND001'
-- Optimizer chooses Index Scan (cheaper than reading 10M rows)
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE branch_code = 'AND001';

Index Scan using ix_accounts_branch on accounts
  Index Cond: (branch_code = 'AND001')
  Rows: 50  Cost: 12.5

-- Low selectivity: 9.5 million accounts are 'ACTIVE'
-- Optimizer chooses Seq Scan (cheaper than 9.5M random lookups)
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE status = 'ACTIVE';

Seq Scan on accounts
  Filter: (status = 'ACTIVE')
  Rows: 9,500,000  Cost: 180000

-- Sargable: direct comparison on indexed column
SELECT txn_id, amount
FROM   transactions
WHERE  account_id = 'SAV2001';  -- Index Scan

-- Sargable: range on indexed column
SELECT txn_id, amount
FROM   transactions
WHERE  created_at >= '2026-01-01'
  AND  created_at <  '2026-04-01';  -- Index Range Scan

-- NOT sargable: function on indexed column
SELECT txn_id, amount
FROM   transactions
WHERE  YEAR(created_at) = 2026;  -- Seq Scan (index defeated)

-- National Bank of India: join accounts to transactions
-- Index on transactions(account_id) enables index nested-loop join
CREATE INDEX ix_txn_account ON transactions(account_id);

EXPLAIN ANALYZE
SELECT a.customer_name, t.amount, t.created_at
FROM   accounts a
JOIN   transactions t ON t.account_id = a.account_id
WHERE  a.branch_code = 'MUM001';

Nested Loop
  -> Index Scan using ix_accounts_branch on accounts a
       Index Cond: (branch_code = 'MUM001')
  -> Index Scan using ix_txn_account on transactions t
       Index Cond: (account_id = a.account_id)

-- Composite index on (account_id, created_at)
CREATE INDEX ix_txn_acct_date
    ON transactions(account_id, created_at);

-- The index is already sorted by created_at within each account
-- No sort step needed
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 in the plan

-- Index is on (account_id, created_at)
-- But query orders by amount – no match
SELECT txn_id, amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
ORDER BY amount DESC;
-- Plan includes: Sort (cost = extra)

-- Index on branch_code
CREATE INDEX ix_accounts_branch ON accounts(branch_code);

-- GROUP BY uses the sorted index
EXPLAIN ANALYZE
SELECT branch_code, COUNT(*), SUM(balance)
FROM   accounts
GROUP BY branch_code;

GroupAggregate
  -> Index Scan using ix_accounts_branch on accounts
  -- All rows for each branch are contiguous in the index

-- MIN reads the first leaf entry
SELECT MIN(created_at) FROM transactions;
-- Plan: Index Scan (reads 1 entry, not 200M rows)

-- MAX reads the last leaf entry
SELECT MAX(balance) FROM accounts;
-- Plan: Index Scan Backward (reads 1 entry)

-- Covering index: includes amount in INCLUDE clause
CREATE INDEX ix_txn_cover ON transactions(account_id, created_at)
    INCLUDE (amount);

-- All needed columns are in the index
EXPLAIN ANALYZE
SELECT amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
  AND  created_at >= '2026-01-01';

Index Only Scan using ix_txn_cover on transactions
  Index Cond: (account_id = 'SAV2001'
    AND created_at >= '2026-01-01')
  Heap Fetches: 0

-- After loading 5 million new transactions
-- Statistics still say the table has 1 million rows
-- Optimizer underestimates cost and may choose wrong plan

-- Fix: update statistics
ANALYZE transactions;

-- PostgreSQL auto-analyzes, but a manual ANALYZE after bulk loads
-- ensures the optimizer has fresh data immediately

// com.bank.service.DataLoadService
package com.bank.service;

import java.sql.*;

public class DataLoadService {

    public void refreshStatisticsAfterBulkLoad(Connection conn)
            throws SQLException {
        // Always ANALYZE after a large data load
        // so the optimizer has fresh statistics
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("ANALYZE transactions");
            stmt.execute("ANALYZE accounts");
        }
    }
}

-- Debugging: force index usage to compare costs
SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE status = 'ACTIVE';
-- Shows the index scan cost (likely higher than seq scan)
RESET enable_seqscan;