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
______________________________________
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
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.
В списке pgsql-hackers по дате отправления:
Следующее
От: Thomas MunroДата:
Сообщение: Re: Proposal: "Causal reads" mode for load balancing reads without stale data