Re: Poor OFFSET performance in PostgreSQL 9.1.6

On Wed, Aug 28, 2013 at 3:39 PM, <fburgess(at)radiantblue(dot)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