Re: 9.5: Better memory accounting, towards memory-bounded HashAgg

On Tue, 2014-12-23 at 01:16 -0800, Jeff Davis wrote:
> New patch attached (rebased, as well).
>
> I also see your other message about adding regression testing. I'm
> hesitant to slow down the tests for everyone to run through this code
> path though. Should I add regression tests, and then remove them later
> after we're more comfortable that it works?

Attached are some tests I ran. First, generate the data sets with
hashagg_test_data.sql. Then, do (I used work_mem at default of 4MB):

set enable_hashagg=false;
\o /tmp/sort.out
\i /tmp/hashagg_test.sql
\o
set enable_hashagg=true;
\o /tmp/hash.out
\i /tmp/hashagg_test.sql

and then diff'd the output to make sure the results are the same (except
the plans, of course). The script loads the results into a temp table,
then sorts it before outputting, to make the test order-independent. I
didn't just add an ORDER BY, because that would change the plan and it
would never use hashagg.

I think that has fairly good coverage of the hashagg code. I used 3
different input data sets, byval and byref types (for group key and
args), and a group aggregate query as well as DISTINCT. Let me know if I
missed something.

I also did some performance comparisons between disk-based sort+group
and disk-based hashagg. The results are quite favorable for hashagg
given the data sets I provided. Simply create the data using
hashagg_test_data.sql (if not already done), set the work_mem to the
value you want to test, and run hashagg_test_perf.sql. It uses EXPLAIN
ANALYZE for the timings.

singleton: 10M groups of 1
even: 1M groups of 10
skew: wildly different group sizes; see data script

q1: group aggregate query
q2: distinct query

The total memory requirements for the test to run without going to disk
ranges from about 100MB (for "even") to about 1GB (for "singleton").
Regardless of work_mem, these all fit in memory on my machine, so they
aren't *really* going to disk. Also note that, because of how the memory
blocks are allocated, and that hashagg waits until memory is exceeded,
then hashagg might use about double work_mem when work_mem is small (the
effect is not important at higher values).

work_mem='1MB':
sort+group (s) hashagg (s)
singleton q1 12 10
singleton q2 8 7
even q1 14 7
even q2 10 5
skew q1 22 6
skew q2 16 4

work_mem='4MB':
sort+group (s) hashagg (s)
singleton q1 12 11
singleton q2 8 6
even q1 12 7
even q2 9 5
skew q1 19 6
skew q2 13 3

work_mem='16MB':
sort+group (s) hashagg (s)
singleton q1 12 11
singleton q2 8 7
even q1 14 7
even q2 10 5
skew q1 15 6
skew q2 12 4

work_mem='64MB':
sort+group (s) hashagg (s)
singleton q1 13 12
singleton q2 9 8
even q1 14 8
even q2 10 5
skew q1 17 6
skew q2 13 4

work_mem='256MB':
sort+group (s) hashagg (s)
singleton q1 12 12
singleton q2 9 8
even q1 14 7
even q2 11 4
skew q1 16 6
skew q2 13 4

work_mem='512MB':
sort+group (s) hashagg (s)
singleton q1 12 12
singleton q2 9 7
even q1 14 7
even q2 10 4
skew q1 16 6
skew q2 12 4

work_mem='2GB':
sort+group (s) hashagg (s)
singleton q1 9 12
singleton q2 6 6
even q1 8 7
even q2 6 4
skew q1 7 6
skew q2 5 4

These numbers are great news for disk-based hashagg. It seems to be the
same or better than sort+group in nearly all cases (again, this example
doesn't actually go to disk, so those numbers may come out differently).
Also, the numbers are remarkably stable for varying work_mem for both
plans. That means that it doesn't cost much to keep a lower work_mem as
long as your system has plenty of memory.

Do others have similar numbers? I'm quite surprised at how little
work_mem seems to matter for these plans (HashJoin might be a different
story though). I feel like I made a mistake -- can someone please do a
sanity check on my numbers?

Regards,
Jeff Davis