Thread: 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