Union View Optimization
От | Cyril VELTER |
---|---|
Тема | Union View Optimization |
Дата | |
Msg-id | 00a901c162f3$f8439c40$6901a8c0@dev1 обсуждение исходный текст |
Ответы |
Re: Union View Optimization
|
Список | 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 по дате отправления: