Query planner quirk?
От | Dave Menendez |
---|---|
Тема | Query planner quirk? |
Дата | |
Msg-id | a9alrq$ja2$1@jupiter.hub.org обсуждение исходный текст |
Ответы |
Re: Query planner quirk?
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: