Re: Poor OFFSET performance in PostgreSQL 9.1.6

[<fburgess@xxxxxxxxxxxxxxx>]

Hi Greg,

The labor_task_report table is already Partitioned by this_.work_date_time and this table contains approx. 15 billion rows. The other table labor_tasks is not partitioned. I'm thinking that the size of the external sort is part of the problem. if I remove the labor_tasks table from the SQL, the query returns in 10 sec. Could there be a postgresql.conf parameter that I could tweak to provide additional sorting resources to improve the overall query?

Unfortunately this query is being generated by Hibernate 4.1.6, so the cursor solution won't help I don;t think.

thanks
 

-------- Original Message --------
Subject: Re: Poor OFFSET performance in PostgreSQL 9.1.6
From: Greg Spiegelberg <gspiegelberg@xxxxxxxxx>
Date: Wed, August 28, 2013 2:26 pm
To: fburgess@xxxxxxxxxxxxxxx
Cc: pgsql-performance <pgsql-performance@xxxxxxxxxxxxxx>

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@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.

thanks

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

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