A problem about join ordering

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: A problem about join ordering
Date: 2022-11-11 11:28:02
Message-ID: CAMbWs4_8n5ANh_aX2PinRZ9V9mtBguhnRd4DOVt9msPgHmEMOQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While reviewing the outer-join Vars patch, I encountered something
confusing me which can also be seen on HEAD. According to outer join
identity 3

(A leftjoin (B leftjoin C on (Pbc)) on (Pab)) left join D on (Pcd)

should be equal to

((A leftjoin B on (Pab)) leftjoin C on (Pbc)) left join D on (Pcd)

Assume Pbc is strict for B.

In the first form, the C/D join will be illegal because we find that Pcd
uses A/B join's RHS (we are checking syn_righthand here, so it's {B, C})
and is not strict for A/B join's min_righthand, which is {B}, so that we
decide we need to preserve the ordering of the two OJs, by adding A/B
join's full syntactic relset to min_lefthand.

In the second form, the C/D join will be legal, as 1) Pcd does not use
A/B join's RHS, and 2) Pcd uses B/C join's RHS and meanwhile is strict
for B/C join's min_righthand.

As a result, with the second form, we may be able to generate more
optimal plans as we have more join ordering choices.

I'm wondering whether we need to insist on being strict for the lower
OJ's min_righthand. What if we instead check strictness for its whole
syn_righthand?

Thanks
Richard

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2022-11-11 11:31:18 Add test module for Custom WAL Resource Manager feature
Previous Message Bharath Rupireddy 2022-11-11 10:57:14 Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL