Horton

Re: [HACKERS] Partition-wise aggregation/grouping

Konstantin Knizhnik <k.knizhnik@postgrespro.ru>

View thread View on postgr.es
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2017-11-15T16:57:36Z
Lists: pgsql-hackers 

On 15.11.2017 13:35, Jeevan Chalke wrote:
>
> As explained by Ashutosh Bapat in reply
> https://www.postgresql.org/message-id/CAFjFpRdpeMTd8kYbM_x0769V-aEKst5Nkg3+coG=8ki7s8Zqjw@mail.gmail.com
> we cannot rely on just aggtype==aggtranstype.

Obviously this check (aggtype==aggtranstype) is not correct criteria for 
all user defined aggregates.
I just did it as temporary work around for standard aggregates.

>
> 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_gvu7mt2fXtVt6GY3g@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.
...
> Interesting idea of "asynchronous append". However, IMHO it deserves 
> its own
> email-chain.

The main problem IMHO is that there are a lot of different threads and 
patches related with this topic:(
And it is very difficult to combine all of them together to achieve the 
final goal: efficient execution of OLAP queries on sharded table.
It will be nice if somebody who is making the most contribution in this 
direction can somehow maintain it...
I just faced with particular problem with our pg_shardman extension and 
now (thanks to your patch) I have some working solution for it.
But certainly I prefer to have this support in mainstream version of 
Postgres.

There are two open questions, which I wan to discuss (sorry, may be one 
again this is not the right thread for it):

1. Parallel append and FDW/postgres_fdw: should FDW support parallel 
scan and do we really need it to support concurrent execution of query 
on local and remote partitions?
"Asynchronous append" partly solves this problem, but only for remote 
partitions. I do not completely understand all complexity of alternative 
approaches.

2. Right now partition-wise aggregation/grouping works only for tables 
partitioned using new PG 10 partitioning mechanism. But it doesn't work 
for inherited tables, although
there seems to be not so much difference between this two cases. Do you 
think that sometimes it will be also supported for standard inheritance 
mechanism or there is no sense in it?


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

Commits

  1. postgres_fdw: Push down partition-wise aggregation.

  2. Remove 'target' from GroupPathExtraData.

  3. Implement partition-wise grouping/aggregation.

  4. Don't pass the grouping target around unnecessarily.

  5. Determine grouping strategies in create_grouping_paths.

  6. Defer creation of partially-grouped relation until it's needed.

  7. Split create_grouping_paths into degenerate and non-degenerate cases.

  8. Pass additional arguments to a couple of grouping-related functions.

  9. Fix logic error in add_paths_to_partial_grouping_rel.

  10. Minor cleanup of code related to partially_grouped_rel.

  11. Add a new upper planner relation for partially-aggregated results.

  12. Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.

  13. Rename enable_partition_wise_join to enable_partitionwise_join

  14. Factor some code out of create_grouping_paths.

  15. Pad XLogReaderState's main_data buffer more aggressively.

  16. Prevent int128 from requiring more than MAXALIGN alignment.

  17. Fix DROP SUBSCRIPTION hang

  18. Inject $(ICU_LIBS) regardless of platform.

  19. Some preliminary refactoring towards partitionwise join.