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
