Skip site navigation (1) Skip section navigation (2)

Query is not using index when it should

From: tomas(at)nocrew(dot)org (Tomas =?iso-8859-1?q?Sk=E4re?=)
To: pgsql-general(at)postgresql(dot)org
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Query is not using index when it should
Date: 2004-12-10 11:40:50
Message-ID: 80oeh2tm7x.fsf@junk.nocrew.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
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=12 and 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

Responses

pgsql-performance by date

Next:From: Mike RylanderDate: 2004-12-10 18:40:02
Subject: LIMIT causes SEQSCAN in subselect
Previous:From: Stacy WhiteDate: 2004-12-08 04:32:49
Subject: Re: Partitioned table performance

pgsql-sql by date

Next:From: Wei WengDate: 2004-12-10 19:24:56
Subject: Cast NULL into Timestamp?
Previous:From: Alex BeamishDate: 2004-12-09 20:19:56
Subject: parse error at or near "(" -- Huh???

pgsql-general by date

Next:From: Lada 'Ray' LostakDate: 2004-12-10 14:27:50
Subject: Sql performace - why soo long ?
Previous:From: Vikas KumawatDate: 2004-12-10 10:26:13
Subject: Regarding Postgres installation and administration on linux suse 9.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group