From eaa159632d034a59ede1695f4b86098b1a372b48 Mon Sep 17 00:00:00 2001 From: David Rowley Date: Thu, 6 Nov 2025 16:34:55 +1300 Subject: [PATCH] Fix UNION planner estimate_num_groups with varno==0 03d40e4b5 added code to provide better row estimates for when a UNION query ended up only with a single child due to other children being found to be dummy rels. In that case, ordinarily it would be ok to call estimate_num_groups() on the targetlist of the only child path, however that's not safe to do if the UNION child is the result of some other set operation as we generate targetlists containing Vars with varno==0 for those, which estimate_num_groups() can't handle. This could lead to: ERROR: XX000: no relation entry for relid 0 Fix this by avoiding doing this when the only child is the result of another set operation. In that case we'll fall back on the assume-all-rows-are-unique method. Reported-by: Alexander Lakhin Author: David Rowley Discussion: https://postgr.es/m/cfbc99e5-9d44-4806-ba3c-f36b57a85e21@gmail.com --- src/backend/optimizer/prep/prepunion.c | 23 +++++++++++++---------- src/test/regress/expected/union.out | 22 ++++++++++++++++++++++ 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 -- 2.39.5