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 AA35956525 for ; Tue, 22 Mar 2005 17:53:16 +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 14353-06 for ; Tue, 22 Mar 2005 17:53:08 +0000 (GMT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id 2E8AA56A30 for ; Tue, 22 Mar 2005 17:53:07 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 4.42) id 1DDnYw-000Iyw-0Y; Tue, 22 Mar 2005 17:53:06 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 95296175D9; Tue, 22 Mar 2005 17:53:05 +0000 (GMT) Message-ID: <42405B81.20107@archonet.com> Date: Tue, 22 Mar 2005 17:53:05 +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> <424007E8.1020700@archonet.com> <87y8cfbqlf.fsf@stark.xeocode.com> In-Reply-To: <87y8cfbqlf.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/457 X-Sequence-Number: 11162 Greg Stark wrote: > Richard Huxton writes: > >>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? > > I think you need to log them all. Otherwise when you go to analyze the numbers > and come up with ideal values you're going to be basing your optimization on a > skewed subset. I can see your thinking, I must admit I was thinking of a more iterative process: estimate deltas, change config, check, repeat. I'm not convinced there are "ideal" values with a changing workload - for example, random_page_cost will presumably vary depending on how much contention there is for random seeks. Certainly, effective_cache size could vary. > I don't know whether the stats collector or the logs is better suited to this. > >>>(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. > > I guess you only need top level values. But you also might want some flag if > the row counts for any node were far off. In that case perhaps you would want > to discard the data point. I think you'd need to adjust work-estimates by actual-rows / estimated-rows. I _was_ trying to think of a clever way of using row mis-estimates to correct statistics automatically. This was triggered by the discussion a few weeks ago about hints to the planner and the recent talk about plan cacheing. Some sort of feedback loop so the planner would know its estimates were off should be a big win from an ease-of-use point of view. Didn't look easy to do though :-( -- Richard Huxton Archonet Ltd