Postgres Pro
Downloads
Home   >   mailing lists

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

From Victor Yegorov
Subject Re: query plan question, nested loop vs hash join
Date
Msg-id CAGnEboifi+E_cMRZLbH5grhGeonVwvzF-01Ev=h64--WFuMd6Q@mail.gmail.com
Whole thread Raw
In response to query plan question, nested loop vs hash join  (Andrey Lizenko <lizenko79@gmail.com>)
Responses Re: query plan question, nested loop vs hash join  (Andrey Lizenko <lizenko79@gmail.com>)
List pgsql-performance
Tree view
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: query plan question, nested loop vs hash join
Next
From: Tom Lane
Date:
Subject: Re: query plan question, nested loop vs hash join