Re: More tablescanning fun

On Wed, Apr 30, 2003 at 04:14:46PM +0200, Manfred Koizar wrote:
> On Fri, 25 Apr 2003 09:38:01 -0500, "Jim C. Nasby" <jim@nasby.net>
> wrote:
> >In this case, the interpolation can't be at fault, because correlation
> >is 1 (unless the interpolation is backwards, but that doesn't appear to
> >be the case).
>
> But your index has 3 columns which causes the index correlation to be
> assumed as 1/3.  So the interpolation uses 1/9 (correlation squared)
> and you get a cost estimation that almost equals the upper bound.

Hmm... interesting... maybe it would also be a good idea to expand
ANALYZE so that it will analyze actual index correlation? ie: in this
case, it would notice that the index on project_id, id, date is highly
correlated, across all 3 columns.

Supporting something close to a real clustered index would also work as
well, since the optimizer would treat that case differently (essentially
as a combination between an index scan but doing a seq. scan within each
page).
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"