Re: bad estimates

On Fri, 29 Aug 2003, Ken Geis wrote:

> Ken Geis wrote:
> > I went through the code (7.4 beta2) that estimates the cost of an index
> > scan path. What I need to be sure of is that when running a query in
> > pgsql that uses only the columns that are in an index, the underlying
> > table need not be accessed. I know that Oracle does this.
>
> Thinking about it some more, it's obvious to me that a pgsql index scan
> must be accessing the underlying table even though all of the
> information needed is in the index itself. A linear scan of a 660M file
> should not take 55 minutes. I could confirm this with stats, but
> someone out there probably already knows the answer here.

Unfortunately not all the information needed is in the index. You can't
tell from the index alone currently whether or not the row is visible to
you. Adding said information would be possible but there are downsides to
that as well (there are some past discussions on the topic, but I'm too
lazy to look them up to give a link, check the archives ;) ).