Re: [PERFORM] query plan question, nested loop vs hash join

Thanks for your reply, Marti, as I answered to Tom couple of days ago adjusting of 'effective_cache_size' to 80% of RAM and 'random_page_cost' from 2 to 1 helped me. On 8 October 2014 00:26, Marti Raudsepp ma...@juffo.org wrote: On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko

Re: [PERFORM] query plan question, nested loop vs hash join

On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko lizenk...@gmail.com wrote: Is it possible to force optimizer choose the second plan without doing set enable_hashjoin = off; ? Increasing of 'effective_cache_size' leads to similar thing with mergejoin, other options (work_mem, shared_buffers.

Re: [PERFORM] query plan question, nested loop vs hash join

Thanks a lot, Tom, reducing 'random_page_cost' from 2 to 1 and increasing 'effective_cache_size' from 70% to 80% of RAM solved this at least on my virtual sandbox. By the way, why increasing of cache only (with the same random_page_cost=2) can lead to mergejoin selection? On 5 October 2014

Re: [PERFORM] query plan question, nested loop vs hash join

As I answered to Tom few moments ago: reducing 'random_page_cost' from 2 to 1 and increasing 'effective_cache_size' from 70% to 80% of RAM solved this at least on my virtual sandbox. I've observed same behaviour both on weak virtual machine and on the quite powerfull stress test platform. The

Re: [PERFORM] query plan question, nested loop vs hash join

2014-10-05 21:57 GMT+03:00 Andrey Lizenko lizenk...@gmail.com: Increasing of 'effective_cache_size' leads to similar thing with mergejoin, other options (work_mem, shared_buffers. etc) do not change anything. I think increasing `work_mem` should have effects, as plan with `Nested Loop` is

Re: [PERFORM] query plan question, nested loop vs hash join

Andrey Lizenko lizenk...@gmail.com writes: What is the reason of Seq Scan on activities_example in the first case? Is it possible to force optimizer choose the second plan without doing set enable_hashjoin = off; ? Disabling hashjoins altogether would be a pretty dangerous fix. I think the

Re: [PERFORM] query plan question

] Sent: Wednesday, November 17, 2004 11:41 AM To: David Parker Cc: Tom Lane; Jeff; Russell Smith; [EMAIL PROTECTED] Subject: Re: [PERFORM] query plan question David Parker wrote: We're using postgresql 7.4.5. I've only recently put pg_autovacuum in place as part of our installation, and I'm

Re: [PERFORM] query plan question

Thanks! - DAP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russell Smith Sent: Tuesday, November 16, 2004 11:36 PM To: [EMAIL PROTECTED] Subject: Re: [PERFORM] query plan question On Wed, 17 Nov 2004 02:54 pm, you wrote: I have a query for which

Re: [PERFORM] query plan question

If they are the same and PostgreSQL are the same, are the intel machines Xeons? Yup, dual 3.06-GHz Intel Xeon Processors. I'm not sure off the top of my head what the sparcs are exactly. We're in the process of moving completely to intel, but we still have to support our app on sparc, and we

Re: [PERFORM] query plan question

On Nov 17, 2004, at 7:32 AM, David Parker wrote: Oh, I didn't realize that analyze gave that much more info. I've got a lot to learn about this tuning stuff ;-) I've attached the output. I see from the new output where the slow query is taking its time (the nested loop at line 10), but I still

Re: [PERFORM] query plan question

9:01 AM To: David Parker Cc: Russell Smith; [EMAIL PROTECTED] Subject: Re: [PERFORM] query plan question On Nov 17, 2004, at 7:32 AM, David Parker wrote: Oh, I didn't realize that analyze gave that much more info. I've got a lot to learn about this tuning stuff ;-) I've attached the output

Re: [PERFORM] query plan question

analyzing those tables. - DAP -Original Message- From: David Parker Sent: Wednesday, November 17, 2004 9:44 AM To: 'Jeff' Cc: Russell Smith; [EMAIL PROTECTED] Subject: RE: [PERFORM] query plan question I've got pg_autovacuum running on both platforms. I've verified that the tables involved

Re: [PERFORM] query plan question

David Parker [EMAIL PROTECTED] writes: So I guess I need to figure out why pg_autovacuum isn't analyzing those tables. Which autovacuum version are you using? The early releases had some nasty bugs that would allow it to skip tables sometimes. I think all the known problems are fixed as of

Re: [PERFORM] query plan question

threads would be greatly appreciated! Thanks. - DAP -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 10:46 AM To: David Parker Cc: Jeff; Russell Smith; [EMAIL PROTECTED] Subject: Re: [PERFORM] query plan question David Parker [EMAIL

Re: [PERFORM] query plan question

David Parker wrote: We're using postgresql 7.4.5. I've only recently put pg_autovacuum in place as part of our installation, and I'm basically taking the defaults. I doubt it's a problem with autovacuum itself, but rather with my configuration of it. I have some reading to do, so any pointers to

Re: [PERFORM] query plan question

To: David Parker Cc: Tom Lane; Jeff; Russell Smith; [EMAIL PROTECTED] Subject: Re: [PERFORM] query plan question David Parker wrote: We're using postgresql 7.4.5. I've only recently put pg_autovacuum in place as part of our installation, and I'm basically taking the defaults. I doubt it's

Re: [PERFORM] query plan question

On Wed, 17 Nov 2004 02:54 pm, you wrote: I have a query for which postgres is generating a different plan on different machines. The database schema is the same, the dataset is the same, the configuration is the same (e.g., pg_autovacuum running in both cases), both systems are Solaris 9.