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