From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Date: Mon, 12 Dec 2005 22:47:21 -0800
User-Agent: KMail/1.8
Cc: "Anjan Kumar. A." <anjankumar@cse.iitb.ac.in>, pgsql-chat@postgresql.org,
	pgsql-benchmarks@postgresql.org, pgsql-docs@postgresql.org
References: <Pine.LNX.4.61.0512111610320.4525@nsl-33.cse.iitb.ac.in>
	<200512111126.18566.josh@agliodbs.com>
	<Pine.LNX.4.61.0512120139080.6920@nsl-33.cse.iitb.ac.in>
In-Reply-To: <Pine.LNX.4.61.0512120139080.6920@nsl-33.cse.iitb.ac.in>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200512122247.21560.josh@agliodbs.com>

Anjan,

> But, in PostgreSQL  all costs are  scaled relative to a page fetch. If we
> make both sequential_page_fetch_cost and random_page_cost to "1", then  we
> need to increase the various cpu_* paramters by multiplying the default
> values with appropriate  Scaling Factor.  Now, we need to determine this
> Scaling Factor.

I see, so you're saying that because the real cost of a page fetch has 
decreased, the CPU_* costs should increase proportionally because relative to 
the real costs of a page fetch they should be higher?  That makes a sort of 
sense.

The problem that you're going to run into is that currently we have no 
particularly reason to believe that the various cpu_* costs are more than 
very approximately correct as rules of thumb.  So I think you'd be a lot 
better off trying to come up with some means of computing the real cpu costs 
of each operation, rather than trying to calculate a multiple of numbers 
which may be wrong in the first place.

I know that someone on this list was working on a tool to digest EXPLAIN 
ANALYZE results and run statistics on them.   Can't remember who, though.

Also, I'm still curious on how you're handling shared_mem, work_mem and 
maintenance_mem.  You didn't answer last time.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco