public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Anjan Kumar. A. <[email protected]>
Cc: [email protected]
Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer
Date: Sun, 11 Dec 2005 12:19:13 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

[ trimming cc list to something sane ]

"Anjan Kumar. A." <[email protected]> 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

view thread (17+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox