BUG #14411: Issue with using OFFSET
The following bug has been logged on the website:
Bug reference: 14411
Logged by: Jamie Koceniak
Email address: jkoceniak@mediamath.com
PostgreSQL version: 9.4.6
Operating system: Linux
Description:
Query performance decreases as the OFFSET increases.
Bad Query:
SELECT t1.id, ( SELECT count ( * ) FROM site_list_sites t4 WHERE t1.id =
t4.site_list_id ) AS sites_count
FROM site_lists t1
WHERE t1.organization_id IN ( SELECT distinct organization_id FROM
user_permissions ( 2385 ))
ORDER BY t1.created_on DESC LIMIT 100 OFFSET 200;
With offset set to 100, query returns in 92ms
Query Plan (offset = 100):
https://explain.depesz.com/s/nnPd
offset = 200, query take 9.8 seconds
Query plan (offset=200)
https://explain.depesz.com/s/MQAS
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Nov 3, 2016 at 3:29 PM, <jkoceniak@mediamath.com> wrote:
Query performance decreases as the OFFSET increases.
Not a bug, but a fact of life. It must generate the OFFSET number
of rows and continue to generated the next LIMIT rows (or continue
processing until there are no rows). If matching rows are scarce
after OFFSET rows are found, it can take a while to get enough or
to find out that LIMIT rows don't exist.
Personally, I never use OFFSET and LIMIT for pagination; there are
better ways for most situations.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi Kevin,
I would be interested in hearing about alternate solutions to using OFFSET.
We have explored using btree index approach (ordering by id desc), storing last id and then grabbing next set of rows < last id. That works great for fetching next group of rows.
However, how would you implement jumping ahead to a specific range?
I.e. Picture a web site with pages 1 2 3 4 5 … 10 11 and the user jumping to page 11.
Thanks,
Jamie
On 11/3/16, 3:52 PM, "Kevin Grittner" <kgrittn@gmail.com> wrote:
On Thu, Nov 3, 2016 at 3:29 PM, <jkoceniak@mediamath.com> wrote:
Query performance decreases as the OFFSET increases.
Not a bug, but a fact of life. It must generate the OFFSET number
of rows and continue to generated the next LIMIT rows (or continue
processing until there are no rows). If matching rows are scarce
after OFFSET rows are found, it can take a while to get enough or
to find out that LIMIT rows don't exist.Personally, I never use OFFSET and LIMIT for pagination; there are
better ways for most situations.--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Nov 3, 2016 at 7:05 PM, Jamie Koceniak <jkoceniak@mediamath.com> wrote:
On 11/3/16, 3:52 PM, "Kevin Grittner" <kgrittn@gmail.com> wrote:
Personally, I never use OFFSET and LIMIT for pagination; there
are better ways for most situations.
I would be interested in hearing about alternate solutions to
using OFFSET.
We have explored using btree index approach (ordering by id
desc), storing last id and then grabbing next set of rows < last
id. That works great for fetching next group of rows.
That works. If you also save the starting key value, you can use
it to page backward by reversing your ORDER BY.
However, how would you implement jumping ahead to a specific
range?
Range (as in database values) or page (as in count of matching rows)?
I.e. Picture a web site with pages 1 2 3 4 5 … 10 11 and the user
jumping to page 11.
You can't know what's on page 11 without reading pages 1 to 10.
You can either track that as you move forward, or just return all
the rows on the initial query and write everything to working
storage somewhere, navigating through this result when the user
chooses a new page. Normally when using this technique you set
some overall limit of rows for the query.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
2016-11-04 2:05 GMT+02:00 Jamie Koceniak <jkoceniak@mediamath.com>:
I would be interested in hearing about alternate solutions to using OFFSET.
I find this presentation useful in describing how to do pagination:
http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way
--
Victor Yegorov