From: Manfred Koizar <mkoi-pg@aon.at>
To: Sean Chittenden <sean@chittenden.org>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Correlation in cost_index()
Date: Wed, 20 Aug 2003 19:57:12 +0200
Message-ID: <lo97kvkmjatb0ain1e7ad69ccslripcafv@4ax.com>
References: <n6cmpug13b9rk1srebjvhphg0lm8dou1kn@4ax.com>
	<29475.1033598929@sss.pgh.pa.us>
	<20030807204419.GJ94710@perrin.int.nxad.com>
	<9452.1060296663@sss.pgh.pa.us>
	<20030808180656.GL94710@perrin.int.nxad.com>
	<ruu7jvo5vnjl064rm327ndqml0pmvgdsfa@4ax.com>
	<20030808235348.GR94710@perrin.int.nxad.com>
In-Reply-To: <20030808235348.GR94710@perrin.int.nxad.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.

Servus
 Manfred