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
14hours.
Running truss/strace on the backend process running this query on the Medium database reveals that for a big chunk of
thistime Postgres creates/reads/unlinks a very large quantity (millions?) of tiny files inside pgsql_tmp. I also ran an
EXPLAINANALYZE and am attaching the most time-consuming parts of the plan (with names redacted). Although I'm not too
familiarwith the internals of Postgres' Hash implementation, it seems that having over 4 million hash batches could be
what'scausing 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