Re: 9.3.2 server creates hundreds of thousands of temporary files

On Wed, Jan 22, 2014 at 6:59 PM, Florian Weimer <fweimer(at)redhat(dot)com> wrote:
> This happens with postgresql-server-9.3.2-2.fc20.x86_64.
>
> I track this down to a lower-than-usual setting of work_mem, to 1MB, after
> the upgrade to 9.3.
>
> With work_mem set to 8MB, the query completes in a reasonable time frame,
> with just a few thousand temporary files. EXPLAIN ANALYZE gives these
> numbers:

Were the settings different on old and new PostgreSQL versions?
Do you have a query plan with the old version?
Are the join columns indexed on both sides?

Usually the planner prefers nested loops and merge joins with low
work_mem, if the join columns are indexed. Increasing
effective_cache_size and reducing random_page_cost to match your
hardware may lead the planner to make better choices.

Regards,
Marti