Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

From: "Anjan Kumar(dot) A(dot)" <anjankumar(at)cse(dot)iitb(dot)ac(dot)in>
To: pgsql-hackers(at)postgresql(dot)org
Cc: pgsql-chat(at)postgresql(dot)org, pgsql-benchmarks(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org
Subject: Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Date: 2005-12-11 22:06:01
Message-ID: Pine.LNX.4.61.0512120139080.6920@nsl-33.cse.iitb.ac.in (view raw or flat)
Thread:
Lists: pgsql-benchmarkspgsql-chatpgsql-docspgsql-hackers

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.

As every thing is present in Main Memory, we need to give approximately same cost to read/write to Main Memory and CPU Related operations.


But, in PostgreSQL  all costs are  scaled relative to a page fetch. 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.


Still, i want to confirm whether this approach is the correct one.





On Sun, 11 Dec 2005, Josh Berkus wrote:

> Anjan,
>
>> In our case we are reading pages from Main Memory File System, but not from
>> Disk. 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;
>
> This should be dramatically lowered.  It's supposed to represent the ratio of
> seek-fetches to seq scans on disk.  Since there's no disk, it should be a
> flat 1.0.   However, we are aware that there are flaws in our calculations
> involving random_page_cost, such that the actual number for a system where
> there is no disk cost would be lower than 1.0.   Your research will hopefully
> help us find these flaws.
>
>>          cpu_tuple_cost = 2;
>>          cpu_index_tuple_cost = 0.2;
>>          cpu_operator_cost = 0.05;
>
> I don't see why you're increasing the various cpu_* costs.  CPU costs would be
> unaffected by the database being in memory.   In general, I lower these by a
> divisor based on the cpu speed; for example, on a dual-opteron system I lower
> the defaults by /6.   However, that's completely unrelated to using an MMDB.
>
> So, other than random_page_cost, I don't know of other existing GUCs that
> would be directly related to using a disk/not using a disk.  How are you
> handling shared memory and work memory?
>
> I look forward to hearing more about your test!
>
>

-- 
Regards.

Anjan Kumar A.
MTech2,  Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
______________________________________________________________
Do not handicap your children by making their lives easy.
 		-- Robert Heinlein

In response to

Responses

pgsql-docs by date

Next:From: Carlos MorenoDate: 2005-12-11 22:55:09
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer

pgsql-hackers by date

Next:From: Carlos MorenoDate: 2005-12-11 22:55:09
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 21:26:22
Subject: Re: Reducing contention for the LockMgrLock

pgsql-chat by date

Next:From: Carlos MorenoDate: 2005-12-11 22:55:09
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer

pgsql-benchmarks by date

Next:From: Carlos MorenoDate: 2005-12-11 22:55:09
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group