Skip site navigation (1) Skip section navigation (2)

Bug #809: View not using index

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #809: View not using index
Date: 2002-11-01 01:40:52
Message-ID: 20021101014052.4B6094758BD@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Philip Warner (pjw(at)rhyme(dot)com(dot)au) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
View not using index

Long Description
It seems that a UNION view fails to use underlying table indexes. This is a major pain when using subclassed tables which are updated frquently - even if the underlying tables are vacuumued regularly, the seq scan can take a very long time.

Sample Code

create table t1(id serial,name text);
insert into t1(name) values('fred');
insert into t1(name) select name || id from t1;
insert into t1(name) select name || id from t1;
...keep doing this until the table is big

create table t2(id serial,name text);

create view tv as select id,name from t1 union select id,name from t2;

vacuum full;
analyze;

explain select * from t1 where id=1;
NOTICE:  QUERY PLAN:

Index Scan using t1_id_key on t1  (cost=0.00..3.01 rows=1 width=34)

explain select * from tv where id=1;

NOTICE:  QUERY PLAN:

Subquery Scan tv  (cost=24029.48..24821.48 rows=15840 width=36)
  ->  Unique  (cost=24029.48..24821.48 rows=15840 width=36)
        ->  Sort  (cost=24029.48..24029.48 rows=158401 width=36)
              ->  Append  (cost=0.00..2739.00 rows=158401 width=36)
                    ->  Subquery Scan *SELECT* 1  (cost=0.00..2739.00 rows=158400 width=34)
                          ->  Seq Scan on t1  (cost=0.00..2739.00 rows=158400 width=34)
                    ->  Subquery Scan *SELECT* 2  (cost=0.00..0.00 rows=1 width=36)
                          ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=36)




No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2002-11-01 02:03:12
Subject: Re: Bug #809: View not using index
Previous:From: Florian WeimerDate: 2002-10-31 20:25:59
Subject: Re: Bug #807: Sequence currupted on recovery after kill -9

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group