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

On Wed, 7 Nov 2001, Tom Lane wrote:

> If you're doing test rather than production work, I'd advise using 7.2
> beta not 7.1.

It is test in the sense that I only do selective work, not in the sense
that what I am doing is not needed. For instance since I have access to
the database machine from home I just did some work today with the copy of
the data that I have done (I am home today).



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

Agree, but I am using the current "production" version.
Moreover, I use the ports system on FreeBSD so I am usually a bit behind
too since I commonly wait until there is a port available.

> In this particular case I believe the difficulty comes from the lack of
> any stats associated with the expression lower(horse)

As you mention this may be re-considered in the future. How could this be
better addressed? The only way I could work around this would be to change
the case of the column, but this would be a problem for some of the work
that I may need to do.

> we only keep stats on simple columns, not on functions of columns.
> (Perhaps that should be improved at some point, but not today.)

How much work would it be to consider functions?
In particular if the key of an existing index matches exactly a condition
on the where clause.

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

drf=# select count(*) from hraces;
  count
---------
 7579331
(1 row)


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

What if anything I can do to "help" the optimizer?

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

When is 7.2 due?