From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
Date: Fri, 09 May 2003 09:30:12 -0400
Message-ID: <249.1052487012@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.44.0305091618280.7304-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0305091618280.7304-100000@matrix.gatewaynet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, achill@matrix.gatewaynet.com, pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
In-Reply-To: <249.1052487012@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox