Query is not using index when it should
От | tomas@nocrew.org (Tomas Skäre) |
---|---|
Тема | Query is not using index when it should |
Дата | |
Msg-id | 80oeh2tm7x.fsf@junk.nocrew.org обсуждение исходный текст |
Ответы |
Re: Query is not using index when it should
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
Список | pgsql-general |
I tried to subscribe to pgsql-performance, but there seems to be something wrong with the majordomo, so I'm sending to general too, where I'm already subscribed. My problem is this, using PostgreSQL 7.4.6: I have a table that looks like this: Table "public.cjm_object" Column | Type | Modifiers -----------+-------------------+----------- timestamp | bigint | not null jobid | bigint | not null objectid | bigint | not null class | integer | not null field | character varying | not null data | bytea | Indexes: "cjm_object_pkey" primary key, btree ("timestamp", jobid, objectid, "class", field) "idx_cjm_object1" btree (objectid, "class", field) The table has 283465 rows, and the column combination (objectid,class,field) can occur several times. Doing a search with all columns in the pkey works, it uses the index: db=# explain analyze select * from cjm_object where timestamp=1102497954815296 and jobid=9 and objectid=4534 and class=12and field='paroid'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using cjm_object_pkey on cjm_object (cost=0.00..32.75 rows=1 width=54) (actual time=0.169..0.172 rows=1 loops=1) Index Cond: ("timestamp" = 1102497954815296::bigint) Filter: ((jobid = 9) AND (objectid = 4534) AND ("class" = 12) AND ((field)::text = 'paroid'::text)) Total runtime: 0.381 ms (4 rows) But when doing a search with objectid, class and field, it doesn't use the idx_cjm_object1 index. db=# explain analyze select * from cjm_object where objectid=4534 and class=12 and field='paroid'; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on cjm_object (cost=0.00..7987.83 rows=2 width=54) (actual time=21.660..475.664 rows=1 loops=1) Filter: ((objectid = 4534) AND ("class" = 12) AND ((field)::text = 'paroid'::text)) Total runtime: 475.815 ms (3 rows) I have tried to set enable_seqscan to false, but it gives the same result, except that the estimated cost is higher. I have also done a vacuum full analyze, and I have reindexed the database, the table and the index. I have dropped the index and recreated it, but it still gives the same result. Please, could someone give me a clue to this? Tomas
В списке pgsql-general по дате отправления: