pg13dev: explain partial, parallel hashagg, and memory use

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: David Rowley <dgrowleyml(at)gmail(dot)com>, Jeff Davis <jdavis(at)postgresql(dot)org>
Subject: pg13dev: explain partial, parallel hashagg, and memory use
Date: 2020-08-05 01:21:05
Message-ID: 20200805012105.GZ28072@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm testing with a customer's data on pg13dev and got output for which Peak
Memory doesn't look right/useful. I reproduced it on 565f16902.

CREATE TABLE p(i int) PARTITION BY RANGE(i);
CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (0)TO(1000);
CREATE TABLE p2 PARTITION OF p FOR VALUES FROM (1000)TO(2000);
CREATE TABLE p3 PARTITION OF p FOR VALUES FROM (2000)TO(3000);
INSERT INTO p SELECT i%3000 FROM generate_series(1,999999)i;
VACUUM ANALYZE p;

postgres=# explain(analyze,settings) SELECT i, COUNT(1) FROM p GROUP BY 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=7469.00..14214.45 rows=2502 width=12) (actual time=489.409..514.209 rows=3000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=6469.00..12964.25 rows=1251 width=12) (actual time=476.291..477.179 rows=1000 loops=3)
-> HashAggregate (cost=6487.99..6497.99 rows=1000 width=12) (actual time=474.454..475.203 rows=1000 loops=1)
Group Key: p.i
Batches: 1 Memory Usage: 0kB
Worker 0: Batches: 1 Memory Usage: 193kB
Worker 1: Batches: 1 Memory Usage: 0kB
-> Seq Scan on p1 p (cost=0.00..4817.99 rows=333999 width=4) (actual time=0.084..100.677 rows=333999 loops=1)
-> HashAggregate (cost=6469.00..6479.00 rows=1000 width=12) (actual time=468.517..469.272 rows=1000 loops=1)
Group Key: p_1.i
Batches: 1 Memory Usage: 0kB
Worker 0: Batches: 1 Memory Usage: 0kB
Worker 1: Batches: 1 Memory Usage: 193kB
-> Seq Scan on p2 p_1 (cost=0.00..4804.00 rows=333000 width=4) (actual time=0.082..102.154 rows=333000 loops=1)
-> HashAggregate (cost=6469.00..6479.00 rows=1000 width=12) (actual time=485.887..486.509 rows=1000 loops=1)
Group Key: p_2.i
Batches: 1 Memory Usage: 193kB
Worker 0: Batches: 1 Memory Usage: 0kB
Worker 1: Batches: 1 Memory Usage: 0kB
-> Seq Scan on p3 p_2 (cost=0.00..4804.00 rows=333000 width=4) (actual time=0.043..104.631 rows=333000 loops=1)
Settings: effective_io_concurrency = '0', enable_partitionwise_aggregate = 'on', enable_partitionwise_join = 'on', work_mem = '127MB'

--
Justin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2020-08-05 01:44:17 Re: pg13dev: explain partial, parallel hashagg, and memory use
Previous Message Amit Langote 2020-08-05 01:12:53 Re: FailedAssertion("pd_idx == pinfo->nparts", File: "execPartition.c", Line: 1689)