When might a database ignore an index: functions on columns, leading wildcard, small tables, low selectivity, large result sets

-- Index on accounts(email)
CREATE INDEX ix_accounts_email ON accounts(email);

-- Non-sargable: UPPER() on the indexed column
-- The B-tree is sorted by raw email, not uppercased email
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE UPPER(email) = 'RAJESH@BANK.IN';

Seq Scan on accounts
  Filter: (upper(email) = 'RAJESH@BANK.IN')
  Rows Removed by Filter: 9,999,999
  -- Full table scan: 10 million rows examined

-- Expression index on LOWER(email)
CREATE INDEX ix_accounts_email_lower ON accounts(LOWER(email));

-- Query must use the identical function
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE LOWER(email) = 'rajesh@bank.in';

Index Scan using ix_accounts_email_lower on accounts
  Index Cond: (lower(email) = 'rajesh@bank.in')
  -- Fast: only 1 row examined

-- Trailing wildcard: WORKS with the index
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE customer_name LIKE 'Raj%';
-- Plan: Index Scan (seeks to 'Raj', stops at 'Rak')

-- Leading wildcard: CANNOT use the index
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE customer_name LIKE '%kumar';
-- Plan: Seq Scan (no prefix to seek to)

-- PostgreSQL: enable the pg_trgm extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a trigram GIN index
CREATE INDEX ix_accounts_name_trgm
    ON accounts USING gin (customer_name gin_trgm_ops);

-- Now leading wildcard works
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE customer_name LIKE '%kumar';
-- Plan: Bitmap Index Scan on ix_accounts_name_trgm

-- Small lookup table: 50 branches
CREATE TABLE branches (
    branch_code VARCHAR(10) PRIMARY KEY,
    branch_name VARCHAR(100),
    city        VARCHAR(50)
);

-- Index exists (primary key), but optimizer chooses Seq Scan
EXPLAIN ANALYZE
SELECT * FROM branches WHERE city = 'Mumbai';

Seq Scan on branches
  Filter: (city = 'Mumbai')
  Rows: 5  (total table: 50 rows, 1 page)
  -- Reading 1 page is cheaper than an index traversal

-- 95% of accounts are ACTIVE
-- Index on status exists but is nearly useless for 'ACTIVE'
CREATE INDEX ix_accounts_status ON accounts(status);

EXPLAIN ANALYZE
SELECT * FROM accounts WHERE status = 'ACTIVE';
-- Plan: Seq Scan (9.5M random lookups > 1 sequential scan)

-- But the same index IS used for the rare value
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE status = 'FROZEN';
-- Plan: Index Scan (only 500 rows match)

-- Partial index: only indexes the rare FROZEN accounts
CREATE INDEX ix_accounts_frozen
    ON accounts(account_id)
    WHERE status = 'FROZEN';

-- Tiny index, always used for FROZEN queries
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE status = 'FROZEN';
-- Plan: Index Scan on ix_accounts_frozen

-- account_id is VARCHAR(20)
-- Wrong: comparing to a number causes implicit cast on the column
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = 2001;
-- Plan: Seq Scan – implicit CAST(account_id AS INTEGER) on every row

-- Correct: compare string to string
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = '2001';
-- Plan: Index Scan using accounts_pkey

// com.bank.service.AccountService
package com.bank.service;

import java.sql.*;

public class AccountService {

    // Wrong: passing int when column is VARCHAR
    public void lookupWrong(Connection conn, int accountId)
            throws SQLException {
        String sql = "SELECT * FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, accountId); // implicit cast on column!
            // This defeats the index
        }
    }

    // Correct: passing String to match VARCHAR column
    public void lookupCorrect(Connection conn, String accountId)
            throws SQLException {
        String sql = "SELECT * FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId); // types match, index used
        }
    }
}

-- Before bulk load: 1 million transactions
-- After bulk load: 50 million transactions
-- Statistics still say 1 million

-- Optimizer underestimates rows for a date range
-- and picks an index scan when a seq scan would be cheaper
-- (or vice versa)

-- Fix: update statistics immediately after bulk loads
ANALYZE transactions;

-- Check statistics
SELECT relname, n_live_tup, last_analyze
FROM   pg_stat_user_tables
WHERE  relname = 'transactions';

-- Diagnostic query: find sequential scans on large tables
SELECT schemaname, relname, seq_scan, idx_scan,
       seq_scan - idx_scan AS excess_seq_scans
FROM   pg_stat_user_tables
WHERE  seq_scan > idx_scan
  AND  n_live_tup > 100000
ORDER BY excess_seq_scans DESC;