Index not being used properly

[Waldo Nell]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Waldo Nell <pw****@telkomsa.net> writes:

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.

7.5 will be smarter about this, but in 7.4 and before you need to fool
with the column order of your indexes. The query structure is basically

WHERE col1 = const1 AND (col2 = const2 OR col2 = const3)

7.4 can turn this into a 2-column indexscan given an index on (col2,col1)
but not one on (col1,col2).

Your concern about the 3-column index can likewise be explained by
thinking about column order and how much of the index has to be scanned
for the given constraints. Generally you want equalities for the
leftmost index columns and ranges for only the rightmost columns in
order to keep the index scan range reasonable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings