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