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

Francisco Reyes <lists(at)natserv(dot)com> writes:
> We are a Foxpro and Oracle shop and I am jut starting to do tests with
> PostgreSQL to see its capability to handle at least part of our operation
> in the future.

If you're doing test rather than production work, I'd advise using 7.2
beta not 7.1. A lot of this stuff has changed due to the new planner
statistics work in 7.2. There's no good reason to base a decision on
whether you will use Postgres in the future on the state of the code
six months ago.

In this particular case I believe the difficulty comes from the lack of
any stats associated with the expression lower(horse) --- we only keep
stats on simple columns, not on functions of columns. (Perhaps that
should be improved at some point, but not today.) So you're getting
a default estimate about the number of retrieved rows, which in 7.1
happens to be 1% of the table rows --- I'll bet there are about 7.5M
rows in the table? For typical row sizes, this estimate is close to the
critical value that will make the planner switch over between seq and
indexscan plans, and you seem to be coming down on the wrong side of
the choice. Note that the estimated cost of the indexscan plan is just
a little larger than the estimate for seqscan.

7.2 is not materially smarter about functional index stats than 7.1,
but it does use a smaller default selectivity estimate (0.5%) which
I suspect will solve your problem.

regards, tom lane