Re: [PERFORM] ported application having performance issues
On Thu, 30 Jun 2005, John Mendenhall wrote: Our setting for effective_cache_size is 2048. random_page_cost = 4, effective_cache_size = 2048 time approximately 4500ms random_page_cost = 3, effective_cache_size = 2048 time approximately 1050ms random_page_cost = 3, effective_cache_size = 4096 time approximately 1025ms The decrease of random_page_cost to 3 caused the plan to work properly, using the lead_requests table as a join starting point and using the contacts index. The effective_cache_size still looks small. As a rule of tumb you might want effective_cache_size to be something like 1/2 or 2/3 of your total memory. I don't know how much you had, but effective_cache_size = 4096 is only 32M. shared_buffers and effective_cache_size is normally the two most important settings in my experience. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] planner picking more expensive plan
Hi, I've just been referred here after a conversion on IRC and everybody seemed to think I've stumbled upon some strangeness. The planner (in PG version 8.0.2) is choosing what it thinks is a more expensive plan. I've got a table of animals (about 3M rows) and their movements (about 16M rows), and I'm trying to execute this query: SELECT a.birthlocnid, m.locnid FROM animals a LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0) LIMIT 10; If I have work_mem set to something small (1000) it uses this plan: QUERY PLAN Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10 loops=1) - Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual time=0.211..0.576 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using animals_pkey on animals a (cost=0.00..10198983.91 rows=3302780 width=8) (actual time=0.112..0.276 rows=10 loops=1) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235 rows=10 loops=1) Filter: (mtypeid = 0) Total runtime: 0.413 ms But if I increase work_mem to 1 it uses this plan: QUERY PLAN Limit (cost=565969.42..566141.09 rows=10 width=8) (actual time=27769.047..27769.246 rows=10 loops=1) - Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8) (actual time=27769.043..27769.228 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154 rows=10 loops=1) Filter: (mtypeid = 0) - Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual time=27768.991..27769.001 rows=10 loops=1) Sort Key: a.animalid - Seq Scan on animals a (cost=0.00..77086.80 rows=3302780 width=8) (actual time=0.039..5620.651 rows=3303418 loops=1) Total runtime: 27851.097 ms I've tried playing with the statistics as people suggested on IRC but to no effect. There was some discussion about why it would be doing this, but nothing obvious came out of it. SHOW ALL output is at the end of this mail but it should be pretty standard apart from: shared_buffers = 1 work_mem = 8192 max_connections = 100 effective_cache_size = 1 Hope that's enough information to be useful. Thanks. Sam name |setting + add_missing_from | on archive_command| /home/postgres/pgarchive %p australian_timezones | off authentication_timeout | 60 bgwriter_delay | 200 bgwriter_maxpages | 100 bgwriter_percent | 1 block_size | 8192 check_function_bodies | on checkpoint_segments| 3 checkpoint_timeout | 300 checkpoint_warning | 30 client_encoding| SQL_ASCII client_min_messages| notice commit_delay | 0 commit_siblings| 5 config_file| /home/pgdata/postgresql.conf cpu_index_tuple_cost | 0.001 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 custom_variable_classes| unset data_directory | /home/pgdata DateStyle | ISO, MDY db_user_namespace | off deadlock_timeout | 1000 debug_pretty_print | off debug_print_parse | off debug_print_plan | off debug_print_rewritten | off debug_shared_buffers | 0 default_statistics_target | 10 default_tablespace | unset default_transaction_isolation | read committed default_transaction_read_only | off default_with_oids | on dynamic_library_path | $libdir effective_cache_size | 1 enable_hashagg | on enable_hashjoin| on enable_indexscan | on enable_mergejoin | on enable_nestloop| on enable_seqscan | off enable_sort| on enable_tidscan | on explain_pretty_print | on external_pid_file | unset extra_float_digits | 0 from_collapse_limit| 8 fsync | on geqo | on geqo_effort| 5 geqo_generations | 0 geqo_pool_size | 0 geqo_selection_bias| 2 geqo_threshold | 12 hba_file | /home/pgdata/pg_hba.conf ident_file | /home/pgdata/pg_ident.conf integer_datetimes | off join_collapse_limit| 8
Re: [PERFORM] planner picking more expensive plan
Sam Mason [EMAIL PROTECTED] writes: The planner (in PG version 8.0.2) is choosing what it thinks is a more expensive plan. I fooled around trying to duplicate this behavior, without success. Can you create a self-contained test case? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] planner picking more expensive plan
Tom Lane wrote: I fooled around trying to duplicate this behavior, without success. Can you create a self-contained test case? I'll try and see if I can put something together, it's probably going to be early next week though. I wont be able to give you our data, so I'll be a bit of a headscratching exercise generating something that'll provoke the same behaviour. Not sure if it'll help, but here's what the database schema looks like at the moment: Table public.animals Column| Type | Modifiers -+---+--- animalid| integer | not null sex | character(1) | not null dob | date | not null birthlocnid | integer | breedid | character varying(8) | eartag_1| character varying(20) | eartag_2| character varying(20) | eartag_3| character varying(20) | Indexes: animals_pkey primary key, btree (animalid) animal_birthlocn btree (birthlocnid) animal_breed btree (breedid) animal_eartag btree (eartag_1) Check constraints: animal_sex CHECK (sex = 'M'::bpchar OR sex = 'F'::bpchar) Table public.movements Column | Type | Modifiers --+-+--- locnid | integer | not null animalid | integer | not null movedate | date| not null mtypeid | integer | not null Indexes: movement_animal btree (animalid) movement_location btree (locnid) movement_movedate btree (movedate) movement_movetype btree (mtypeid) Foreign-key constraints: movement_location FOREIGN KEY (locnid) REFERENCES locations(locnid) movement_animal FOREIGN KEY (animalid) REFERENCES animals(animalid) movement_type FOREIGN KEY (mtypeid) REFERENCES k_movement_type(mtypeid) Table public.locations Column | Type | Modifiers +---+--- locnid | integer | not null ptype | character varying(8) | ltype | character varying(8) | not null cph| character varying(20) | unk| integer | Indexes: locations_pkey primary key, btree (locnid) location_cph btree (cph) location_ltype btree (ltype) location_ptype btree (ptype) Foreign-key constraints: location_ptype FOREIGN KEY (ptype) REFERENCES k_premise_type(ptypeid) location_ltype FOREIGN KEY (ltype) REFERENCES k_location_type(ltypeid) As I said, animals contains about 3M rows, movements about 16M rows and locations about 80K rows. There are about 3 to 8 rows for each and every animal in the movements table, with at most one entry of mtypeid=0 for each animal (95% of the animals have an entry). Not sure if that's going to help making some demo data. It's just that it took quite a while loading it all here, so coming up with some code to make demo data may take a while. Thanks! Sam ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] planner picking more expensive plan
Sam Mason wrote: Hi, I've just been referred here after a conversion on IRC and everybody seemed to think I've stumbled upon some strangeness. The planner (in PG version 8.0.2) is choosing what it thinks is a more expensive plan. I've got a table of animals (about 3M rows) and their movements (about 16M rows), and I'm trying to execute this query: SELECT a.birthlocnid, m.locnid FROM animals a LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0) LIMIT 10; Why are you using LIMIT without having an ORDER BY? What are actually trying to get out of this query? Is it just trying to determine where the 'home' locations are? It just seems like this query isn't very useful. As it doesn't restrict by animal id, and it just gets 10 randomly selected animals where m.mtypeid=0. And why a LEFT JOIN instead of a normal join? Anyway, the general constraints you are applying seem kind of confusing. What happens if you change the plan to: SELECT a.birthlocnid, m.locnid FROM animals a LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0) ORDER BY a.animalid LIMIT 10; I would guess that this would help the planner realize it should try to use an index, since it can realize that it wants only a few rows by a.animalid in order. Though I also recognize that you aren't returning a.animalid so you don't really know which animals you are returning. I get the feeling you are trying to ask something like do animals stay at their birth location, or at least how are animals moving around. I don't know what m.typeid = 0 means, but I'm guessing it is something like where their home is. Anyway, I would say you need to put a little bit more restriction in, so the planner can figure out how to get only 10 rows. John =:- If I have work_mem set to something small (1000) it uses this plan: QUERY PLAN Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10 loops=1) - Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual time=0.211..0.576 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using animals_pkey on animals a (cost=0.00..10198983.91 rows=3302780 width=8) (actual time=0.112..0.276 rows=10 loops=1) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235 rows=10 loops=1) Filter: (mtypeid = 0) Total runtime: 0.413 ms But if I increase work_mem to 1 it uses this plan: QUERY PLAN Limit (cost=565969.42..566141.09 rows=10 width=8) (actual time=27769.047..27769.246 rows=10 loops=1) - Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8) (actual time=27769.043..27769.228 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154 rows=10 loops=1) Filter: (mtypeid = 0) - Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual time=27768.991..27769.001 rows=10 loops=1) Sort Key: a.animalid - Seq Scan on animals a (cost=0.00..77086.80 rows=3302780 width=8) (actual time=0.039..5620.651 rows=3303418 loops=1) Total runtime: 27851.097 ms I've tried playing with the statistics as people suggested on IRC but to no effect. There was some discussion about why it would be doing this, but nothing obvious came out of it. SHOW ALL output is at the end of this mail but it should be pretty standard apart from: shared_buffers = 1 work_mem = 8192 max_connections = 100 effective_cache_size = 1 Hope that's enough information to be useful. Thanks. Sam signature.asc Description: OpenPGP digital signature
Re: [PERFORM] ported application having performance issues
Dennis, On Fri, 01 Jul 2005, Dennis Bjorklund wrote: On Thu, 30 Jun 2005, John Mendenhall wrote: Our setting for effective_cache_size is 2048. random_page_cost = 4, effective_cache_size = 2048 time approximately 4500ms random_page_cost = 3, effective_cache_size = 2048 time approximately 1050ms random_page_cost = 3, effective_cache_size = 4096 time approximately 1025ms The effective_cache_size still looks small. As a rule of tumb you might want effective_cache_size to be something like 1/2 or 2/3 of your total memory. I don't know how much you had, but effective_cache_size = 4096 is only 32M. shared_buffers and effective_cache_size is normally the two most important settings in my experience. I have increased the effective_cache_size to 16384 (128M). I have kept random_page_cost at 3 for now. This appears to give me the performance I need at this time. In the future, we'll look at other methods of increasing the performance. Thank you all for all your suggestions. JohnM -- John Mendenhall [EMAIL PROTECTED] surf utopia internet services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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
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
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
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 picking more expensive plan
On Fri, 1 Jul 2005, Sam Mason wrote: The key thing with the query that Sam have is that if you turn off seqscan you get the first plan that run in 0.4ms and if seqscan is on the runtime is 27851ms. There are 100 way to make it select the seq scan, including rewriting the query to something more useful, tweaking different parameters and so on. The interesting part is that pg give the fast plan a cost of 202 and the slow a cost of 566141, but still it chooses the slow query unless seqscan is turned off (or some other tweak with the same effect). It know very well that the plan with the index scan will be much faster, it just don't manage to generate it unless you force it to. It makes you wonder if pg throws away some plans too early in the planning phase. Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10 loops=1) - Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual time=0.211..0.576 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using animals_pkey on animals a (cost=0.00..10198983.91 rows=3302780 width=8) (actual time=0.112..0.276 rows=10 loops=1) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235 rows=10 loops=1) Filter: (mtypeid = 0) Total runtime: 0.413 ms Limit (cost=565969.42..566141.09 rows=10 width=8) (actual time=27769.047..27769.246 rows=10 loops=1) - Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8) (actual time=27769.043..27769.228 rows=10 loops=1) Merge Cond: (outer.animalid = inner.animalid) - Index Scan using movement_animal on movements m (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154 rows=10 loops=1) Filter: (mtypeid = 0) - Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual time=27768.991..27769.001 rows=10 loops=1) Sort Key: a.animalid - Seq Scan on animals a (cost=0.00..77086.80 rows=3302780 width=8) (actual time=0.039..5620.651 rows=3303418 loops=1) Total runtime: 27851.097 ms Another thing to notice is that if one remove the Limit node then the situation is reversed and the plan that pg choose (with the Limit node) is the one with the lowest cost. The startup cost is however very high so combining that Merge Join with a Limit will of course produce something slow compared to the upper plan where the startup cost is 0.0. A stand alone test case would be nice, but even without the above plans are interesting. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
