From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Robert Haas <robertmhaas@gmail.com>,
        Andrey Lepikhov <a.lepikhov@postgrespro.ru>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars
 outer-join aware)
In-reply-to: <1526312.1683912310@sss.pgh.pa.us>
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>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 12 May 2023 13:25:10 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1539383.1683913747.1@sss.pgh.pa.us>
Date: Fri, 12 May 2023 13:49:07 -0400
Message-ID: <1539384.1683913747@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1539384.1683913747%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> This is correct AFAICS, and it's slightly cheaper, so an optimistic
> conclusion would be that we're making a cost-based decision to use a plan
> shape that the old code could not find for some reason.  But these costs
> are pretty close, within the "fuzz factor", so this might be a random
> effect.  It might be interesting to see if inserting unequal amounts
> of data in the tables would drive the costs further apart, allowing
> us to say that this code really does beat v15 in terms of planning
> flexibility.

Hah, indeed so:

DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE;
CREATE TABLE t1 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t2 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t3 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t4 AS SELECT true AS x FROM generate_series(0,1000) x;
ANALYZE t1,t2,t3,t4;

EXPLAIN --(COSTS OFF)
select * from t1
  left join t2 on true
  left join t3 on t2.x
  left join t4 on t3.x;

v15 (and HEAD) find a plan of cost 180, this patch finds one
of cost 120.  This test case is pretty artificial of course;
can we come up with a more plausible query that we win on?

			regards, tom lane