Date: Fri, 30 Sep 2022 09:09:30 +0200
Subject: Re: disfavoring unparameterized nested loops
From: Benjamin Coutu <ben.coutu@zeyos.com>
To: Tom Lane <tgl@sss.pgh.pa.us>,
	Bruce Momjian <bruce@momjian.us>
Cc: Peter Geoghegan <pg@bowt.ie>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Robert Haas <robertmhaas@gmail.com>,
	David Rowley <dgrowleyml@gmail.com>
Message-ID: <96dbbf5d33fa97dc4c1f@zeyos.com>
In-Reply-To: <482086.1664495178@sss.pgh.pa.us>
References: <482086.1664495178@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/96dbbf5d33fa97dc4c1f%40zeyos.com>
Precedence: bulk

> Agreed, but dealing with uncertainty in those numbers is an enormous
> task if you want to do it right.  "Doing it right", IMV, would start
> out by extending all the selectivity estimation functions to include
> error bars; then we could have error bars on rowcount estimates and
> then costs; then we could start adding policies about avoiding plans
> with too large a possible upper-bound cost.  Trying to add such
> policy with no data to go on is not going to work well.=0A=0AError bars w=
ould be fantastic, no question. But that would make things very complex.=0A=
A lot of judgment calls would be necessary for the policy behind upper-boun=
d pruning, picking up on Peter's comment about "conviction multiplier of co=
nviction multiplier" ;)=0AAlso, the math in deriving those bounds based on =
the stats and how they propagate up the join tree doesn't seem trivial eith=
er.=0A
> I think Peter's point is that a quick-n-dirty patch is likely to make
> as many cases worse as it makes better.  That's certainly my opinion
> about the topic.=0A=0AAs in my reply to Peter, I think the join level/dep=
th metric is a simple but principled way of dealing with it, given the refe=
renced research.=0AIn the first step, we'd use this merely to be more risk-=
averse towards nested loop joins as we climb up the join tree - we are not =
fiddling with the cost model itself, nor the join ordering, just when it co=
mes to considering that particular join algorithm. Later this could be expa=
nded to be more broadly scoped.=0A=0APlease not give up on a simple way to =
reap most of the fruits just yet.