Re: Please Help: PostgreSQL Query Optimizer

Defaulat values of various parameters in PostgreSQL:

#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
#effective_cache_size = 1000 # typically 8KB each

Since sequential access is not significantly faster than random access in a MMDB, random_page_cost will be approximately same as sequential page fetch cost.

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.

Through googling, i found that Normal Disk has external data transfer rate of around 40MBps,
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
______________________________________________________________
A woman physician has made the statement that smoking is neither
physically defective nor morally degrading, and that nicotine, even
when indulged to in excess, is less harmful than excessive petting."
-- Purdue Exponent, Jan 16, 1925