Re: bad estimates

Sorry, all, to wipe out the context, but it was getting a little long.

Bruno Wolff III wrote:
> I am calling it quits for tonight, but will check back tomorrow
> to see how things turned out.

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.

The cost_index function is assuming that after finding an entry in the
index it will be looking it up in the underlying table. That table is
not well correlated to the index, so it is assuming (in the worst case)
a random page lookup for each of 17 million records! In my case, if the
underlying table is indeed not touched, the estimated cost is 1000 times
the real cost.

63388.624000 to scan the index
67406506.915595 to scan the index and load a random page for each entry

Ken