Re: The issue of incorrect width estimation in UNION queries

David Rowley <dgrowleyml@gmail.com>

From: David Rowley <dgrowleyml@gmail.com>

To: "sunw.fnst" <936739278@qq.com>

Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Date: 2025-09-24T10:47:02Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Improve planner's width estimates for set operations
- 2cb49c609bd9 19 (unreleased) landed

Attachments

better_width_estimates_for_union_queries.patch (application/octet-stream) patch

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