Re: BUG #18927: Poor query plan - HashAggregate is more than twice as fast but at a higher cost

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