From: Andrei Lepikhov <lepihov@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
To: 萧鸿骏 <23031212454@stu.xidian.edu.cn>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18927: Poor query plan - HashAggregate is more than twice as fast but at a higher cost
Date: Thu, 15 May 2025 11:15:56 +0200
Message-ID: <52f073ae-7fb0-40a1-9a29-e5a68199baf1@gmail.com> (raw)
In-Reply-To: <CAFiTN-tBLWU8GN1x-oFU3YDXdrE=wBFjCcboN_ETOsvSUnSRaQ@mail.gmail.com>
References: <18927-95043e0792441d50@postgresql.org>
<21222aa8.36d.196cde41666.Coremail.23031212454@stu.xidian.edu.cn>
<CAFiTN-tBLWU8GN1x-oFU3YDXdrE=wBFjCcboN_ETOsvSUnSRaQ@mail.gmail.com>
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
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: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, dilipbalaut@gmail.com, 23031212454@stu.xidian.edu.cn, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18927: Poor query plan - HashAggregate is more than twice as fast but at a higher cost
In-Reply-To: <52f073ae-7fb0-40a1-9a29-e5a68199baf1@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox