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

> Right. But that seems fraught with difficulty. I suspect that the
> costs that the planner attributes to each plan often aren't very
> reliable in any absolute sense, even when everything is working very
> well by every available metric. Even a very noisy cost model with
> somewhat inaccurate selectivity estimates will often pick the cheapest
> plan, or close enough.=0A=0ASure, the absolute cost of a complex plan wil=
l always be inaccurate at best.=0AMy point is that we can be very confident=
 in the cardinalities of base tables. As the paper states in "3.1. Estimate=
s for Base Tables":=0A=0A"The median q-error is close to the optimal value =
of 1 for all systems,=0Aindicating that the majority of all selections are =
estimated correctly."=0A=0AThanks to the statistics will practically never =
be off by an order of magnitude when estimating base table cardinalities.=
=0A=0AThe paper also clearly shows (and that certainly coincides with my ex=
perience) that those cardinality underestimations grow exponentially as the=
y propagate up the join tree.=0A=0AGiven the research I'd stipulate that at=
 any given level of the join tree, the current depth is a reasonable indica=
tor of underestimation. Taking that into account (even if only to mitigate =
nested loops on higher levels) is IMV a principled approach, and not necess=
eraly a hack.=0A=0AObviously having something like error bars as proposed b=
y Tom would be even better and perhaps more general, but that is on a whole=
 different level in terms of complexity and I certainly have no idea how we=
 would easily get there.