Re: Parallel Aggregate

Cool,

I've been testing how this works with partitioning (which seems to be
strange, but I'll post separately about that) and something odd seems to be
going on now with the parallel triggering:

postgres=# create table a as select * from base_p2015_11;
SELECT 20000000

postgres=# select * from a limit 1;
ts | count | a | b | c | d | e
----------------------------+-------+-----+------+------+------+---
2015-11-26 21:10:04.856828 | 860 | 946 | 1032 | 1118 | 1204 |
(1 row)

postgres-# \d a
Table "datamart_owner.a"
Column | Type | Modifiers
--------+-----------------------------+-----------
ts | timestamp without time zone |
count | integer |
a | integer |
b | integer |
c | integer |
d | integer |
e | integer |

postgres=# select pg_size_pretty(pg_relation_size('a'));
pg_size_pretty
----------------
1149 MB

postgres=# explain select sum(count) from a group by date_trunc('DAY',ts);
QUERY PLAN
----------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=218242.96..218254.46 rows=200 width=16)
Group Key: (date_trunc('DAY'::text, ts))
-> Sort (cost=218242.96..218245.96 rows=1200 width=16)
Sort Key: (date_trunc('DAY'::text, ts))
-> Gather (cost=218059.08..218181.58 rows=1200 width=16)
Number of Workers: 5
-> Partial HashAggregate (cost=217059.08..217061.58
rows=200 width=16)
Group Key: date_trunc('DAY'::text, ts)
-> Parallel Seq Scan on a (cost=0.00..197059.06
rows=4000005 width=12)
(9 rows)

postgres=# analyze a;

postgres=# explain select sum(count) from a group by date_trunc('DAY',ts);
QUERY PLAN
--------------------------------------------------------------------------
GroupAggregate (cost=3164211.55..3564212.03 rows=20000024 width=16)
Group Key: (date_trunc('DAY'::text, ts))
-> Sort (cost=3164211.55..3214211.61 rows=20000024 width=12)
Sort Key: (date_trunc('DAY'::text, ts))
-> Seq Scan on a (cost=0.00..397059.30 rows=20000024 width=12)
(5 rows)

Unsure what's happening here.

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099

On Mon, Mar 14, 2016 at 1:31 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 14 March 2016 at 14:52, James Sewell <james(dot)sewell(at)lisasoft(dot)com> wrote:
> > One question - how is the upper limit of workers chosen?
>
> See create_parallel_paths() in allpaths.c. Basically the bigger the
> relation (in pages) the more workers will be allocated, up until
> max_parallel_degree.
>
> There is also a comment in that function which states:
> /*
> * Limit the degree of parallelism logarithmically based on the size of the
> * relation. This probably needs to be a good deal more sophisticated, but
> we
> * need something here for now.
> */
>
> So this will likely see some revision at some point, after 9.6.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--

------------------------------
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.