Growth Notes

Query Performance

Tags: web-dev analytics concept

Query Performance

Date: 2026-09-27

A handful of rewrites fix most slow SQL, and they share one idea: let the database discard rows as early and as cheaply as possible. In an application database the cost is time; in a warehouse it’s bytes scanned, which is money — and the rewrites differ.

Query performance is how much work a query makes the database do — rows read, sorted and moved — for the answer it returns. Reading the plan to find where that work goes is Query Planning; this note is what you change once you’ve found it.

Two cost models

                     APPLICATION DB (Postgres, MySQL)     WAREHOUSE (BigQuery, Snowflake)
storage              rows, together                       columns, separately
what's expensive     reading many rows, sorting them      reading many columns × many rows
main lever           indexes                              partitioning, clustering, fewer columns
you pay in           latency                              latency and bytes billed
SELECT *             wasteful                             the single most expensive habit

Columnar storage keeps each column in its own file, so a query reads only the columns it names. SELECT * on a 200-column events table reads all 200 even if you use three — Warehouse-First Analytics. [CHECK: BigQuery on-demand bills by bytes processed, with capacity pricing as the alternative — verify current model; no figures here.]

The rewrites

1. Don’t wrap an indexed column in a function. The index is ordered by the column’s raw value, not by the function’s output.

-- can't use an index on created_at: every row's DATE() has to be computed
WHERE DATE(created_at) = '2026-09-01'
 
-- range on the raw column: index seek
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

A predicate the index can serve is called sargable (from “search argument”). The same applies to LOWER(email) = ..., amount * 1.2 > ... and implicit casts where the column’s type differs from the value’s — Indexing.

2. Filter on the partition column, directly. A warehouse table partitioned by date is split into one chunk per day; a filter on that column skips whole chunks — partition pruning.

-- prunes: reads 7 days of the table
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-07'
 
-- may not prune: the engine can't know which partitions a subquery result hits
WHERE event_date IN (SELECT d FROM campaign_dates)

[CHECK: exactly which predicate forms each engine can prune on — dynamic pruning support varies.]

3. Aggregate before joining. Shrinks the join and removes fan-out in one move — Joins.

join 10M events to 50k users, then GROUP BY user   → join processes 10M rows
GROUP BY user in events first (→ 50k rows), then join → join processes 50k

4. EXISTS over join-plus-DISTINCT. “Customers who bought a gift card”: EXISTS stops at the first match per customer; a join finds every match, then DISTINCT sorts them away.

5. Keyset pagination, not deep OFFSET.

-- page 5,000: reads and throws away 100,000 rows to return 20
ORDER BY id LIMIT 20 OFFSET 100000
 
-- keyset: seeks straight to where the last page ended
WHERE id > :last_seen_id ORDER BY id LIMIT 20

Cost of OFFSET grows with page depth; keyset stays flat. The trade is that you can’t jump to page 5,000 directly.

6. UNION ALL, not UNION, when duplicates are impossible or wanted. UNION deduplicates, which is a sort or hash of the whole result.

7. Filter early inside CTEs and subqueries, rather than relying on the planner to push an outer filter in — usually it does, sometimes it can’t — Common Table Expressions.

8. LIMIT doesn’t reduce warehouse cost. It cuts the output, but a columnar engine typically scans the full columns first. Preview with the table-preview feature or a partition filter instead. [CHECK: holds for BigQuery on-demand; clustered tables can sometimes stop early.]

The worked cost

An events table: 2 billion rows, 180 columns, ~2 TB, partitioned by day, 500 days.

SELECT * , no date filter                        reads ~2 TB
SELECT user_id, event_name, event_time           3 of 180 columns → roughly 3/180 of it,
  (no filter)                                    if columns are similar width: ~33 GB
... WHERE event_date in the last 7 days          7/500 of that → ~0.5 GB

Four-thousandfold difference between the first and last, and the answer might be the same. Column widths aren’t equal in practice, so treat the middle step as an order of magnitude, not a figure — the engine’s dry-run estimate is the real number.

Before rewriting anything

  • Check the query count before the query. A page issuing 300 fast queries won’t be fixed by tuning one — N+1 Queries
  • Measure with the plan, not the stopwatch. Second runs hit cache and look fast — Query Planning
  • Correctness before speed. A rewrite that changes grain changes the number; compare row counts and totals before and after
  • Materialise what’s recomputed. A dashboard running the same heavy aggregation every load wants a pre-aggregated table refreshed on a schedule — Event Streams vs Aggregates

Graph View

Backlinks