pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <decibel@decibel.org>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dave Held <dave.held@arrayservicesgrp.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sort and index
Date: Wed, 11 May 2005 16:15:16 -0500
Message-ID: <20050511211516.GX31103@decibel.org> (raw)
In-Reply-To: <ssa481td7d7jpl31rqdf9m0c64iui6qqsf@email.aon.at>
References: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184B6@asg002.asg.local>
	<20050420004234.GX58835@decibel.org>
	<19631.1113966086@sss.pgh.pa.us>
	<20050420034041.GC58835@decibel.org>
	<20050423015404.GV58835@decibel.org>
	<4269.1114222086@sss.pgh.pa.us>
	<20050423030002.GY58835@decibel.org>
	<6536.1114232440@sss.pgh.pa.us>
	<20050424220146.GN58835@decibel.org>
	<ssa481td7d7jpl31rqdf9m0c64iui6qqsf@email.aon.at>

First, I've got some updated numbers up at
http://stats.distributed.net/~decibel/

timing2.log shows that the planner actually under-estimates an index
scan by several orders of magnitude. Granted, random_page_cost is set to
an unrealistic 1.1 (otherwise I can't force the index scan), but that
alone isn't enough to explain the difference.

On Wed, May 11, 2005 at 05:59:10PM +0200, Manfred Koizar wrote:
> On Sun, 24 Apr 2005 17:01:46 -0500, "Jim C. Nasby" <decibel@decibel.org>
> wrote:
> >> >> Feel free to propose better cost equations.
> 
> I did.  More than once.
> 
> >estimated index scan cost for (project_id, id, date) is
> >0.00..100117429.34 while the estimate for work_units is
> >0.00..103168408.62; almost no difference,
> 
> ~3%
> 
> > even though project_id correlation is .657
> 
> This is divided by the number of index columns, so the index correlation
> is estimated to be 0.219.

That seems like a pretty bad assumption to make.

Is there any eta on having statistics for multi-column indexes?

> >you'll see that the cost of the index scan is way overestimated. Looking
> >at the code, the runcost is calculated as
> >
> >    run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
> >
> >where csquared is indexCorrelation^2. Why is indexCorrelation squared?
> >The comments say a linear interpolation between min_IO and max_IO is
> >used, but ISTM that if it was linear then instead of csquared,
> >indexCorrelation would just be used.
> 
> In my tests I got much more plausible results with
> 
> 	1 - (1 - abs(correlation))^2

What's the theory behind that?

And I'd still like to know why correlation squared is used.

> Jim, are you willing to experiment with one or two small patches of
> mine?  What version of Postgres are you running?

It depends on the patches, since this is a production machine. Currently
it's running 7.4.*mumble*, though I need to upgrade to 8, which I was
intending to do via slony. Perhaps the best thing would be for me to get
that setup and we can experiment against version 8.0.3.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



view thread (17+ messages)  latest in thread

Message-ID: <20050511211516.GX31103@decibel.org>
Permalink:  ../20050511211516.GX31103@decibel.org/
Also on:    postgresql.org/message-id/20050511211516.GX31103@decibel.org

 · 

reply

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-performance@postgresql.org
  Cc: decibel@decibel.org, mkoi-pg@aon.at, tgl@sss.pgh.pa.us, dave.held@arrayservicesgrp.com
  Subject: Re: Sort and index
  In-Reply-To: <20050511211516.GX31103@decibel.org>

* 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