Horton

Re: Partition-wise join for join between (declaratively) partitioned tables

Amit Khandekar <amitdkhan.pg@gmail.com>

View thread View on postgr.es
From: Amit Khandekar <amitdkhan.pg@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Robert Haas <robertmhaas@gmail.com>, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>, Thomas Munro <thomas.munro@enterprisedb.com>, Rafia Sabih <rafia.sabih@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-09-13T09:52:02Z
Lists: pgsql-hackers 
On 13 September 2017 at 13:05, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> On Wed, Sep 13, 2017 at 12:32 PM, Amit Khandekar <amitdkhan.pg@gmail.com> wrote:
>> Hi,
>>
>> Rafia had done some testing on TPCH queries using Partition-wise join
>> patch along with Parallel Append patch.
>>
>> There, we had observed that for query 4, even though the partition
>> wise joins are under a Parallel Append, the join are all non-partial.
>>
>> Specifically, the partition-wise join has non-partial nested loop
>> joins when actually it was expected to have partial nested loop joins.
>> (The difference can be seen by the observation that the outer relation
>> of that join is scanned by non-parallel Bitmap Heap scan when it
>> should have used Parallel Bitmap Heap Scan).
>>
>> Here is the detailed analysis , including where I think is the issue :
>>
>> https://www.postgresql.org/message-id/CAJ3gD9cZms1ND3p%3DNN%3DhDYDFt_SeKq1htMBhbj85bOmvJwY5fg%40mail.gmail.com
>>
>> All the TPCH results are posted in the same above mail thread.
>
> Can you please check if the attached patch fixes the issue.

Thanks Ashutosh. Yes, it does fix the issue. Partial Nested Loop joins
are generated now. If I see any unexpected differences in the
estimated or actual costs, I will report that in the Parallel Append
thread. As far as Partition-wise join is concerned, this issue is
solved, because Partial nested loop join does get created.

>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company



-- 
Thanks,
-Amit Khandekar
EnterpriseDB Corporation
The Postgres Database Company

Commits

  1. Add test for partitionwise join involving default partition.

  2. Rewrite the code that applies scan/join targets to paths.

  3. Fix code related to partitioning schemes for dropped columns.

  4. Copy information from the relcache instead of pointing to it.

  5. Basic partition-wise join functionality.

  6. Associate partitioning information with each RelOptInfo.

  7. Expand partitioned table RTEs level by level, without flattening.

  8. Set partitioned_rels appropriately when UNION ALL is used.

  9. Remove dedicated B-tree root-split record types.

  10. Assorted preparatory refactoring for partition-wise join.

  11. Teach adjust_appendrel_attrs(_multilevel) to do multiple translations.

  12. Avoid unnecessary single-child Append nodes.

  13. Revisit handling of UNION ALL subqueries with non-Var output columns.