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

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