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

> 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?

drf=# explain select horse from hraces where lower(horse) = 'little irish
nut';
NOTICE:  QUERY PLAN:

Seq Scan on hraces  (cost=0.00..208976.96 rows=75793 width=12)

EXPLAIN



> If you try to force an
> indexscan by doing "SET enable_seqscan TO off", does the EXPLAIN
> result change?

yes.

drf=# SET enable_seqscan TO off;
SET VARIABLE
drf=# explain select horse from hraces where lower(horse) = 'little irish
nut';
NOTICE:  QUERY PLAN:

Index Scan using hr_lhorse on hraces  (cost=0.00..223420.22 rows=75793
width=12)

EXPLAIN



>Have you VACUUM ANALYZEd the table recently?

Yes. Did it after I created the index.

What implication is there on leaving the enable_seqscan to Off?
Just as a test I am running VACUUM ANALYZE again

Another piece of info. I have another table where I also have an index on
lower(horse) and that works fine. That table though is only about 500,000
rows.

General question. After creating an index is it advasible to run a VACUUM
ANALYZE?