Re: Slow hash join performance with many batches

Alex Adriaanse <alex(at)oseberg(dot)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