Practice — optimise a slow analytics pipeline

What You'll Build

In this exercise you play the role of a senior data engineer at CricketAnalytics.io — a real-time analytics platform serving 5 million daily active users during IPL season. Four production queries are causing dashboard timeouts. Your task is to diagnose each query using EXPLAIN (ANALYZE, BUFFERS), identify the root cause from the plan, apply the correct fix, and verify the improvement. Each query has a different root cause: non-sargable date predicate, stale statistics, unindexed JSONB filter, and a LIKE-based text search that needs replacing with FTS.

The exercise follows production debugging discipline: for each query, read the EXPLAIN output and identify the specific bottleneck node before making any changes. Document the before-and-after timing for each fix. The discipline of diagnosing before fixing — rather than trying random fixes — is the transferable skill this exercise develops. After fixing all four queries, implement a materialised view for the highest-traffic dashboard metric and schedule a concurrent refresh.

Each query maps to a specific M4 lesson: Query 1 maps to psql EXPLAIN patterns (Lesson 2), Query 2 maps to stale statistics diagnosis (Lesson 2), Query 3 maps to JSONB indexing (Lesson 3), and Query 4 maps to FTS implementation (Lesson 4). The materialised view in Step 5 applies views and MV concepts from Module 3 with M4 monitoring patterns. The complete tuning_report.sql documents all findings, a format directly usable in production post-incident reviews.

Analogy🏏Cricket

🏏 Think of it like cricket: This exercise runs like a proper tournament bureau's production week, and the step order is the point. Step 1 is the pitch inspection before play: you verify the ground truth — no orphaned scorecard lines, no impossible totals — because an analysis built on a corrupt book is a match played on a dangerous pitch: everything after it is invalidated. Step 2 is the specialist coaches' reports: batting summaries with rankings and form lines, each an independent, checkable piece of work using window functions over the validated data. Step 3 is the selectors' composite: batting and bowling folded into one all-rounder view via conditional aggregation — the wide wall chart built from the long book. Step 4 is the match referee's reconciliation: the chart's totals must re-add to the book's totals exactly, or something was dropped or double-counted on the way. Validate, analyse, combine, reconcile — every production pipeline plays in that order.

Setup

Query 1 — Non-Sargable Date Predicate

The 'Recent Matches' widget runs this query on every page load. It takes over 1 second on 10,000 matches. Using EXPLAIN ANALYZE, identify that EXTRACT() wraps match_date making it non-sargable, preventing index use. Create the missing index and rewrite the predicate as a sargable range.

Analogy🏏Cricket

🏏 Think of it like cricket: wrapping match_date in EXTRACT() is like asking the archivist 'find every match where, if you compute the year from the date, it equals 2024' — phrased that way, they must pull every file and do the arithmetic per document, even though the archive is already sorted by date. That's a non-sargable predicate: the index exists in spirit but the question's phrasing forbids using it. Rewriting as a sargable range — match_date >= '2024-01-01' AND match_date < '2025-01-01' — is like asking 'hand me the drawer from January 1st to December 31st 2024': the archivist walks straight to the right shelf section because the question now matches how the files are sorted. Just as a well-drilled twelfth man fetches the exact drinks tray requested instead of auditing the whole pavilion, the B-tree index seeks directly to the range's start and reads forward. And just as this widget runs on every page load — like the ground scoreboard refreshing for every spectator — a one-second scan multiplied across all traffic is the real cost. The payoff: same answer, same data, but the query is now phrased in the index's language, turning a per-request full sweep into a direct seek.

Query 2 — Stale Statistics

The season leaderboard ran in 3 seconds before yesterday's bulk load of 300,000 innings. Now it takes 45 seconds. No schema changes were made. The EXPLAIN plan shows Nested Loop where Hash Join appeared before — a sign that estimated rows are massively wrong after the load. Diagnose stale statistics and apply ANALYZE.

Analogy🏏Cricket

