On Fri, Jul 14, 2017 at 12:32 PM, Ashutosh Bapat <
ashutosh.bapat@enterprisedb.com> wrote:
>
>
> Here's revised patch set with only 0004 revised. That patch deals with
> creating multi-level inheritance hierarchy from multi-level partition
> hierarchy. The original logic of recursively calling
> inheritance_planner()'s guts over the inheritance hierarchy required
> that for every such recursion we flatten many lists created by that
> code. Recursion also meant that root->append_rel_list is traversed as
> many times as the number of partitioned partitions in the hierarchy.
> Instead the revised version keep the iterative shape of
> inheritance_planner() intact, thus naturally creating flat lists,
> iterates over root->append_rel_list only once and is still easy to
> read and maintain.
>
On testing this patch for TPC-H (for scale factor 20) benchmark I found a regression for Q21, on head it was taking some 600 seconds and with this patch it is taking 3200 seconds. This comparison is on the same partitioned database, one using the partition wise join patch and other is without it. The execution time of Q21 on unpartitioned head is some 300 seconds. The explain analyse output for each of these cases is attached.
This suggests that partitioning is not a suitable strategy for this query, but then may be partition wise should not be picked for such a case to aggravate the performance issue.
The details of the setup is as follows,
Server parameter settings,
work_mem - 1GB
effective_cache_size - 8GB
shared_buffers - 8GB
enable_partition_wise_join = on
Partition information:
Type of partitioning - single column range partition
Tables partitioned - Lineitem and orders
Lineitem -
Partition key = l_orderkey
No of partitions = 18
Orders -
Partition key = o_orderkey
No of partitions = 11
Commit id - 42171e2cd23c8307bbe0ec64e901f58e297db1c3
I chose orderkey as the partition key since it is the primary key of orders and along with l_linenumber it forms the primary key for lineitem.
For the above mentioned settings, there was no other query that used partitioned wise join.
Please let me know if any more information is required regarding this experimentation.
--
Regards,
Rafia Sabih
EnterpriseDB:
http://www.enterprisedb.com/