Re: unstable query plan on pg 16,17,18

On 23/2/26 21:25, Andrei Lepikhov wrote:
> On 23/2/26 18:03, Attila Soki wrote:
> So, let me discover a little more, but your PG14 explain could add more
> details here.
It seems much more interesting than just a trivial accumulation of cost
estimation errors. Look:

...
-> Hash (cost=86.59..86.59 rows=8 width=67)
(actual time=0.136..0.136 rows=44.56 loops=21798)
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) ...
-> Nested Loop (cost=1.12..86.59 rows=8 width=67)
(actual time=0.017..0.126 rows=44.56 loops=21798)
...

This hash table has been rescanned multiple times. And on each rescan,
it was rebuilt as well (the number of loops in the underlying Join was
also 21798). It is the first time I have seen such a query plan. And
discovering how rescan reckons in the cost model, this Hash table
rebuilding == subtree rescanning, you may find the following:

cost_rescan():

case T_HashJoin:
/*
* If it's a single-batch join, we don't need to rebuild the hash
* table during a rescan.
*/
if (((HashPath *) path)->num_batches == 1)
{
/* Startup cost is exactly the cost of hash table building */
*rescan_startup_cost = 0;
*rescan_total_cost = path->total_cost - path->startup_cost;
}
...

That means (if I read the code correctly) we don't take into account the
cost=86.59 of subtree rescanning and htab rebuilding at all!
So, it looks like a rare cost model bug.
To learn more, I still need your PG14 EXPLAIN. Can you also share your
SQL so we can understand which combination of SQL structures led to this
unusual query plan?

--
regards, Andrei Lepikhov,
pgEdge