Definition
- Evidence-driven – End-to-end query optimization on a large table starts with
EXPLAIN ANALYZEto identify the real bottleneck, not with guessing. - Common causes – Sequential scan from a missing index, non-sargable predicate wrapping a column in a function, stale statistics causing bad plan choices.
- Least invasive first – Refresh statistics, add or correct indexes, rewrite predicates to be sargable, select only needed columns.
- Escalate – Partitioning, summary tables, read replicas, and caching are heavier measures for when basic optimization is insufficient.
Imagine the National Bank of India has a ledger book with 2 million transaction lines. Rajesh asks 'show me all my transactions in March.' If the clerk reads every single line from page one, it takes forever. Optimization means giving the clerk an index at the back of the book, keeping the index fresh, and asking the clerk a clear question they can answer without scanning everything.
Understanding the Concepts
Why is 2 million rows not actually large?
Two million rows is small for a modern database. PostgreSQL handles tables with hundreds of millions of rows routinely. If a query on 2 million rows is slow, the problem is almost never the row count itself. It is a missing index, a non-sargable predicate, stale statistics, or a bad join order.
-- 2 million rows is approximately:
-- 200 MB of data at 100 bytes per row
-- A modern SSD reads 200 MB in under 1 second
-- With a good index, a query touches only a few pages
-- The question is NOT "how do I handle 2 million rows?"
-- The question is "why is the query not using an index?"
How do you capture the actual execution plan?
Start by running EXPLAIN (ANALYZE, BUFFERS) which executes the query and shows actual time, actual rows, and whether it read from disk or cache.
-- Rajesh's account statement query is slow
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
AND txn_date >= '2024-03-01'
AND txn_date < '2024-04-01'
ORDER BY txn_date DESC;
-- BAD plan output:
-- Sort (cost=30000..30100 rows=500) (actual time=6200..6210 rows=42)
-- Sort Key: txn_date DESC
-- -> Seq Scan on transactions (cost=0..28000 rows=500)
-- (actual time=0.05..6180 rows=42)
-- Filter: (account_id = 'SAV2001' AND txn_date >= ... AND txn_date < ...)
-- Rows Removed by Filter: 1999958
-- Buffers: shared read=15000
-- Execution Time: 6215 ms
The plan reveals: a sequential scan reading all 2 million rows, filtering down to 42, and a sort on top. The Buffers: shared read=15000 means 15,000 pages were read from disk.
package com.bank.diagnostic;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class QueryPlanService {
private final DataSource dataSource;
public QueryPlanService(DataSource dataSource) {
this.dataSource = dataSource;
}
public void showPlan(String query) throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("EXPLAIN ANALYZE " + query)) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}
How do you fix a sequential scan with an index?
If the plan shows a sequential scan on a filtered query, add a composite index matching the filter. Equality columns go first, range columns last.
-- Add composite index: equality (account_id) first, range (txn_date) last
CREATE INDEX idx_txn_acct_date ON transactions(account_id, txn_date);
-- Re-run EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
AND txn_date >= '2024-03-01'
AND txn_date < '2024-04-01'
ORDER BY txn_date DESC;
-- GOOD plan output:
-- Index Scan Backward using idx_txn_acct_date on transactions
-- (cost=0.4..25 rows=42) (actual time=0.03..0.15 rows=42)
-- Index Cond: (account_id = 'SAV2001' AND txn_date >= ... AND txn_date < ...)
-- Buffers: shared hit=5
-- Execution Time: 0.2 ms
-- From 6215ms to 0.2ms: 31,000x improvement
-- Buffers dropped from 15,000 pages to 5 pages
How do you fix stale statistics?
If the estimated rows in the plan differ wildly from actual rows, the statistics are stale.
-- Plan says: estimated rows=500, actual rows=42
-- Or worse: estimated rows=10, actual rows=500000
-- Both indicate stale statistics
-- Fix: refresh statistics
ANALYZE transactions;
-- PostgreSQL's autovacuum normally keeps stats fresh
-- But after bulk loads or large deletes, a manual ANALYZE is wise
package com.bank.maintenance;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class PostBulkLoadService {
private final DataSource dataSource;
public PostBulkLoadService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Always run ANALYZE after a bulk data load
public void afterBulkLoad(String tableName) throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("ANALYZE " + tableName);
System.out.println("Statistics refreshed for " + tableName);
}
}
}
How do you fix non-sargable predicates?
A non-sargable predicate wraps the column in a function, preventing the index from being used.
-- NON-SARGABLE: wraps column in EXTRACT function
SELECT * FROM transactions
WHERE EXTRACT(YEAR FROM txn_date) = 2024
AND EXTRACT(MONTH FROM txn_date) = 3;
-- Plan: Seq Scan (index cannot be used!)
-- SARGABLE: plain range comparison
SELECT * FROM transactions
WHERE txn_date >= '2024-03-01' AND txn_date < '2024-04-01';
-- Plan: Index Scan (fast!)
package com.bank.repository;
public class SargableQueries {
// WRONG: YEAR() and MONTH() on the column
public static final String NON_SARGABLE =
"SELECT * FROM transactions "
+ "WHERE EXTRACT(YEAR FROM txn_date) = ? "
+ "AND EXTRACT(MONTH FROM txn_date) = ?";
// CORRECT: plain range on the column
public static final String SARGABLE =
"SELECT * FROM transactions "
+ "WHERE txn_date >= ?::date AND txn_date < ?::date";
// WRONG: LOWER() on column
// "WHERE LOWER(holder_name) = 'rajesh'"
// CORRECT: functional index or store normalized
// CREATE INDEX idx_lower_name ON accounts(LOWER(holder_name));
}
How does SELECT * hurt performance?
SELECT * forces the database to fetch wide rows from the heap even when the index has all the needed columns.
-- BAD: SELECT * forces heap access
SELECT * FROM transactions WHERE account_id = 'SAV2001';
-- GOOD: select only what you need
SELECT amount, txn_date FROM transactions WHERE account_id = 'SAV2001';
-- BEST: covering index for Index-Only Scan
CREATE INDEX idx_txn_covering
ON transactions(account_id) INCLUDE (amount, txn_date);
-- Plan: Index Only Scan (no heap access at all)
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 javax.sql.DataSource;
public class EfficientQueryService {
private final DataSource dataSource;
public EfficientQueryService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Select only the columns needed for the API response
public BigDecimal getMonthlyTotal(String accountId, String start, String end)
throws SQLException {
String sql = "SELECT SUM(amount) FROM transactions "
+ "WHERE account_id = ? AND txn_date >= ?::date AND txn_date < ?::date";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, start);
ps.setString(3, end);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
}
}
}
}
When do you escalate to structural changes?
Only after exhausting indexing and rewriting do you consider partitioning, summary tables, or caching.
-- Partitioning: for tables growing beyond millions into billions
CREATE TABLE transactions_partitioned (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2), txn_date DATE
) PARTITION BY RANGE (txn_date);
-- Summary table: for expensive aggregate queries run frequently
CREATE TABLE monthly_totals (
account_id VARCHAR(20),
month DATE,
total_debit NUMERIC(15,2),
total_credit NUMERIC(15,2),
txn_count INTEGER,
PRIMARY KEY (account_id, month)
);
-- Pre-aggregate nightly so the dashboard reads from this small table
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 javax.sql.DataSource;
public class SummaryTableService {
private final DataSource dataSource;
public SummaryTableService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Dashboard query reads from small summary table instead of scanning millions
public BigDecimal getMonthlyTotalFast(String accountId, String month)
throws SQLException {
String sql = "SELECT total_credit - total_debit FROM monthly_totals "
+ "WHERE account_id = ? AND month = ?::date";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, month);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
}
}
}
}
What is the end-to-end checklist?
End-to-end optimization checklist: 1. EXPLAIN (ANALYZE, BUFFERS) → identify actual bottleneck 2. Estimated vs actual rows gap? → ANALYZE table 3. Seq Scan on filtered query? → composite index (equality, range) 4. Function on column? → rewrite as sargable range 5. SELECT *? → select only needed columns 6. Bad join? → index join keys, filter early 7. Re-run EXPLAIN after EACH change 8. Still slow? → partitioning, summary table, cache 9. Read-heavy bottleneck? → read replica 10. Last resort → hardware (hides, does not fix)
Visual Overview
┌───────────────────────────────────────────────────────────┐ │ End-to-End Query Optimization on 2M Row Table │ ├───────────────────────────────────────────────────────────┤ │ │ │ Before: Seq Scan on 2M rows → 6215ms │ │ ┌─────────────────────────────────────────────────┐ │ │ │ Seq Scan on transactions (2,000,000 rows) │ │ │ │ Filter: account_id = 'SAV2001' AND date range │ │ │ │ Rows Removed: 1,999,958 │ │ │ │ Buffers: shared read = 15,000 pages │ │ │ └─────────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ Fix: CREATE INDEX idx_txn_acct_date(account_id, date) │ │ │ │ │ ▼ │ │ After: Index Scan → 0.2ms (31,000x faster) │ │ ┌─────────────────────────────────────────────────┐ │ │ │ Index Scan using idx_txn_acct_date │ │ │ │ Index Cond: account_id AND date range │ │ │ │ Rows returned: 42 │ │ │ │ Buffers: shared hit = 5 pages │ │ │ └─────────────────────────────────────────────────┘ │ └───────────────────────────────────────────────────────────┘
Key Takeaway
The approach to a slow query on a two-million-row table is always evidence first. Two million rows is actually small for a modern database, so if the query is slow the problem is almost never the row count. It is a missing index, a predicate that cannot use an index, stale statistics, or a bad join plan. The very first step is EXPLAIN (ANALYZE, BUFFERS) to get the real execution plan with actual timings and row counts.
The plan tells exactly where the time goes. If it shows a sequential scan on a filtered query, add a composite index matching the filter with equality columns first and range columns last. If it shows a huge gap between estimated and actual rows, the statistics are stale and running ANALYZE can be the entire fix. If the index exists but is ignored, the predicate is probably non-sargable because the column is wrapped in a function; rewriting it as a plain range comparison fixes it.
Apply the cheapest effective fix first and re-measure after every single change, because stacking fixes blindly makes it impossible to know what helped. Only when the data is genuinely huge (billions of rows, not millions) reach for structural changes like range partitioning, pre-aggregated summary tables, read replicas, and caching.
At the National Bank of India, the statement screen for Rajesh's monthly transactions took six seconds. The plan showed a sequential scan over two million rows plus a disk sort. Rajesh added a composite index on account_id and txn_date, made the date filter a plain range instead of an EXTRACT function, and refreshed statistics. The plan turned into an index scan with no sort, and the query dropped to under one millisecond.
Remember one sentence: measure first with EXPLAIN ANALYZE, fix the cheapest proven cause, and re-measure after every change.
---