Re: Poor OFFSET performance in PostgreSQL 9.1.6

<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Hi Greg,</div><div><br /></div><div>The
labor_task_reporttable is already Partitioned by this_.work_date_time and this table contains approx. 15 billion rows.
Theother table labor_tasks is not partitioned. I'm thinking that the size of the external sort is part of the problem.
ifI remove the labor_tasks table from the SQL, the query returns in 10 sec. Could there be a postgresql.conf parameter
thatI could tweak to provide additional sorting resources to improve the overall query?<br /></div><div><br
/></div><div>Unfortunatelythis query is being generated by Hibernate 4.1.6, so the cursor solution won't help I don;t
think.</div><div><br/></div><div>thanks<br /> </div><div></div><blockquote id="replyBlockquote" style="border-left: 2px
solidblue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;" webmail="1"><div
id="wmQuoteWrapper">-------- Original Message --------<br /> Subject: Re: [PERFORM] Poor OFFSET performance in
PostgreSQL9.1.6<br /> From: Greg Spiegelberg <<a
href="mailto:gspiegelberg@gmail.com">gspiegelberg@gmail.com</a>><br/> Date: Wed, August 28, 2013 2:26 pm<br /> To:
<ahref="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><br /> Cc: pgsql-performance <<a
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>><br/><br /><div
dir="ltr"><div>Twosolutions 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
applicationcould benefit.<br /><br /></div><div>HTH<br /></div>-Greg<br /></div><div class="gmail_extra"><br /><br
/><divclass="gmail_quote">On Wed, Aug 28, 2013 at 2:39 PM, <span dir="ltr"><<a
href="mailto:fburgess@radiantblue.com"target="_blank">fburgess@radiantblue.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><span
style="font-size:10pt;font-family:Verdana"><div>Cananyone offer suggestions on how I can optimize a query that contains
theLIMIT OFFSET clause?</div><div><br /></div><div>The explain plan of the query is included in the notepad
attachment.</div><div><br/></div><div>thanks</div><div><br /></div><div><br /></div></span></div><br /><br /> --<br />
Sentvia pgsql-performance mailing list (<a href="mailto:pgsql-performance@postgresql.org"
target="_blank">pgsql-performance@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-performance"
target="_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br/><br /></blockquote></div><br
/></div></div></blockquote></span>