Message-ID: <60961f98-4976-531a-82e8-3a4a97d03ee1@gmail.com>
Date: Fri, 19 Jul 2024 07:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Should consider materializing the cheapest inner path in
 consider_parallel_nestloop()
To: Richard Guo <guofenglinux@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, Tomasz Rybak <tomasz.rybak@post.pl>,
 pgsql-hackers@lists.postgresql.org, robertmhaas@gmail.com,
 David Rowley <dgrowleyml@gmail.com>,
 "Fujii.Yuki@df.MitsubishiElectric.co.jp"
 <Fujii.Yuki@df.mitsubishielectric.co.jp>
References: <CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com>
 <CAMbWs49agQ1THL0XYv_4aeZXN2R1cRksevja05n-MFOzZtNsuA@mail.gmail.com>
 <CAHewXN=-XQwFY0n5EMUTzvK4G0aHWkNzdOPCfwnmQw3kDed2zQ@mail.gmail.com>
 <9FBD029B-3E71-4D47-AB54-938C73A4668F@yandex-team.ru>
 <CAHewXN=rP49ySfue9FA437jW4Me+FLBY-58zqTy+A_jxGGW=Xg@mail.gmail.com>
 <5641923793cef7706395a34e62538b75d05e498b.camel@post.pl>
 <CAHewXNmWbA9Oe6JCEc66dJrVyyBDv1uSx6z_zdz-BQRE0Jr9pQ@mail.gmail.com>
 <CAMbWs48TaubitHF+JXqysbSHLCzTLOU9PD3zpLGFUtizsonAMA@mail.gmail.com>
 <CAHewXNkTh0RS7FMmVjMmPbMjG1=mUQ6uchxG3BuKsVH1=tNjag@mail.gmail.com>
 <CAMbWs48jJHxQfb0F0G1c6O=qgzNxRbJ-uE8FwpH=adAVmvnxSg@mail.gmail.com>
 <CAMbWs48sBaMRtu+WfiAaVOuFU6tWr_++y=Ythkvj-ufgxTiH-Q@mail.gmail.com>
 <7c09a439-e48d-5460-cfa0-a371b1a57066@gmail.com>
 <CAMbWs48fpP=OU5VLOGBBgvA4kxnPXTCVJh3Qayo1i-K=3Pt4Nw@mail.gmail.com>
 <CAMbWs4-A4+ugdRXGeTSNRhhBcZHf9-YtwcL7iCeUH4meSq4P1A@mail.gmail.com>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <CAMbWs4-A4+ugdRXGeTSNRhhBcZHf9-YtwcL7iCeUH4meSq4P1A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/60961f98-4976-531a-82e8-3a4a97d03ee1%40gmail.com>
Precedence: bulk

Hello Richard,

18.07.2024 17:30, Richard Guo wrote:
> The problemed plan is a non-parallel nestloop join.  It's just chance
> which join order the planner will pick, and slight variations in
> underlying statistics could result in a different displayed plan.
>  From the two verbose plans, we can see slight variations in the
> statistics for the parallel seqscan of tenk1.
>
> ->  Parallel Seq Scan on public.tenk1 t1  (cost=0.00..370.00 rows=2500
> width=244)
>
> VS.
>
> ->  Parallel Seq Scan on public.tenk1 t1  (cost=0.00..369.99 rows=2499
> width=244)
>
> I have no idea why the underlying statistics changed, but it seems
> that this slight change is sufficent to result in a different plan.

I think it could be caused by the same reason as [1] and I really can
easily (without multiple instances/loops. just with `make check`) reproduce
the failure with cranky-ConditionalLockBufferForCleanup.patch (but
targeted for "VACUUM ANALYZE tenk1;").

> According to the discussion in [1], I think what we wanted to test
> with this query is that parallel nestloop join is not generated if the
> inner path is not parallel-safe.  Therefore, I modified this test case
> to use a lateral join, rendering the inner path not parallel-safe
> while also enforcing the join order.  Please see attached.

The modified test survives my testing procedure. Thank you for the patch!

[1] https://www.postgresql.org/message-id/flat/66eb9a6e-fc67-a230-c5b1-2a741e8b88c6%40gmail.com

Best regards,
Alexander