Wrapping a column in a function stops the engine from using the column's index or its partition and min/max statistics in the usual way. This kind of filter is called non-sargable (not "search-argument-able"). A plain range comparison on the bare column is sargable.
The two versions
-- Slow: function applied to every row's created_at WHERE DATE(created_at) = '2025-01-01' -- Better: the column is compared to constants, as a half-open range WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'
With an index on created_at, the second form jumps straight to the start of the range in the B-tree. The first has to compute DATE(created_at) for every row, because the index is sorted by created_at, not by DATE(created_at).
In warehouses it is about pruning
Warehouses do not use B-tree indexes. They store a min and max per partition or micro-partition, and skip blocks that cannot match. A filter directly on the partition column against constants lets the engine read the metadata and skip most of the table. Wrapping the column in functions can make it unable to prove a block is irrelevant, depending on the function and the engine. In BigQuery you should filter on the partitioning column itself, with constant values, and then check the dry-run "bytes processed" to confirm that pruning happened. Some engines are smart enough to rewrite simple cases like DATE(ts) = ..., but do not count on it.
Other common offenders
WHERE LOWER(email) = 'a@x.com' -- function on column WHERE amount + 10 > 100 -- arithmetic on column (write amount > 90) WHERE CAST(customer_id AS VARCHAR) = '7' -- cast on the column side WHERE name LIKE '%son' -- leading wildcard
For a case like LOWER(email), Postgres lets you create an expression index on LOWER(email). Otherwise, store a normalised column.
Use a half-open range (>= start, < next start) instead of BETWEEN for timestamps. BETWEEN '2025-01-01' AND '2025-01-01 23:59:59' quietly misses the last second.