From: | "Anjan Kumar(dot) A(dot)" <anjankumar(at)cse(dot)iitb(dot)ac(dot)in> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Please Help: PostgreSQL Query Optimizer |
Date: | 2006-01-12 23:05:45 |
Message-ID: | Pine.LNX.4.61.0601130435010.24920@nsl-22.cse.iitb.ac.in |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-benchmarks pgsql-chat pgsql-docs pgsql-hackers |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Anjan Kumar. A. | 2006-01-15 23:35:04 | Problem with the Planner |
Previous Message | Josh Berkus | 2005-12-13 06:47:21 | Re: [HACKERS] Please Help: PostgreSQL Query Optimizer |
From | Date | Subject | |
---|---|---|---|
Next Message | Anjan Kumar. A. | 2006-01-15 23:35:04 | Problem with the Planner |
Previous Message | Josh Berkus | 2005-12-13 06:47:21 | Re: [HACKERS] Please Help: PostgreSQL Query Optimizer |
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-01-14 00:06:40 | Re: Stable function being evaluated more than once in a single query |
Previous Message | David Fetter | 2006-01-12 22:17:27 | Example for UPDATE FROM with correllation |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-12 23:12:27 | Re: Libpq COPY optimization |
Previous Message | David Fetter | 2006-01-12 22:46:04 | Contrib Schemas |