Обсуждение: The issue of incorrect width estimation in UNION queries

On Wed, 24 Sept 2025 at 18:13, sunw.fnst <936739278@qq.com> wrote:
>    ->  Append  (cost=0.00..4082.00 rows=200000 width=38) (actual time=0.016..39.050 rows=200000 loops=1)
>          ->  Seq Scan on t1  (cost=0.00..1541.00 rows=100000 width=11) (actual time=0.015..10.056 rows=100000 loo
> ps=1)
>          ->  Seq Scan on t2  (cost=0.00..1541.00 rows=100000 width=11) (actual time=0.018..10.654 rows=100000 loo
> ps=1)

This happens because in generate_union_paths() -> create_pathtarget()
-> set_pathtarget_cost_width() in get_expr_width(), the varno for the
union's RelOptInfo is 0, which results in using the get_typavgwidth()
result.

set_append_rel_size() does a better job of this for the UNION ALL code
path. Maybe we can copy the relevant parts of that.

Also, I think if we're doing this, then it'd be a master-only fix.
Changing this could result in plan changes in the back-branches, which
we normally try to avoid.  The attached patch is against master. I
need to spend a bit longer on this as generate_nonunion_paths() might
need the same treatment. I've just run out of time for tonight.

David

On Wed, 24 Sept 2025 at 22:47, David Rowley <dgrowleyml@gmail.com> wrote:
> The attached patch is against master. I
> need to spend a bit longer on this as generate_nonunion_paths() might
> need the same treatment. I've just run out of time for tonight.

Here's an updated patch which includes adjusting the width estimate
for generate_nonunion_paths() too.

I gave a bit of thought to how that should work for EXCEPT and
INTERSECT and concluded that for EXCEPT, we should take the width
estimate for the left-hand child, as no rows from the right-child will
be used. For INTERSECT, I couldn't think of anything better than
taking the weighted average of both children, the same as UNION.  Of
course, only the rows which exist in both will make the final result,
but I don't see any way to do anything smart with a single width
estimate for each child.

David

On Fri, 26 Sept 2025 at 09:42, David Rowley <dgrowleyml@gmail.com> wrote:
> Here's an updated patch which includes adjusting the width estimate
> for generate_nonunion_paths() too.
>
> I gave a bit of thought to how that should work for EXCEPT and
> INTERSECT and concluded that for EXCEPT, we should take the width
> estimate for the left-hand child, as no rows from the right-child will
> be used. For INTERSECT, I couldn't think of anything better than
> taking the weighted average of both children, the same as UNION.  Of
> course, only the rows which exist in both will make the final result,
> but I don't see any way to do anything smart with a single width
> estimate for each child.

I looked at this again today. All seems fairly trivial and not too
dissimilar to what happens in set_append_rel_size(). I did consider if
it should be done by having some special varno for setops and then
have get_expr_width() to something better, but I don't see how that
would work exactly. In any case, that would be way more complex than
what I did.

With some more adjustments to the comments, I pushed the v1 patch.

David