Below, I've cut out a little dialogue between postgres and I. This
puzzles me. Why am I forced to a sequential scan if the query does
not include the indexed item? I'm sure that I've done this before
without these results... but the database is just so sticky about this
point.
loggrok=> create index devil_dupi on devil_dup(item);
CREATE
loggrok=> explain select id from devil_dup where item='/';
NOTICE: QUERY PLAN:
Seq Scan on devil_dup (cost=0.00 size=0 width=4)
EXPLAIN
loggrok=> vacuum;
VACUUM
loggrok=> explain select id from devil_dup where item='/';
NOTICE: QUERY PLAN:
Seq Scan on devil_dup (cost=0.00 size=0 width=4)
EXPLAIN
loggrok=> explain select id,item from devil_dup where item='/';
NOTICE: QUERY PLAN:
Index Scan on devil_dup (cost=0.00 size=0 width=16)
EXPLAIN
loggrok=> \d devil_dup
Table = devil_dup
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id | int4 | 4 |
| item | text | var |
+----------------------------------+----------------------------------+-------+
