Re: Parallel Aggregate - Mailing list pgsql-hackers

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.rowley@2ndquadrant.com> wrote:

On 14 March 2016 at 14:52, James Sewell <james.sewell@lisasoft.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