From: | "Dave Menendez" <dave(at)sycamorehq(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Query planner quirk? |
Date: | 2002-04-13 23:17:59 |
Message-ID: | a9ae8j$1lfj$1@jupiter.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Egon Reetz | 2002-04-14 11:26:05 | Wrong date inserts |
Previous Message | Nigel J. Andrews | 2002-04-13 23:17:23 | Re: COPY performance |