Re: optimizer tuning/forcing correct index use

On Tuesday 19 March 2002 11:32 am, Tom Lane wrote:
<snip>
>
> Hmm. So the correlation of fill_ts with physical position is actually
> negative, according to the analyze results. Still, -0.54 represents
> rather strong correlation which would reduce the cost of the index scan.
>
> There was some discussion a couple weeks ago on the pgsql-bugs list about
> changing the equation the planner uses to estimate the effects of
> correlation order. Are you interested in experimenting? I previously
>
> said:
> : 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.

in costsize.c:334 I've changed:

csquared = indexCorrelation * indexCorrelation;

to:

csquared = fabs(indexCorrelation);

It looks like the estimated cost of the fill index scan is lower, but not
enough to change the plan.

I'll be happy to run more tests if you can think of other things to try.

-K