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 F13058B9BCA for ; Tue, 1 Feb 2005 05:06:46 +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 07018-04 for ; Tue, 1 Feb 2005 05:06:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 12F918B9CE7 for ; Tue, 1 Feb 2005 05:06:38 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1156RUO010115; Tue, 1 Feb 2005 00:06:27 -0500 (EST) To: "Jim C. Nasby" Cc: josh@agliodbs.com, Markus Schaber , Christopher Kings-Lynne , PostgreSQL Performance List Subject: Re: Automagic tuning In-reply-to: <20050201045211.GB32356@decibel.org> References: <20040727151531.4f60b5b0@kingfisher.intern.logi-track.com> <41066485.7080502@familyhealth.com.au> <41FE4692.6080104@logi-track.com> <200501311209.31827.josh@agliodbs.com> <3900.1107203172@sss.pgh.pa.us> <20050201045211.GB32356@decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Mon, 31 Jan 2005 22:52:11 -0600" Date: Tue, 01 Feb 2005 00:06:27 -0500 Message-ID: <10114.1107234387@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/5 X-Sequence-Number: 10334 "Jim C. Nasby" writes: > On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote: >> Preferably a whole lot of queries. All the measurement techniques I can >> think of are going to have a great deal of noise, so you shouldn't >> twiddle these cost settings based on just a few examples. > Are there any examples of how you can take numbers from pg_stats_* or > explain analize and turn them into configuration settings (such and > random page cost)? Well, the basic idea is to adjust random_page_cost so that the ratio of estimated cost to real elapsed time (as shown by EXPLAIN ANALYZE) is the same for seqscans and indexscans. What you have to watch out for is that the estimated cost model is oversimplified and doesn't take into account a lot of real-world factors, such as the activity of other concurrent processes. The reason for needing a whole lot of tests is essentially to try to average out the effects of those unmodeled factors, so that you have a number that makes sense within the planner's limited view of reality. regards, tom lane