🏏 Think of it like cricket: Stale statistics are a captain setting fields from last season's scouting report. The planner, like the captain, never inspects every ball in the archive before choosing a strategy — it consults its summary notes: roughly how many rows per team, how values are distributed. Yesterday's bulk load of 300,000 innings is a mid-season trade window that transformed the squad: the notes now say 'this filter matches a handful of rows' when it actually matches hundreds of thousands, so the captain posts three slips for a batter who now plays nothing but sweeps — a nested-loop plan chosen for data that no longer exists. Nothing is broken in the schema, which is what makes this failure so confusing: the fix is not new equipment but fresh scouting — run ANALYZE, let the planner re-sample reality, and watch the same query pick a sensible plan again. After every bulk load, update the notes.

Query 3 — Unindexed JSONB Filter

The 'Elite Players' filter uses JSONB containment for specialisation and a cast for fitness threshold. Both are slow without indexes. Create a GIN index for the containment query and a generated column with B-tree index for the numerical range.

Analogy🏏Cricket

🏏 Think of it like cricket: filtering players by JSONB attributes without indexes is like scouting for 'all leg-spinning all-rounders with fitness above 85' by reading every player's free-form scouting dossier cover to cover. Two questions need two tools. The containment query (specialisation @> a tag) is a keyword question — so build the GIN index, the equivalent of a tag board where every dossier is cross-filed under its skills: 'leg-spin' pulls the exact set of dossiers instantly. The fitness threshold is a range question — and tag boards are useless for 'greater than 85', just as you can't line binders up by a number buried in prose. So you promote that one number to a generated column with a B-tree index: like copying each player's fitness score out of the dossier onto a sorted wall chart that updates automatically whenever the dossier changes — the range scan then reads a contiguous slice of the chart. The payoff: each predicate gets the index type built for its shape — GIN for 'contains this tag', B-tree for 'above this number' — and the elite-players filter stops re-reading the whole squad's paperwork.

Query 4 — LIKE Scan Replaced with FTS

Commentary search uses ILIKE '%keyword%' — always a full sequential scan with no index support for leading wildcards. The fts_vector stored column already exists from the setup. Create the GIN index and rewrite the query using @@ with ranked results and ts_headline snippets.

Analogy🏏Cricket

🏏 Think of it like cricket: searching commentary with ILIKE '%keyword%' is like finding every mention of 'reverse sweep' in a season of commentary by replaying every broadcast end to end — the leading wildcard means no index can help, so every search is a full sequential scan. Full-text search flips the work to ingest time: the fts_vector stored column is the producer's log where every clip was tagged with its keywords as it aired, and the GIN index is the tag catalogue — search now means looking up 'reverse sweep' in the catalogue and jumping straight to the tagged moments, exactly what the @@ operator does. Ranking with ts_rank_cd is the highlights editor ordering clips by how central the moment was, not just whether the phrase occurred, and ts_headline is cutting a short preview clip around the key delivery so the viewer sees context — applied only to the results that made the reel, since editing previews is expensive. Just as a broadcaster who tags footage once can answer any editorial request in seconds for the rest of the season, tagging text once at write time makes every future search cheap. The payoff: search cost paid once at ingest, not on every query.

Step 5 — Materialised View for High-Traffic Leaderboard

The all-time batting leaderboard query (which underlies Query 2) runs 500 times per minute during IPL season and takes 2 seconds each time — 1,000 seconds per minute of pure compute waste. A materialised view refreshed every 5 minutes reduces query time to under 5ms, cutting compute cost by 99.5%.

Analogy🏏Cricket

🏏 Think of it like cricket: recomputing the all-time batting leaderboard on every request is like the scorers re-adding every run from every scorebook in history each time a spectator glances at the big screen — 500 glances a minute during IPL season, two seconds of arithmetic per glance, over 1,000 seconds of duplicated effort every minute for an answer that barely changes. A materialised view is the stadium scoreboard operator's approach instead: compute the standings once, paint them on the board, and let half a million eyes read the same painted numbers in milliseconds. REFRESH every five minutes is the operator updating the board between overs — fresh enough for a leaderboard where positions shift only when an innings ends, and nobody protests that the board is four minutes behind. Just as the scoreboard is a real physical object (not a live recalculation), the materialised view is a real table with its own indexes, so the read is a 5ms lookup rather than a 2-second aggregation. The payoff: compute once, serve everyone — a 99.5% cost reduction bought with a staleness window the business already tolerates.