Re: indexes not being used!

Tom Lane wrote:
> Jodi Kanter <jkanter@virginia.edu> writes:
>
>>I apologize if this is the wrong list. I have posted explain analyzes below.
>
>
> Given the small size of the tables, I think the planner is doing the
> right thing not to use indexes.  Seqscans are probably less I/O until
> the tables get a lot bigger.

If you want to convince yourself of this, try EXPLAIN ANALYZE on your
query with enable_seqscan set to off (discard the first result however,
because in either case some caching will happen). For example:

regression=# select * from foo;
  f0 |  f1  |  f2
----+------+-------
   1 | cat1 |  1.21
   2 | cat1 |  1.24
   3 | cat1 |  1.18
   4 | cat1 |  1.26
   5 | cat1 |  1.15
   6 | cat2 |  1.15
   7 | cat2 |  1.26
   8 | cat2 |  1.32
   9 | cat2 |   1.3
  10 | cat3 | 3.333
(10 rows)

regression=# VACUUM ANALYZE;
VACUUM
regression=# create index foo_idx on foo(f0);
CREATE INDEX

regression=# explain analyze select * from foo where f0 = 1;
                                          QUERY PLAN
--------------------------------------------------------------------------------------------
  Seq Scan on foo  (cost=0.00..1.12 rows=2 width=20) (actual
time=0.03..0.05 rows=1 loops=1)
    Filter: (f0 = 1)
  Total runtime: 0.22 msec
(3 rows)
regression=# set enable_seqscan to off;
SET
regression=# explain analyze select * from foo where f0 = 1;
                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------
  Index Scan using foo_idx on foo  (cost=0.00..3.01 rows=2 width=20)
(actual time=0.06..0.07 rows=1 loops=1)
    Index Cond: (f0 = 1)
  Total runtime: 0.20 msec
(3 rows)

regression=# set enable_seqscan to on;
SET
regression=# explain analyze select * from foo where f0 = 1;
                                          QUERY PLAN
--------------------------------------------------------------------------------------------
  Seq Scan on foo  (cost=0.00..1.12 rows=2 width=20) (actual
time=0.03..0.05 rows=1 loops=1)
    Filter: (f0 = 1)
  Total runtime: 0.14 msec
(3 rows)

HTH,

Joe