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
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; |
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... |
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 |