Hi hackers, I think removing the 4th check in join_clause_is_movable_to when called from extract_restriction_or_clauses can produce better query plan for some queries without introducing any problem.
Function join_clause_is_movable_to 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->lateral_referencers, rinfo->clause_relids)), because the referencers 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 not only unnecessary but can also keep good plans from being generated for some queries. That's because what we are going to do here 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 think it might be better to 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, ---- Geng Lei
