Re: Query planner quirk?

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"