Writing SQL people can read, and reading the plan

There are two audiences for a query: the engine, and the next human. The engine is forgiving — modern optimisers rewrite most of what you give them. The human is not.

CTEs are the unit of thought#

Write a query as a sequence of named steps, each doing one thing.

sql

with recent_orders as (
    select *
    from fct_order_lines
    where ordered_at >= current_date - interval '90 days'
),

customer_totals as (
    select customer_id,
           sum(revenue_usd)         as revenue_90d,
           count(distinct order_id) as orders_90d
    from recent_orders
    group by 1
),

segmented as (
    select *,
           case when revenue_90d >= 2000 then 'high'
                when revenue_90d >=  400 then 'mid'
                else 'low'
           end as value_segment
    from customer_totals
)

select value_segment,
       count(*)                as customers,
       round(avg(revenue_90d)) as avg_revenue,
       round(avg(orders_90d), 2) as avg_orders
from segmented
group by 1
order by avg_revenue desc;

Each CTE has a name that says what it is, and the final select is short. Compare with the same logic as nested subqueries and the difference is not stylistic — it is the difference between a colleague understanding it in thirty seconds and not understanding it at all.

The old advice that "CTEs are optimisation fences" is largely obsolete — Postgres inlines them since 12, and Snowflake, BigQuery and DuckDB always have. Write for clarity. If profiling shows a materialisation problem, fix it then, with evidence.

Formatting conventions that pay for themselves#

Keywords lower case, one clause per line.
select columns one per line, trailing commas, aligned aliases.
Join conditions on their own line, on aligned under join.
group by 1, 2 for short groupings; name the columns when there are more than three.
Comment the why, never the what. -- excludes internal test accounts, see metric definition earns its place. -- group by country does not.

Reading a plan#

explain output is intimidating because it shows everything. Look for four things and ignore the rest.

1. Bytes scanned. If a query filtered to one day still scans the whole table, partition pruning failed. Almost always because the partition column was wrapped in a function or compared to a different type.

2. The join strategy. Broadcast (or hash join with a small build side) ships the small table to every worker: fast. Shuffle (or sort-merge) redistributes both sides across the network: slow, and correct only when both sides are genuinely large. A shuffle on a small dimension means the optimiser's statistics are stale — refresh them.

3. Rows in versus rows out. Walk the plan bottom-up and find where the row count explodes. A join taking 100k rows to 40M is a fan-out, which means a grain bug, which means your numbers are wrong regardless of speed.

4. Spill to disk. A sort or aggregation that exceeds memory writes to disk and gets dramatically slower. Usually caused by a distinct or an order by on more data than necessary.

sql

explain analyze
select ...;

The analyze matters: without it you get estimates, and the interesting failures are exactly where estimates and reality diverge.

The usual suspects, and their fixes#

Symptom	Likely cause	Fix
Scans whole table despite a date filter	Function on the partition column	Transform the constant instead
Slow and returns too many rows	Join fan-out	Fix the grain; do not add `distinct`
`distinct` on millions of rows	Deduplicating a bad join	Aggregate the many side first
`order by` on a huge intermediate	Sorting before filtering	Filter first, sort last
Correlated subquery in select list	Executes per row	Rewrite as a join or window function
`count(distinct x)` very slow	Exact distinct needs a full shuffle	Use approximate distinct if an estimate suffices

The correlated subquery trap#

sql

-- Runs the inner query once per outer row.
select o.order_id,
       (select count(*) from order_events e where e.order_id = o.order_id) as events
from orders o;

-- One pass.
select o.order_id, coalesce(e.events, 0) as events
from orders o
left join (select order_id, count(*) as events from order_events group by 1) e
  on e.order_id = o.order_id;

Good optimisers de-correlate the first form. Not all do, and the second is clearer about what it costs.

Cost hygiene for scheduled queries#

An expensive interactive query is annoying once. An expensive scheduled query is a monthly bill.

Anything a dashboard runs should hit a pre-aggregated table, not raw facts.
Set a byte limit on scheduled jobs where your platform allows it. A query that suddenly scans 40× more than usual should fail, not succeed expensively.
Materialise anything computed more than a handful of times a day.
Review the top ten queries by spend monthly. It is a fifteen-minute meeting that regularly finds a dashboard nobody has opened since March.