X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BB4FA9DCD5A for ; Sun, 11 Dec 2005 13:19:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97408-10 for ; Sun, 11 Dec 2005 13:19:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 633B59DCD26 for ; Sun, 11 Dec 2005 13:19:22 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBBHJDSN017379; Sun, 11 Dec 2005 12:19:16 -0500 (EST) To: "Anjan Kumar. A." cc: pgsql-hackers@postgresql.org Subject: Re: [DOCS] Please Help: PostgreSQL Query Optimizer In-reply-to: References: Comments: In-reply-to "Anjan Kumar. A." message dated "Sun, 11 Dec 2005 16:15:26 +0530" Date: Sun, 11 Dec 2005 12:19:13 -0500 Message-ID: <17378.1134321553@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.003 required=5 tests=[AWL=0.003] X-Spam-Score: 0.003 X-Spam-Level: X-Archive-Number: 200512/563 X-Sequence-Number: 77411 [ trimming cc list to something sane ] "Anjan Kumar. A." 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