Re: Correlation in cost_index()

On Fri, 8 Aug 2003 16:53:48 -0700, Sean Chittenden
<sean@chittenden.org> wrote:
>the problem with your patch was
>that it picked an index less often than the current code when there
>was low correlation.

Maybe bit rot?  What version did you apply the patch against?  Here is
a new version for Postgres 7.3.4:
http://www.pivot.at/pg/16d-correlation_734.diff

The only difference to the previous version is that
for (nKeys = 1; index->indexkeys[nKeys] != 0; nKeys++)

is now replaced with
for (nKeys = 1; nKeys < index->ncolumns; nKeys++)

Don't know whether the former just worked by chance when I tested the
7.3.2 version :-(.  Tests with 7.4Beta1 showed that index correlation
comes out too low with the old loop termination condition.  Anyway,
the latter version seems more robust.

In my tests the new index_cost_algorithms (1, 2, 3, 4) gave
consistently lower cost estimates than the old method (set
index_cost_algorithm = 0), except of course for correlations of 1.0 or
0.0, because in these border cases you get always min_IO_cost or
max_IO_cost, respectively.

Care to re-evaluate?  BTW, there's a version of the patch for 7.4Beta1
(http://www.pivot.at/pg/16d-correlation_74b1.diff) which also applies
cleanly against cvs snapshot from 2003-08-17.

ServusManfred