Avoid unnecessary wrapping for more complex expressions When pulling up a subquery that is under an outer join, if the subquery's target list contains a strict expression that uses a subquery variable, it's okay to pull up the expression without wrapping it in a PlaceHolderVar: if the subquery variable is forced to NULL by the outer join, the expression result will come out as NULL too.
If the strict expression does not contain any subquery variables, the current code always wraps it in a PlaceHolderVar. While this is not incorrect, the analysis could be tighter: if the strict expression contains any variables of rels that are under the same lowest nulling outer join as the subquery, we can also avoid wrapping it. This is safe because if the subquery variable is forced to NULL by the outer join, the variables of rels that are under the same lowest nulling outer join will also be forced to NULL, resulting in the expression evaluating to NULL as well. Therefore, it's not necessary to force the expression to be evaluated below the outer join. It could be beneficial to get rid of such PHVs because they could imply lateral dependencies, which force us to resort to nestloop joins. This patch checks if the lateral references in the strict expression contain any variables of rels under the same lowest nulling outer join as the subquery, and avoids wrapping the expression in that case. This is fundamentally a generalization of the optimizations for bare Vars and PHVs introduced in commit f64ec81a8. No backpatch as this could result in plan changes. Author: Richard Guo Discussion: https://postgr.es/m/CAMbWs4_ENtfRdLaM_bXAxiKRYO7DmwDBDG4_2=vtdi0mjp-...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/60be3f9f0a64a071822c0711f69c7b5f6467d84f Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 67 ++++++++++++++----- src/test/regress/expected/subselect.out | 103 ++++++++++++++++++++++++++++++ src/test/regress/sql/subselect.sql | 33 ++++++++++ 3 files changed, 186 insertions(+), 17 deletions(-)
