Postgres Pro
Downloads
Home   >   mailing lists

Thread: Poor OFFSET performance in PostgreSQL 9.1.6

Poor OFFSET performance in PostgreSQL 9.1.6

From
Date:
<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Can anyone offer suggestions on how I can
optimizea query that contains the LIMIT OFFSET clause?</div><div><br /></div><div>The explain plan of the query is
includedin the notepad attachment.</div><div><br /></div><div>thanks</div><div><br /></div><div><br /></div></span>

Re: Poor OFFSET performance in PostgreSQL 9.1.6

From
Greg Spiegelberg
Date:
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


Re: Poor OFFSET performance in PostgreSQL 9.1.6

From
Date:
<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>

Re: Poor OFFSET performance in PostgreSQL 9.1.6

From
Merlin Moncure
Date:
On Wed, Aug 28, 2013 at 3: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.

OFFSET is working as designed (that is, slowly).  Managing pagination
with OFFSET is essentially a hack and will not scale to even medium
sized tables.   You have some SQL alternatives.  One is cursors as
greg mentioned.   Another is client side pagination:

Select * from labor_task_report this_
inner join labor_tasks labor1_ on this_.labor_UID=20178
order by
  labor1_START_TIME asc,
  this_.work_DATE_TIME asc,
  this_.work_UID asc,
  this_.task_REPORT_UID
limit 10000 offset 940000;

could become

Select * from labor_task_report this_
inner join labor_tasks labor1_ on this_.labor_UID=20178
where (
  labor1_START_TIME,
  this_.work_DATE_TIME asc,
  this_.work_UID asc,
  this_.task_REPORT_UID) >
  ($1, $2, $3, $4)
order by
  labor1_START_TIME asc,
  this_.work_DATE_TIME asc,
  this_.work_UID asc,
  this_.task_REPORT_UID
limit 10000;


where $1-$4 are the corresponding fields of the last row you read from
the last fetch.

merlin

Re: Poor OFFSET performance in PostgreSQL 9.1.6

From
David Rowley
Date:
On Thu, Aug 29, 2013 at 8:39 AM, <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


Before I write anything, I should warn that it has been a while since I had to read an explain analyze without also having a schema design to back it up, so I may have this wrong.

Since the sort must be applied before the limit and the join must take place before the sort, it's naturally pretty slow given the number of rows being processed.

It's hard to tell how the tables are constrained by looking at the explain analyze. It seems that labor_uid is the primary key for the labor_tasks table... (going by the 1 record that is returned. If so, then it's a bit confusing why you'd need to sort on labor1.start_time at all, since only 1 row exists anyway...

I got that from here:
Index Scan using corporate_labor_pkey on labor_tasks labor1_  (cost=0.00..4.27 rows=1 width=954) (actual time=0.017..0.020 rows=1 loops=1)                   Index Cond:  (labor_uid = 20178)

So this is the case then you could do all of your sorting before the join takes place by using a sub query. This would also mean you could do your LIMIT and OFFSET in the same sub query. If the tables in the sub query were properly indexed then this could be a a pretty fast operation which would only hit less than 1 million rows. I'm not quite sure how your partitioning is setup though as the names seem to indicate both dates and labour UIDs... so I'll just ignore these for now...


The query I came up with looked like this:

 SELECT * 


 FROM labor_tasks labor_1
 INNER JOIN (SELECT *


             FROM labor_task_report this_
             WHERE labor_UID = 20178


             ORDER BY work_DATE_TIME asc, work_UID asc, task_REPORT_UID 
             LIMIT 10000 OFFSET 940000


) this_ ON labor_1.labor_uid = this_.labor_uid
ORDER BY this_.work_DATE_TIME asc, this.work_UID asc, this.task_REPORT_UID 


If you had an index on labor_UID, work_date_time, work_UID, task_Report_uid then inner would have a better chance at running quickly.



Though I may have mis-read the explain analyze results and have it completely wrong.


Postgresql is a pretty amazing feat of engineering, but it does lack sometimes when it comes to properly optimising queries to the most optimal way by detecting functional dependencies in tables to speed up joins and grouping.


Regards
David


 



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


Re: Poor OFFSET performance in PostgreSQL 9.1.6

From
hubert depesz lubaczewski
Date:
On Wed, Aug 28, 2013 at 01:39:46PM -0700, 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

large offsets are slow, and there is no real escape from it.

You can change your paging solution, though, to something that will be
faster.

Possible solutions/optimizations:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/

or

http://www.depesz.com/2011/05/20/pagination-with-fixed-order/

Best regards,

depesz