Postgres Pro
Downloads
Home   >   mailing lists

Re: query plan question, nested loop vs hash join - Mailing list pgsql-performance

From Andrey Lizenko
Subject Re: query plan question, nested loop vs hash join
Date
Msg-id CADKuZZA2OFEtyNFFxjHeqNo4xu6mitoVRFgt--dVo7VY4teVYQ@mail.gmail.com
Whole thread Raw
In response to Re: query plan question, nested loop vs hash join  (Victor Yegorov <vyegorov@gmail.com>)
List pgsql-performance
Tree view
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 first one is Ubuntu 12.04 LTS, second one is RedHat 6.4
Of course, RAM. RAID, CPUs and so on are different enough, so I believe the root clause of this issue is not connected with hardware at all.

Thanks for your idea with external sort, I'll test it


On 5 October 2014 23:18, Victor Yegorov <vyegorov@gmail.com> wrote:
2014-10-05 21:57 GMT+03:00 Andrey Lizenko <lizenko79@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 using disk-based sort.
Increase it till you'll stop seeing `external sort` in the EXPLAIN output. Something like '10MB' should do.

Also, it'd be handy if you could provide `EXPLAIN (analyze, buffers)` output along with the results of these queries:

    SELECT name,setting,source FROM pg_settings WHERE name ~ 'cost' AND NOT name ~ 'vacuum';
    SELECT name,setting,source FROM pg_settings WHERE NOT source IN ('default','override');

And describe your setup: what OS? how much RAM? what kind of disks? RAID?

--
Victor Y. Yegorov



--
С уважением, Андрей Лизенко

pgsql-performance by date:

Previous
From: Andrey Lizenko
Date:
Subject: Re: query plan question, nested loop vs hash join
Next
From: Emi Lu
Date:
Subject: issue?