Re: [PERFORM] Planner constants for RAM resident databases
Emil, > -> Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161) > (actual time=164.423..361.477 rows=49 loops=1) That would indicate that you need to either increase your statistical sampling (SET STATISTICS) or your frequency of running ANALYZE, or both. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Planner constants for RAM resident databases
> When you do "explain analyze" of a query that you have difficulties > with, how are the planner's estimates. Are the estimated number of rows > about equal to the actual number of rows? Some of them are pretty far off. For example -> Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161) (actual time=164.423..361.477 rows=49 loops=1) I tried setting enable_merge_joins to off and that made the query about three times faster. It's using a hash join instead. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Planner constants for RAM resident databases
Emil Briggs wrote: I just mentioned random_page_cost, but you should also tune effective_cache_size, since that is effectively most of your RAM. It depends what else is going on in the system, but setting it as high as say 12-14GB is probably reasonable if it is a dedicated machine. With random_page_cost 1.5-2, and higher effective_cache_size, you should be doing pretty well. John =:-> I tried playing around with these and they had no effect. It seems the only thing that makes a difference is cpu_tuple_cost. I'm surprised. I know cpu_tuple_cost can effect it as well, but usually the recommended way to get indexed scans is the above two parameters. When you do "explain analyze" of a query that you have difficulties with, how are the planner's estimates. Are the estimated number of rows about equal to the actual number of rows? If the planner is mis-estimating, there is a whole different set of tuning to do to help it estimate correctly. John =:-> PS> Use reply-all so that your comments go to the list. signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Planner constants for RAM resident databases
Emil Briggs wrote: I'm working with an application where the database is entirely resident in RAM (the server is a quad opteron with 16GBytes of memory). It's a web application and handles a high volume of queries. The planner seems to be generating poor plans for some of our queries which I can fix by raising cpu_tuple_cost. I have seen some other comments in the archives saying that this is a bad idea but is that necessarily the case when the database is entirely resident in RAM? Emil Generally, the key knob to twiddle when everything fits in RAM is random_page_cost. If you truly have everything in RAM you could set it almost to 1. 1 means that it costs exactly the same to go randomly through the data then it does to go sequential. I would guess that even in RAM it is faster to go sequential (since you still have to page and deal with L1/L2/L3 cache, etc). But the default random_page_cost of 4 is probably too high for you. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Planner constants for RAM resident databases
On Fri, Jul 01, 2005 at 09:59:38PM -0400, Emil Briggs wrote: > I'm working with an application where the database is entirely resident in > RAM > (the server is a quad opteron with 16GBytes of memory). It's a web > application and handles a high volume of queries. The planner seems to be > generating poor plans for some of our queries which I can fix by raising > cpu_tuple_cost. I have seen some other comments in the archives saying that > this is a bad idea but is that necessarily the case when the database is > entirely resident in RAM? If I'm understanding correctly that'll mostly increase the estimated cost of handling a row relative to a sequential page fetch, which sure sounds like it'll push plans in the right direction, but it doesn't sound like the right knob to twiddle. What do you have random_page_cost set to? Cheers, Steve ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Planner constants for RAM resident databases
I'm working with an application where the database is entirely resident in RAM (the server is a quad opteron with 16GBytes of memory). It's a web application and handles a high volume of queries. The planner seems to be generating poor plans for some of our queries which I can fix by raising cpu_tuple_cost. I have seen some other comments in the archives saying that this is a bad idea but is that necessarily the case when the database is entirely resident in RAM? Emil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
