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
The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.
Simple Query cached resuits
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;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=193432.05..193432.05 rows=1 width=1158) (actual time=34369.092..34376 rows=10000 loops=1)
-> Sort (cost=193256.16..193432.05 rows=70354 width=1158) (actual time=33764.533..34322.856 rows=940000 loops=1)
Sort key: labor1_.start_time, this_.work_date_time, this_.work_uid, this_.task_report_uid
Sort Method: external sort Disk: 1112224kB
-> Nested Loop (cost=0.00..162169.34 rows=70354 width=1158) (actual time=175.388..3642.170 rows=2410585 loops=1)
-> 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)
-> Append (cost=0.00..161461.53 rows=70354 width=204) (actual time=175.362..2769.808 rows=2410585 loops=1)
-> Seq Scan on labor_task_report this_ (cost=0.00..0.00 rows=1 width=648) (actual time=0.001..0.001 rows=0 loops=1)
FIlter: (labor_uid = 20178)
-> Index Scan using idx_task_report_by_labor_uid_y2008m01 on corporate_task_report_y2008m01 this_ (cost=0.00..19.97 rows=1 width=204) (actual time=0.227..1.227 rows=0 loops=1)
Index Cond: (labor_uid = 20178)
...
...
...
-> Index Scan using idx_task_report_by_labor_uid_y2013m12 on corporate_task_report_y2013m12 this_ (cost=0.00..8.28 rows=1 width=204) (actual time=0.056..1.056 rows=0 loops=1)
Index Cond: (labor_uid = 20178)
Total runtime: 34890.400 ms <- 34 seconds
Very same query, but without offset
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=167195.33..167220.33 rows=10000 width=1158) (actual time=6365.434..6367.545 rows=10000 loops=1)
-> Sort (cost=167195.33..167371.21 rows=70354 width=1158) (actual time=6365.432..6366.413 rows=10000 loops=1)
Sort key: labor1_.start_time, this_.work_date_time, this_.work_uid, this_.task_report_uid
Sort Method: top-N heapsort Memory: 5925kB
-> Nested Loop (cost=0.00..162169.34 rows=70354 width=1158) (actual time=176.395..2847.287 rows=2410585 loops=1)
-> Index Scan using corporate_labor_pkey on labor_tasks labor1_ (cost=0.00..4.27 rows=1 width=954) (actual time=0.016..0.020 rows=1 loops=1)
Index Cond: (labor_uid = 20178)
-> Append (cost=0.00..161461.53 rows=70354 width=204) (actual time=176.370..2116.998 rows=2410585 loops=1)
-> Seq Scan on labor_task_report this_ (cost=0.00..0.00 rows=1 width=648) (actual time=0.001..0.001 rows=0 loops=1)
FIlter: (labor_uid = 20178)
-> Index Scan using idx_task_report_by_labor_uid_y2008m01 on corporate_task_report_y2008m01 this_ (cost=0.00..19.97 rows=1 width=204) (actual time=0.227..1.227 rows=0 loops=1)
Index Cond: (labor_uid = 20178)
...
...
...
-> Index Scan using idx_task_report_by_labor_uid_y2013m12 on corporate_task_report_y2013m12 this_ (cost=0.00..8.28 rows=1 width=204) (actual time=0.056..1.056 rows=0 loops=1)
Index Cond: (labor_uid = 20178)
Total runtime: 6368.894 ms <- 6.3 seconds-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
