Re: Selection not "pushed down into" CTE

Clemens Eisserer <linuxhippy(at)gmail(dot)com> 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