join over 'view ... union all' ignores indices

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=2 width=19)
-> Append (cost=0.00..6.33 rows=2 width=19)
-> Subquery Scan "*SELECT* 1" (cost=0.00..3.31 rows=1 width=17)
-> Index Scan using a_pkey on a (cost=0.00..3.31 rows=1 width=17)
Index Cond: (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=1 width=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.00 rows=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)
-> Seq Scan on b (cost=0.00..1637.00 rows=100000 width=19)
-> Hash (cost=5.94..5.94 rows=1 width=34)
-> Index Scan using 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