Union View Optimization

From: "Cyril VELTER" <cyril(dot)velter(at)libertysurf(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Union View Optimization
Date: 2001-11-01 16:40:46
Message-ID: 00a901c162f3$f8439c40$6901a8c0@dev1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general pgsql-hackers


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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruno Wolff III 2001-11-01 17:24:13 Referential integrity checking issue
Previous Message Thomas Yackel 2001-11-01 06:42:16 Re: user authentication crash by Erik Luke (20-08-2001;

Browse pgsql-general by date

  From Date Subject
Next Message Oliver Elphick 2001-11-01 17:02:40 Re: PostgreSQL dirver?
Previous Message tony 2001-11-01 16:01:38 Re: function gurus...

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-11-01 16:49:28 Re: Posgresql 7.2b1 crashes
Previous Message Stephan Szabo 2001-11-01 16:37:12 Re: Serious performance problem