Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5E12D64FC36 for ; Fri, 2 Jan 2009 07:18:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 26050-05 for ; Fri, 2 Jan 2009 07:18:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by mail.postgresql.org (Postfix) with ESMTP id ADDE964FC2A for ; Fri, 2 Jan 2009 07:18:07 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id n02BHVxG002705; Fri, 2 Jan 2009 06:17:31 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id n02BHTMW002701; Fri, 2 Jan 2009 06:17:29 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Fri, 2 Jan 2009 06:17:29 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Tom Lane cc: Robert Haas , Gregory Stark , Kevin Grittner , "jd@commandprompt.com" , Josh Berkus , pgsql-hackers@postgresql.org Subject: Re: benchmarking the query planner In-Reply-To: <9565.1229018968@sss.pgh.pa.us> Message-ID: References: <603c8f070812061019r5aca3aajeaa5493b11c5feff@mail.gmail.com> <9897C4AF-6D47-4F44-AD3D-F5A82AC392DB@enterprisedb.com> <87ljuq4rmk.fsf@oxford.xeocode.com> <603c8f070812092025k6325e0fcud001b205a507a634@mail.gmail.com> <4313.1228931932@sss.pgh.pa.us> <603c8f070812101828p1bd7df49yb8c916adedb49fe1@mail.gmail.com> <9565.1229018968@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/46 X-Sequence-Number: 130690 On Thu, 11 Dec 2008, Tom Lane wrote: > On the whole I think we have some evidence here to say that upping the > default value of default_stats_target to 100 wouldn't be out of line, > but 1000 definitely is. Comments? Circling back to where this started from now that the discussion has slowed down here, the original tuning suggestions Josh threw out were: > Mixed: > default_statistics_target = 100 > DW: > default_statistics_target = 400 I think this data suggesting serious quadratic behavior doesn't kick in until much higher than original theorized supports increasing the mixed number to 100. And if the database default goes to there I think that's even better. Compared to the current default of 10, that makes for a 4X increase in planning time for Robert's original test case, and a 5.8X increase in the more difficult array test Tom came up with. The only question really open in my mind is whether 400 is still considered too high for a DW application. Relative to 100, Tom's numbers suggest that further 4X increase ends up turning into as much as a 8X increase in planning time. Given the context of a DW application, where bad plans are really expensive, that suggestion of 400 seems confirmed to be sane now to me. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD