Re: Please Help: PostgreSQL Query Optimizer

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

> 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." <anjankumar@cse.iitb.ac.in> 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.