How Three Changes Cut One Query from $550,000 to $400

Concept. A company runs one weekly aggregation 100 times a day against a 1 TB table. Without an index, the query can consume $550,000 a year in cloud compute. A B+Tree index, columnar storage, and compression cut the bill to about $400.

Intuition. A Hash Partition Join scans the full 1 TB of listen history and costs about 49,200 IOs per run. A B+Tree index restricts the scan to this week's 1% slice. Columnar storage reads only the four required columns, and compression reduces their size. Together, the three changes cut the query to about 35 IOs, roughly 1,400× fewer.

Putting It All Together Reading Time: 15 mins

The bill

An unoptimized weekly "Trending Genres" query runs 100 times a day. Each run takes 9 hours, and the workload costs $1,500 a day, or about $550,000 a year, in AWS compute.

The query: weekly trending genres

Count listens and listeners, and average ratings by genre for October 21-27. This is the concrete query for the worked example; the storage costs below are illustrative estimates.

SELECT s.genre,
       COUNT(*) AS listens,
       COUNT(DISTINCT l.user_id) AS listeners,
       AVG(l.rating) AS avg_rating
FROM Listens AS l
JOIN Songs AS s ON s.song_id = l.song_id
WHERE l.listen_time >= '2026-10-21'
  AND l.listen_time <  '2026-10-28'
GROUP BY s.genre
ORDER BY listens DESC;

Start with the physical schema

The cost model uses the following physical layout.

Component Stats Disk footprint (64 MB pages)
Songs table ~50 M songs 400 pages (25 GB)
Listens table ~10 B listens 16,000 pages (1 TB)
Weekly scope Oct 21-27 filter 150 pages (~1% of data)
Memory (B) Buffer pool 20 pages (1.28 GB)

All calculations below use a 64 MB page size.

Work through the query cost

Songs: 400 pages. Listens: 16,000 pages. Buffer: 20 pages. This week's listens: 150 pages.

Problem 1 · Baseline join

What does a Hash Partition Join cost? Would a Block Nested Loop Join be cheaper?

Problem 2 · Skip irrelevant dates

Only 150 Listens pages match the week. Which index would help, and which pages must we still read?

Problem 3 · Skip unused columns

The query uses only a few columns. How could we avoid reading the rest of each row?

Problem 4 · Store fewer bytes

Genres and ratings repeat. Which lossless encodings could shrink these columns?

Problem 1: The Baseline Investigation

Problem. The weekly trending report takes 9 hours. Before optimizing, we need the baseline cost. What is the IO cost of a Hash Partition Join (HPJ) on these two tables? Should the optimizer choose a Block Nested Loop Join (BNLJ) instead?

Show Solution

Option 1: Block Nested Loop Join (BNLJ)

BNLJ would scan the 1 TB Listens table once for every 20-page block of Songs.

  • Formula: M + (M / B) × N (M = outer = Songs pages, N = inner = Listens pages)

  • Math: 400 + (400 / 20) × 16,000 = 320,400 IOs

  • Why: Those repeated scans drive the cost to 320,400 IOs.

Option 2: Hash Partition Join (HPJ)

HPJ avoids those repeated scans but still reads the full Listens table.

  • Formula: 3 × (M + N)

  • Math: 3 × (400 + 16,000) = 49,200 IOs

  • Why: The query needs only 1% of the data, but HPJ reads all 16,000 pages.

Problem 2: The Index Win

Problem. Only about 150 of the 16,000 Listens pages contain data for Oct 21-27. How do we skip the other pages, and what is the new IO cost?

Show Solution

  • Change. Create a B+Tree index on Listens(listen_time). The index jumps to the start of the date range and scans only the matching pages.

  • IO math:

    • Scan Songs: 400 IOs
    • Index lookup: ~4 IOs
    • Fetch filtered Listens: 150 IOs
    • Total: ~554 IOs

  • Why. The index skips the other 99% of the Listens table and cuts the cost by about 90×.

Problem 3: The Column Store Win

Problem. Even with an index, row-based storage loads the whole 150-byte row just to read the 24 bytes we actually need (user_id, listen_time, genre, rating), plus the song_id join keys. Treat the byte counts as illustrative estimates. How do we eliminate this column waste, and what is the estimated IO savings?

Show Solution

  • Change. Store each column separately with a columnar system such as BigQuery or Parquet. The engine reads only the columns referenced by the query and skips fields such as IP addresses and device IDs.

  • IO math:

    • The engine reads only one-sixth of the row width.
    • Total: ~60 IOs (Listens 25 + Songs 35)

  • Why. Columnar storage is optimized for aggregation. Reading only the relevant columns cuts IO by another 9×.

Problem 4: The Compression Win

Problem. How do we further shrink the genre, rating, and user_id columns without losing data?

Show Solution

  • Technique 1 (Dictionary encoding). Map the 200 unique genres to 1-byte IDs (16 pages down to 1 page).

  • Technique 2 (Bit packing). Ratings are 1 to 5, so store them in 4 bits instead of a 32-bit integer (7 pages down to 1 page).

  • Technique 3 (Run-length encoding). Since the data is sorted, compress repeating user IDs (13 pages down to 5 pages).

  • Final result: ~35 IOs, 20 seconds per run, ~$400 a year.

  • Why. Each encoding matches the shape of its column and reduces storage without losing information.

How the three changes compound

Four stages show one weekly aggregation falling from 49,200 IOs and about 550,000 dollars a year to about 35 IOs and 400 dollars a year. A B+Tree index cuts the query to about 554 IOs, columnar storage cuts it to about 60, and compression cuts it to about 35. Dividing the endpoints gives roughly 1,400 times fewer IOs.

Figure 1. Four stages of the same weekly aggregation. The Hash Partition Join costs 49,200 IOs. A B+Tree index cuts the query to about 554 IOs, columnar storage cuts it to about 60, and compression cuts it to about 35. The endpoint ratio gives roughly 1,400× fewer IOs and lowers annual cost from about $550,000 to about $400.

Takeaway: A B+Tree index, columnar storage, and compression remove three independent sources of waste, cutting one weekly aggregation from about $550,000 to about $400 a year.