Implicitely use the index.
explain SELECT d.bank_id, d.time_id, d.item_name, d.item_value FROM mbz_rpt_item_val d WHERE d.bank_id IN (277,535,739,1234,1330) and d.time_id IN ('P_6') and d.org_allow = 1 ORDER BY time_id,org_allow,bank_id ; Actually, just the time_id might be enough, depending on if any other indexes are out there. - brian On Sat, 13 Apr 2002, Dave Menendez wrote: > > I have a 2 million+ table, mbz_rpt_item_val: > > bank_id (integer) > item_name (character(16)) > org_allow (integer) > time_id (character(10)) > item_value(character varying(12)) > > and an index test_idx2: > > time_id > org_allow > bank_id > > > The query planner seems to choose a very dumb method when I tell it to > explain the following query: > > explain SELECT d.bank_id, d.time_id, d.item_name, d.item_value FROM > mbz_rpt_item_val d WHERE d.bank_id IN (277,535,739,1234,1330) and d.time_id > IN ('P_6') and d.org_allow = 1; > > Seq Scan on mbz_rpt_item_val d (cost=0.00..81988.51 rows=36 width=45) > > This query takes about 20 seconds. However, if I explicitly tell it not do > do sequential scans (SET ENABLE_SEQSCAN TO OFF), and explain it again, it > reluctantly decides to use the index even though it thinks the cost is > higher, but the query comes back in 2 seconds. I turn the sequential scan > back on, and it goes back to doing a sequential scan, taking 20 seconds. > > When I do the exact same query with a very large list of bank_id's (maybe > 500 or so), THEN it decides on its own to use the index, returning in about > 18 seconds, which is great. > > Any comments? I'm using postgres 7.2 and did a full vacuum analyze before > trying this. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber" ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly