Date: Fri, 9 May 2003 11:00:30 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem. 
In-Reply-To: <20336.1052404975@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.44.0305091044540.6198-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 8 May 2003, Tom Lane wrote:

> Andrew Sullivan <andrew@libertyrms.info> writes:
> > On Thu, May 08, 2003 at 10:48:52AM -0200, Achilleus Mantzios wrote:
> >> That is, we have a marginal decrease of the total cost
> >> for the index scan when random_page_cost = 1.9,
> >> whereas the "real cost" in the means of total runtime
> >> ranges from 218 msecs (seq scan) to 19 msecs (index scan).
> >> (is it sane?)
> 
> > You're right that the problem is the poor estimate of the cost of
> > that selection.
> 
> Are the table and index orders the same?  Oliver Elphick pointed out
> awhile ago that we're doing a bad job of index order correlation
> estimation for multi-column indexes --- the correlation is taken to
> be much lower than it should be.  But if the correlation is near
> zero anyway then this wouldn't explain Achilleus' problem...

Please correct me if i am wrong. (i think i probably am)
The correlation value in pg_statistc for a column refers to the 
correlation between
the ordering of a table's tuples and the ordering of that column.
(So it plays some role in determining the execution plan
if an index exists on that column. Also CLUSTERing a
single-column index on the table makes reordering
of the table according to that index, that is the ordering of that 
column).

Is that correct??


If so, how can one find the correlation between the ordering
of a table and a multicolumn index?


> 
> 			regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr