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 C93A656926 for ; Tue, 22 Mar 2005 04:42:59 +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 25748-09 for ; Tue, 22 Mar 2005 04:42:58 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id 95CAE56923 for ; Tue, 22 Mar 2005 04:42:57 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DDbEA-0005mY-00; Mon, 21 Mar 2005 23:42:50 -0500 To: Josh Berkus Cc: "Tambet Matiisen" , Subject: Re: What about utility to calculate planner cost constants? References: <200503210951.06044.josh@agliodbs.com> In-Reply-To: <200503210951.06044.josh@agliodbs.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 21 Mar 2005 23:42:50 -0500 Message-ID: <87acowcmut.fsf@stark.xeocode.com> Lines: 48 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.014 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/427 X-Sequence-Number: 11132 Josh Berkus writes: > > Otherwise it could just collect statements, run EXPLAIN ANALYZE for all > > of them and then play with planner cost constants to get the estimated > > values as close as possible to actual values. Something like Goal Seek > > in Excel, if you pardon my reference to MS :). > > 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. It's just a linear algebra problem with a bunch of independent variables and a system of equations. Solving for values for all of them is a straightforward problem. Of course in reality these variables aren't actually independent because the costing model isn't perfect. But that wouldn't be a problem, it would just reduce the accuracy of the results. 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. (costs penalties are currently normalized to sequential_page_cost being 1. That could be maintained, or it could be changed to be normalized to an expected 1ms.) (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) -- greg