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
Date: Sun, 07 Jan 2024 11:55:51 -0500
Message-ID: <1606644.1704646551@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFvQSYSWH2mJbnh0A16TXppm71PsYF1E9pnTw7Vi751hgpKufg@mail.gmail.com>
References: <CAFvQSYSWH2mJbnh0A16TXppm71PsYF1E9pnTw7Vi751hgpKufg@mail.gmail.com>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, linuxhippy@gmail.com
Subject: Re: Selection not "pushed down into" CTE
In-Reply-To: <1606644.1704646551@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox