when doing some works with views, I faced the following problem :
consider the following schema :
create table A (v1 int4,v2 int4);
create table B (v1 int4,v2 int4);
create view C as select v1,v2 from A union all select v1,v2 from B;
populate A and B with several thousands records
select v1 from c where v2=1000; give the following plan :
Subquery Scan c (cost=0.00..4544.12 rows=294912 width=8)
-> Append (cost=0.00..4544.12 rows=294912 width=8)
-> Subquery Scan *SELECT* 1 (cost=0.00..252.84 rows=16384 width=8)
-> Seq Scan on a (cost=0.00..252.84 rows=16384 width=8)
-> Subquery Scan *SELECT* 2 (cost=0.00..4291.28 rows=278528
width=8)
-> Seq Scan on b (cost=0.00..4291.28 rows=278528 width=8)
select v1 from a where v2=5 union all select v1 from b where v2=1000;
give the following plan :
Append (cost=0.00..217.88 rows=83 width=4)
-> Subquery Scan *SELECT* 1 (cost=0.00..2.02 rows=1 width=4)
-> Index Scan using idx1 on a (cost=0.00..2.02 rows=1 width=4)
-> Subquery Scan *SELECT* 2 (cost=0.00..215.86 rows=82 width=4)
-> Index Scan using idx2 on b (cost=0.00..215.86 rows=82 width=4)
Is there a way for the optimizer to move the view "where" clause in the
elementary union queries in order to use an index scan instead of the Seq
scan ?
I'm using 7.1.3
cyril
