Queries that read less
🎯You'll be able to
Read a query plan, and rewrite queries to scan fewer rows and columns, filter early and avoid repeated work.
🏠The everyday picture
To find one bill from March, you open the March file, not every file since the shop opened.
📘What it really is
-
Read the plan:
EXPLAINshows what the engine will do;EXPLAIN ANALYZEruns it and shows the time and rows at each step. Look for full scans of big tables, rows multiplying after joins, and steps that take most of the time. -
Select columns, not
*. Columnar stores read only what you name. - Filter on partition or sort columns (dates, usually), so whole chunks are skipped.
- Filter before joining and aggregate before joining when you can: join 30 daily totals, not 10,000 lines.
-
Pre-compute common answers: a mart such as
mart_daily_sales(30 days × 3 stores = 90 rows) answers the dashboard without scanning the fact table each time. -
LIMITisn't a filter for cost: many engines still scan everything before returning a few rows. - Approximate functions such as
approx_count_distinctare much cheaper for huge tables when an exact figure isn't needed.
📝A worked example
Input: compare
select order_date, sum(net_amount_paise) from marts.fct_order_lines group by 1 with
select order_date, sum(net_revenue_paise) from marts.mart_daily_sales group by 1.
Expected output: both give the same 30 daily totals (₹19,65,764 for the month). The first reads 10,181 rows; the second reads 90. At SabziSathi's size both are instant, but the ratio is what matters as data grows.
🌍Where it's used
- Dashboards: built on small summary tables refreshed daily.
- Cost reviews: the most expensive queries rewritten first.
- Ad-hoc analysis: analysts taught to filter on dates and name columns.
📝Case scenario
A logistics company's daily report joined 3 billion tracking events to shipments, then filtered to the last day. Moving the date filter before the join cut the job from 40 minutes to 2, because the join then handled one day's events instead of five years'.
🖱️Try it
Run both worked-example queries with explain analyze and note the rows read by each scan. Success: you can point to 10,181 and 90 in the
plans.
⚠️Common mistakes
- Trusting
LIMITto save cost. Sign: a 10-row preview bills a full scan. Fix: filter and select columns. - Filtering after joining. Sign: huge intermediate row counts in the plan. Fix: filter each side first.
- Querying detail when a summary would do. Sign: dashboards that scan the fact table on every view. Fix: a mart.
🛠️For your project
Project 3's dashboard reads mart_daily_sales, not the fact table, for its headline numbers.
✅Quick check
Does LIMIT 10 make a query cheap on a columnar warehouse?
Show the answer
Often not: the engine may still scan everything first.
Why filter before a join?
Show the answer
The join then works on fewer rows.