4

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'.

create extension if not exists pgcrypto;
create table jtest (data jsonb not null default '{}');
insert into jtest (data)
select json_build_object('id', gen_random_uuid(), 'key', i)::jsonb
FROM generate_series(1,200000) i;
create index jtest_key on jtest ((data->'key'));

First query (fast):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key' 
limit 20;

-- "Limit  (cost=0.42..1.43 rows=20 width=74) (actual time=0.023..0.044 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.023..0.039 rows=20 loops=1)"
-- "Planning time: 0.082 ms"
-- "Execution time: 0.066 ms"

Second query with large offset (slow):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key' 
offset 100000
limit 20;

-- "Limit  (cost=5075.42..5076.44 rows=20 width=74) (actual time=112.861..112.896 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.027..106.378 rows=100020 loops=1)"
-- "Planning time: 0.105 ms"
-- "Execution time: 112.931 ms"

In PG docs I found:

The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.

But they don't say anything about how to deal with large offsets. How I can improve this query? Also is this behaviour (inefficiency for large offsets) common to all RDMS or just PG (using 9.4)?

0

Why not try a where clause? If you index j.data->'key', a where will only be a log(n).

Another reason may be order by j.data->'key' is a slow computation. Indexing this can also boost your order by (but the benefit will be far less noticeable than the benefit due to the where clause itself).

This article may help better answer your question: https://use-the-index-luke.com/sql/partial-results/fetch-next-page

| improve this answer | |
0

Where possible, try to remember the last-seen value and use a query that says where some_sort_key > '40000' LIMIT 100 or whatever. This is often considerably more efficient than using an OFFSET.

Issues with OFFSET (or similar) performance apply to all RDBMSes that have SQL extensions with functionality equivalent to LIMIT/OFFSET, AFAIK.

Here is a very good presentation explaining that in detail : Pagination Done the Right Way

Another option to improve performance is to use a server-side cursor. See DECLARE and FETCH in the manual.

Note, however, that server-side cursors don't play especially well with connection-pooled applications that have arbitrarily long delays between requests, as a database session is held up waiting for the client's next request. - craig-ringer

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.