On Sun, Nov 12, 2017 at 1:59 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote:
> On 10/27/2017 02:01 PM, Jeevan Chalke wrote: > >> Hi, >> >> Attached new patch-set here. Changes include: >> >> 1. Added separate patch for costing Append node as discussed up-front in >> the >> patch-set. >> 2. Since we now cost Append node, we don't need >> partition_wise_agg_cost_factor >> GUC. So removed that. The remaining patch hence merged into main >> implementation >> patch. >> 3. Updated rows in test-cases so that we will get partition-wise plans. >> >> Thanks >> > > I applied partition-wise-agg-v6.tar.gz patch to the master and use > shard.sh example from https://www.postgresql.org/mes > sage-id/14577.1509723225%40localhost > Plan for count(*) is the following: > > shard=# explain select count(*) from orders; > QUERY PLAN > ------------------------------------------------------------ > --------------------------- > Finalize Aggregate (cost=100415.29..100415.30 rows=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 Scan on orders_1 (cost=101.00..50195.13 > rows=5000 width=0) > > > We really calculate partial aggregate for each partition, but to do we > still have to fetch all data from remote host. > So for foreign partitions such plans is absolutely inefficient. > Amy be it should be combined with some other patch? > For example, with agg_pushdown_v4.tgz patch > https://www.postgresql.org/message-id/14577.1509723225%40localhost ? > But it is not applied after partition-wise-agg-v6.tar.gz patch. > Also postgres_fdw in 11dev is able to push down aggregates without > agg_pushdown_v4.tgz patch. > > In 0009-Teach-postgres_fdw-to-push-aggregates-for-child-rela.patch > there is the following check: > > /* Partial aggregates are not supported. */ > + if (extra->isPartial) > + return; > > If we just comment this line then produced plan will be the following: > > shard=# explain select sum(product_id) from orders; > QUERY PLAN > ---------------------------------------------------------------- > Finalize Aggregate (cost=308.41..308.42 rows=1 width=8) > -> Append (cost=144.18..308.41 rows=2 width=8) > -> Foreign Scan (cost=144.18..154.20 rows=1 width=8) > Relations: Aggregate on (public.orders_0 orders) > -> Foreign Scan (cost=144.18..154.20 rows=1 width=8) > Relations: Aggregate on (public.orders_1 orders) > (6 rows) > > And it is actually desired plan! > Obviously such approach will not always work. FDW really doesn't support > partial aggregates now. > But for most frequently used aggregates: sum, min, max, count > aggtype==aggtranstype and there is no difference > between partial and normal aggregate calculation. > So instead of (extra->isPartial) condition we can add more complex check > which will traverse pathtarget expressions and > check if it can be evaluated in this way. Or... extend FDW API to support > partial aggregation. > As explained by Ashutosh Bapat in reply https://www.postgresql.org/message-id/CAFjFpRdpeMTd8kYbM_x0769V-aEKst5Nkg3+coG=8ki7s8z...@mail.gmail.com we cannot rely on just aggtype==aggtranstype. However, I have tried pushing partial aggregation over remote server and also submitted a PoC patch here: https://www.postgresql.org/message-id/CAM2+6=uakp9+tsjuh2fbhhwnjc7oyfl1_gvu7mt2fxtvt6g...@mail.gmail.com I have later removed these patches from Partition-wise-Aggregation patch set as it is altogether a different issue than this mail thread. We might need to discuss on it separately. > > But even the last plan is not ideal: it will calculate predicates at each > remote node sequentially. > There is parallel append patch: > https://www.postgresql.org/message-id/CAJ3gD9ctEcrVUmpY6fq_J > UB6WDKGXAGd70EY68jVFA4kxMbKeQ%40mail.gmail.com > but ... FDW doesn't support parallel scan, so parallel append can not be > applied in this case. > And we actually do not need parallel append with all its dynamic workers > here. > We just need to start commands at all remote servers and only after it > fetch results (which can be done sequentially). > > I am investigating problem of efficient execution of OLAP queries on > sharded tables (tables with remote partitions). > After reading all this threads and corresponding patches, it seems to me > that we already have most of parts of the puzzle, what we need is to put > them on right places and may be add missed ones. > I wonder if somebody is busy with it and can I somehow help here? > > Also I am not quite sure about the best approach with parallel execution > of distributed query at all nodes. > Should we make postgres_fdw parallel safe and use parallel append? How > difficult it will be? > Or in addition to parallel append we should also have "asynchronous > append" which will be able to initiate execution at all nodes? > It seems to be close to merge append, because it should simultaneously > traverse all cursors. > > Looks like second approach is easier for implementation. But in case of > sharded table, distributed query may need to traverse both remote > and local shards and this approach doesn't allow to processed several > local shards in parallel. > > Interesting idea of "asynchronous append". However, IMHO it deserves its own email-chain. > -- > Konstantin Knizhnik > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > Thanks -- Jeevan Chalke Technical Architect, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
