Re: [HACKERS] advanced partition matching algorithm for partition-wise join

On Wed, Jun 27, 2018 at 12:28 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2018/06/26 18:02, Ashutosh Bapat wrote:
>> On Tue, Jun 26, 2018 at 2:27 PM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> Hi Ashutosh,
>>>
>>> On 2018/05/14 20:14, Ashutosh Bapat wrote:
>>>> 0001-Hash-partition-bound-equality-refactoring.patch
>>>> 0002-Targetlist-of-a-child-join-is-produced-by-translatin.patch
>>>> 0003-Partition-wise-join-for-1-1-1-0-0-1-partition-matchi.patch
>>>> 0004-Add-a-debug-message-to-notify-whether-partition-wise.patch
>>>> 0005-Tests-for-0-1-1-1-and-1-0-partition-matching.patch
>>>> 0006-Extra-extensive-tests-for-advanced-partition-matchin.patch
>>>
>>> I noticed after *cleanly* applying 0001-0004 to today's HEAD that while
>>> 0005's test all pass, there are many failures in 0006's tests.  Maybe, you
>>> need to adjust something in one of the patches or adjust test outputs.
>>
>> If the failures are because of plan changes, it's expected. If those
>> are because of crashes or changed output, those need to be fixed. I
>> have kept that patch to notice any crashes or output changes, in which
>> case, I pull that test into 0005 test set after fixing the code. Once
>> we are near commit, I will remove that patch from the patchset.
>
> Ah, okay.  I thought of reporting this because I felt the errors may have
> to do with changes to the related code in HEAD between May 14 when you
> last posted the patches and today that you may need to account for in you
> patches.  For instance, there are many diffs like this:
>
> ***************
> *** 90,132 ****
>   -- left outer join, with whole-row reference
>   EXPLAIN (COSTS OFF)
>   SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b =
> 0 ORDER BY t1.a, t1.b, t1.c, t2.a, t2.b, t2.c;
> !                        QUERY PLAN
> ! --------------------------------------------------------
>    Sort
>      Sort Key: t1.a, t1.c, t2.a, t2.b, t2.c
> !    ->  Result
> !          ->  Append
> !                ->  Hash Right Join
> !                      Hash Cond: (t2.b = t1.a)
> !                      ->  Seq Scan on prt2_p0 t2
> !                      ->  Hash
> !                            ->  Seq Scan on prt1_p0 t1
> <snip>
>
> --- 90,131 ----
>   -- left outer join, with whole-row reference
>   EXPLAIN (COSTS OFF)
>   SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b =
> 0 ORDER BY t1.a, t1.b, t1.c, t2.a, t2.b, t2.c;
> !                     QUERY PLAN
> ! --------------------------------------------------
>    Sort
>      Sort Key: t1.a, t1.c, t2.a, t2.b, t2.c
> !    ->  Append
> !          ->  Hash Right Join
> !                Hash Cond: (t2.b = t1.a)
> !                ->  Seq Scan on prt2_p0 t2
> !                ->  Hash
> !                      ->  Seq Scan on prt1_p0 t1
> !                            Filter: (b = 0)
>
> Looks like the Result node on top of Append is no longer there after
> applying your patch.

Yes. They are coming because of a commit which removed Result node on
top of an Append node. I don't remember exactly which.

I wouldn't worry about those diffs at this time. As I have mentioned
in earlier mails, the expected output from 0006 is quite large and is
not supposed to be committed. So, I don't see much value in fixing the
plans in that output.

Do you see that as a hindrance in reviewing the code changes and tests in 0005?

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