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 357A39DCA0F for ; Tue, 21 Mar 2006 07:58:35 -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 55096-02 for ; Tue, 21 Mar 2006 07:58:37 -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 BFA529DC885 for ; Tue, 21 Mar 2006 07:58:32 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 1AE795643A; Tue, 21 Mar 2006 05:58:36 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Tue, 21 Mar 2006 05:58:35 -0600 Date: Tue, 21 Mar 2006 05:58:35 -0600 From: "Jim C. Nasby" To: Guillaume Cottenceau Cc: pgsql-performance@postgresql.org Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants? Message-ID: <20060321115835.GN15742@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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87irq8kswd.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::eSDO4gFO99xSDSbV:000000001gMv X-Hashcash: 1:20:060321:pgsql-performance@postgresql.org::bke6OE+D5l2/obyh:00000 0000000000000000000000003lK0 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/388 X-Sequence-Number: 17825 On Tue, Mar 21, 2006 at 11:13:06AM +0100, Guillaume Cottenceau wrote: > "Jim C. Nasby" 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). 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); 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 -- 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