Hi Greg,

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.

thanks
 
-------- Original Message --------
Subject: Re: [PERFORM] Poor OFFSET performance in PostgreSQL 9.1.6
From: Greg Spiegelberg <gspiegelberg@gmail.com>
Date: Wed, August 28, 2013 2:26 pm
To: fburgess@radiantblue.com
Cc: pgsql-performance <pgsql-performance@postgresql.org>

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.

HTH
-Greg


On Wed, Aug 28, 2013 at 2:39 PM, <fburgess@radiantblue.com> wrote:
Can anyone offer suggestions on how I can optimize a query that contains the LIMIT OFFSET clause?

The explain plan of the query is included in the notepad attachment.

thanks




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance