Re: Weird indices

Stephan Szabo wrote:
>
> On Mon, 19 Feb 2001, Joseph Shraibman wrote:
>
> > > Of course, if the 10113-match estimate is wildly off (as it was in this
> > > case), then the wrong plan may be chosen. But it IS NOT CORRECT to
> > > suppose that indexscans always beat seqscans. The planner's job would
> > > be a lot easier if that were true.
> >
> > Can't postgres do the index lookup first and find out there are only a
> > few tuples that might match?
>
> Well, theoretically the estimate is supposed to match reality. There are
> still some cases where there isn't enough information kept to allow that
> to be true (the case where there is a single very common non-NULL value is
> one such case).

But the index should give the upper bounds of the query and show that
this that this query is not going to return 10113 rows. It appeared to
work like this in my query. I don't really know what his database is
like or how many times it was updated since he last vacuumed, but it
seems that postgres should have been able to tell that query would have
returned much less than 10113 entries.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com