Обсуждение: Bug #809: View not using index

Philip Warner (pjw@rhyme.com.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
whichare 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

On Thu, 31 Oct 2002 pgsql-bugs@postgresql.org wrote:

> Philip Warner (pjw@rhyme.com.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.

Reasonably current 7.3 beta sources show it doing an index scan for a
sequence like the below on t1.

At 06:03 PM 31/10/2002 -0800, Stephan Szabo wrote:
>Reasonably current 7.3 beta sources show it doing an index scan for a
>sequence like the below on t1.

Thanks. Do you know if there are any patches for 7.2.1? Or if it was fixed
in 7.2.N?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

On Fri, 1 Nov 2002, Philip Warner wrote:

> At 06:03 PM 31/10/2002 -0800, Stephan Szabo wrote:
> >Reasonably current 7.3 beta sources show it doing an index scan for a
> >sequence like the below on t1.
>
> Thanks. Do you know if there are any patches for 7.2.1? Or if it was fixed
> in 7.2.N?

I don't know offhand (don't have the 7.2.x machine accessable from
home), but I would doubt it unless it was part of some other bug fix
where behavior was incorrect.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I don't know offhand (don't have the 7.2.x machine accessable from
> home), but I would doubt it unless it was part of some other bug fix
> where behavior was incorrect.

AFAIR, it was you that convinced me it's safe to push down qual clauses
into UNION/INTERSECT.  See discussion back around 1-Aug.  This is most
definitely not back-patched into any 7.2.* release...

            regards, tom lane