Union View Optimization - Mailing list pgsql-general

From Cyril VELTER
Subject Union View Optimization
Date
Msg-id 00a901c162f3$f8439c40$6901a8c0@dev1
Whole thread Raw
Responses Re: Union View Optimization
List pgsql-general
    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


pgsql-general by date:

Previous
From: "Oliver Elphick"
Date:
Subject: Re: PostgreSQL dirver?
Next
From: elwood@agouros.de (Konstantinos Agouros)
Date:
Subject: 7.1.3 on same machine with Solaris 2.6 vs Sol7