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 571259DC84A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Mar 2006 06:13:15 -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 24272-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Mar 2006 06:13:16 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.mnc.ch (62-2-77-205.business.cablecom.ch [62.2.77.205])
	by postgresql.org (Postfix) with ESMTP id D15E29DC81D
	for <pgsql-performance@postgresql.org>;
	Tue, 21 Mar 2006 06:13:12 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.mnc.ch (Postfix) with ESMTP id 21577A82F9;
	Tue, 21 Mar 2006 11:13:20 +0100 (CET)
Received: from mail.mnc.ch ([127.0.0.1])
	by localhost (mail.test.lan [127.0.0.1]) (amavisd-new,
	port 10025) with ESMTP
	id 10313-05; Tue, 21 Mar 2006 11:13:18 +0100 (CET)
Received: from meuh.mnc.lan (62-2-77-204.business.cablecom.ch [62.2.77.204])
	by mail.mnc.ch (Postfix) with ESMTP id B40A2A82B2;
	Tue, 21 Mar 2006 11:13:18 +0100 (CET)
Received: by meuh.mnc.lan (Postfix, from userid 1000)
	id 6E904183669; Tue, 21 Mar 2006 11:13:06 +0100 (CET)
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?
References: <87ek11gz69.fsf@meuh.mnc.lan>
	<1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com>
	<87hd5tmt1z.fsf@meuh.mnc.lan> <20060321095700.GX15742@pervasive.com>
X-Hashcash: 
 1:20:060321:jnasby@pervasive.com::NephfnUQY6UPWiWv:000000000000000000000000000000000000000003XZD
X-Hashcash: 
 1:20:060321:pgsql-performance@postgresql.org::EUi0K4R5aN4gsqyR:000000000000000000000000000006qmL
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 21 Mar 2006 11:13:06 +0100
In-Reply-To: <20060321095700.GX15742@pervasive.com>
Message-ID: <87irq8kswd.fsf@meuh.mnc.lan>
Lines: 31
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: amavisd-new at mnc.ch
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096]
X-Spam-Score: 0.096
X-Spam-Level: 
X-Archive-Number: 200603/373
X-Sequence-Number: 17810

"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