Why your query costs what it costs

Analysts are told to "write efficient SQL" without ever being told what the machine does with it. Most of the performance difference in a warehouse is not clever SQL — it is how much data the engine had to read off disk. Understand that and the optimisations become obvious rather than superstitious.

Row storage versus column storage#

A transactional database stores a row together:

[1|Ana|Berlin|4200][2|Marcus|Lagos|3100][3|Priya|Chennai|5600]

Fetching one whole customer is one read. Excellent for an application.

A warehouse stores each column together:

ids:     [1|2|3]
names:   [Ana|Marcus|Priya]
cities:  [Berlin|Lagos|Chennai]
revenue: [4200|3100|5600]

Now select sum(revenue) from customers reads only the revenue block. On a table with sixty columns, that is roughly a sixtieth of the I/O.

Two consequences fall straight out:

select * is genuinely expensive in a way it never was in Postgres. On a warehouse it is not stylistic sloppiness, it is asking to read every column block on disk.

Compression is dramatically better, because a column holds one type with repeated values. A country column with eight distinct values across 50 million rows compresses to almost nothing via dictionary encoding. This is also why adding a low-cardinality column costs far less than intuition suggests, and a high-cardinality one costs far more.

Partition pruning: the big one#

Data is split into files by a partition key, usually a date. The engine reads a table's metadata, sees which files could possibly contain matching rows, and skips the rest entirely.

-- Reads one day's files.
select count(*) from events where event_date = '2026-03-14';

-- Reads every file, ever. Same logical filter.
select count(*) from events where cast(event_date as string) like '2026-03-14%';

The second query wraps the partition column in a function, which defeats pruning: the engine cannot know the function's output without opening the file. Never transform the partition column in a WHERE clause. Transform the constant instead.

Predicate pushdown and file statistics#

Parquet files store min/max statistics per column per row group. If a row group's revenue ranges 0–50 and you filter revenue > 1000, the engine skips that group without decompressing it.

This only works if the data is sorted or at least clustered on the column you filter. Randomly ordered data means every row group's min/max spans the full range, and nothing can be skipped. This is why cluster by / sortkey / Z-ordering exist: they make statistics selective.

The small files problem#

Ten thousand files of 40KB each is far slower than forty files of 10MB, even though the bytes are identical. Every file costs a metadata lookup, an open, and a scheduling decision. Streaming pipelines that write a file per micro-batch create this by default.

The fix is compaction: periodically rewrite small files into larger ones. Target roughly 128MB–1GB per file. Table formats like Iceberg and Delta will do it for you, and if you are on plain Parquet in object storage, it is a scheduled job you will need to write.

What to actually do#

In descending order of impact:

1. Name your columns. Never select * on a wide table. The single biggest win.
2. Filter on the partition column, unwrapped. The second biggest.
3. Filter before you join, not after. A CTE that narrows to one month before joining a billion-row fact table is a completely different query from one that joins first.
4. Aggregate before you join where the grain allows it. Joining two pre-aggregated results is often orders of magnitude cheaper than joining raw and aggregating after.
5. Avoid distinct as a duplicate-fix. It is a full sort. If you need distinct because a join fanned out, fix the join — distinct hides the bug and pays for it forever.
6. Materialise anything queried more than a few times a day. Compute is charged per query; storage is nearly free. A table that ten dashboards recompute hourly should be a table.

Read the plan#

Every engine has explain. You do not need to understand all of it. You need three numbers:

• Bytes scanned. The cost driver. If it equals the whole table, pruning failed.
• The join type. A broadcast join ships the small side everywhere and is fast. A shuffle join redistributes both sides across the network and is not. If a small table is being shuffled, your engine misjudged its size.
• Rows in versus rows out at each step. Look for the step where a hundred thousand rows become eighty million. That is your fan-out, and it is a bug.

explain analyze
select c.country, sum(f.revenue_usd)
from fct_order_lines f
join dim_customers c on c.customer_key = f.customer_key
where f.order_date >= '2026-01-01'
group by 1;