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

On Wed, Jun 27, 2018 at 12:28 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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(at)lab(dot)ntt(dot)co(dot)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