Thread: [HACKERS] Aggregates push-down to partitions
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
Hi Konstantin! 09.11.17 20:14, Konstantin Knizhnik wrote: > 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=1 width=8) > -> Append (cost=0.00..29047.75 rows=2002926 width=4) > -> Seq Scan on base (cost=0.00..0.00 rows=1 width=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? > Maybe in this thread[1] your described problem are solved through introducing Parallel Append node? 1. https://www.postgresql.org/message-id/CAJ3gD9dy0K_E8r727heqXoBmWZ83HwLFwdcaSSmBQ1%2BS%2BvRuUQ%40mail.gmail.com -- Regards, Maksim Milyutin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Nov 10, 2017 at 12:20 AM, Maksim Milyutin <milyutinma@gmail.com> wrote: > Hi Konstantin! >> 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? >> > > Maybe in this thread[1] your described problem are solved through > introducing Parallel Append node? > > 1. > https://www.postgresql.org/message-id/CAJ3gD9dy0K_E8r727heqXoBmWZ83HwLFwdcaSSmBQ1%2BS%2BvRuUQ%40mail.gmail.com You may want to review [2] and [3] as well. [2] https://www.postgresql.org/message-id/9666.1491295317@localhost [3] https://www.postgresql.org/message-id/CAM2+6=V64_xhstVHie0Rz=KPEQnLJMZt_e314P0jaT_oJ9MR8A@mail.gmail.com -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On 10.11.2017 12:15, Ashutosh Bapat wrote: > Maybe in this thread[1] your described problem are solved through >> introducing Parallel Append node? >> >> 1. >> https://www.postgresql.org/message-id/CAJ3gD9dy0K_E8r727heqXoBmWZ83HwLFwdcaSSmBQ1%2BS%2BvRuUQ%40mail.gmail.com > You may want to review [2] and [3] as well. > > [2] https://www.postgresql.org/message-id/9666.1491295317@localhost > [3] https://www.postgresql.org/message-id/CAM2+6=V64_xhstVHie0Rz=KPEQnLJMZt_e314P0jaT_oJ9MR8A@mail.gmail.com Thank you very much for this references. I applied partition-wise-agg-v6 patches and for partitioned tables it works perfectly: shard=# explain select count(*) from orders; QUERY PLAN --------------------------------------------------------------------------------------- Finalize Aggregate (cost=100415.29..100415.30rows=1 width=8) -> Append (cost=50207.63..100415.29 rows=2 width=8) -> Partial Aggregate (cost=50207.63..50207.64 rows=1 width=8) -> Foreign Scan on orders_0 (cost=101.00..50195.13 rows=5000 width=0) -> Partial Aggregate (cost=50207.63..50207.64 rows=1 width=8) -> Foreign Scanon orders_1 (cost=101.00..50195.13 rows=5000 width=0) (6 rows) But I wonder why the same optimization is not applied to normal inherited table: shard=# explain select count(*) from base; QUERY PLAN ---------------------------------------------------------------------------------- Aggregate (cost=44087.99..44088.00 rows=1width=8) -> Append (cost=0.00..39079.46 rows=2003414 width=0) -> Seq Scan on base (cost=0.00..0.00 rows=1width=0) -> Seq Scan on derived1 (cost=0.00..14425.00 rows=1000000 width=0) -> Seq Scan on derived2 (cost=0.00..14425.00 rows=1000000 width=0) -> Foreign Scan on derived_fdw (cost=100.00..212.39 rows=3413 width=0) (6 rows) Are there some principle problems? -- 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