Reinhard Max <max@suse.de> writes:
>> The major estimation error is evidently in this indexscan. What
>> statistics does pg_stats show for this table?
> See attached file.
Okay. It looks like foo.id has a pretty strong but not perfect
descending order (the correlation statistic is -0.563276). The
planner is evidently not rating that effect strongly enough.
If you look in cost_index (see approx. lines 270-340 in
src/backend/optimizer/path/costsize.c) you'll see that it computes
access cost estimates for both the perfectly sequential case and
the perfectly uncorrelated case, and then tries to interpolate
between them. I have reasonable faith in both of the endpoint
estimation methods, but very little in the interpolation equation ---
it was chosen on the spur of the moment and hasn't really been tested.
It might be interesting to replace csquared with just
fabs(indexCorrelation) to see if the results are better. Also, if you
cared to step through the code with a debugger or add some printout
statements, we could learn what the min and max costs are that it's
interpolating between; that'd be interesting to know as well.
regards, tom lane