public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Attila Soki <[email protected]>
To: [email protected]
Cc: Laurenz Albe <[email protected]>
Cc: Tom Lane <[email protected]>
Subject: Re: unstable query plan on pg 16,17,18
Date: Tue, 24 Feb 2026 10:11:15 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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

view thread (25+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: unstable query plan on pg 16,17,18
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox