The issue of incorrect width estimation in UNION queries

When I perform a union operation on the varchar fields of two tables,

the planner incorrectly selects the sort method. But the efficiency of hashagg will be higher.

This seems to be because the planner has miscalculated the length of the columns.

The test cases are as follows：

(version is PostgreSQL 17.4)

CREATE TABLE public.t1 (

    id integer primary key,

    name varchar(10)

);

INSERT INTO public.t1 (id, name)

SELECT  generate_series(1, 100000) AS id,'hgbnmjujty' AS name;

   

CREATE TABLE public.t2 (

    id integer primary key,

    name varchar(10)

);

INSERT INTO public.t2 (id, name)

SELECT  generate_series(1, 100000) AS id,'mnyknvkuma' AS name;

                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

------------

 Unique  (cost=38103.14..39103.14 rows=200000 width=38) (actual time=84.674..127.446 rows=2 loops=1)

   ->  Sort  (cost=38103.14..38603.14 rows=200000 width=38) (actual time=84.670..111.885 rows=200000 loops=1)

         Sort Key: t1.name

         Sort Method: external merge  Disk: 2976kB

         ->  Append  (cost=0.00..4082.00 rows=200000 width=38) (actual time=0.016..40.349 rows=200000 loops=1)

               ->  Seq Scan on t1  (cost=0.00..1541.00 rows=100000 width=11) (actual time=0.015..11.187 rows=1000

00 loops=1)

               ->  Seq Scan on t2  (cost=0.00..1541.00 rows=100000 width=11) (actual time=0.011..11.098 rows=1000

00 loops=1)

 Planning Time: 0.098 ms

 Execution Time: 127.757 ms

                                                      QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

------

 HashAggregate  (cost=37582.00..45832.00 rows=200000 width=38) (actual time=69.529..69.550 rows=2 loops=1)

   Group Key: t1.name

   Planned Partitions: 16  Batches: 1  Memory Usage: 121kB

   ->  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)

 Planning Time: 0.105 ms

 Execution Time: 69.606 ms