diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/optimizer/plan/analyzejoins.c | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 21 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 15 |
3 files changed, 49 insertions, 5 deletions
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 97f8839ff2c..2a913578eba 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -26,6 +26,7 @@ #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/planmain.h" +#include "optimizer/var.h" /* local functions */ static bool join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo); @@ -197,16 +198,23 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo) } /* - * Similarly check that the inner rel doesn't produce any PlaceHolderVars - * that will be used above the join. + * Similarly check that the inner rel isn't needed by any PlaceHolderVars + * that will be used above the join. We only need to fail if such a PHV + * actually references some inner-rel attributes; but the correct check + * for that is relatively expensive, so we first check against ph_eval_at, + * which must mention the inner rel if the PHV uses any inner-rel attrs. */ foreach(l, root->placeholder_list) { PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l); - if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids) && - !bms_is_subset(phinfo->ph_needed, joinrelids)) - return false; + if (bms_is_subset(phinfo->ph_needed, joinrelids)) + continue; /* PHV is not used above the join */ + if (!bms_overlap(phinfo->ph_eval_at, innerrel->relids)) + continue; /* it definitely doesn't reference innerrel */ + if (bms_overlap(pull_varnos((Node *) phinfo->ph_var), + innerrel->relids)) + return false; /* it does reference innerrel */ } /* diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 5299a10ac4e..795495b14d1 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2644,3 +2644,24 @@ SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0 (1 row) rollback; +-- another join removal bug: this is not optimizable, either +begin; +create temp table innertab (id int8 primary key, dat1 int8); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "innertab_pkey" for table "innertab" +insert into innertab values(123, 42); +SELECT * FROM + (SELECT 1 AS x) ss1 + LEFT JOIN + (SELECT q1, q2, COALESCE(dat1, q1) AS y + FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2 + ON true; + x | q1 | q2 | y +---+------------------+-------------------+------------------ + 1 | 123 | 456 | 123 + 1 | 123 | 4567890123456789 | 123 + 1 | 4567890123456789 | 123 | 42 + 1 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 1 | 4567890123456789 | -4567890123456789 | 4567890123456789 +(5 rows) + +rollback; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index ea237f977f1..b5971b60448 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -644,3 +644,18 @@ SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); rollback; + +-- another join removal bug: this is not optimizable, either +begin; + +create temp table innertab (id int8 primary key, dat1 int8); +insert into innertab values(123, 42); + +SELECT * FROM + (SELECT 1 AS x) ss1 + LEFT JOIN + (SELECT q1, q2, COALESCE(dat1, q1) AS y + FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2 + ON true; + +rollback; |