Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables - Mailing list pgsql-hackers

From Rafia Sabih
Subject Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
Date
Msg-id CAOGQiiNwEZ3hepxCAYL5pxvHMTQWfY-8x1R3dAxS=HRUrVG0vA@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables  (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>)
Responses Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
List pgsql-hackers


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

pgsql-hackers by date:

Previous
From: Mark Dilger
Date:
Subject: Re: [HACKERS] Something for the TODO list: deprecating abstime and friends
Next
From: Yugo Nagata
Date:
Subject: Re: [HACKERS] [PATCH] Make sure all statistics is sent after a fewDML are performed