Hi all,
I have a view similar to this (regression at end):
CREATE VIEW published_reports AS
SELECT true AS aired, *
FROM published_aired_reports
UNION ALL
SELECT false AS aired, *
FROM published_unaired_reports;
Given that view definition, I expected a WHERE clause with the hard-coded value to ignore one of the tables:
SELECT COUNT(*) FROM published_reports WHERE aired;
This is on 9.1.3, but may have changed later.
Looking at EXPLAIN ANALYZE, I see seq scans for both tables, which was unexpected. I expected a truncated plan, where
oneof the seq scans was simply absent, since the view specifies the value that's present.
Did that change in 9.2 / 9.3? Is this behavior expected?
Bye!
François Beausoleil
$ psql regression
create table aired(n int primary key);
create table unaired(n int primary key);
insert into aired select * from generate_series(1, 1000000, 1);
insert into unaired select * from generate_series(1000001, 2000000, 1);
vacuum analyze aired;
vacuum analyze unaired;
create view all_rows as select true as aired, * from aired union all select false as aired, * from unaired;
explain analyze select count(*) from all_rows where aired;
explain analyze select count(*) from all_rows where aired is true;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=31350.00..31350.01 rows=1 width=0) (actual time=385.338..385.338 rows=1 loops=1)
-> Append (cost=0.00..28850.00 rows=1000000 width=0) (actual time=0.006..290.811 rows=1000000 loops=1)
-> Seq Scan on aired (cost=0.00..14425.00 rows=500000 width=0) (actual time=0.006..128.439 rows=1000000
loops=1)
Filter: true
-> Seq Scan on unaired (cost=0.00..14425.00 rows=500000 width=0) (actual time=51.019..51.019 rows=0 loops=1)
Filter: false
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=31350.00..31350.01 rows=1 width=0) (actual time=498.833..498.833 rows=1 loops=1)
-> Append (cost=0.00..28850.00 rows=1000000 width=0) (actual time=0.010..376.716 rows=1000000 loops=1)
-> Seq Scan on aired (cost=0.00..14425.00 rows=500000 width=0) (actual time=0.010..159.688 rows=1000000
loops=1)
Filter: (true IS TRUE)
-> Seq Scan on unaired (cost=0.00..14425.00 rows=500000 width=0) (actual time=52.926..52.926 rows=0 loops=1)
Filter: (false IS TRUE)
select version();
version
--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.3 on x86_64-apple-darwin10.8.0, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build
5664),64-bit
