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 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." <anjankumar(at)cse(dot)iitb(dot)ac(dot)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.