Hi, not sure if this bug is already known, so sorry if it is ;)
Filtering a Union on constant custom type:
-------------------------------------------------------------------------------
DROP VIEW v;
DROP TABLE a;
DROP TABLE b;
DROP TYPE IF EXISTS mytype;
CREATE TYPE mytype AS ENUM ('x', 'y');
CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
CREATE VIEW v AS SELECT t, id FROM (SELECT 'x'::mytype t, id FROM a
UNION ALL SELECT 'y'::mytype t, id FROM b) c;
EXPLAIN SELECT * FROM v WHERE t = 'y';
PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
Result (cost=0.00..34.00 rows=2400 width=8)
-> Append (cost=0.00..34.00 rows=2400 width=8)
-> Seq Scan on b (cost=0.00..34.00 rows=2400 width=8)
PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit and also
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
Result (cost=0.00..80.00 rows=24 width=8)
-> Append (cost=0.00..80.00 rows=24 width=8)
-> Seq Scan on a (cost=0.00..40.00 rows=12 width=8)
Filter: ('x'::mytype = 'y'::mytype)
-> Seq Scan on b (cost=0.00..40.00 rows=12 width=8)
Filter: ('y'::mytype = 'y'::mytype)
Regression: Expected result: That of 8.4.10
=====================================
Additionally, it is interesting that it does not work with text (not
tested with other types such as integers):
-------------------------------------------------------------------------------
DROP VIEW v;
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
CREATE VIEW v AS SELECT t, id FROM (SELECT 'x' t, id FROM a UNION ALL
SELECT 'y' t, id FROM b) c;
EXPLAIN SELECT * FROM v WHERE t = 'y';
PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
Subquery Scan d (cost=0.00..1107744.06 rows=150000 width=36)
Filter: (d.t = 'y'::text)
-> Append (cost=0.00..732744.04 rows=30000002 width=4)
-> Subquery Scan "*SELECT* 1" (cost=0.00..244247.54
rows=9999977 width=4)
-> Seq Scan on a (cost=0.00..144247.77 rows=9999977
width=4)
-> Subquery Scan "*SELECT* 2" (cost=0.00..244248.96
rows=10000048 width=4)
-> Seq Scan on b (cost=0.00..144248.48 rows=10000048
width=4)
-> Subquery Scan "*SELECT* 3" (cost=0.00..244247.54
rows=9999977 width=4)
-> Seq Scan on c (cost=0.00..144247.77 rows=9999977
width=4)
PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit and also
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
Result (cost=0.00..80.00 rows=24 width=36)
-> Append (cost=0.00..80.00 rows=24 width=36)
-> Seq Scan on a (cost=0.00..40.00 rows=12 width=36)
Filter: ('x'::text = 'y'::text)
-> Seq Scan on b (cost=0.00..40.00 rows=12 width=36)
Filter: ('y'::text = 'y'::text)
Expected: Query optimizer should discard the scan as filter cannot be
satisfied.
Filter: ('x'::text = 'y'::text)
================================================================================
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
