Message-ID: <00a0ce96e20119fc7e35e773a2bbc161ef3a206c.camel@j-davis.com>
Subject: Re: Memory-Bounded Hash Aggregation
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, Tomas Vondra
 <tomas.vondra@2ndquadrant.com>, Taylor Vesely <tvesely@pivotal.io>, Adam
 Lee <ali@pivotal.io>, Melanie Plageman <mplageman@pivotal.io>, PostgreSQL
 Hackers <pgsql-hackers@postgresql.org>
Date: Fri, 14 Feb 2020 13:53:22 -0800
In-Reply-To: <3bed775951a72dbdf8446c6750936e38dcf107b9.camel@j-davis.com>
References: <507ac540ec7c20136364b5272acbcd4574aa76ef.camel@j-davis.com>
	 <20190703001753.s5e5cme3ucjgeu6c@development>
	 <e13b52d4d8f9c10f7b58f39270213b868c99b685.camel@j-davis.com>
	 <20190711155520.zqipi23o3yybx7ym@development>
	 <9be86fc1adc315f69b8af4b379087ab451008d8a.camel@j-davis.com>
	 <20190802064405.GA94036@mars.local>
	 <617fea8258ba3df7bc8b21cd7f3afc219f2383d9.camel@j-davis.com>
	 <20190802152123.ichmacgx3y3x7dc3@development>
	 <CAFaX_4Ls5UGQ2UDYkWhn2xFq=V6BKpUbKSQnWmy27VWhQ2=enA@mail.gmail.com>
	 <e52707d4665e32867fb6dd181825ef15f853b8bf.camel@j-davis.com>
	 <20191214173225.3kzsbncr6brpxdll@development>
	 <d72e494db7b075ef02c3163e6614e698701d2c3e.camel@j-davis.com>
	 <775d754c-9a61-5363-6998-f87d15f1b592@iki.fi>
	 <e5566f7def33a9e9fdff337cca32d07155d7b635.camel@j-davis.com>
	 <CAH2-WznXPuKOee6EMxc7jD4vSQv=yFVVTz6gridGkmzxTSkyNA@mail.gmail.com>
	 <6e2b4a4475a690fa515829950aeed935696b0df3.camel@j-davis.com>
	 <97c46a59c27f3c38e486ca170fcbc618d97ab049.camel@j-davis.com>
	 <8bf8d5f77db49cc1f032b6c7c0cfe0d6da89d64a.camel@j-davis.com>
	 <7e58d8c57690373d66e0a07e8cf683f7d141c74b.camel@j-davis.com>
	 <3bed775951a72dbdf8446c6750936e38dcf107b9.camel@j-davis.com>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Wed, 2020-02-12 at 21:51 -0800, Jeff Davis wrote:
> On Mon, 2020-02-10 at 15:57 -0800, Jeff Davis wrote:
> > Attaching latest version (combined logtape changes along with main
> > HashAgg patch).
> 
> I ran a matrix of small performance tests to look for regressions.

I ran some more tests, this time comparing Hash Aggregation to
Sort+Group.

Summary of trends:

  group key complexity : favors Hash
  group key size       : favors Hash
  group size           : favors Hash
  higher work_mem      : favors Sort[1]
  data set size        : favors Sort[1]
  number of aggregates : favors Hash[2]

  [1] I have closed the gap a bit with some post-experiment tuning.
      I have just begun to analyze this case so I think there is
      quite a bit more room for improvement.
  [2] Could use more exploration -- I don't have an explanation.

Data sets:
  t20m_1_int4: ~20 million groups of size ~1 (uniform)
  t1m_20_int4: ~1 million groups of size ~20 (uniform)
  t1k_20k_int4: ~1k groups of size ~20k (uniform)

  also, text versions of each of those with collate "C.UTF-8"

Results:

1. A general test to vary the group size, key type, and work_mem.

Query:
  select i from $TABLE group by i offset 100000000;

work_mem='4MB'

+----------------+----------+-------------+--------------+
|                | sort(ms) | hashagg(ms) | sort/hashagg |
+----------------+----------+-------------+--------------+
| t20m_1_int4    |   11852  |      10640  |         1.11 |
| t1m_20_int4    |   11108  |       8109  |         1.37 |
| t1k_20k_int4   |    8575  |       2732  |         3.14 |
| t20m_1_text    |   80463  |      12902  |         6.24 |
| t1m_20_text
|   58586  |       9252  |         6.33 |
| t1k_20k_text   |   21781  | 
5739  |         3.80 |
+----------------+----------+-------------+----
----------+

work_mem='32MB'

+----------------+----------+-------------+--------------+
|                | sort(ms) | hashagg(ms) | sort/hashagg |
+----------------+----------+-------------+--------------+
| t20m_1_int4    |    9656  |      11702  |         0.83 |
| t1m_20_int4    |    8870  |       9804  |         0.90 |
| t1k_20k_int4   |    6359  |       1852  |         3.43 |
| t20m_1_text    |   74266  |      14434  |         5.15 |
| t1m_20_text    |   56549  |      10180  |         5.55 |
| t1k_20k_text   |   21407  |       3989  |         5.37 |
+----------------+----------+-------------+--------------+

2. Test group key size

data set:
   20m rows, four int4 columns.
   Columns a,b,c are all the constant value 1, forcing each
   comparison to look at all four columns.

Query: select a,b,c,d from wide group by a,b,c,d offset 100000000;

  work_mem='4MB'
  Sort         : 30852ms
  HashAgg      : 12343ms
  Sort/HashAgg : 2.50

In theory, if the first grouping column is highly selective, then Sort
may have a slight advantage because it can look at only the first
column, while HashAgg needs to look at all 4. But HashAgg only needs to
perform this calculation once and it seems hard enough to show this in
practice that I consider it an edge case. In "normal" cases, it appears
that more grouping columns significantly favors Hash Agg.

3. Test number of aggregates

Data Set: same as for test #2 (group key size).

Query: select d, count(a),sum(b),avg(c),min(d)
       from wide group by d offset 100000000;

  work_mem='4MB'
  Sort         : 22373ms
  HashAgg      : 17338ms
  Sort/HashAgg : 1.29

I don't have an explanation of why HashAgg is doing better here. Both
of them are using JIT and essentially doing the same number of
advancements. This could use more exploration, but the effect isn't
major.

4. Test data size

Data 400 million rows of four random int8s. Group size of one.

Query: select a from t400m_1_int8 group by a offset 1000000000;

  work_mem='32MB'
  Sort         : 300675ms
  HashAgg      : 560740ms
  Sort/HashAgg : 0.54

I tried increasing the max number of partitions and brought the HashAgg
runtime down to 481985 (using 1024 partitions), which closes the gap to
0.62. That's not too bad for HashAgg considering this is a group size
of one with a simple group key. A bit more tuning might be able to
close the gap further.

Conclusion:

HashAgg is winning in a lot of cases, and this will be an important
improvement for many workloads. Not only is it faster in a lot of
cases, but it's also less risky. When an input has unknown group size,
it's much easier for the planner to choose HashAgg -- a small downside
and a big upside.

Regards,
	Jeff Davis