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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to