How will you optimize a slow query

-- At the National Bank of India, the account statement query is slow
EXPLAIN ANALYZE
SELECT t.txn_id, t.amount, t.txn_type, t.created_at
FROM transactions t
WHERE t.account_id = 'SAV2001'
  AND t.created_at >= '2024-01-01'
  AND t.created_at < '2024-02-01'
ORDER BY t.created_at DESC;

-- BAD plan output:
-- Sort  (cost=45000..45100 rows=1000 width=48) (actual time=8500..8520 rows=150)
--   Sort Key: created_at DESC
--   Sort Method: external merge  Disk: 2048kB
--   ->  Seq Scan on transactions  (cost=0..42000 rows=1000 width=48)
--                                 (actual time=0.1..8400 rows=150)
--         Filter: (account_id = 'SAV2001' AND created_at >= ... AND created_at < ...)
--         Rows Removed by Filter: 9999850
-- Planning Time: 0.2 ms
-- Execution Time: 8525 ms

-- The optimizer estimated 1000 rows but got 150
-- After a bulk load, statistics may be outdated

-- Fix: refresh statistics
ANALYZE transactions;

-- Re-run EXPLAIN ANALYZE to see if the plan changes
-- Sometimes this alone fixes the problem

package com.bank.maintenance;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class StatisticsRefreshService {

    private final DataSource dataSource;

    public StatisticsRefreshService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Run after bulk loads or large data changes
    public void refreshStatistics(String tableName) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute("ANALYZE " + tableName);
            System.out.println("Statistics refreshed for " + tableName);
        }
    }
}

-- The query filters by account_id (equality) and created_at (range)
-- Composite index: equality first, range last
CREATE INDEX idx_txn_acct_date
    ON transactions(account_id, created_at);

-- Re-run EXPLAIN ANALYZE:
-- Index Scan using idx_txn_acct_date on transactions
--   (cost=0.4..50 rows=150 width=48) (actual time=0.05..0.5 rows=150)
--   Index Cond: (account_id = 'SAV2001' AND created_at >= ... AND created_at < ...)
-- Planning Time: 0.1 ms
-- Execution Time: 0.6 ms

-- From 8525ms to 0.6ms: a 14,000x improvement

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

public class StatementService {

    private final DataSource dataSource;

    public StatementService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Now fast because of idx_txn_acct_date composite index
    public List<String> getMonthlyStatement(String accountId, String startDate,
                                             String endDate) throws SQLException {
        String sql = "SELECT txn_id, amount, txn_type, created_at "
                   + "FROM transactions "
                   + "WHERE account_id = ? AND created_at >= ?::date AND created_at < ?::date "
                   + "ORDER BY created_at DESC";
        List<String> results = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            ps.setString(2, startDate);
            ps.setString(3, endDate);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    results.add(rs.getLong("txn_id") + ": "
                            + rs.getBigDecimal("amount"));
                }
            }
        }
        return results;
    }
}

-- NON-SARGABLE: function on indexed column prevents index usage
SELECT * FROM transactions
WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- Plan: Seq Scan (reads ALL rows, applies function to each)

-- SARGABLE: rewrite as a range so the index works
SELECT * FROM transactions
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- Plan: Index Scan using idx_txn_acct_date

-- NON-SARGABLE: UPPER() on column
SELECT * FROM accounts WHERE UPPER(holder_name) = 'RAJESH KUMAR';
-- Fix: use a functional index
CREATE INDEX idx_upper_name ON accounts(UPPER(holder_name));
-- Or: store normalized data and compare directly

package com.bank.repository;

public class SargabilityExamples {

    // WRONG: non-sargable predicate in application code
    // "SELECT * FROM transactions WHERE YEAR(created_at) = ?"
    // The index on created_at CANNOT be used

    // CORRECT: sargable range predicate
    // "SELECT * FROM transactions WHERE created_at >= ? AND created_at < ?"
    // The index on created_at IS used

    public static final String SARGABLE_QUERY =
            "SELECT txn_id, amount FROM transactions "
          + "WHERE account_id = ? "
          + "AND created_at >= ?::date AND created_at < ?::date";
}

-- Slow: nested loop because transaction_details has no index on txn_id
EXPLAIN ANALYZE
SELECT t.amount, d.category
FROM transactions t
JOIN transaction_details d ON t.txn_id = d.txn_id
WHERE t.account_id = 'SAV2001';

-- Fix: index the join key on the other table
CREATE INDEX idx_txndetail_txnid ON transaction_details(txn_id);

-- Now the planner uses Hash Join or Merge Join: much faster

package com.bank.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class JoinOptimizationService {

    private final DataSource dataSource;

    public JoinOptimizationService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Filter early: WHERE on account_id shrinks rows BEFORE the join
    public void getCategorizedTransactions(String accountId) throws SQLException {
        String sql = "SELECT t.amount, d.category "
                   + "FROM transactions t "
                   + "JOIN transaction_details d ON t.txn_id = d.txn_id "
                   + "WHERE t.account_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getBigDecimal("amount") + " - "
                            + rs.getString("category"));
                }
            }
        }
    }
}

-- BAD: SELECT * forces heap access even when the index has what we need
SELECT * FROM transactions WHERE account_id = 'SAV2001';

-- GOOD: select only needed columns
SELECT amount, created_at FROM transactions WHERE account_id = 'SAV2001';

-- BEST: covering index answers entirely from the index
CREATE INDEX idx_txn_covering
    ON transactions(account_id) INCLUDE (amount, created_at);
-- Plan: Index Only Scan (no heap access at all)

-- Partitioning: split huge table by date range
CREATE TABLE transactions_partitioned (
    txn_id BIGSERIAL,
    account_id VARCHAR(20) NOT NULL,
    amount NUMERIC(15,2) NOT NULL,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions_2024_01 PARTITION OF transactions_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Date-range queries prune to one partition

package com.bank.cache;

import redis.clients.jedis.Jedis;
import java.math.BigDecimal;

public class StatementCacheService {

    private final Jedis redis;

    public StatementCacheService(Jedis redis) {
        this.redis = redis;
    }

    // Caching: hot read-mostly data served from Redis
    public void cacheStatement(String accountId, String month, String result) {
        String key = "stmt:" + accountId + ":" + month;
        redis.setex(key, 300, result);  // 5-minute TTL
    }

    public String getCachedStatement(String accountId, String month) {
        return redis.get("stmt:" + accountId + ":" + month);
    }
}