X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 193CD9DC809
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Mar 2006 13:46:00 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 22080-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Mar 2006 13:45:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id DC1FA9DC869
	for <pgsql-performance@postgresql.org>;
	Tue, 21 Mar 2006 13:45:57 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id EF23056431; Tue, 21 Mar 2006 11:45:57 -0600 (CST)
Received: (hashcash-sendmail, from uid 1001); Tue, 21 Mar 2006 11:45:57 -0600
Date: Tue, 21 Mar 2006 11:45:57 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost,
	advices to tweak cost constants?
Message-ID: <20060321174556.GF15742@pervasive.com>
References: <87ek11gz69.fsf@meuh.mnc.lan>
	<1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com>
	<87hd5tmt1z.fsf@meuh.mnc.lan>
	<20060321095700.GX15742@pervasive.com>
	<87irq8kswd.fsf@meuh.mnc.lan>
	<20060321115835.GN15742@pervasive.com>
	<8764m8kjrl.fsf@meuh.mnc.lan>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <8764m8kjrl.fsf@meuh.mnc.lan>
X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Hashcash: 1:20:060321:gc@mnc.ch::ls//wXg2Byj/hVUh:000000007Dtf
X-Hashcash: 
 1:20:060321:pgsql-performance@postgresql.org::rYFZ9d7pz4M9tCLe:00000
	0000000000000000000000005djy
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108]
X-Spam-Score: 0.108
X-Spam-Level: 
X-Archive-Number: 200603/413
X-Sequence-Number: 17850

On Tue, Mar 21, 2006 at 02:30:22PM +0100, Guillaume Cottenceau wrote:
> "Jim C. Nasby" <jnasby 'at' pervasive.com> writes:
> 
> > If you feel like running some tests, you need to change
> > 
> >     run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
> > 
> > in src/backend/optimizer/path/costsize.c to something like
> > 
> >     run_cost += max_IO_cost + abs(indexCorrelation) * (min_IO_cost - max_IO_cost);
> 
> Short after the beginning of a discussion about planner
> associating too high cost for index scan, I'm suggested to change
> source-code.. I'm already frightened about the near future :)

Well, this is mostly because I've just never gotten around to following
up on this.

> > That might not produce a perfect cost estimate, but I'll wager that it
> > will be substantially better than what's in there now. FYI, see also
> > http://archives.postgresql.org/pgsql-performance/2005-04/msg00669.php
> 
> Sad that Tom didn't share his thoughts about your cost algorithm
> question in this message.

See above. :)

If someone comes up with a before and after comparison showing that the
change makes the estimator more accurate I'm sure the code will be
changed in short order. The nice thing about this case is that basically
any PostgreSQL user can do the heavy lifting, instead of relying on the
primary contributors for a change.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461