Hi
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;
set work_mem to '256kB';
explain analyze select name from t1 union select name from t2;
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
set enable_sort to off;
explain analyze select name from t1 union select name from t2;
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
In the execution plan, the width of the name field is estimated to be 38,
but the name fields in both tables are strings with a length of 10.
It seems that this is the reason why the optimizer incorrectly estimated the data size.
Regards
