From: sunw.fnst <936739278@qq.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: The issue of incorrect width estimation in UNION queries
Date: Wed, 24 Sep 2025 14:13:32 +0800
Message-ID: <tencent_34CF8017AB81944A4C08DD089D410AB6C306@qq.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: 936739278@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: The issue of incorrect width estimation in UNION queries
In-Reply-To: <tencent_34CF8017AB81944A4C08DD089D410AB6C306@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox