From: Marinos J. Yannikos <mjy@geizhals.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: why do optimizer parameters have to be set manually?
Date: Fri, 19 Dec 2003 14:53:22 +0100
Message-ID: <3FE302D2.7080901@geizhals.at> (raw)
In-Reply-To: <22686.1071777380@sss.pgh.pa.us>
References: <3FE1F5A1.6080908@geizhals.at>
<22686.1071777380@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mjy@geizhals.at, tgl@sss.pgh.pa.us
Subject: Re: why do optimizer parameters have to be set manually?
In-Reply-To: <3FE302D2.7080901@geizhals.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox