X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 28034D1B449 for ; Fri, 19 Dec 2003 13:52:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03274-04 for ; Fri, 19 Dec 2003 09:52:12 -0400 (AST) Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34]) by svr1.postgresql.org (Postfix) with ESMTP id 88178D1B43A for ; Fri, 19 Dec 2003 09:52:08 -0400 (AST) Received: from localhost (morework [127.0.0.1]) by morework.geizhals.at (Postfix) with ESMTP id 4A808524177; Fri, 19 Dec 2003 14:51:58 +0100 (CET) Received: from geizhals.at (chello080110242194.117.11.tuwien.teleweb.at [80.110.242.194]) by morework.geizhals.at (Postfix) with ESMTP id 0046C52416C; Fri, 19 Dec 2003 14:51:57 +0100 (CET) Message-ID: <3FE302D2.7080901@geizhals.at> Date: Fri, 19 Dec 2003 14:53:22 +0100 From: "Marinos J. Yannikos" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: why do optimizer parameters have to be set manually? References: <3FE1F5A1.6080908@geizhals.at> <22686.1071777380@sss.pgh.pa.us> In-Reply-To: <22686.1071777380@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS snapshot-20020222 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/274 X-Sequence-Number: 5134 Tom Lane wrote: > No, they are not that easy to determine. In particular I think the idea > of automatically feeding back error measurements is hopeless, because > you cannot tell which parameters are wrong. Isn't it just a matter of solving an equation system with n variables (n being the number of parameters), where each equation stands for the calculation of the run time of a particular query? I.e. something like this for a sequential scan over 1000 rows with e.g. 2 operators used per iteration that took 2 seconds (simplified so that the costs are actual timings and not relative costs to a base value): 1000 * sequential_scan_cost + 1000 * 2 * cpu_operator_cost = 2.0 seconds With a sufficient number of equations (not just n, since not all query plans use all the parameters) this system can be solved for the particular query mix that was used. E.g. with a second sequential scan over 2000 rows with 1 operator per iteration that took 3 seconds you can derive: sequential_scan_cost = 1ms cpu_operator_cost = 0.5ms This could probably be implemented with very little overhead compared to the actual run times of the queries. Regard, Marinos