Re: [BUG] Remove self joins causes 'variable not found in subplan target lists' error

Richard Guo <guofenglinux@gmail.com> writes:
> Instead of repeatedly calling make_pathkeys_for_sortclauses to detect
> redundant expressions, I'm wondering if we could introduce a function
> that detects whether a given expression is known equal to a constant
> by the EquivalenceClass machinery.  This function should not be too
> costly, as we'd only need to examine ECs that contain pseudoconstants.

I did consider that, but rejected it.  I kind of like the fact that
the v3 patch gets rid of duplicated columns-to-be-made-unique.
Yes, repeatedly doing make_pathkeys_for_sortclauses is a bit grotty,
because it's O(N^2) in the number of columns-to-be-made-unique ...
but realistically, how often is that ever more than a couple?
In the common case where the semijoin has only one join column,
the patch adds basically zero work, which is nice too.

> We could then use this function to remove expressions that are known
> constant from semi_rhs_exprs.  And if we find that all expressions
> in semi_rhs_exprs are known constant (the second loose end you
> mentioned), we can give up building unique paths and fall back to a
> traditional JOIN_SEMI.

Yeah, I was thinking we could just use the paths of the existing
rel, but really this case means that we'd need to de-dup down
to a single row.  We could maybe do something involving plastering
LIMIT 1 on top of each input path, but it's such a weird and
probably-useless-in-the-real-world case that I don't think it's
worth expending effort on.  Failing outright seems fine.

> Another question we need to consider is how to fix this error in v18,
> where it seems we'll need to remove redundant expressions during
> createplan.c.

Ugh ... I forgot you just refactored all that code.

I wonder if the right answer in v18 is to back out a3179ab69.
Not fixing that performance regression for another year would
be mildly annoying; but AFAIR we've still had only the one
complaint, so it seems like it's not hurting many people.
And we are getting mighty late in the release calendar to be
inventing new stuff in v18.

            regards, tom lane