Re: [HACKERS] Partition-wise aggregation/grouping

On Tue, Feb 13, 2018 at 12:37 PM, Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>
wrote:

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

Thanks Rafia for testing this with TPC-H benchmarking.

>
> 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.
>

I see that partition-wise aggregate plan too uses parallel index, am I
missing something?

>
> 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.

However, do you see similar behaviour with patches applied,
"enable_partition_wise_agg = on" and "enable_partition_wise_agg = off" ?

Also, does rest of the queries perform better with partition-wise
aggregates?

>
> 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/
>

--
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company