Hi,
when using views that aggregate tuples during a join operation, indices on
the tables used in the view are ignored.
This problem appears consistently with 7.3.1, 7.3.2 and today's CVS
version (2003-05-01).
Please see below for a full database session typescript.
Is there any way to convince / change the optimizer to use the indices?
Do you know of any work-around for this situation? The sequence scans kill
the performance of these queries.
Thank you,
best wishes, Mike
**** shell: generate test data *******************************************
perl -e 'for (0 .. 99999) { print "item:$_\t$_\n"; }' > a.pg
perl -e 'for (100000 .. 199999) { print "item:$_\t$_\n"; }' > b.pg
perl -e 'for (0 .. 199999) { print "other:item:",$_*2,"\titem:$_\n"; }' > c.pg
**** psql ***************************************************************
create table a (urn varchar(64) primary key, i integer);
create table b (urn varchar(64) primary key, i integer);
create table c (urn varchar(64) primary key, to_urn varchar(64));
\copy a from a.pg
\copy b from b.pg
\copy c from c.pg
create index c_to_urn_idx on c (to_urn);
vacuum full analyze a;
vacuum full analyze b;
vacuum full analyze c;
create view v (urn, i) as select urn, i from a union all select urn, i
from b;
-- ok (uses indices)
explain select * from v where urn = 'item:3456';
-- not ok, ignores indices
explain select v.urn, c.urn from v, c where c.to_urn = v.urn and c.urn = 'other:item:6912';
**** typescript of psql session ******************************************
$ perl -e 'for (0 .. 99999) { print "item:$_\t$_\n"; }' > a.pg
$ perl -e 'for (100000 .. 199999) { print "item:$_\t$_\n"; }' > b.pg
$ perl -e 'for (0 .. 199999) { print "other:item:",$_*2,"\titem:$_\n"; }' > c.pg
$ createdb test
CREATE DATABASE
$ psql test
Welcome to psql 7.3.1, the PostgreSQL interactive terminal.
test=# create table a (urn varchar(64) primary key, i integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE TABLE
test=# create table b (urn varchar(64) primary key, i integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' for table 'b'
CREATE TABLE
test=# create table c (urn varchar(64) primary key, to_urn varchar(64));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'c_pkey' for table 'c'
CREATE TABLE
test=# \copy a from a.pg
\.
test=# \copy b from b.pg
\.
test=# \copy c from c.pg
\.
test=# create index c_to_urn_idx on c (to_urn);
CREATE INDEX
test=# vacuum full analyze a;
VACUUM
test=# vacuum full analyze b;
VACUUM
test=# vacuum full analyze c;
VACUUM
test=# create view v (urn, i) as select urn, i from a union all select urn, i fr om b;
CREATE VIEW
test=# -- ok (uses indices)
test=# explain select * from v where urn = 'item:3456'; QUERY PLAN
-----------------------------------------------------------------------------------Subquery Scan v (cost=0.00..6.33
rows=2width=19) -> Append (cost=0.00..6.33 rows=2 width=19) -> Subquery Scan "*SELECT* 1" (cost=0.00..3.31
rows=1width=17) -> Index Scan using a_pkey on a (cost=0.00..3.31 rows=1 width=17)
IndexCond: (urn = 'item:3456'::character varying) -> Subquery Scan "*SELECT* 2" (cost=0.00..3.01 rows=1
width=19) -> Index Scan using b_pkey on b (cost=0.00..3.01 rows=1 width=19) Index
Cond:(urn = 'item:3456'::character varying)
(8 rows)
test=# -- not ok, ignores indices
test=# explain select v.urn, c.urn from v, c where c.to_urn = v.urn
test-# and c.urn = 'other:item:6912'; QUERY PLAN
-----------------------------------------------------------------------------------------Hash Join (cost=5.95..4279.96
rows=1width=66) Hash Cond: ("outer".urn = "inner".to_urn) -> Subquery Scan v (cost=0.00..3274.00 rows=200000
width=19) -> Append (cost=0.00..3274.00 rows=200000 width=19) -> Subquery Scan "*SELECT* 1"
(cost=0.00..1637.00rows=100000 width=17) -> Seq Scan on a (cost=0.00..1637.00 rows=100000
width=17) -> Subquery Scan "*SELECT* 2" (cost=0.00..1637.00 rows=100000 width=19) ->
SeqScan on b (cost=0.00..1637.00 rows=100000 width=19) -> Hash (cost=5.94..5.94 rows=1 width=34) -> Index
Scanusing c_pkey on c (cost=0.00..5.94 rows=1 width=34) Index Cond: (urn = 'other:item:6912'::character
varying)
(11 rows)
**** that's all, folks ***************************************************
--
Life is like a fire. DI Michael Wildpaner
Flames which the passer-by forgets. Ph.D. Student
Ashes which the wind scatters.
A man lived. -- Omar Khayyam