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
