trades(ts timestamptz, symbol text, px numeric) has an index on ts and 1 billion rows. WHERE date(ts) = '2026-03-02' does a full scan while WHERE ts >= '2026-03-02' AND ts < '2026-03-03' uses the index. Explain both, and state the selectivity above which ignoring the index is correct anyway.

trades(ts timestamptz, symbol text, px numeric) has an index on ts and 1 billion rows. WHERE date(ts) = '2026-03-02' does a full scan while WHERE ts >= '2026-03-02' AND ts < '2026-03-03' uses the index. Explain both, and state the selectivity above which ignoring the index is correct anyway.

Approach: Ask what value the index is sorted on and whether the predicate can be rewritten as a contiguous range over that value, then compare scattered page fetches against a sequential scan.

date(ts) is not a sargable predicate, so the planner cannot turn it into a range over the index and falls back to a scan, while the second form is a range over the sort key and drives an index range scan. The index is sorted on the raw timestamp, and date(ts) is opaque to the planner, so it has no way to know that the set of ts satisfying it is contiguous, and no statistics on the function output either, so its selectivity guess falls back to a default. The fix is the rewritten range, or an expression index on date(ts) that stores the function output and makes it sargable at the price of a second index maintained on every write. Ignoring the index is correct above a selectivity crossover of roughly 5 to 10 percent of the table for a non covering index. The reason is the random page cost: each matching row costs one scattered heap fetch, and once the number of fetches approaches the number of pages in the table, a sequential scan reading every page in order is cheaper, because sequential io runs ten to a hundred times faster per page. Clustering the table on ts pushes that crossover much higher, since matching rows then share pages.

Follow-up: What changes if the session time zone differs from the one the expression index was built under?

Key concepts: sargable predicate, index range scan, selectivity crossover, random page cost.