pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (6+ messages)  latest in thread

Message-ID: <3FE302D2.7080901@geizhals.at>
Permalink:  ../3FE302D2.7080901@geizhals.at/
Also on:    postgresql.org/message-id/3FE302D2.7080901@geizhals.at

 · 

reply

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