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

Francisco Reyes <lists(at)natserv(dot)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