From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benoit Ryder <b.ryder@ateme.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18429: Inconsistent results on similar queries with join lateral
Date: Fri, 12 Apr 2024 11:50:02 -0400
Message-ID: <450117.1712937002@sss.pgh.pa.us> (raw)
In-Reply-To: <MR1P264MB1971C01DA74122691EBBEE9FF9042@MR1P264MB1971.FRAP264.PROD.OUTLOOK.COM>
References: <18429-8982d4a348cc86c6@postgresql.org>
<CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
<441100.1712933417@sss.pgh.pa.us>
<MR1P264MB1971C01DA74122691EBBEE9FF9042@MR1P264MB1971.FRAP264.PROD.OUTLOOK.COM>
Benoit Ryder <b.ryder@ateme.com> writes:
> Would you know a way to avoid this bug? I could probably tweak the query
> until the result is looking good, but I would prefer to make sure the
> bug won't reappear if the query is used with different data or context.
I'm not seeing a bulletproof way offhand, other than "update to v16".
Disabling nestloop plans fixes your Q4 in the back branches, but not
Q1 (because there's no opportunity for a hash or merge join in Q1).
It looks like the problem is that the old join_clause_is_movable
logic is incorrectly deciding that the WHERE condition can be
pushed down to the sub-select relation. So we should be able to
fix it there, but I'm not sure how messy that will be or whether
we'll lose the ability to generate some correct plans. It's hard
to justify putting a huge amount of work into old branches though.
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, b.ryder@ateme.com, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18429: Inconsistent results on similar queries with join lateral
In-Reply-To: <450117.1712937002@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