Optimizing a slow query on a billion rows beyond "add an index"

• Execution plan first – every optimization starts by reading the plan to find the bottleneck: full scans, spills, missing index usage, or wrong join strategies.
• Partitioning – date or range partitioning lets the engine skip entire partitions so a date-filtered query touches only the relevant months.
• Composite and covering indexes – a composite index matches the WHERE predicates; a covering index also includes the SELECT columns so the engine never touches the main table.
• Materialized views and pre-aggregation – precomputed rollups serve repeated reports from a tiny summary instead of rescanning the fact table.
• Query rewrites – replacing correlated subqueries with joins, pushing filters earlier, and avoiding functions on indexed columns removes avoidable work the plan exposes.

What does "optimize beyond add an index" mean?

When a query is slow on a billion rows, the instinct is to add a single-column index and hope for the best. That rarely solves the problem because the bottleneck is usually how much data the engine reads, not whether it can look up one value. Real optimization means reducing the data the engine touches at every level – partitions, columns, rows, and repeated work – and proving each reduction in the execution plan.

-- Step zero: read the plan
EXPLAIN ANALYZE
SELECT account_id, SUM(amount)
FROM transactions
WHERE txn_date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY account_id;

The plan tells you whether the engine does a full-table scan, how many rows it estimates versus actual, whether indexes are used, and whether sorts or joins spill to disk. Every optimization below targets a specific line in that plan.

Why does reading the execution plan come first?

The execution plan is the diagnostic tool. Without it you are guessing. You look for a sequential scan on the large table, a big gap between estimated and actual row counts, expensive hash joins or sorts that spill to disk, and whether existing indexes are ignored.

-- PostgreSQL style plan output
Seq Scan on transactions  (cost=0.00..18432100.00 rows=1000000000)
  Filter: (txn_date >= '2026-10-01' AND txn_date <= '2026-10-31')
  Rows Removed by Filter: 970000000

This plan shows a sequential scan reading all one billion rows and discarding 970 million. That is the problem to fix.

How does date or range partitioning help?

Partitioning divides the table into physical segments by a column value, typically a date. When the query filters on that column, the engine performs partition pruning and skips segments that cannot contain matching rows.

-- Create the table partitioned by month
CREATE TABLE transactions (
    txn_id       BIGINT,
    account_id   BIGINT,
    amount       DECIMAL(18,2),
    txn_date     DATE,
    branch_code  VARCHAR(10)
) PARTITION BY RANGE (txn_date);

-- Add monthly partitions
CREATE TABLE transactions_2026_10
    PARTITION OF transactions
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE transactions_2026_11
    PARTITION OF transactions
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- The same query now prunes to one partition
EXPLAIN ANALYZE
SELECT account_id, SUM(amount)
FROM transactions
WHERE txn_date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY account_id;

Append  (actual rows=30000000)
  -> Seq Scan on transactions_2026_10  (actual rows=30000000)

The plan now reads only the October partition instead of the full billion rows. The key is that the partition column must match how queries filter, which in banking is almost always by date.

How do you choose a composite or covering index?

A composite index is chosen from the WHERE clause predicates, and column order matters because the leading column must appear in the filter.

-- Composite index matching the common query pattern
CREATE INDEX idx_txn_acct_date
    ON transactions (account_id, txn_date);

-- This query uses the index because account_id is the leading column
SELECT amount, txn_date
FROM transactions
WHERE account_id = 100042
  AND txn_date BETWEEN '2026-10-01' AND '2026-10-31';

A covering index additionally includes the columns the query selects, so the engine answers entirely from the index without visiting the main table.

-- Covering index: includes amount so the query never touches the heap
CREATE INDEX idx_txn_covering
    ON transactions (account_id, txn_date)
    INCLUDE (amount);

-- Plan shows index-only scan
Index Only Scan using idx_txn_covering on transactions
  Index Cond: (account_id = 100042 AND txn_date >= '2026-10-01')
  Heap Fetches: 0

Zero heap fetches means the engine never reads the main table. That is the power of a covering index.

When do you use a materialized view or pre-aggregation?

When the same heavy rollup runs repeatedly – for example a dashboard showing total spend per branch per day – you precompute it so the dashboard reads a tiny summary instead of scanning a billion rows each time.

-- Materialized view for the daily branch summary
CREATE MATERIALIZED VIEW mv_daily_branch_totals AS
SELECT branch_code,
       txn_date,
       COUNT(*)        AS txn_count,
       SUM(amount)     AS total_amount
FROM transactions
GROUP BY branch_code, txn_date;

