[HACKERS] Problem with the Planner
Please observe the following queries. Why PostgreSQL is favouring MergeJoin eventhough, it leading to higher execution times than NestedLoopJoin. Any suggestions to fix this problem. bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 50 AND t1.unique2 = t2.unique2; QUERY PLAN Merge Join (cost=665.09..4704.60 rows=166701 width=488) (actual time=10.128..40.843 rows=50 loops=1) Merge Cond: (outer.unique2 = inner.unique2) - Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..1514.00 rows=1 width=244) (actual time=0.031..20.520 rows=1 loops=1) - Sort (cost=665.09..673.42 rows=3334 width=244) (actual time=9.601..9.646 rows=50 loops=1) Sort Key: t1.unique2 - Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.154..9.140 rows=50 loops=1) Filter: (unique1 50) Total runtime: 41.101 ms (8 rows) bench=# SET enable_mergejoin = off; SET bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 50 AND t1.unique2 = t2.unique2; QUERY PLAN -- Hash Join (cost=588.34..11841.35 rows=166701 width=488) (actual time=9.028..70.453 rows=50 loops=1) Hash Cond: (outer.unique2 = inner.unique2) - Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=1 width=244) (actual time=0.007..11.846 rows=1 loops=1) - Hash (cost=470.00..470.00 rows=3334 width=244) (actual time=8.378..8.378 rows=0 loops=1) - Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.093 rows=50 loops=1) Filter: (unique1 50) Total runtime: 70.659 ms (7 rows) bench=# SET enable_hashjoin = off; SET bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 50 AND t1.unique2 = t2.unique2; QUERY PLAN Nested Loop (cost=0.00..633218.15 rows=166701 width=488) (actual time=0.178..9.389 rows=50 loops=1) - Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.349 rows=50 loops=1) Filter: (unique1 50) - Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..189.16 rows=50 width=244) (actual time=0.009..0.011 rows=1 loops=50) Index Cond: (outer.unique2 = t2.unique2) Total runtime: 9.552 ms (6 rows) -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Through googling, i found that Normal Disk has external data transfer rate of around 40MBps, ^^ Does this includes, seek and rotational latency ? where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps. As we can see, the ratio between Disk and Main Memory data transfer rates is around 50. Then, if we multiply all cpu_* paramters by 50, the resulting values will be: random_page_cost = 1; cpu_tuple_cost = 0.5; cpu_index_tuple_cost = 0.05; cpu_operator_cost = 0.0125; Would it be a suitable approach ? We request all of u to give comments/suggestions on this calcualations. Thanking You. On Sun, 11 Dec 2005, Tom Lane wrote: [ 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 -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ Bradley's Bromide: If computers get too powerful, we can organize them into a committee -- that will do them in. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Defaulat values of various parameters in PostgreSQL: #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 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. 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. Through googling, i found that Normal Disk has external data transfer rate of around 40MBps, where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps. As we can see, the ratio between Disk and Main Memory data transfer rates is around 50. Then, if we multiply all cpu_* paramters by 50, the resulting values will be: random_page_cost = 1; cpu_tuple_cost = 0.5; cpu_index_tuple_cost = 0.05; cpu_operator_cost = 0.0125; Would it be a suitable approach ? We request all of u to give comments/suggestions on this calcualations. Thanking You. On Sun, 11 Dec 2005, Tom Lane wrote: [ 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 -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ A woman physician has made the statement that smoking is neither physically defective nor morally degrading, and that nicotine, even when indulged to in excess, is less harmful than excessive petting. -- Purdue Exponent, Jan 16, 1925 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Please Help: PostgreSQL Query Optimizer
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.) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
