X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 713B456471 for ; Tue, 22 Mar 2005 11:56:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97235-09 for ; Tue, 22 Mar 2005 11:56:33 +0000 (GMT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 938E4569CB for ; Tue, 22 Mar 2005 11:56:31 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1DDhzq-000I1E-Du; Tue, 22 Mar 2005 11:56:33 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id E9D19158FC; Tue, 22 Mar 2005 11:56:25 +0000 (GMT) Message-ID: <424007E8.1020700@archonet.com> Date: Tue, 22 Mar 2005 11:56:24 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Greg Stark Cc: Josh Berkus , Tambet Matiisen , pgsql-performance@postgresql.org Subject: Re: What about utility to calculate planner cost constants? References: <200503210951.06044.josh@agliodbs.com> <87acowcmut.fsf@stark.xeocode.com> In-Reply-To: <87acowcmut.fsf@stark.xeocode.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/438 X-Sequence-Number: 11143 Greg Stark wrote: > Josh Berkus writes: >>That's not really practical. There are currently 5 major query tuning >>parameters, not counting the memory adjustments which really can't be left >>out. You can't realistically test all combinations of 6 variables. > > I don't think it would be very hard at all actually. [snip] > What's needed is for the explain plan to total up the costing penalties > independently. So the result would be something like > > 1000 * random_page_cost + 101 * sequential_page_cost + 2000 * index_tuple_cost > + ... > > In other words a tuple like <1000,101,2000,...> > > And explain analyze would produce the above tuple along with the resulting > time. > > Some program would have to gather these values from the log or stats data and > gather them up into a large linear system and solve for values that minimize > the divergence from the observed times. You'd only need to log them if they diverged from expected anyway. That should result in fairly low activity pretty quickly (or we're wasting our time). Should they go to the stats collector rather than logs? > (Also, currently explain analyze has overhead that makes this impractical. > Ideally it could subtract out its overhead so the solutions would be accurate > enough to be useful) Don't we only need the top-level figures though? There's no need to record timings for each stage, just work completed. -- Richard Huxton Archonet Ltd