To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem. 
In-reply-to: <Pine.LNX.4.44.0305091618280.7304-100000@matrix.gatewaynet.com> 
References: <Pine.LNX.4.44.0305091618280.7304-100000@matrix.gatewaynet.com>
Comments: In-reply-to Achilleus Mantzios <achill@matrix.gatewaynet.com>
	message dated "Fri, 09 May 2003 16:22:23 -0200"
Date: Fri, 09 May 2003 09:30:12 -0400
Message-ID: <249.1052487012@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Fri, 9 May 2003, Tom Lane wrote:
>> Well, that's suggestive, isn't it?  What about the remaining columns?

> The index is defined as:

> status_all btree (assettable, assetidval, appname, apptblname, status, 
> isvalid)

> And correlations are:

>   attname   | correlation
> -------------+-------------
>  assettable  |           1
>  assetidval  |    0.125902
>  appname     |    0.942771
>  apptblname  |    0.928761
>  status      |    0.443405
>  isvalid     |    0.970531

Actually, thinking twice about it, I'm not sure if the correlations of
the righthand columns mean anything.  If the table were perfectly
ordered by the index, you'd expect righthand values to cycle through
their range for each lefthand value, and so they'd show low
correlations.

The fact that most of the columns show high correlation makes me think
that they are not independent --- is that right?

But anyway, I'd say that yes this table is probably quite well ordered
by the index.  You could just visually compare the results of

select * from tab

select * from tab
  order by assettable, assetidval, appname, apptblname, status, isvalid

to confirm this.

And that tells us where the problem is: the code is estimating a low
index correlation where it should be estimating a high one.  If you
don't mind running a nonstandard version of Postgres, you could try
making btcostestimate() in src/backend/utils/adt/selfuncs.c estimate
the indexCorrelation as just varCorrelation, instead of
varCorrelation / nKeys.  This is doubtless an overcorrection in the
other direction (which is why it hasn't been done in the official
sources) but it's probably better than what's there, at least for
your purposes.

			regards, tom lane