Not using index on VERY simple query
От | Jeffrey W. Baker |
---|---|
Тема | Not using index on VERY simple query |
Дата | |
Msg-id | Pine.LNX.4.33.0110081323480.1597-100000@desktop обсуждение исходный текст |
Ответы |
Re: Not using index on VERY simple query
|
Список | 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
В списке pgsql-general по дате отправления: