From: Guillaume Cottenceau <gc@mnc.ch>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants?
Date: 21 Mar 2006 11:13:06 +0100
Message-ID: <87irq8kswd.fsf@meuh.mnc.lan> (raw)
In-Reply-To: <20060321095700.GX15742@pervasive.com>
References: <87ek11gz69.fsf@meuh.mnc.lan>
<1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com>
<87hd5tmt1z.fsf@meuh.mnc.lan>
<20060321095700.GX15742@pervasive.com>
"Jim C. Nasby" <jnasby 'at' pervasive.com> writes:
[...]
> > My point is that the planner's cost estimate is way above the
> > actual cost of the query, so the planner doesn't use the best
> > plan. Even if the index returns so much rows, actual cost of the
> > query is so that index scan (worst case, all disk cache flushed)
> > is still better than seq scan but the planner uses seq scan.
>
> Yes. The cost estimator for an index scan supposedly does a linear
> interpolation between a minimum cost and a maximum cost depending on the
> correlation of the first field in the index. The problem is that while
> the comment states it's a linear interpolation, the actual formula
> squares the correlation before interpolating. This means that unless the
> correlation is very high, you're going to get an unrealistically high
> cost for an index scan. I have data that supports this at
> http://stats.distributed.net/~decibel/, but I've never been able to get
> around to testing a patch to see if it improves things.
Interesting.
It would be nice to investigate the arguments behind the choice
you describe for the formula used to perform the interpolation. I
have absolutely no knowledge on pg internals so this is rather
new/fresh for me, I have no idea how smart that choice is (but
based on my general feeling about pg, I'm suspecting this is
actually smart but I am not smart enough to see why ;p).
--
Guillaume Cottenceau
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: gc@mnc.ch, jnasby@pervasive.com
Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants?
In-Reply-To: <87irq8kswd.fsf@meuh.mnc.lan>
* 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