|From:||Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>|
|To:||Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>|
|Cc:||Robert Haas <robertmhaas(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: [HACKERS] Partition-wise aggregation/grouping|
|Views:||Raw Message | Whole Thread | Download mbox|
On Tue, Feb 13, 2018 at 12:37 PM, Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>
> I was testing this patch for TPC-H benchmarking and came across following
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
> 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
> 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.
> Rafia Sabih
> EnterpriseDB: http://www.enterprisedb.com/
Technical Architect, Product Development
The Enterprise PostgreSQL Company
|Next Message||Alvaro Herrera||2018-02-13 13:23:19||Re: reorganizing partitioning code (was: Re: [HACKERS] path toward faster partition pruning)|
|Previous Message||Ashutosh Bapat||2018-02-13 12:51:04||Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled.|