diff options
| -rw-r--r-- | src/backend/optimizer/prep/prepunion.c | 23 | ||||
| -rw-r--r-- | src/test/regress/expected/union.out | 22 | ||||
| -rw-r--r-- | src/test/regress/sql/union.sql | 8 |
3 files changed, 43 insertions, 10 deletions
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 72539545656..f528f096a56 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -901,19 +901,22 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, double dNumGroups; bool can_sort = grouping_is_sortable(groupList); bool can_hash = grouping_is_hashable(groupList); + Path *first_path = linitial(cheapest_pathlist); - if (list_length(cheapest_pathlist) == 1) + /* + * Estimate the number of UNION output rows. In the case when only a + * single UNION child remains, we can use estimate_num_groups() on + * that child. We must be careful not to do this when that child is + * the result of some other set operation as the targetlist will + * contain Vars with varno==0, which estimate_num_groups() wouldn't + * like. + */ + if (list_length(cheapest_pathlist) == 1 && + first_path->parent->reloptkind != RELOPT_UPPER_REL) { - Path *path = linitial(cheapest_pathlist); - - /* - * In the case where only one union child remains due to the - * detection of one or more dummy union children, obtain an - * estimate on the surviving child directly. - */ dNumGroups = estimate_num_groups(root, - path->pathtarget->exprs, - path->rows, + first_path->pathtarget->exprs, + first_path->rows, NULL, NULL); } diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 4533967e84a..709c85f2294 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1355,6 +1355,28 @@ ORDER BY 1; Output: tenk1.two (5 rows) +-- Try a mixed setop case. Ensure the right-hand UNION child gets removed. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 t1 +EXCEPT +SELECT four FROM tenk1 t2 +UNION +SELECT ten FROM tenk1 dummy WHERE 1=2; + QUERY PLAN +----------------------------------------------- + Unique + Output: t1.two + -> Sort + Output: t1.two + Sort Key: t1.two + -> HashSetOp Except + Output: t1.two + -> Seq Scan on public.tenk1 t1 + Output: t1.two + -> Seq Scan on public.tenk1 t2 + Output: t2.four +(11 rows) + -- Test constraint exclusion of UNION ALL subqueries explain (costs off) SELECT * FROM diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 782cca23701..d0c70fafbea 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -523,6 +523,14 @@ EXCEPT ALL SELECT four FROM tenk1 WHERE 1=2 ORDER BY 1; +-- Try a mixed setop case. Ensure the right-hand UNION child gets removed. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 t1 +EXCEPT +SELECT four FROM tenk1 t2 +UNION +SELECT ten FROM tenk1 dummy WHERE 1=2; + -- Test constraint exclusion of UNION ALL subqueries explain (costs off) SELECT * FROM |