pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17721: A completely unused CTE negatively affect Query Plan
6+ messages / 4 participants
[nested] [flat]
* BUG #17721: A completely unused CTE negatively affect Query Plan
@ 2022-12-14 17:41 PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2022-12-14 17:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: nh.dev@sturdy.ai
The following bug has been logged on the website:
Bug reference: 17721
Logged by: Nathaniel Hazelton
Email address: nh.dev@sturdy.ai
PostgreSQL version: 15.1
Operating system: Docker
Description:
I've boiled down an issue we have in production to a simple query that can
demonstrate it. I've run this on 13,14 and 15 locally in a docker container
with the same results. If a CTE that is completely unexecuted exists in a
subquery (or view in our production case) it affects the query plan VERY
negatively. The first explain shows a sequential scan, where the second
explain shows an index scan, just by the existence of the (obviously)
unexecuted CTE.
In this example, the plans might not differ much in performance. However,
in our production system, the parallel to this is VERY costly. In fresh
local docker Postgres, the random page cost must be set to 1 to reflect this
issue. With our production data, the page cost is the default 4, and
reflects the issue.
drop table if exists conv, conv_acc;
select setseed(0);
create temp table conv as (select id, 'meta' as meta from generate_series
(1, 1000) id);
create temp table conv_acc as (
select conv.id, acc_id
from conv
left join lateral (
select conv.id, floor(random()*1000) as acc_id
from generate_series (1, 4) limit floor(random()*4+1)::int
) acc on true
);
create index conv_acc_id_index
on conv_acc(id);
analyze conv; analyze conv_acc;
set random_page_cost to 1;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select * from
(select id from conv limit 10) limitconv
left join (
with someccte as materialized (select 1/0)
select id
from conv_acc ca
) vca
on vca.id = limitconv.id;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select * from
(select id from conv limit 10) limitconv
left join (
--with someccte as materialized (select 1/0)
select id
from conv_acc ca
) vca
on vca.id = limitconv.id;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17721: A completely unused CTE negatively affect Query Plan
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
@ 2022-12-14 18:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-14 22:14 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Nathaniel Hazelton <nathaniel@sturdyai.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-12-14 18:25 UTC (permalink / raw)
To: nh.dev@sturdy.ai; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I've boiled down an issue we have in production to a simple query that can
> demonstrate it. I've run this on 13,14 and 15 locally in a docker container
> with the same results. If a CTE that is completely unexecuted exists in a
> subquery (or view in our production case) it affects the query plan VERY
> negatively. The first explain shows a sequential scan, where the second
> explain shows an index scan, just by the existence of the (obviously)
> unexecuted CTE.
The presence of the CTE prevents flattening of the subquery.
If you don't like it, don't attach the CTE right there.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17721: A completely unused CTE negatively affect Query Plan
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-12-14 22:14 ` Nathaniel Hazelton <nathaniel@sturdyai.com>
2022-12-14 22:47 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Nathaniel Hazelton @ 2022-12-14 22:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
The CTE in that location was emulating a view in which we have a CTE - which responds the same way. Does this mean CTEs can never be used in views that could be flattened without affecting performance? I'm able to find any mention of this. The only mention of CTEs being optimization fences goes back when all CTEs were materialized.
Is there some way to know when it is safe or not to use a CTE in a view / subquery? It would be great to have some documentation on that.
On 12/14/22, 11:25 AM, "Tom Lane" <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
PG Bug reporting form <noreply@postgresql.org <mailto:noreply@postgresql.org>> writes:
> I've boiled down an issue we have in production to a simple query that can
> demonstrate it. I've run this on 13,14 and 15 locally in a docker container
> with the same results. If a CTE that is completely unexecuted exists in a
> subquery (or view in our production case) it affects the query plan VERY
> negatively. The first explain shows a sequential scan, where the second
> explain shows an index scan, just by the existence of the (obviously)
> unexecuted CTE.
The presence of the CTE prevents flattening of the subquery.
If you don't like it, don't attach the CTE right there.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17721: A completely unused CTE negatively affect Query Plan
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
2022-12-14 22:14 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Nathaniel Hazelton <nathaniel@sturdyai.com>
@ 2022-12-14 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-15 05:45 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-12-14 22:47 UTC (permalink / raw)
To: Nathaniel Hazelton <nathaniel@sturdyai.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Nathaniel Hazelton <nathaniel@sturdyai.com> writes:
> The CTE in that location was emulating a view in which we have a CTE -
> which responds the same way. Does this mean CTEs can never be used in
> views that could be flattened without affecting performance?
You've got a view with an unused CTE? Why?
But at any rate, yes, the presence of a CTE is an optimization
fence: we can't flatten the subquery containing it without losing
the query level at which the CTE should be evaluated. Perhaps there
are special cases where that could be relaxed, but nobody's tried
very hard.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17721: A completely unused CTE negatively affect Query Plan
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
2022-12-14 22:14 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Nathaniel Hazelton <nathaniel@sturdyai.com>
2022-12-14 22:47 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-12-15 05:45 ` David Rowley <dgrowleyml@gmail.com>
2022-12-15 16:23 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Nathaniel Hazelton <nathaniel@sturdyai.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2022-12-15 05:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Nathaniel Hazelton <nathaniel@sturdyai.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thu, 15 Dec 2022 at 11:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Nathaniel Hazelton <nathaniel@sturdyai.com> writes:
> > The CTE in that location was emulating a view in which we have a CTE -
> > which responds the same way. Does this mean CTEs can never be used in
> > views that could be flattened without affecting performance?
>
> You've got a view with an unused CTE? Why?
hmm, but the CTE isn't unused. It's just that we don't push quals
down due to the "materialized". Perhaps the view only contains the
CTE? Strange way to write a view... Perhaps there was some reason to
materialise it that's not obvious to us.
> But at any rate, yes, the presence of a CTE is an optimization
> fence: we can't flatten the subquery containing it without losing
> the query level at which the CTE should be evaluated. Perhaps there
> are special cases where that could be relaxed, but nobody's tried
> very hard.
Just to expand on that a little. Prior to v12, CTEs could never have
quals pushed down into them. That was relaxed in v12 and the original
behaviour can be obtained using "materialized". Without that, the
planner is more free to perform its typical optimisations such as
pulling up subqueries or pushing quals down into subqueries.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17721: A completely unused CTE negatively affect Query Plan
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
2022-12-14 22:14 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Nathaniel Hazelton <nathaniel@sturdyai.com>
2022-12-14 22:47 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan Tom Lane <tgl@sss.pgh.pa.us>
2022-12-15 05:45 ` Re: BUG #17721: A completely unused CTE negatively affect Query Plan David Rowley <dgrowleyml@gmail.com>
@ 2022-12-15 16:23 ` Nathaniel Hazelton <nathaniel@sturdyai.com>
0 siblings, 0 replies; 6+ messages in thread
From: Nathaniel Hazelton @ 2022-12-15 16:23 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 12/14/22, 10:46 PM, "David Rowley" <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com>> wrote:
On Thu, 15 Dec 2022 at 11:47, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Nathaniel Hazelton <nathaniel@sturdyai.com <mailto:nathaniel@sturdyai.com>> writes:
> > The CTE in that location was emulating a view in which we have a CTE -
> > which responds the same way. Does this mean CTEs can never be used in
> > views that could be flattened without affecting performance?
>
> You've got a view with an unused CTE? Why?
hmm, but the CTE isn't unused. It's just that we don't push quals
down due to the "materialized". Perhaps the view only contains the
CTE? Strange way to write a view... Perhaps there was some reason to
materialise it that's not obvious to us.
NH: Oops. I posted a slightly incorrect version of what I was trying to show. Remove the 'materialized', and it acts the same way. Given that it selects 1/0, it's clearly not executed, materialized or not. BUT it does affect the query plan, materialized OR NOT, which is the part I couldn't understand. We do not have an unused CTE in our view. I was just attempting to boil what I saw as a bug down to its minimal example.
> But at any rate, yes, the presence of a CTE is an optimization
> fence: we can't flatten the subquery containing it without losing
> the query level at which the CTE should be evaluated. Perhaps there
> are special cases where that could be relaxed, but nobody's tried
> very hard.
Just to expand on that a little. Prior to v12, CTEs could never have
quals pushed down into them. That was relaxed in v12 and the original
behaviour can be obtained using "materialized". Without that, the
planner is more free to perform its typical optimisations such as
pulling up subqueries or pushing quals down into subqueries.
NH; We do use a non-materialized CTE in the view, because we want to use a CTE for what it's for - organizing our code. Given that it's not materialized, we thought there should be no ill effect. Remove the materialized from the original post, and you will see it DOES affect the query plan.
NH: I will post a more complete example with views today.
Nathaniel
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-12-15 16:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-14 17:41 BUG #17721: A completely unused CTE negatively affect Query Plan PG Bug reporting form <noreply@postgresql.org>
2022-12-14 18:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-14 22:14 ` Nathaniel Hazelton <nathaniel@sturdyai.com>
2022-12-14 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-15 05:45 ` David Rowley <dgrowleyml@gmail.com>
2022-12-15 16:23 ` Nathaniel Hazelton <nathaniel@sturdyai.com>
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