| From: | Alex Adriaanse <alex(at)oseberg(dot)io> | 
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> | 
| Subject: | Slow hash join performance with many batches | 
| Date: | 2015-06-01 16:03:10 | 
| Message-ID: | 52B47B47-0926-4E15-B25E-212DF52FE695@oseberg.io | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-performance | 
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.
Is there any way I can work around this problem, other than to experiment with disabling enable_hashjoin for this query/database?
Alex
| Attachment | Content-Type | Size | 
|---|---|---|
| explain_with_slow_hash_join.txt | text/plain | 2.5 KB | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2015-06-01 17:58:12 | Re: Slow hash join performance with many batches | 
| Previous Message | Merlin Moncure | 2015-06-01 13:20:45 | Re: Postgres is using 100% CPU |