Re: Performance regression caused by SubLink pull-up

Hi Richard,

Richard Guo <guofenglinux(at)gmail(dot)com> 于2026年8月12日周三 21:16写道：
>
> Regarding the fix, I think one way is to teach the planner to pull up
> sublinks that reference both sides of a new semijoin.
>
> Form 3: A SEMI JOIN (B JOIN C ON Pbc) ON Pabc

This transformation looks correct to me. In the original thread, I
naively thought that not incrementing sublevels_up in
contain_vars_of_level_walker() would work, but I now realize that was
incorrect.
I think your approach is the right way to handle this.

>
> Write R = |B JOIN C ON Pbc|, then the cost is:
>
> W = producing the join. At least R, since those rows must be
> emitted; b + c + R for a hash join, which is what a hashable
> Pbc gives.
> Cost3 = W + a Pabc gives a clean A | {B,C} split, so the RHS
> is built once and probed a times
>
> R can be as small as O(b), when the C side of Pbc is key-like, and as
> large as b*c when it is not, on the data alone. In the first case
> Cost3 = O(a+b+c).
>
> Which end R falls at depends on the selectivity of Pbc, and that isn't
> knowable this early, so the test is a heuristic.
>
> I ran this with the same data, and the Execution Time: 0.605 ms.
>
> Attached is the patch implementing this. Any thoughts?
>

I looked through the attached patch. The code logic looks good to me,
but I found some of the comments a bit hard to follow.

One is the comment for sublink_has_join_clause(). Perhaps something
like the following would be clearer:

"
/*
* Does this SubLink contain a clause that can join its subquery to rels
* after pull-up?
*
* Without one, pulling it up alongside rels makes only a cross product,
* which is worse than leaving it as a SubPlan; see
* pull_up_sublinks_qual_recurse.
*/
"
The other one is the comment for qual_has_join_clause(); I find the
phrase "and, levelsup levels up, nothing outside rels" a bit hard to
parse.
Perhaps this comment could describe the two conditions separately: the
clause must reference the sub-select (when checking a sub-select
qual), and the relations referenced at levelsup must be confined to
rels.

--
Thanks,
Tender Wang