pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)
Date: Tue, 16 May 2023 15:23:34 -0400
Message-ID: <2766442.1684265014@sss.pgh.pa.us> (raw )
In-Reply-To: <CAMbWs4-Kx3ZHUByn6D2ZUTeF4Fm7uudkzgHNguDutpV8tepxtg@mail.gmail.com >
References: <0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru >
<CAMbWs4_HJNuLv9HDReEBDrQrCPV-uqpVOecoJkurprnY+Do9Fg@mail.gmail.com >
<CAMbWs4_8EZU4DetHyZGm1CwCxUhBdWpRnA9dtM-Z82i=bVKA6A@mail.gmail.com >
<CAMbWs4_XcPHqc=A=36cxvJRhkjAgEeJtOFLOnBPEfQYGqnGOCw@mail.gmail.com >
<efb33bee-145d-95a8-1048-2e7b8f282e2c@postgrespro.ru >
<CAMbWs48FHw_tTXSbA-_MPaeu7S7JCt6Xs-gxwkx=pvLiwC6eTg@mail.gmail.com >
<c69bd07e-1175-10da-9ac0-4bfe59235ac2@postgrespro.ru >
<402433.1677524538@sss.pgh.pa.us >
<2663884e-769a-b298-55b0-014c5a1ee55c@postgrespro.ru >
<524303.1677538463@sss.pgh.pa.us >
<999171.1677611407@sss.pgh.pa.us >
<CAMbWs4-Pi5cM=V8B-g5Qfnr0t3EgAbhus0YNQN=XquJ8fNoatw@mail.gmail.com >
<CAMbWs4-Pmh9umsUbfs8Lay=k6wFrsuLdLJ7ci=Af6DqAwsN1Hg@mail.gmail.com >
<CA+TgmoZ9yQLYziHAbt_X3hpU8FKkPXGLV=OhoD-GejAUckQbrA@mail.gmail.com >
<CA+TgmoaeccFHN9ouXiSQmMJVh6uF-ANbbDjrwopQ+icYyzJSJw@mail.gmail.com >
<CAMbWs4_nudsuzd-bLLgu1WCzeV1pLv3SG1Ub8vo-ETJzvgc0Bg@mail.gmail.com >
<1526312.1683912310@sss.pgh.pa.us >
<CA! MbWs4-doWdYa3AJdw4wMAn7ApSSyRDQrWuFV1_xW5muY0mLJw@mail.gmail.com >
<2489296.1684195396@sss.pgh.pa.us >
<CAMbWs4-Kx3ZHUByn6D2ZUTeF4Fm7uudkzgHNguDutpV8tepxtg@mail.gmail.com >
Richard Guo <guofenglinux@gmail.com> writes:
> I doubt this is always right to add joins_below to all the vars
> belonging to sjinfo->syn_lefthand. What if the joins in joins_below
> cannot commute with each other? As a counterexample, consider the query
> below which causes assertion failure in search_indexed_tlist_for_var.
> The query is designed so that t1/t2 join cannot commute with t2/t3 join
> but can commute with t3/t4 join.
> explain (costs off)
> select * from t t1
> left join t t2 on true
> left join t t3 on true
> left join t t4 on t2.a = t3.a;
Hm. Actually, t1/t2 *can't* commute with the t4 join. You can re-order
the t2 and t3 joins per identity 2:
select * from t t1
left join t t3 on true
left join t t2 on true
left join t t4 on t2.a = t3.a;
but you're still stuck, identity 3 applies nowhere (because in either
case, the putative Pbc clause has also got a reference to A).
make_outerjoininfo is setting the t4 join's commute_below_l to include
t1/t2, but it seems to me that that indicates inadequate analysis in
make_outerjoininfo. Is there still a problem here if we get rid of
that overoptimistic conclusion? We should strive to do so even if
it's not fixing an observable problem, because in any case it's
causing the planner to waste time on unreachable join orders.
regards, tom lane
view thread (46+ messages) latest in thread
Message-ID: <2766442.1684265014@sss.pgh.pa.us>
Permalink: ../2766442.1684265014@sss.pgh.pa.us/
Also on: postgresql.org/message-id/2766442.1684265014@sss.pgh.pa.us
copy link · copy postgr.es
reply 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, guofenglinux@gmail.com, robertmhaas@gmail.com, a.lepikhov@postgrespro.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)
In-Reply-To: <2766442.1684265014@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