Re: Disk-based hash aggregate's cost model

On Mon, Sep 07, 2020 at 01:55:28PM -0700, Jeff Davis wrote:
>On Sun, 2020-09-06 at 23:21 +0200, Tomas Vondra wrote:
>> I've tested the costing changes on the simplified TPC-H query, on two
>> different machines, and it seems like a clear improvement.
>
>Thank you. Committed.
>
>> So yeah, the patched costing is much closer to sort (from the point
>> of
>> this cost/duration metric), although for higher work_mem values
>> there's
>> still a clear gap where the hashing seems to be under-costed by a
>> factor
>> of ~2 or more.
>
>There seems to be a cliff right after 4MB. Perhaps lookup costs on a
>larger hash table?
>

I assume you mean higher costs due to hash table outgrowing some sort of
CPU cache (L2/L3), right? Good guess - the CPU has ~6MB cache, but no.
This seems to be merely due to costing, because the raw cost/duration
looks like this:

work_mem cost duration
---------------------------------
1MB 20627403 216861
2MB 15939722 178237
4MB 15939722 176296
8MB 11252041 160479
16MB 11252041 168304
32MB 11252041 179567
64MB 11252041 189410
256MB 11252041 204206

This is unpatched master, with the costing patch it looks similar except
that the cost is about 2x higher. On the SATA RAID machine, it looks
like this:

work_mem cost duration
-----------------------------------
1MB 108358461 1147269
2MB 77381688 1004895
4MB 77381688 994853
8MB 77381688 980071
16MB 46404915 930511
32MB 46404915 902167
64MB 46404915 908757
256MB 46404915 926862

So roughly the same - the cost drops to less than 50%, but the duration
really does not. This is what I referred to when I said "Not sure if we
need/should tweak the costing to reduce the effect of work_mem (on
hashagg)."

For sort this seems to behave a bit more nicely - the cost and duration
(with increasing work_mem) are correlated quite well, I think.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services