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 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
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 indexLong 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