Reading 3 columns instead of 10

saas-subscriptions has 10 columns and 2,500 rows across 947 days. A query that needs three of those columns for one day is doing a lot less work than one that does not say so.

Naming 3 of 10 columns cuts the scan to roughly 30.0%. Filtering to one of 947 days cuts it to 0.1%. Together — and they multiply — the query reads about 0.03% of what select * would.

-- reads every column, every day
select * from saas_subscriptions;

-- reads 3 columns, one day
select account_id, signed_up_on, plan
from saas_subscriptions
where signed_up_on >= date '2023-06-01'
  and signed_up_on <  date '2023-06-02';

The second version is not a micro-optimisation. On a real warehouse those two queries differ by three orders of magnitude in cost, and the expensive one is the one that is easier to type.

The trap worth knowing: wrapping the partition column in a function — date(signed_up_on) = '2023-06-01' — usually defeats pruning, because the engine can no longer reason about the raw column. Same result, full scan, no warning. Compare against a range on the bare column instead.

Read the bytes-scanned line in the plan before optimising anything else. It is usually the entire answer. Why your query costs what it costs has the rest.