Re: indexes not being used!

Tom Lane wrote:
> Jodi Kanter <jkanter(at)virginia(dot)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