Not using index on VERY simple query - Mailing list pgsql-general

Hi there,

I know this is a FAW but I'm stumped.  I have a table object_attribute
with five columns:

id bigint not null default nextval('obj_attr_id_seq')
object bigint not null
name varchar(32) not null
value varchar(256)
date timestamp default now()

I have four indexes on this table: one each for id, object, name, and
value.  The index for id is unique, the others are non-unique.

I recently loaded about 800000 records into this table and followed with a
vacuum analyze.  There are about 160000 distinct values for object, a
handful of values for name, and the values for value are quite well
distributed.

Unfortunately for me, pg chooses NOT to EVER use the indexes for id and
object:

bb=# explain select * from object_attribute where id = 400000;
NOTICE:  QUERY PLAN:

Seq Scan on object_attribute  (cost=0.00..17196.42 rows=1 width=48)

EXPLAIN
bb=# explain select * from object_attribute where object = 20000;
NOTICE:  QUERY PLAN:

Seq Scan on object_attribute  (cost=0.00..17196.42 rows=1 width=48)

EXPLAIN

oh BOY HOWDY do those queries take a long time.  On this query, the object
index gets used:

                    SELECT oa1.object as id
                         , oa1.value  as type
                      FROM relation r
                         , relation_type rt
                         , object_attribute oa1
                     WHERE r.type = rt.id
                       AND rt.name = 'Aggregation'
                       AND r.r = 100000
                       AND r.l = oa1.object
                       AND oa1.name = 'ObjectType'

So, it seems like doing a join will invoke the index but just trying to
select the one row will not.  I need these single row simple selects to be
really, really fast.  What am I doing wrong?

BTW I have the same problem on my table "object" where the "id" column is
unique and not null, but pgsql WILL NOT use the index on simple statements
like:

bb=# explain select * from object where id = 60;
NOTICE:  QUERY PLAN:

Seq Scan on object  (cost=0.00..3432.07 rows=1 width=28)

EXPLAIN

But I swear thee are indexed:

bb=# \d object_pkey
Index "object_pkey"
 Attribute |  Type
-----------+--------
 id        | bigint
unique btree (primary key)

Hopeful,
Jeffrey Baker