Wrong width of UNION statement

From: Kenichiro Tanaka <kenichirotanakapg(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Wrong width of UNION statement
Date: 2020-06-01 13:35:02
Message-ID: CALyBiZLxGtQ9T14-of5ueyXb=YZ+PCsyig=HWNDQe_JXLhV0Jw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello hackers

I think I found a bug about estimating width of table column when I
perform SQL with UNION statement.

I think table column width of UNION statement should be equal one of UNION ALL.
But they don't match.This can be reproduce it on HEAD.

See following example.

--CREATE TEST TABLE
DROP TABLE union_test;DROP TABLE union_test2;
CREATE TABLE union_test AS SELECT md5(g::text)::char(84) as data FROM
generate_series(1,1000) as g;
CREATE TABLE union_test2 AS SELECT md5(g::text)::char(84) as data FROM
generate_series(1,1000) as g;
ANALYZE union_test;
ANALYZE union_test2;

--width of union_test is 85.
SELECT * FROM union_test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on union_test (cost=0.00..25.00 rows=1000 width=85) (actual
time=0.591..1.166 rows=1000 loops=1)
Planning Time: 10.559 ms
Execution Time: 2.974 ms
(3 rows)

--width of UNION is 340(wrong)
EXPLAIN ANALYZE
SELECT * FROM union_test
UNION
SELECT * FROM union_test2;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=85.00..105.00 rows=2000 width=*340*) (actual
time=3.323..3.672 rows=1000 loops=1)
Group Key: union_test.data
Peak Memory Usage: 369 kB
-> Append (cost=0.00..80.00 rows=2000 width=340) (actual
time=0.021..1.191 rows=2000 loops=1)
-> Seq Scan on union_test (cost=0.00..25.00 rows=1000 width=85)
(actual time=0.019..0.393 rows=1000 loops=1)
-> Seq Scan on union_test2 (cost=0.00..25.00 rows=1000 width=85)
(actual time=0.027..0.302 rows=1000 loops=1)
Planning Time: 0.096 ms
Execution Time: 3.908 ms
(8 rows)

--width of UNION ALL is 85
EXPLAIN ANALYZE
SELECT * FROM union_test
UNION ALL
SELECT * FROM union_test2;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..60.00 rows=2000 width=85) (actual
time=0.017..1.187 rows=2000 loops=1)
-> Seq Scan on union_test (cost=0.00..25.00 rows=1000 width=85)
(actual time=0.017..0.251 rows=1000 loops=1)
-> Seq Scan on union_test2 (cost=0.00..25.00 rows=1000 width=85)
(actual time=0.018..0.401 rows=1000 loops=1)
Planning Time: 0.213 ms
Execution Time: 1.444 ms
(5 rows)

I think this is bug, is it right?

Regards
Kenichiro Tanaka.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Khandekar 2020-06-01 13:59:39 Re: Inlining of couple of functions in pl_exec.c improves performance
Previous Message Andrew Dunstan 2020-06-01 13:23:24 Re: OpenSSL 3.0.0 compatibility