Views with unions - Mailing list pgsql-performance

Hello,

While testing multi-select views I found some problems. Here are details. I have 3 tables and I created a view on them:

create view view123 as
select key, value from tab1 where key=1
union all
select key, value from tab2 where key=2
union all
select key, value from tab3 where key=3;

When querying with no conditions, I get plan:

test_db=# explain analyze select key, value from view123;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Subquery Scan view123  (cost=0.00..3.19 rows=15 width=11) (actual time=0.15..1.00 rows=15 loops=1)
   ->  Append  (cost=0.00..3.19 rows=15 width=11) (actual time=0.14..0.80 rows=15 loops=1)
         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1.06 rows=5 width=11) (actual time=0.13..0.30 rows=5 loops=1)
               ->  Seq Scan on tab1  (cost=0.00..1.06 rows=5 width=11) (actual time=0.11..0.22 rows=5 loops=1)
                     Filter: ("key" = 1)
         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..1.06 rows=5 width=11) (actual time=0.07..0.22 rows=5 loops=1)
               ->  Seq Scan on tab2  (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
                     Filter: ("key" = 2)
         ->  Subquery Scan "*SELECT* 3"  (cost=0.00..1.06 rows=5 width=11) (actual time=0.06..0.22 rows=5 loops=1)
               ->  Seq Scan on tab3  (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
                     Filter: ("key" = 3)
 Total runtime: 1.57 msec
(12 rows)

But with "key = 3":

test_db# explain analyze select key, value from view123 where key=3;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Subquery Scan view123  (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
   ->  Append  (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
               ->  Seq Scan on tab1  (cost=0.00..1.07 rows=1 width=11) (actual time=0.17..0.17 rows=0 loops=1)
                     Filter: (("key" = 1) AND ("key" = 3))
         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
               ->  Seq Scan on tab2  (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
                     Filter: (("key" = 2) AND ("key" = 3))
         ->  Subquery Scan "*SELECT* 3"  (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
               ->  Seq Scan on tab3  (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
                     Filter: (("key" = 3) AND ("key" = 3))
 Total runtime: 1.22 msec
(12 rows)

I would expect, that false filters, like (("key" = 1) AND ("key" = 3)) will make table full scan unnecessary. So I
expectedplan like: 

test_db# explain analyze select key, value from view123 where key=3;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Subquery Scan view123  (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
   ->  Append  (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
               ->  Result  (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
               ^^^^^^^^^^^ my change
                     Filter: (("key" = 1) AND ("key" = 3)) [always false]
                                                                                   ^^^^^^^^^^^ my change
         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
               ->  Result  (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
               ^^^^^^^^^^^ my change
                     Filter: (("key" = 2) AND ("key" = 3)) [always false]
                                                                                   ^^^^^^^^^^^ my change
         ->  Subquery Scan "*SELECT* 3"  (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
               ->  Seq Scan on tab3  (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
                     Filter: (("key" = 3) AND ("key" = 3))
 Total runtime: 1.22 msec
(12 rows)

No "Seq Scan" on tables where filter is false.

I realize that's how it works now, but:

a) is there any way to avoid such scans?
b) is it possible (or in TODO) to optimize for such cases?

Regards,

Mariusz Czułada