pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor OFFSET performance in PostgreSQL 9.1.6
6+ messages / 5 participants
[nested] [flat]
* Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-28 20:39 fburgess@radiantblue.com
0 siblings, 4 replies; 6+ messages in thread
From: fburgess@radiantblue.com @ 2013-08-28 20:39 UTC (permalink / raw)
To: pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
tuning_SQL_with_offest.txt (text/plain, 4.0K) download
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
* Re: Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-28 21:26 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: fburgess@radiantblue.com
3 siblings, 0 replies; 6+ messages in thread
From: Greg Spiegelberg @ 2013-08-28 21:26 UTC (permalink / raw)
To: fburgess@radiantblue.com; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-28 22:08 fburgess@radiantblue.com
0 siblings, 0 replies; 6+ messages in thread
From: fburgess@radiantblue.com @ 2013-08-28 22:08 UTC (permalink / raw)
To: Greg Spiegelberg <gspiegelberg@gmail.com>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
tuning_SQL_with_offest.txt (text/plain, 4.0K) download
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
* Re: Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-28 23:10 Merlin Moncure <mmoncure@gmail.com>
parent: fburgess@radiantblue.com
3 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2013-08-28 23:10 UTC (permalink / raw)
To: fburgess@radiantblue.com; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-29 04:43 David Rowley <dgrowleyml@gmail.com>
parent: fburgess@radiantblue.com
3 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2013-08-29 04:43 UTC (permalink / raw)
To: fburgess@radiantblue.com; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor OFFSET performance in PostgreSQL 9.1.6
@ 2013-08-29 11:00 hubert depesz lubaczewski <depesz@depesz.com>
parent: fburgess@radiantblue.com
3 siblings, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2013-08-29 11:00 UTC (permalink / raw)
To: fburgess@radiantblue.com; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-08-29 11:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-28 20:39 Poor OFFSET performance in PostgreSQL 9.1.6 fburgess@radiantblue.com
2013-08-28 21:26 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2013-08-28 23:10 ` Merlin Moncure <mmoncure@gmail.com>
2013-08-29 04:43 ` David Rowley <dgrowleyml@gmail.com>
2013-08-29 11:00 ` hubert depesz lubaczewski <depesz@depesz.com>
2013-08-28 22:08 Re: Poor OFFSET performance in PostgreSQL 9.1.6 fburgess@radiantblue.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox