SQL Performance Tuning Exercise

What You'll Build

In this exercise you will profile and tune slow queries against a large IPL deliveries table, turning sluggish full scans into fast indexed lookups. You will generate a substantial dataset, measure baseline performance with EXPLAIN ANALYZE, identify why each query is slow, then apply targeted fixes, indexes, query rewrites, and window functions, and measure the speedup, just as a data engineer optimises a real production workload.

By the end you will have a single SQL file that demonstrates the full tuning loop on several realistic queries: a selective filter that should use an index, a function-wrapped filter that defeats one, a top-N-per-group query rewritten with a window function, and a join tuned by indexing its key. This is performance engineering in miniature, the skill that keeps real systems responsive at scale.

The focus is on measurement, diagnosis, and verification rather than guessing. You will read execution plans to see scans versus index usage, form a hypothesis about each slowdown, apply one change, and confirm the improvement in the plan and timing. Predict the effect of each fix before measuring, so you build genuine intuition for what makes queries fast rather than cargo-culting indexes.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the analytics team assembling the broadcast's end-of-day statistics package. Just as they compile separate panels, top scorers, team totals, venue records, and trend graphs, from the same ball-by-ball data, you build separate grouped queries from one deliveries table. Just as each panel must be accurate before it airs to millions, each query must be verified before it feeds the dashboard. Just as the package tells the day's story through numbers, your query layer turns raw deliveries into the season's narrative. Just as a wrong figure on air is caught by cross-checking panels against each other, team totals summing to the tournament total, your queries can be validated against one another for consistency. Just as the package is rebuilt fresh from the feed each day, every dashboard number is derived from raw rows at query time, never hand-edited. This reveals why disciplined aggregation is the backbone of every stats broadcast.

Prerequisites

A working PostgreSQL setup with the psql client, as built in earlier exercises, ready to run SQL files against a local database.
Completion of Module 5's lessons on indexes and EXPLAIN, plus this module's window functions, all of which you will apply to tune queries.
Comfort reading basic EXPLAIN output, distinguishing a sequential scan from an index scan, since diagnosis depends on interpreting plans.
A text editor to maintain a single tuning_lab.sql file so the dataset generation, queries, and fixes stay reproducible and rerunnable.

Setup & Project Structure

You will create a fresh database and generate a deliveries table with hundreds of thousands of rows using generate_series, so query timings are meaningful rather than trivially instant. All work lives in one rerunnable script: the data generation, the baseline queries, and the tuning fixes, keeping the entire performance experiment reproducible from a single command.

Analogy🏏Cricket

🏏 Think of it like cricket: Building the deliveries table with season, match_date, and dismissal columns is like laying out a season-long scorebook designed for every question you will ask. Just as a good scorebook records not only runs but the date and manner of each dismissal so any summary can be drawn later, your enriched table carries the extra columns every aggregation and date query will need. Just as a scorer keeps the blank scorebook template so next season starts identically, your schema lives in a rerunnable script. Just as all the tournament's summary graphics are produced from that one book, every dashboard panel lives in one queries file you can run end to end. Just as reopening the book regenerates any figure on demand, rerunning the file regenerates every number at once. This reveals why a well-designed table and scripted panels make a dashboard reproducible.

Generating a large synthetic dataset programmatically is standard practice for performance testing, because real slowness only appears at scale. Keeping generation and tuning in scripts means you can rebuild the exact same large table and re-run the experiment, which is essential for fairly comparing before-and-after timings rather than chasing noise from a hand-built, inconsistent dataset.

bash

# Fresh database for the tuning lab.
dropdb ipl_tuning 2>/dev/null; createdb ipl_tuning
mkdir -p ipl_tuning_lab && cd ipl_tuning_lab
touch tuning_lab.sql   # generation + baseline + fixes

psql ipl_tuning -f tuning_lab.sql

# Confirm the row count is large enough to matter:
psql ipl_tuning -c "SELECT COUNT(*) FROM deliveries;"   # expect 500000

Step 1 — Foundation

Step one generates a large deliveries table with about five hundred thousand rows using generate_series, assigning pseudo-random teams, venues, scores, and dates so the data has realistic variety. No indexes are created yet, deliberately, so your baseline queries run against an unindexed table and the slowness is real and measurable, giving you a meaningful starting point to improve from.

This generated dataset is your test bed. Because every row is produced by a formula, the table is reproducible: rerunning the script yields the same data and the same baseline behaviour. Establishing this large, index-free starting point is essential, since the entire exercise is about observing how targeted changes transform performance on data big enough for those changes to matter.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the groundstaff and scorers preparing before the season opener. Just as they set up the pitch, boundary, and a fresh scorebook with columns for every stat to be recorded, the schema lays out typed columns for runs, dates, and dismissals. Just as a complete scorebook lets every later statistic be computed accurately, a complete table lets every dashboard panel aggregate correctly. Just as the same setup repeats reliably each match, rerunning the schema rebuilds identical data. Just as the scorers agree before the first ball how a wide or a no-ball will be recorded, the schema fixes types and NULL conventions up front so every later aggregate interprets the data identically. Just as an incomplete scorebook column ruins every statistic that depends on it, a mistyped or missing column undermines every panel built on the table. This shows why a thorough foundation makes every later summary dependable.

sql

-- tuning_lab.sql : run with  psql ipl_tuning -f tuning_lab.sql
DROP TABLE IF EXISTS deliveries;

CREATE TABLE deliveries (
    delivery_id INTEGER PRIMARY KEY,
    batsman_id  INTEGER NOT NULL,
    team_name   TEXT    NOT NULL,
    venue       TEXT    NOT NULL,
    runs_scored INTEGER NOT NULL,
    match_date  DATE    NOT NULL
);

-- Generate ~500,000 rows with pseudo-random but reproducible values.
INSERT INTO deliveries (delivery_id, batsman_id, team_name, venue, runs_scored, match_date)
SELECT g,
       1 + (g % 400),                                  -- 400 distinct batsmen
       (ARRAY['Mumbai Indians','Royal Challengers',
              'Chennai Super Kings','Gujarat Titans'])[1 + (g % 4)],
       (ARRAY['Wankhede','Chinnaswamy','Chepauk','Motera'])[1 + (g % 4)],
       (g * 7) % 7,                                     -- runs 0..6 per ball
       DATE '2024-03-01' + ((g % 60))                  -- spread over 60 days
FROM generate_series(1, 500000) AS g;

-- No indexes yet: baseline queries below will run as sequential scans.
ANALYZE deliveries;   -- gather statistics for honest planner estimates
SELECT COUNT(*) AS rows_loaded FROM deliveries;   -- expect 500000

Step 2 — Core Logic

Now you measure baselines and apply the first fixes. You will run EXPLAIN ANALYZE on a selective filter and a join, observe the sequential scans, then add targeted indexes and re-measure to see the plans switch to index scans and the timings drop. Predict the effect of each index before running, then confirm it in the plan.

These steps demonstrate the core tuning loop on the two most common slow patterns: an unindexed selective filter and an unindexed join key. Adding the right index transforms each from a full table scan into a direct lookup. Save every measurement in tuning_lab.sql as comments so you have a clear before-and-after record of each fix's impact.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the broadcast analyst slicing the same day's play three ways for three on-screen panels. Just as they break the data down by team for one graphic, by player for another, and by venue for a third, each query groups the same table on a different column. Just as every panel must rank its entries clearly, you order each by its key aggregate. Just as the panels share one underlying scorecard, your queries share one deliveries table. Just as a panel showing only three teams instantly warns the analyst the feed is incomplete, eyeballing each query's row counts against known facts catches bugs early. Just as the director insists each graphic answers exactly one question cleanly, each query should group on exactly the dimension its panel needs. This reveals why a single clean dataset can power many distinct summary views.

sql

-- tuning_lab.sql (continued) : the tuning loop.

-- BASELINE 1: a selective filter on runs, no index -> sequential scan.
EXPLAIN ANALYZE
SELECT delivery_id, team_name FROM deliveries WHERE runs_scored = 6;
-- Observe: Seq Scan on deliveries, scanning all 500k rows.

-- FIX 1: index the filtered column, then re-measure.
CREATE INDEX idx_runs ON deliveries (runs_scored);
EXPLAIN ANALYZE
SELECT delivery_id, team_name FROM deliveries WHERE runs_scored = 6;
-- Observe: now an Index Scan (or Bitmap Index Scan); far fewer rows touched.

-- BASELINE 2: aggregate per batsman with no supporting index.
EXPLAIN ANALYZE
SELECT batsman_id, SUM(runs_scored) AS runs
FROM deliveries
GROUP BY batsman_id;
-- Likely a Seq Scan + HashAggregate (acceptable, but watch the timing).

-- FIX 2: index the grouping/join key to speed lookups and joins.
CREATE INDEX idx_batsman ON deliveries (batsman_id);
EXPLAIN ANALYZE
SELECT batsman_id, SUM(runs_scored) AS runs
FROM deliveries
WHERE batsman_id = 42                  -- now a selective lookup
GROUP BY batsman_id;
-- Observe: Index Scan on idx_batsman; the filter is satisfied via the index.

Step 3 — Integration & Enhancement

Step three tackles the subtler problems: a function-wrapped filter that defeats an index, a date filter rewritten into an index-friendly range, and a top-N-per-group query rewritten from a slow correlated subquery into an efficient window function. These integrate the module's window functions with Module 5's indexing to fix the slow patterns that catch out even experienced developers.

Watch how a query's shape, not just missing indexes, determines speed. Wrapping a column in a function or using a leading wildcard blocks index use, and a correlated subquery for top-N re-scans per group, while a window function does it in one pass. Saving these rewrites with their before-and-after plans completes a realistic tuning portfolio demonstrating genuine performance skill.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the analyst adding the advanced graphics that follow the basic scoreboard. Just as they show only players who met a minimum-innings cut for an awards panel and chart scoring momentum month by month, you apply HAVING thresholds and DATE_TRUNC trends. Just as these refined panels demand more care than a raw total, they require correct filter placement and NULL handling. Just as the full package tells a richer story, your integrated queries deliver a complete dashboard. Just as the momentum chart would mislead viewers if rain-abandoned fixtures were charted as scoring dips, your trend query must decide explicitly how missing and NULL data appear. Just as the awards cut-off is printed on the graphic so viewers trust the ranking, the HAVING threshold belongs visibly in the query, not buried in app code. This shows why combining the module's tools yields professional-grade reporting.

sql

-- tuning_lab.sql (continued) : shape-based fixes.

-- PROBLEM 3: a function-wrapped filter cannot use an index -> scan.
EXPLAIN ANALYZE
SELECT delivery_id FROM deliveries
WHERE EXTRACT(MONTH FROM match_date) = 4;     -- wraps the column -> Seq Scan

-- FIX 3: rewrite as an index-friendly range, with a date index.
CREATE INDEX idx_date ON deliveries (match_date);
EXPLAIN ANALYZE
SELECT delivery_id FROM deliveries
WHERE match_date >= DATE '2024-04-01' AND match_date < DATE '2024-05-01';
-- Observe: Index/Bitmap scan on idx_date; the range is sargable.

-- PROBLEM 4: top scorer per team via a SLOW correlated subquery.
EXPLAIN ANALYZE
SELECT team_name, batsman_id, runs_scored
FROM deliveries d
WHERE runs_scored = (
    SELECT MAX(d2.runs_scored) FROM deliveries d2 WHERE d2.team_name = d.team_name
);   -- re-scans deliveries per row: expensive

-- FIX 4: one-pass window function with a ranking, filtered in a CTE.
EXPLAIN ANALYZE
WITH ranked AS (
    SELECT team_name, batsman_id, runs_scored,
           ROW_NUMBER() OVER (PARTITION BY team_name
                              ORDER BY runs_scored DESC, batsman_id) AS rn
    FROM deliveries
)
SELECT team_name, batsman_id, runs_scored FROM ranked WHERE rn = 1;

Step 4 — Testing & Verification

Finally, rebuild and run the whole script, comparing the before-and-after EXPLAIN ANALYZE output for each query to confirm every fix worked. Because generation and tuning live in one reproducible script, you can rerun the entire experiment and verify that the scans became index scans, the function-wrapped filter became sargable, and the window rewrite replaced the costly correlated subquery.

Analogy🏏Cricket

🏏 Think of it like cricket: Rebuilding the whole dashboard and checking each panel is like reprinting the entire season summary from the scorebook and confirming every graphic matches the record. Just as a broadcaster would not trust a figure that only appears if statements are run in a lucky order, you rebuild from scratch so no result depends on the sequence you happened to type. Just as the scorer reruns the totals and cross-checks the Orange Cap, run rates, and dismissal counts against the book, you check each panel against the expected results below. Just as the season book lets them regenerate every graphic in minutes, your scripted logic regenerates the entire dashboard data layer in seconds. Just as matching every panel to the record proves the summary is trustworthy, matching outputs confirms your figures are reproducible and correct. This reveals why a clean full rebuild is the real proof of a dashboard's integrity.

bash

# Rebuild and run end to end, then read the plans.
dropdb ipl_tuning && createdb ipl_tuning
psql ipl_tuning -f tuning_lab.sql 2>&1 | grep -E "Seq Scan|Index|Execution Time"

# WHAT TO VERIFY in the EXPLAIN ANALYZE output:
# FIX 1: WHERE runs_scored = 6 changes from 'Seq Scan' to an index/bitmap scan;
#        Execution Time drops substantially.
# FIX 2: filtering batsman_id = 42 uses idx_batsman (Index Scan).
# FIX 3: the date RANGE uses idx_date; the EXTRACT version did NOT (Seq Scan).
# FIX 4: the window-function plan has ONE scan + WindowAgg, versus the
#        correlated subquery's repeated inner scans; compare Execution Time.

# Tip: run a single query's plan in isolation to read it clearly:
psql ipl_tuning -c \
"EXPLAIN ANALYZE SELECT delivery_id FROM deliveries WHERE runs_scored = 6;"
# Look for 'Index Scan' or 'Bitmap Heap Scan' rather than 'Seq Scan'.

Warning: Do not add indexes blindly hoping for speed. Every index slows writes and consumes storage, and an index the planner never uses, because a query wraps the column in a function or is not selective, is pure overhead. Always confirm with EXPLAIN ANALYZE that a new index is actually used and that timing improved, and drop indexes that no query relies on.

Extension Challenge: Add a composite index on (team_name, runs_scored) and test whether it speeds the Fix 4 window query's per-team ranking; compare its plan to the single-column version. Then deliberately create an unused index, observe it being ignored by EXPLAIN, and measure how it slows a bulk INSERT, demonstrating firsthand the write cost of over-indexing.

Performance tuning is a disciplined loop: measure with EXPLAIN ANALYZE, diagnose the slow step, apply one targeted fix, then verify the improvement.
A selective filter on an unindexed column does a sequential scan; indexing that column turns it into a fast index or bitmap scan.
Query shape matters as much as indexes: wrapping a column in a function or using a leading wildcard blocks index use and forces a scan.
Rewrite function-wrapped date filters as half-open ranges so the planner can seek through an index on the date column.
A correlated subquery for top-N-per-group re-scans per row; a ROW_NUMBER window function computes the same answer in a single efficient pass.
Verify every index is actually used and improves timing, and avoid over-indexing, since unused indexes only add write cost and storage with no benefit.