What is a partial index, and when is it worth it

-- National Bank of India: partial index on PENDING loans only
CREATE INDEX ix_loans_pending
    ON loans(applied_at)
    WHERE status = 'PENDING';

-- The index stores entries ONLY for rows where status = 'PENDING'
-- All CLOSED and APPROVED rows are NOT in the index

-- Full index on status: 95% of loans are CLOSED
CREATE INDEX ix_loans_status ON loans(status);

-- Optimizer ignores the index for CLOSED (too many rows)
EXPLAIN ANALYZE
SELECT * FROM loans WHERE status = 'CLOSED';
-- Plan: Seq Scan (9.5M rows match – index is useless)

-- Optimizer might use it for PENDING (few rows)
EXPLAIN ANALYZE
SELECT * FROM loans WHERE status = 'PENDING';
-- Plan: Index Scan (only 200 rows match)
-- But the full index is huge: stores entries for all 10M rows

-- Partial index: only PENDING rows
CREATE INDEX ix_loans_pending
    ON loans(applied_at)
    WHERE status = 'PENDING';

-- The index has ~200 entries instead of 10,000,000
EXPLAIN ANALYZE
SELECT loan_id, applied_at, amount
FROM   loans
WHERE  status = 'PENDING'
  AND  applied_at < CURRENT_DATE - INTERVAL '30 days';

Index Scan using ix_loans_pending on loans
  Index Cond: (applied_at < '2026-09-25')
  Filter: (status = 'PENDING')
  Rows: 15
  Execution Time: 0.1 ms

-- 1. Size: partial index is ~1% of a full index
-- Full index on (applied_at): 10,000,000 entries, ~200 MB
-- Partial index on (applied_at) WHERE PENDING: ~200 entries, ~16 KB

-- 2. Write cost: only PENDING rows maintain the index
-- 99% of INSERTs (for CLOSED/APPROVED loans) never touch this index
INSERT INTO loans (status, applied_at, amount)
VALUES ('CLOSED', '2026-10-01', 500000.00);
-- This INSERT does NOT update ix_loans_pending (status != PENDING)

INSERT INTO loans (status, applied_at, amount)
VALUES ('PENDING', '2026-10-25', 250000.00);
-- This INSERT DOES update ix_loans_pending (status = PENDING)

-- 3. Selectivity: the partial index is always highly selective
-- because it only contains the rare rows that queries target

-- This query MATCHES the partial index filter
SELECT * FROM loans
WHERE status = 'PENDING' AND applied_at < '2026-09-01';
-- Optimizer: query requires status = 'PENDING'
-- Index filter: status = 'PENDING'
-- Match: query is a subset of filter -> use the index

-- This query does NOT match the partial index filter
SELECT * FROM loans
WHERE applied_at < '2026-09-01';
-- Optimizer: query does not require status = 'PENDING'
-- Rows outside the filter are absent from the index
-- Cannot use the partial index -> falls back to full scan

-- Each customer can have at most ONE primary account
CREATE UNIQUE INDEX ux_one_primary
    ON accounts(customer_id)
    WHERE is_primary = true;

-- Priya can have many non-primary accounts
INSERT INTO accounts (account_id, customer_id, is_primary)
VALUES ('SAV3001', 'CUST100', false);  -- OK
INSERT INTO accounts (account_id, customer_id, is_primary)
VALUES ('SAV3002', 'CUST100', false);  -- OK (not primary)

-- Priya can have exactly one primary account
INSERT INTO accounts (account_id, customer_id, is_primary)
VALUES ('SAV3003', 'CUST100', true);   -- OK (first primary)
INSERT INTO accounts (account_id, customer_id, is_primary)
VALUES ('SAV3004', 'CUST100', true);   -- FAILS!
-- ERROR: duplicate key value violates unique constraint "ux_one_primary"

-- Soft-delete: rows are marked deleted but not physically removed
ALTER TABLE accounts ADD COLUMN deleted BOOLEAN DEFAULT false;

-- Partial index on live (non-deleted) accounts only
CREATE INDEX ix_accounts_live
    ON accounts(branch_code)
    WHERE deleted = false;

-- Application queries always include WHERE deleted = false
SELECT * FROM accounts
WHERE branch_code = 'MUM001'
  AND deleted = false;
-- Plan: Index Scan on ix_accounts_live (tiny, fast)

// com.bank.service.LoanReviewService
package com.bank.service;

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

public class LoanReviewService {

    /**
     * Uses the partial index ix_loans_pending.
     * Only PENDING loans are in the index, so this is instant
     * even on a table with millions of closed loans.
     */
    public List<String> getOverduePendingLoans(
            Connection conn, int daysOld) throws SQLException {

        String sql = """
            SELECT loan_id, applicant_name, amount, applied_at
            FROM   loans
            WHERE  status = 'PENDING'
              AND  applied_at < CURRENT_DATE - ? * INTERVAL '1 day'
            ORDER BY applied_at
            """;

        List<String> results = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, daysOld);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    BigDecimal amount = rs.getBigDecimal("amount");
                    results.add(rs.getString("loan_id")
                        + " | " + rs.getString("applicant_name")
                        + " | " + amount.toPlainString());
                }
            }
        }
        return results;
    }
}

-- Partial + covering: the ultimate hot-path index
CREATE INDEX ix_loans_pending_cover
    ON loans(applied_at)
    INCLUDE (loan_id, applicant_name, amount)
    WHERE status = 'PENDING';

-- Index-only scan on a tiny partial index
EXPLAIN ANALYZE
SELECT loan_id, applicant_name, amount, applied_at
FROM   loans
WHERE  status = 'PENDING'
ORDER BY applied_at;
-- Plan: Index Only Scan, Heap Fetches: 0, ~200 entries