Clemens Eisserer <linuxhippy@xxxxxxxxx> writes: > running postgresql 15.5 I was recently surpised postgresql didn't > perform an optimization which I thought would be easy to apply. It is not. > running the following query results in a full sort (caused by lead > over order by) as the ts > '2024-01-04' selection doesn't seem to be > applied to the CTE but only later: > with cte as (select ts, lead(ts, 1) over (order by ts) as ts2 from smartmeter) > select ts, ts2 from cte where ts > '2024-01-04' and extract(epoch > from ts2) - extract(epoch from ts) > 9; The ts restriction is not pushed down because of the rules in allpaths.c: * 4. If the subquery has any window functions, we must not push down quals * that reference any output columns that are not listed in all the subquery's * window PARTITION BY clauses. We can push down quals that use only * partitioning columns because they should succeed or fail identically for * every row of any one window partition, and totally excluding some * partitions will not change a window function's results for remaining * partitions. (Again, this also requires nonvolatile quals, but * subquery_is_pushdown_safe handles that.) To conclude that it'd be safe with this particular window function requires deep knowledge of that function's semantics, which the planner has not got. regards, tom lane
