Обсуждение: Union View Optimization

    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

"Cyril VELTER" <cyril.velter@libertysurf.fr> writes:
>     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 ?

This is on the "to look at" list.  It's not immediately clear to me
whether there are any restrictions on when the system can safely make
such a transformation, nor whether there are cases where it would be
a pessimization rather than an optimization.

            regards, tom lane