[HACKERS] Aggregates push-down to partitions - Mailing list pgsql-hackers

There is a huge thread concerning pushing-down aggregates to FDW:


https://www.postgresql.org/message-id/flat/CAFjFpRcnueviDpngJ3QSVvj7oyukr9NkSiCspqd4N%2BdCEdvYvg%40mail.gmail.com#CAFjFpRcnueviDpngJ3QSVvj7oyukr9NkSiCspqd4N+dCEdvYvg@mail.gmail.com

but as far as I understand nothing is done for efficient calculation of 
aggregates for partitioned table.
In case of local partitions it is somehow compensated by parallel query 
plan:

postgres=# create table base(x integer);
CREATE TABLE
postgres=# create table derived1() inherits (base);
CREATE TABLE
postgres=# create table derived2() inherits (base);
CREATE TABLE
postgres=# insert into derived1  values (generate_series(1,1000000));
INSERT 0 1000000
postgres=# insert into derived2  values (generate_series(1,1000000));
INSERT 0 1000000
postgres=# explain select sum(x) from base;                                           QUERY PLAN
------------------------------------------------------------------------------------------------- Finalize Aggregate 
(cost=12176.63..12176.64rows=1 width=8)   ->  Gather  (cost=12176.59..12176.61 rows=8 width=8)         Workers Planned:
8        ->  Partial Aggregate  (cost=12175.59..12175.60 rows=1 width=8)               ->  Append  (cost=0.00..11510.47
rows=266048width=4)                     ->  Parallel Seq Scan on base (cost=0.00..0.00 
 
rows=1 width=4)                     ->  Parallel Seq Scan on derived1 
(cost=0.00..5675.00 rows=125000 width=4)                     ->  Parallel Seq Scan on derived2 
(cost=0.00..5835.47 rows=141047 width=4)
(8 rows)

It is still far from ideal plan because each worker is working with all 
partitions, instead of spitting partitions between workers and calculate 
partial aggregates for each partition.

But if we add FDW as a child of parent table, then parallel scan can not 
be used and we get the worst possible plan:

postgres=# create foreign table derived_fdw() inherits(base) server 
pg_fdw options (table_name 'derived1');CREATE FOREIGN TABLE
postgres=# explain select sum(x) from base;                                    QUERY PLAN
---------------------------------------------------------------------------------- Aggregate  (cost=34055.07..34055.08
rows=1width=8)   ->  Append  (cost=0.00..29047.75 rows=2002926 width=4)         ->  Seq Scan on base  (cost=0.00..0.00
rows=1width=4)         ->  Seq Scan on derived1  (cost=0.00..14425.00 rows=1000000 
 
width=4)         ->  Seq Scan on derived2  (cost=0.00..14425.00 rows=1000000 
width=4)         ->  Foreign Scan on derived_fdw  (cost=100.00..197.75 
rows=2925 width=4)
(6 rows)

So we sequentially pull all data to this node and compute aggregates 
locally.
Ideal plan will calculate in parallel partial aggregates at all nodes 
and then combine partial results.
It requires two changes:
1. Replace Aggregate->Append with 
Finalize_Aggregate->Append->Partial_Aggregate
2. Concurrent execution of Append. It also can be done in two different 
ways: we can try to use existed parallel workers infrastructure and
replace Append with Gather. It seems to be the best approach for local 
partitioning. In case of remote (FDW) partitions, it is enough
to split starting of execution (PQsendQuery in postgres_fdw) and getting 
results. So it requires some changes in FDW protocol.


I wonder if somebody already investigate this problem or working in this 
direction.
May be there are already some patches proposed?
I have searched hackers archive, but didn't find something relevant...
Are there any suggestions about the best approach to implement this feature?

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers