From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Adriaanse <alex@oseberg.io>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow hash join performance with many batches
Date: Mon, 01 Jun 2015 13:58:12 -0400
Message-ID: <28009.1433181492@sss.pgh.pa.us> (raw)
In-Reply-To: <52B47B47-0926-4E15-B25E-212DF52FE695@oseberg.io>
References: <52B47B47-0926-4E15-B25E-212DF52FE695@oseberg.io>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Alex Adriaanse <alex@oseberg.io> writes:
> I have several databases that have the same schema but different amounts of data in it (let's categorize these as Small, Medium, and Large). We have a mammoth query with 13 CTEs that are LEFT JOINed against a main table. This query takes <30 mins on the Small database, <2 hours to run on Large, but on the Medium database it takes in the vicinity of 14 hours.
> Running truss/strace on the backend process running this query on the Medium database reveals that for a big chunk of this time Postgres creates/reads/unlinks a very large quantity (millions?) of tiny files inside pgsql_tmp. I also ran an EXPLAIN ANALYZE and am attaching the most time-consuming parts of the plan (with names redacted). Although I'm not too familiar with the internals of Postgres' Hash implementation, it seems that having over 4 million hash batches could be what's causing the problem.
> I'm running PostgreSQL 9.3.5, and have work_mem set to 32MB.
I'd try using a significantly larger work_mem setting for this query,
so as to have fewer hash batches and more buckets per batch.
It might be unwise to raise your global work_mem setting, but perhaps
you could just do a "SET work_mem" within the session running the query.
Also, it looks like the planner is drastically overestimating the sizes
of the CTE outputs, which is contributing to selecting unreasonably large
numbers of batches. If you could get those numbers closer to reality it'd
likely help. Hard to opine further since no details about the CTEs were
provided.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alex@oseberg.io
Subject: Re: Slow hash join performance with many batches
In-Reply-To: <28009.1433181492@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox