Re: Weird indices

On Tue, 20 Feb 2001, Joseph Shraibman wrote:

> > > 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.
> >
> > The problem is that the stats that are kept are woefully inadequate for
> > these cases. The problem is basically that IIRC it's taking the
> > most common value's # of appearances and using a fraction of that
> > as the estimate for any other value. This is not a really meaningful
> > estimate of the number of rows to return and there's been talk of how
> > to add more detailed statistics to make this number more meaningful.
> > And btree indexes really aren't all that good for getting the exact
> > number of entries - you'd be better off keeping that number somewhere
> > else, but MVCC would probably make that difficult, since I'd guess
> > that the different versions of rows would each have index entries
> > and not all of them apply to your transaction - which is why I think
> > it goes to the heap to test for visibility.
>
> You didn't address my point. The point was that an explain shows that
> it is using the index to get an upper bounds, so why isn't it using
> that?

Where are you seeing something that says the estimator/planner using the
index to get an upper bound? The estimator shouldn't be asking either the
index or the heap for anything, it should be working entirely with the
statistics that were generated from vacuum.