Re: Disk-based hash aggregate's cost model - Mailing list pgsql-hackers

Hi,

I've tested the costing changes on the simplified TPC-H query, on two
different machines, and it seems like a clear improvement.

This is using the same cost/duration measure, which I think is pretty
neat way to look at this. Sure, it's imperfect (depends on which cost
and durations you actually take etc.), but it makes the comparisons
easier and for simple queries it does not matter that much.

The costing clearly depends on parameters like random_page_cost and how
it matches the hardware, but for the machine with SSD and default
random_page_cost the effect looks like this:

      work_mem    sort    master    patched
     ---------------------------------------
           1MB     249        95        215
           2MB     256        89        187
           4MB     233        90        192
           8MB     227        70        124
          16MB     245        67        118
          32MB     261        63        111
          64MB     256        59        104
         256MB     266        55        102

and with random_page_cost reduced to 1.5 it looks like this:

      work_mem       sort    master    patched
     ------------------------------------------
           1MB        221        63        150
           2MB        227        64        133
           4MB        214        65        137
           8MB        214        57         95
          16MB        232        53         90
          32MB        247        50         85
          64MB        249        47         80
         256MB        258        46         77

And on a machine with SATA RAID storage it looks like this:

      work_mem       sort    master   patched
     -----------------------------------------
           1MB        102        41        94
           2MB        101        34        77
           4MB         99        35        78
           8MB         98        35        79
          16MB         98        25        50
          32MB        106        26        51
          64MB        106        26        51
         256MB        105        29        50

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.

I think this is simply showing that sort may the effect of increasing
work_mem is much more pronounced for sort/groupagg compared to hashagg.
For example on the SDD machine the duration changes like this:

     work_mem    hashagg    groupagg
    ---------------------------------
          1MB        217         201
          2MB        178         195
          4MB        176         186
          8MB        160         176
         16MB        168         163
         32MB        180         153
         64MB        189         143
        256MB        204         138

and the SATA RAID storage seems to behave in a similar way (although the
difference is smaller).

So in general I think this costing change is reasonable. It might not go
far enough, but it certainly makes it probably makes it easier to tweak
the rest by changing random_page_cost etc. Not sure if we need/should
tweak the costing to reduce the effect of work_mem (on hashagg).

regards

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