Index not being used properly - Mailing list pgsql-general

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@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