Message-ID: <52f073ae-7fb0-40a1-9a29-e5a68199baf1@gmail.com>
Date: Thu, 15 May 2025 11:15:56 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18927: Poor query plan - HashAggregate is more than twice as
 fast but at a higher cost
To: Dilip Kumar <dilipbalaut@gmail.com>, =?UTF-8?B?6JCn6bi/6aqP?=
 <23031212454@stu.xidian.edu.cn>
Cc: pgsql-bugs@lists.postgresql.org
References: <18927-95043e0792441d50@postgresql.org>
 <21222aa8.36d.196cde41666.Coremail.23031212454@stu.xidian.edu.cn>
 <CAFiTN-tBLWU8GN1x-oFU3YDXdrE=wBFjCcboN_ETOsvSUnSRaQ@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAFiTN-tBLWU8GN1x-oFU3YDXdrE=wBFjCcboN_ETOsvSUnSRaQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/52f073ae-7fb0-40a1-9a29-e5a68199baf1%40gmail.com>
Precedence: bulk

On 15/5/2025 10:45, Dilip Kumar wrote:
> However, during further investigation, I hadn't increased
> hash_mem_multiplier. Instead, I forced the use of hash aggregation by
> disabling other options with SET enable_incremental_sort = off; and
> SET enable_sort = off;. Even without adjusting hash_mem_multiplier,
> the HashAggregate was significantly cheaper than GroupAggregate.  Are
> these points that we are overestimating the cost of the hash
> aggregate?  Not sure, might need to dig further down, but one thing to
> note is that the planner cost shows a hash aggregate with 2x costlier
> than the group aggregate[1][2], whereas the actual execution shows
> that the hash aggregate is 4x faster than the group aggregate.
One more thing to consider: I see four grouping columns. The cost model 
of hash aggregate includes the number of columns. Sort-based grouping 
doesn't take it into account. In the case of full join, when many tuples 
are generated on the fly, it may impact somehow.

-- 
regards, Andrei Lepikhov