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

Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: "Anjan Kumar(dot) A(dot)" <anjankumar(at)cse(dot)iitb(dot)ac(dot)in>, 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 19:26:18
Message-ID: 200512111126.18566.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-benchmarkspgsql-chatpgsql-docspgsql-hackers
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!

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-docs by date

Next:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 17:19:13
Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer
Previous:From: Hannu KrosingDate: 2005-12-11 18:33:57
Subject: Re: Reducing relation locking overhead

pgsql-chat by date

Next:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 17:19:13
Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer

pgsql-benchmarks by date

Next:From: Tom LaneDate: 2005-12-11 20:41:36
Subject: Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer
Previous:From: Tom LaneDate: 2005-12-11 17:19:13
Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer

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