Skip site navigation (1) Skip section navigation (2)

Re: planner with index scan cost way off actual cost, advices to tweak cost constants?

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants?
Date: 2006-03-21 17:45:57
Message-ID: 20060321174556.GF15742@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-03-21 17:49:03
Subject: Re: planner with index scan cost way off actual cost,
Previous:From: Scott MarloweDate: 2006-03-21 17:44:40
Subject: Re: Postmaster using only 4-5% CPU

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group