From: | "Anjan Kumar(dot) A(dot)" <anjankumar(at)cse(dot)iitb(dot)ac(dot)in> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, pgsql-chat(at)postgresql(dot)org, pgsql-benchmarks(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org |
Cc: | Amirishetty Anjan Kumar <anjankumar(at)cse(dot)iitb(dot)ac(dot)in> |
Subject: | Please Help: PostgreSQL Query Optimizer |
Date: | 2005-12-11 10:45:26 |
Message-ID: | Pine.LNX.4.61.0512111610320.4525@nsl-33.cse.iitb.ac.in |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-benchmarks pgsql-chat pgsql-docs pgsql-hackers |
I'm working on a project, whose implementation deals with PostgreSQL. A brief description of the project is given below.
Project Description:
--------------------
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.
We want to implement MMDB by modifying PostgreSQL. We implemented our own Main Memory File System to store the primary copy of the database in main memory, and Modified the PostgreSQL to access the data in the Main Memory File System.
Now, in our implementation Disk access is completely avoided during normal transaction execution. So, we need to modify the Query Optimizer of PostgreSQL so that it wont consider disk related costs during calculation of Query Costs. Query Optimizer should try to minimize the Processing Cost. The criteria for cost can be taken as the number of tuples that have to read/write from main memory, number of comparisons, etc.
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.
In PostgreSQL, Path costs are measured in units of disk accesses. One sequential page fetch has cost 1. I think, in PostgreSQL following paramters are used in calculating the cost of the Query Path :
#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
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;
cpu_tuple_cost = 2;
cpu_index_tuple_cost = 0.2;
cpu_operator_cost = 0.05;
Please help us in this regard. I request all of you to give comments/suggestions on this. Waiting for your kind help.
--
Thanks.
Anjan Kumar A.
MTech2, Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
______________________________________________________________
May's Law:
The quality of correlation is inversly proportional to the density
of control. (The fewer the data points, the smoother the curves.)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-11 17:19:13 | Re: [DOCS] Please Help: PostgreSQL Query Optimizer |
Previous Message | Tomaz Borstnar | 2005-05-21 07:42:00 | Re: Error when try installing pgbench ? |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-11 17:19:13 | Re: [DOCS] Please Help: PostgreSQL Query Optimizer |
Previous Message | Robert Treat | 2005-10-24 23:47:07 | Re: Help for converting Oracle Script |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-11 17:19:13 | Re: [DOCS] Please Help: PostgreSQL Query Optimizer |
Previous Message | Bruce Momjian | 2005-12-08 21:30:19 | Re: Table A-1. PostgreSQL Error Codes (WITH constants) |
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2005-12-11 13:28:10 | Re: Reducing relation locking overhead |
Previous Message | Joshua D. Drake | 2005-12-11 05:30:12 | Re: Upcoming PG re-releases |