public inbox for [email protected]  

help / color / mirror / Atom feed

From: Anjan Kumar. A. <[email protected]>
To: [email protected]
Subject: Re: Please Help: PostgreSQL Query Optimizer
Date: Fri, 13 Jan 2006 04:35:45 +0530 (IST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>



> Through googling, i found that Normal Disk has external data transfer rate of 
> around 40MBps,
 						^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 			Does this includes, seek and rotational latency ?

> where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps.









>
> As we can see, the ratio between Disk and Main Memory data transfer rates is 
> around 50. Then, if we multiply all cpu_* paramters by 50, the resulting 
> values will be:
>
>        random_page_cost = 1;
>        cpu_tuple_cost = 0.5;
>        cpu_index_tuple_cost = 0.05;
>        cpu_operator_cost = 0.0125;
>
>
> Would it be a suitable approach ? We request all of u to give 
> comments/suggestions on this calcualations. Thanking You.
>
>
>
>
>
> On Sun, 11 Dec 2005, Tom Lane wrote:
>
>> [ trimming cc list to something sane ]
>> 
>> "Anjan Kumar. A." <[email protected]> writes:
>>>     In Main Memory DataBase(MMDB) entire database on the disk is loaded 
>>> on to the main memory during initial startup of the system.  There after 
>>> all the references are made to database on the main memory.  When the 
>>> system is going to shutdown, we will write back the database on  the main 
>>> memory to disk.  Here, for the sake of recovery we are writing log records 
>>> on to the disk  during the transaction execution.
>> 
>> Don't you get 99.9% of this for free with Postgres' normal behavior?
>> Just increase shared_buffers.
>> 
>>>   Can any one tell me the modifications needs to be incorporated to 
>>> PostgreSQL,  so that it considers only Processing Costs during 
>>> optimization of the Query.
>> 
>> Assuming that a page fetch costs zero is wrong even in an all-in-memory
>> environment.  So I don't see any reason you can't maintain the
>> convention that a page fetch costs 1.0 unit, and just adjust the other
>> cost parameters in the light of a different idea about what that
>> actually means.
>> 
>>> Will it be sufficient, if we change the  default values of above paramters 
>>> in "src/include/optimizer/cost.h and 
>>> src/backend/utils/misc/postgresql.conf.sample" as follows:
>> 
>>>          random_page_cost = 4;
>>>          cpu_tuple_cost = 2;
>>>          cpu_index_tuple_cost = 0.2;
>>>          cpu_operator_cost = 0.05;
>> 
>> You'd want random_page_cost = 1 since there is presumably no penalty for
>> random access in this context.  Also, I think you'd want
>> cpu_operator_cost a lot higher than that (maybe you dropped a decimal
>> place?  You scaled the others up by 200 but this one only by 20).
>> 
>> It's entirely possible that the ratios of the cpu_xxx_cost values
>> aren't very good and will need work.  In the past we've never had
>> occasion to study them very carefully, since they were only marginal
>> contributions anyway.
>> 
>> 			regards, tom lane
>> 
>
>

-- 
Regards.

Anjan Kumar A.
MTech2,  Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
______________________________________________________________
Bradley's Bromide:
 	If computers get too powerful, we can organize
 	them into a committee -- that will do them in.

view thread (17+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Please Help: PostgreSQL Query Optimizer
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox