Re: Index not used on single select, but used in join.

Francisco Reyes <lists@natserv.com> writes:
> I have a table, hraces,  with a column called "horse" and an index
> "lower(horse)".
> If I try:
> explain select horse from hraces where lower(horse) = 'little irish nut';
> The query doesn't use the index. It says it would do a sequential scan.

Seems to work for me:

regression=# create table hraces (horse text);
CREATE
regression=# create index hri on hraces(lower(horse));
CREATE
regression=# explain select horse from hraces where lower(horse) = 'little irish nut';
NOTICE:  QUERY PLAN:

Index Scan using hri on hraces  (cost=0.00..17.08 rows=5 width=32)

EXPLAIN

What does EXPLAIN actually show for you?  If you try to force an
indexscan by doing "SET enable_seqscan TO off", does the EXPLAIN
result change?  Have you VACUUM ANALYZEd the table recently?

            regards, tom lane