Horton

Re: [HACKERS] Partition-wise aggregation/grouping

Rafia Sabih <rafia.sabih@enterprisedb.com>

View thread View on postgr.es
From: Rafia Sabih <rafia.sabih@enterprisedb.com>
To: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Dilip Kumar <dilipbalaut@gmail.com>, David Rowley <david.rowley@2ndquadrant.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2018-02-13T07:07:58Z
Lists: pgsql-hackers

Attachments

On Thu, Feb 8, 2018 at 6:35 PM, Jeevan Chalke <
jeevan.chalke@enterprisedb.com> wrote:

> Hi,
>
> In this attached version, I have rebased my changes over new design of
> partially_grouped_rel. The preparatory changes of adding
> partially_grouped_rel are in 0001.
>
> Also to minimize finalization code duplication, I have refactored them
> into two separate functions, finalize_sorted_partial_agg_path() and
> finalize_hashed_partial_agg_path(). I need to create these two functions
> as current path creation order in like,
>     Sort Agg Path
>     Sort Agg Path - Parallel Aware (Finalization needed here)
>     Hash Agg Path
>     Hash Agg Path - Parallel Aware (Finalization needed here)
> And if we club those finalizations together, then path creation order will
> be changed and it may result in the existing plan changes.
> Let me know if that's OK, I will merge them together as they are distinct
> anyways. These changes are part of 0002.
>
> 0003 - 0006 are refactoring patches as before.
>
> 0007 is the main patch per new design. I have removed
> create_partition_agg_paths() altogether as finalization code is reused.
> Also, renamed preferFullAgg with forcePartialAgg as we forcefully needed a
> partial path from nested level if the parent is doing a partial
> aggregation. add_single_path_to_append_rel() is no more exists and also
> there is no need to pass OtherUpperPathExtraData to
> add_paths_to_append_rel().
>
> 0008 - 0009, testcase and postgres_fdw changes.
>
> Please have a look at new changes and let me know if I missed any.
>
> Thanks
>

I was testing this patch for TPC-H benchmarking and came across following
results,

Q1 completes in 229 secs with patch and in 66 secs without it. It looks
like with this patch the time of parallel seq scan itself is elevated for
some of the partitions. Notice for partitions, lineitem_3, lineitem_7,
lineitem_10, and linietem_5 it is some 13 secs which was somewhere around 5
secs on head.

Q6 completes in some 7 secs with patch and it takes 4 secs without it. This
is mainly caused because with the new parallel append, the parallel
operator below it (parallel index scan in this case) is not used, however,
on head it was the append of all the parallel index scans, which was saving
quite some time.

Q18 takes some 390 secs with patch and some 147 secs without it.

The experimental setup for these tests is as follows,
work_mem = 500MB
shared_buffers = 10GB
effective_cache_size = 4GB
seq_page_cost = random+page_cost = 0.01
enable_partition_wise_join = off

Partitioning info:
Total 10 partitions on tables - lineitem and orders each with partitioning
key being l_orderkey and o_orderkey respectively.

Please find the attached file for explain analyse outputs of each of the
reported query.
-- 
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/

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.