The labor_task_report table is already Partitioned by this_.work_date_time and this table contains approx. 15 billion rows. The other table labor_tasks is not partitioned. I'm thinking that the size of the external sort is part of the problem. if I remove the labor_tasks table from the SQL, the query returns in 10 sec. Could there be a postgresql.conf parameter that I could tweak to provide additional sorting resources to improve the overall query?
Unfortunately this query is being generated by Hibernate 4.1.6, so the cursor solution won't help I don;t think.
Two solutions come to mind. First possibility is table partitioning on the column you're sorting. Second, depending on your application, is to use a cursor. Cursor won't help with web applications however a stateful application could benefit.
