greetings, data mutilators,
postgres 7.1, redhat 6.1
I've read a few recent threads about the planner not choosing the
indexes people would like, but my situation seems extraordinary
because I don't even get an index scan on the PRIMARY KEY, even when I
set enable_seqscan to off!
The query is as simple as select a from b where c = n;
I would be very grateful if anyone could explain (heh) to me why the
sequential scan is preferred because this exercise is not academic.
here are some details:
webco=# \d allocation
Table "allocation"
Attribute | Type | Modifier
----------------+--------------------------+----------
allocation_oid | bigint | not null
state | character varying |
location_oid | bigint |
agent_oid | bigint |
patron_oid | bigint |
creation_time | timestamp with time zone |
pickup_time | timestamp with time zone |
return_time | timestamp with time zone |
summary | character varying |
schedule_rule | character varying |
resource_rule | character varying |
Index: allocationpk
webco=# \d allocationpk
Index "allocationpk"
Attribute | Type
----------------+--------
allocation_oid | bigint
unique btree
webco=# explain select * from allocation where allocation_oid = 5;
NOTICE: QUERY PLAN:
Seq Scan on allocation (cost=0.00..2295.79 rows=1 width=104)
EXPLAIN
webco=# set enable_seqscan TO OFF;
SET VARIABLE
webco=# explain select * from allocation where allocation_oid = 5;
NOTICE: QUERY PLAN:
Seq Scan on allocation (cost=100000000.00..100002295.79 rows=1 width=104)
EXPLAIN
indeed.
-Lyn
