join over 'view ... union all' ignores indices - Mailing list pgsql-hackers

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