From: Tom Lane <tgl@sss.pgh.pa.us>
To: Clemens Eisserer <linuxhippy@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Selection not "pushed down into" CTE
In-reply-to: 
 <CAFvQSYSWH2mJbnh0A16TXppm71PsYF1E9pnTw7Vi751hgpKufg@mail.gmail.com>
References: 
 <CAFvQSYSWH2mJbnh0A16TXppm71PsYF1E9pnTw7Vi751hgpKufg@mail.gmail.com>
Comments: In-reply-to Clemens Eisserer <linuxhippy@gmail.com>
	message dated "Sun, 07 Jan 2024 08:37:17 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1606643.1704646551.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 07 Jan 2024 11:55:51 -0500
Message-ID: <1606644.1704646551@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1606644.1704646551%40sss.pgh.pa.us>
Precedence: bulk

Clemens Eisserer <linuxhippy@gmail.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 smart=
meter)
> 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 qual=
s
 * that reference any output columns that are not listed in all the subque=
ry's
 * window PARTITION BY clauses.  We can push down quals that use only
 * partitioning columns because they should succeed or fail identically fo=
r
 * 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