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.purchas es"
Column | Type |
Modifiers
-----------------+-----------------------
+-----------------------------------------------------------------
purch_id | integer | not null default
nextval('public .purchases_purc h_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_purchas es" primary key, btree (purch_id)
"idx_purch_art_ id" btree (purch_art_id)
"idx_purch_co_c ode" btree (purch_co_code)
"idx_purch_co_v en" btree (purch_co_code, purch_ven_code)
"idx_purch_per_ co_ven" btree (purch_period, purch_co_code,
purch_ven_code)
"idx_purch_peri od" btree (purch_period)
"idx_purch_st_c ode" btree (purch_st_code)
"idx_purch_ven_ code" btree (purch_ven_code )
Foreign-key constraints:
"fk_pur_ref_art icle" 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_sto re" 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.6 99 rows=1 loops=1)
-> Index Scan using idx_purch_co_ve n on purchases
(cost=0.00..934 2.99 rows=3032 width=11) (actual time=0.033..1.1 73
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_co de on purchases
(cost=0.00..108 690.66 rows=6060 width=11) (actual
time=298.853..1 4372.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_cod e = 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..2 44.79 rows=1 width=11) (actual
time=76.592..76 .593 rows=1 loops=1)
-> Index Scan using idx_purch_per_c o_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_per iod) from purchases;
count
----------
13956180
(1 row)

I am using this PostgreSQL for Linux:

postgres@waldop cl 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****@telkoms a.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