On Wed, Jan 22, 2014 at 6:59 PM, Florian Weimer <fweimer@redhat.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