Why does my query cost so much?

A question that came in more than once, so the answer goes here.

Why does my query cost so much?

Almost always because it read data it did not need.

Three usual suspects, in order of frequency. select * on a columnar table reads every column, and you probably wanted four of thirty. No filter on the partition column, so the engine scans the whole history instead of one day. And a join that fans out, producing intermediate rows that then have to be aggregated away.

-- reads 30 columns × all history
select * from events where user_id = 42;

-- reads 3 columns × one day
select user_id, event_type, occurred_at
from events
where event_date = date '2024-03-01'
  and user_id = 42;

Read the query plan before optimising. The bytes-scanned line is usually the whole answer, and it is usually a filter that was not pushed down because it was applied after a function call on the partition column.

The order to work in: bytes scanned first, because it is usually 90% of the bill and the fix is a filter. Then the join strategy, because a fan-out produces intermediate rows that get aggregated away and you pay for all of them. Then everything else, which is mostly noise by comparison.

What almost never helps: rewriting a subquery as a CTE, reordering the select list, or adding hints. Those change the text and not the work.