Making planner skip hard-coded view values? - Mailing list pgsql-general

From François Beausoleil
Subject Making planner skip hard-coded view values?
Date
Msg-id 5C66AC8E-7693-4AC4-AE7A-1A9663DACE31@teksol.info
Whole thread Raw
Responses Re: Making planner skip hard-coded view values?
List pgsql-general
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 
Attachment

pgsql-general by date:

Previous
From: Natalie Wenz
Date:
Subject: selecting for type cast failures
Next
From: Scott Marlowe
Date:
Subject: Re: Replication stopped on 9.0.2 after making change to conf file