On Wed, Aug 28, 2013 at 3:39 PM, <fburgess@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
