Index not being used properly

Hi,

I have a *huge* problem. I have a table with indexes on but the moment
I have an OR in my SELECT query it seems to not use the appropriate
index.

oasis=> \d purchases
Table "public.purchases"
Column | Type |
Modifiers
-----------------+-----------------------
+-----------------------------------------------------------------
purch_id | integer | not null default
nextval('public.purchases_purch_id_seq'::text)
purch_period | date | not null
purch_ven_code | integer | not null
purch_st_code | smallint | not null
purch_co_code | smallint | not null
purch_art_id | integer |
purch_gr_number | character varying(20) |
purch_qty | integer |
purch_amt | numeric(14,2) | not null
Indexes:
"pk_purchases" primary key, btree (purch_id)
"idx_purch_art_id" btree (purch_art_id)
"idx_purch_co_code" btree (purch_co_code)
"idx_purch_co_ven" btree (purch_co_code, purch_ven_code)
"idx_purch_per_co_ven" btree (purch_period, purch_co_code,
purch_ven_code)
"idx_purch_period" btree (purch_period)
"idx_purch_st_code" btree (purch_st_code)
"idx_purch_ven_code" btree (purch_ven_code)
Foreign-key constraints:
"fk_pur_ref_article" FOREIGN KEY (purch_art_id) REFERENCES
article(art_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_pur_ref_ven" FOREIGN KEY (purch_ven_code, purch_co_code)
REFERENCES vendor(ven_code, ven_co_code) ON UPDATE RESTRICT ON DELETE
RESTRICT
"fk_pur_ref_store" FOREIGN KEY (purch_st_code, purch_co_code)
REFERENCES store(st_code, st_co_code) ON UPDATE RESTRICT ON DELETE
RESTRICT

Look at these SQL queries:

oasis=> explain analyze select sum(purch_amt) as total from purchases
where purch_period between '2002-05-01 00:00:00' and '2003-12-31
00:00:00' and purch_co_code = 1::smallint and purch_ven_code =
2::integer;
QUERY
PLAN
------------------------------------------------------------------------
-------------------------------------------------------------------
Aggregate (cost=9350.57..9350.57 rows=1 width=11) (actual
time=1.699..1.699 rows=1 loops=1)
-> Index Scan using idx_purch_co_ven on purchases
(cost=0.00..9342.99 rows=3032 width=11) (actual time=0.033..1.173
rows=381 loops=1)
Index Cond: ((purch_co_code = 1::smallint) AND (purch_ven_code
= 2))
Filter: ((purch_period >= '2002-05-01'::date) AND
(purch_period <= '2003-12-31'::date))
Total runtime: 1.755 ms
(5 rows)

Firstly, why is there a filter? Why is the whole index not used?
However, the moment I add more entries to the purch_ven_code field,
look what happens:

oasis=> explain analyze select sum(purch_amt) as total from purchases
where purch_period between '2002-05-01 00:00:00' and '2003-12-31
00:00:00' and purch_co_code = 1::smallint and purch_ven_code in
(2::integer,3::integer);

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----
Aggregate (cost=108705.81..108705.81 rows=1 width=11) (actual
time=14375.470..14375.471 rows=1 loops=1)
-> Index Scan using idx_purch_co_code on purchases
(cost=0.00..108690.66 rows=6060 width=11) (actual
time=298.853..14372.228 rows=381 loops=1)
Index Cond: (purch_co_code = 1::smallint)
Filter: ((purch_period >= '2002-05-01'::date) AND
(purch_period <= '2003-12-31'::date) AND ((purch_ven_code = 2) OR
(purch_ven_code = 3)))
Total runtime: 14375.572 ms
(5 rows)

Now only the purch_co_code is in the index condition, not the rest.
Sometimes this takes up to 10 minutes to execute. There are many
records in the DB - and yes I have run VACUUM ANALYZE before running
these queries.

Lastly, look at this query (which uses the index correctly):

oasis=> explain analyze select sum(purch_amt) as total from purchases
where purch_period = '2002-05-01 00:00:00' and purch_co_code =
1::smallint and purch_ven_code in (2);
QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------------------------------
Aggregate (cost=244.79..244.79 rows=1 width=11) (actual
time=76.592..76.593 rows=1 loops=1)
-> Index Scan using idx_purch_per_co_ven on purchases
(cost=0.00..244.62 rows=65 width=11) (actual time=76.508..76.549
rows=14 loops=1)
Index Cond: ((purch_period = '2002-05-01'::date) AND
(purch_co_code = 1::smallint) AND (purch_ven_code = 2))
Total runtime: 76.653 ms
(4 rows)

oasis=> select count(purch_period) from purchases;
count
----------
13956180
(1 row)

I am using this PostgreSQL for Linux:

postgres(at)waldopcl postgresql $ psql --version
psql (PostgreSQL) 7.4.1
contains support for command-line editing

Please can you help? This is for a mission critical system that is
close to its deadline, so I need help urgently please!

Regards,
Waldo Nell
Systems Engineer
PWN Consulting