From: Adam Lee <ali@pivotal.io>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Taylor Vesely <tvesely@pivotal.io>
Cc: Melanie Plageman <mplageman@pivotal.io>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Memory-Bounded Hash Aggregation
Date: Fri, 20 Dec 2019 17:16:26 +0800
Message-ID: <20191220091626.GD45905@mars.local> (raw)
In-Reply-To: <20191214173225.3kzsbncr6brpxdll@development>
References: <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>
On Sat, Dec 14, 2019 at 06:32:25PM +0100, Tomas Vondra wrote:
> I've done a bit more testing on this, after resolving a couple of minor
> conflicts due to recent commits (rebased version attached).
>
> In particular, I've made a comparison with different dataset sizes,
> group sizes, GUC settings etc. The script and results from two different
> machines are available here:
>
> The script essentially runs a simple grouping query with different
> number of rows, groups, work_mem and parallelism settings. There's
> nothing particularly magical about it.
Nice!
> I did run it both on master and patched code, allowing us to compare
> results and assess impact of the patch. Overall, the changes are
> expected and either neutral or beneficial, i.e. the timing are the same
> or faster.
>
> The number of cases that regressed is fairly small, but sometimes the
> regressions are annoyingly large - up to 2x in some cases. Consider for
> example this trivial example with 100M rows:
I suppose this is because the patch has no costing changes yet. I hacked
a little to give hash agg a spilling punish, just some value based on
(groups_in_hashtable * num_of_input_tuples)/num_groups_from_planner, it
would not choose hash aggregate in this case.
However, that punish is wrong, because comparing to the external sort
algorithm, hash aggregate has the respilling, which involves even more
I/O, especially with a very large number of groups but a very small
number of tuples in a single group like the test you did. It would be a
challenge.
BTW, Jeff, Greenplum has a test for hash agg spill, I modified a little
to check how many batches a query uses, it's attached, not sure if it
would help.
--
Adam Lee
create schema hashagg_spill;
set search_path to hashagg_spill;
-- start_ignore
CREATE EXTENSION plpythonu;
-- end_ignore
-- Create a function which checks how many batches there are
-- output example: "Memory Usage: 4096kB Batches: 68 Disk Usage:8625kB"
create or replace function hashagg_spill.num_batches(explain_query text)
returns setof int as
$$
import re
rv = plpy.execute(explain_query)
search_text = 'batches'
result = []
for i in range(len(rv)):
cur_line = rv[i]['QUERY PLAN']
if search_text.lower() in cur_line.lower():
p = re.compile('.*Memory Usage: (\d+).* Batches: (\d+) Disk.*')
m = p.match(cur_line)
batches = int(m.group(2))
result.append(batches)
return result
$$
language plpythonu;
create table testhagg (i1 int, i2 int, i3 int, i4 int);
insert into testhagg select i,i,i,i from generate_series(1, 30000)i;
set work_mem="1800";
select * from (select max(i1) from testhagg group by i2) foo order by 1 limit 10;
select num_batches > 0 from hashagg_spill.num_batches('explain (analyze, verbose) select max(i1) from testhagg group by i2;') num_batches;
select num_batches > 3 from hashagg_spill.num_batches('explain (analyze, verbose) select max(i1) from testhagg group by i2 limit 90000;') num_batches;
reset all;
set search_path to hashagg_spill;
-- Test agg spilling scenarios
create table aggspill (i int, j int, t text);
insert into aggspill select i, i*2, i::text from generate_series(1, 10000) i;
insert into aggspill select i, i*2, i::text from generate_series(1, 100000) i;
insert into aggspill select i, i*2, i::text from generate_series(1, 1000000) i;
-- No spill with large statement memory
set work_mem = '125MB';
select * from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 1) g;');
-- Reduce the statement memory to induce spilling
set work_mem = '10MB';
select num_batches > 32 from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 2) g;') num_batches;
-- Reduce the statement memory, more batches
set work_mem = '5MB';
select num_batches > 64 from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 3) g;') num_batches;
-- Check spilling to a temp tablespace
SET work_mem='1000kB';
CREATE TABLE hashagg_spill(col1 numeric, col2 int);
INSERT INTO hashagg_spill SELECT id, 1 FROM generate_series(1,20000) id;
ANALYZE hashagg_spill;
CREATE TABLE spill_temptblspace (a numeric);
SET temp_tablespaces=pg_default;
INSERT INTO spill_temptblspace SELECT avg(col2) col2 FROM hashagg_spill GROUP BY col1 HAVING(sum(col1)) < 0;
RESET temp_tablespaces;
RESET work_mem;
drop schema hashagg_spill cascade;
Attachments:
[text/plain] hashagg_spill.sql (2.8K, ../20191220091626.GD45905@mars.local/2-hashagg_spill.sql)
download | inline:
create schema hashagg_spill;
set search_path to hashagg_spill;
-- start_ignore
CREATE EXTENSION plpythonu;
-- end_ignore
-- Create a function which checks how many batches there are
-- output example: "Memory Usage: 4096kB Batches: 68 Disk Usage:8625kB"
create or replace function hashagg_spill.num_batches(explain_query text)
returns setof int as
$$
import re
rv = plpy.execute(explain_query)
search_text = 'batches'
result = []
for i in range(len(rv)):
cur_line = rv[i]['QUERY PLAN']
if search_text.lower() in cur_line.lower():
p = re.compile('.*Memory Usage: (\d+).* Batches: (\d+) Disk.*')
m = p.match(cur_line)
batches = int(m.group(2))
result.append(batches)
return result
$$
language plpythonu;
create table testhagg (i1 int, i2 int, i3 int, i4 int);
insert into testhagg select i,i,i,i from generate_series(1, 30000)i;
set work_mem="1800";
select * from (select max(i1) from testhagg group by i2) foo order by 1 limit 10;
select num_batches > 0 from hashagg_spill.num_batches('explain (analyze, verbose) select max(i1) from testhagg group by i2;') num_batches;
select num_batches > 3 from hashagg_spill.num_batches('explain (analyze, verbose) select max(i1) from testhagg group by i2 limit 90000;') num_batches;
reset all;
set search_path to hashagg_spill;
-- Test agg spilling scenarios
create table aggspill (i int, j int, t text);
insert into aggspill select i, i*2, i::text from generate_series(1, 10000) i;
insert into aggspill select i, i*2, i::text from generate_series(1, 100000) i;
insert into aggspill select i, i*2, i::text from generate_series(1, 1000000) i;
-- No spill with large statement memory
set work_mem = '125MB';
select * from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 1) g;');
-- Reduce the statement memory to induce spilling
set work_mem = '10MB';
select num_batches > 32 from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 2) g;') num_batches;
-- Reduce the statement memory, more batches
set work_mem = '5MB';
select num_batches > 64 from hashagg_spill.num_batches('explain (analyze, verbose)
select count(*) from (select i, count(*) from aggspill group by i,j having count(*) = 3) g;') num_batches;
-- Check spilling to a temp tablespace
SET work_mem='1000kB';
CREATE TABLE hashagg_spill(col1 numeric, col2 int);
INSERT INTO hashagg_spill SELECT id, 1 FROM generate_series(1,20000) id;
ANALYZE hashagg_spill;
CREATE TABLE spill_temptblspace (a numeric);
SET temp_tablespaces=pg_default;
INSERT INTO spill_temptblspace SELECT avg(col2) col2 FROM hashagg_spill GROUP BY col1 HAVING(sum(col1)) < 0;
RESET temp_tablespaces;
RESET work_mem;
drop schema hashagg_spill cascade;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ali@pivotal.io, tomas.vondra@2ndquadrant.com, pgsql@j-davis.com, tvesely@pivotal.io, mplageman@pivotal.io
Subject: Re: Memory-Bounded Hash Aggregation
In-Reply-To: <20191220091626.GD45905@mars.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox