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: Fri, 12 May 2023 13:49:07 -0400
Message-ID: <1539384.1683913747@sss.pgh.pa.us> (raw)
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>

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





view thread (46+ messages)  latest in thread

Message-ID: <1539384.1683913747@sss.pgh.pa.us>
Permalink:  ../1539384.1683913747@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1539384.1683913747@sss.pgh.pa.us

 · 

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: <1539384.1683913747@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