Re: [HACKERS] Partition-wise aggregation/grouping
Jeevan Chalke <jeevan.chalke@enterprisedb.com>
On Wed, Feb 14, 2018 at 12:17 PM, Rafia Sabih <rafia.sabih@enterprisedb.com> wrote: > On Tue, Feb 13, 2018 at 6:21 PM, Jeevan Chalke < > jeevan.chalke@enterprisedb.com> wrote: > >> >> I see that partition-wise aggregate plan too uses parallel index, am I >> missing something? >> >> > You're right, I missed that, oops. > >> >>> Q18 takes some 390 secs with patch and some 147 secs without it. >>> >> >> This looks strange. This patch set does not touch parallel or seq scan as >> such. I am not sure why this is happening. All these three queries explain >> plan shows much higher execution time for parallel/seq scan. >> >> Yeah strange it is. > Off-list I have asked Rafia to provide me the perf machine access where she is doing this bench-marking to see what's going wrong. Thanks Rafia for the details. What I have observed that, there are two sources, one with HEAD and other with HEAD+PWA. However the configuration switches were different. Sources with HEAD+PWA has CFLAGS="-ggdb3 -O0" CXXFLAGS="-ggdb3 -O0" flags in addition with other sources. i.e. HEAD+PWA is configured with debugging/optimization enabled which account for the slowness. I have run EXPLAIN for these three queries on both the sources having exactly same configuration switches and I don't find any slowness with PWA patch-set. Thus, it will be good if you re-run the benchmark by keeping configuration switches same on both the sources and share the results. Thanks > However, do you see similar behaviour with patches applied, >> "enable_partition_wise_agg = on" and "enable_partition_wise_agg = off" ? >> > > I tried that for query 18, with patch and enable_partition_wise_agg = > off, query completes in some 270 secs. You may find the explain analyse > output for it in the attached file. I noticed that on head the query plan > had parallel hash join however with patch and no partition-wise agg it is > using nested loop joins. This might be the issue. > >> >> Also, does rest of the queries perform better with partition-wise >> aggregates? >> >> > As far as this setting goes, there wasn't any other query using > partition-wise-agg, so, no. > > BTW, just an FYI, this experiment is on scale factor 20. > > -- > Regards, > Rafia Sabih > EnterpriseDB: http://www.enterprisedb.com/ > -- Jeevan Chalke Technical Architect, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Commits
-
postgres_fdw: Push down partition-wise aggregation.
- 7e0d64c7a57e 11.0 landed
-
Remove 'target' from GroupPathExtraData.
- c1de1a3a8b93 11.0 landed
-
Implement partition-wise grouping/aggregation.
- e2f1eb0ee30d 11.0 landed
-
Don't pass the grouping target around unnecessarily.
- 94150513ec12 11.0 landed
-
Determine grouping strategies in create_grouping_paths.
- b5996c2791f3 11.0 landed
-
Defer creation of partially-grouped relation until it's needed.
- 4f15e5d09de2 11.0 landed
-
Split create_grouping_paths into degenerate and non-degenerate cases.
- 1466bcfa4a83 11.0 landed
-
Pass additional arguments to a couple of grouping-related functions.
- 648a6c7bd815 11.0 landed
-
Fix logic error in add_paths_to_partial_grouping_rel.
- 3bfe957761ac 11.0 landed
-
Minor cleanup of code related to partially_grouped_rel.
- 5e6a63c0d102 11.0 landed
-
Add a new upper planner relation for partially-aggregated results.
- 3bf05e096b9f 11.0 landed
-
Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.
- 7d8ac9814bc9 11.0 landed
-
Rename enable_partition_wise_join to enable_partitionwise_join
- 2fb1abaeb016 11.0 cited
-
Factor some code out of create_grouping_paths.
- 9fd8b7d63257 11.0 landed
-
Pad XLogReaderState's main_data buffer more aggressively.
- 8735978e7aeb 11.0 cited
-
Prevent int128 from requiring more than MAXALIGN alignment.
- 7518049980be 11.0 cited
-
Fix DROP SUBSCRIPTION hang
- 8edacab20995 11.0 cited
-
Inject $(ICU_LIBS) regardless of platform.
- 66ed3829df95 11.0 cited
-
Some preliminary refactoring towards partitionwise join.
- c44c47a773bd 10.0 cited