Re: why do optimizer parameters have to be set manually?

From: "Marinos J(dot) Yannikos" <mjy(at)geizhals(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: why do optimizer parameters have to be set manually?
Date: 2003-12-19 13:53:22
Message-ID: 3FE302D2.7080901@geizhals.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-12-19 14:38:14 Re: is it possible to get the optimizer to use indexes
Previous Message Dave Cramer 2003-12-19 09:50:12 Re: is it possible to get the optimizer to use indexes