Re: 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 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(at)gmail(dot)com> wrote:

> 2014-10-05 21:57 GMT+03:00 Andrey Lizenko <lizenko79(at)gmail(dot)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
>

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