-- Refresh on schedule or incrementally
REFRESH MATERIALIZED VIEW mv_daily_branch_totals;

-- Dashboard query now reads the tiny materialized view
SELECT branch_code, SUM(total_amount)
FROM mv_daily_branch_totals
WHERE txn_date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY branch_code;

The trade-off is freshness versus speed. You tune the refresh schedule to the business tolerance.

What query rewrites remove avoidable work?

Common rewrites target patterns the plan exposes as wasteful.

-- Bad: function on indexed column defeats the index
SELECT * FROM transactions
WHERE EXTRACT(MONTH FROM txn_date) = 10;

-- Good: range predicate uses the index
SELECT * FROM transactions
WHERE txn_date >= '2026-10-01' AND txn_date < '2026-11-01';

-- Bad: correlated subquery runs once per outer row
SELECT t.account_id, t.amount
FROM transactions t
WHERE t.amount > (
    SELECT AVG(amount) FROM transactions WHERE account_id = t.account_id
);

-- Good: window function computes the average in one pass
SELECT account_id, amount
FROM (
    SELECT account_id, amount,
           AVG(amount) OVER (PARTITION BY account_id) AS avg_amt
    FROM transactions
) sub
WHERE amount > avg_amt;

Other rewrites include pushing filters before joins so fewer rows flow through, selecting only needed columns to enable covering index usage, and replacing a full count with an existence check when you only need to know whether rows exist.

How do these techniques apply in a columnar warehouse?

In Spark, Redshift, or BigQuery the storage is columnar, and the equivalents are partition pruning and predicate pushdown.

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("BankQueryOpt").getOrCreate()

# Partitioned Delta table
txn = spark.read.format("delta").load("/lake/transactions")

# Partition pruning: Spark reads only the October partition folder
october = txn.filter(
    (F.col("txn_date") >= "2026-10-01") &
    (F.col("txn_date") < "2026-11-01")
)

# Projection pushdown: only two columns read from Parquet
result = october.select("account_id", "amount") \
    .groupBy("account_id") \
    .agg(F.sum("amount").alias("total_spend"))

result.explain(True)  # Confirm pruned partitions and columns in the plan

== Physical Plan ==
*(2) HashAggregate(keys=[account_id], functions=[sum(amount)])
+- *(1) FileScan delta [account_id, amount]
        PartitionFilters: [txn_date >= 2026-10-01, txn_date < 2026-11-01]
        ReadSchema: struct<account_id:bigint,amount:decimal(18,2)>

The plan confirms both partition pruning (PartitionFilters) and column pruning (ReadSchema lists only two columns).

What are common mistakes when optimizing large queries?

The most frequent mistakes are guessing without reading the plan, adding indexes on columns not in the WHERE clause, partitioning by a high-cardinality column that creates millions of tiny partitions, and refreshing materialized views too aggressively so the refresh cost exceeds the query savings.

-- Mistake: partitioning by txn_id creates one partition per transaction
CREATE TABLE transactions_bad
    PARTITION BY RANGE (txn_id);  -- millions of partitions

-- Correct: partition by a low-cardinality time column
CREATE TABLE transactions_good
    PARTITION BY RANGE (txn_date);  -- one partition per month

Another mistake is adding many single-column indexes instead of one well-chosen composite index, which wastes storage and slows writes.

What should a senior engineer know?

A senior engineer combines these techniques and justifies every change from the execution plan.

• Partitioning plus a covering index can reduce a billion-row scan to an index-only scan over a single month.
• Pre-aggregation on Delta Lake with Z-ordering clusters related data in the same files, improving predicate pushdown.
• In distributed systems, broadcast joins replace shuffle joins when one side is small, which is another form of "read less data."
• The cost model matters: an optimization that saves reads but doubles write cost may not be worth it for a write-heavy workload.

# Z-ordering clusters data by account_id within each partition
spark.sql("""
    OPTIMIZE transactions
    WHERE txn_date >= '2026-10-01' AND txn_date < '2026-11-01'
    ZORDER BY (account_id)
""")

The principle is always the same: read less data, prove it in the plan, and combine techniques for maximum reduction.

What are common interview follow-up questions?

• How do you handle queries that cannot use partition pruning because the filter is on a non-partition column? You use Z-ordering or a secondary index.
• How do you decide between a materialized view and a cache? A materialized view is persistent and shared; a cache is session-scoped and volatile.
• How does this differ in a columnar warehouse versus a row store? Columnar formats benefit more from column pruning; row stores benefit more from covering indexes.
• What if the slow query involves a join? You check whether one side can be broadcast, whether both sides are partitioned on the join key, and whether predicate pushdown reduces either side before the join.