when called from extract_restriction_or_clauses(), the 4th check in join_clause_is_movable_to() is unnecessary

From: iosif <852048472(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: when called from extract_restriction_or_clauses(), the 4th check in join_clause_is_movable_to() is unnecessary
Date: 2025-10-28 00:55:48
Message-ID: tencent_FB166468E6D435FFA4782AA94451B8AC4F0A@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The following analysis is based on version 15.14, but also applies to later versions.&nbsp;
Function&nbsp;join_clause_is_movable_to()&nbsp;is called from multiple callers, like match_join_clauses_to_index(), extract_restriction_or_clauses(), etc. When called from match_join_clauses_to_index(), it's necessary to perform the 4th check, i.e. if (bms_overlap(baserel-&gt;lateral_referencers, rinfo-&gt;clause_relids)), because the referencers&nbsp;can't be put on the outer side of a nestloop with the target relation. However, when called from extract_restriction_or_clauses(), the 4th check is unnecessary because what we are going to do is not putting referencers on the outer side of a nestloop with the target relation, but extracting restriction for the target rel from the or-clause and push the extracted restriction to the target rel, which does no harm even if the target rel has lateral referencers. For example, consider this query:

select * from a, lateral(select sum(b1),sum(b2),b3,b4 from b where b3+b4<a1+a2 group by b3,b4 )I where (b3=a3 and a4=74) or (b3=a3-99 and a4=12);

removing the 4th check from join_clause_is_movable_to() when it is called from extract_restriction_or_clauses() can produce a plan which push (a4 = 74 OR a4 = 12) to relation A in the first place, significantly accelerating execution. So I suggest add an additional parameter to join_clause_is_movable_to() to indicate its caller, if it's called from extract_restriction_or_clauses(), simply skip the 4th check for lateral referencers.

Best Regards

Browse pgsql-hackers by date

  From Date Subject
Next Message Chao Li 2025-10-28 00:57:36 Re: [PATCH] pg_bsd_indent: improve formatting of multiline comments
Previous Message Chao Li 2025-10-28 00:36:33 Re: [PATCH] pg_bsd_indent: improve formatting of multiline comments