Indices arent being used

Hi guys

Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to
choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a
small problem which I hope could be resolved here.

I'm trying to speed up this query:

select count(*) from actvars, prodlevel where
actvars.product_level=prodlevel.code_level and
prodlevel.division_level='OY3S5LAPALL6';

ACTVARS is a fact table of aproximatly 16 million rows, PRODLEVEL has 20700
rows. Both have btree indices.

I executed the query and it took me almost half an hour to execute! Running the
same query on MySQL the result came 6 seconds after. As you can see there is a
large differences between execution times.

After running an explain:

Aggregate (cost=3123459.62..3123459.62 rows=1 width=32)
-> Merge Join (cost=3021564.79..3119827.17 rows=1452981 width=32)
Merge Cond: ("outer".product_level = "inner".code_level)
-> Sort (cost=3020875.00..3060938.81 rows=16025523 width=16)
Sort Key: actvars.product_level
-> Seq Scan on actvars (cost=0.00..365711.23 rows=16025523
width=16)
-> Sort (cost=689.79..694.48 rows=1877 width=16)
Sort Key: prodlevel.code_level
-> Seq Scan on prodlevel (cost=0.00..587.75 rows=1877 width=16)
Filter: (division_level = 'OY3S5LAPALL6'::bpchar)

I found that the indices werent being used.

The database has been vacuumed and analyze has been executed.

I tried disabling the seqscan, so as to force index usage. The planner uses
index scans but the query stil takes a very long time to execute.

Any suggestions on resolving this would would be appreciated.

P.S: Im running PostgrSQL
7.3.2

---------------------------------------------
This message was sent using Endymion MailMan.
http://www.endymion.com/products/mailman/