如何處理選擇中的大偏移量？

具有 200k 行的表jtest，每行包含 jsonb { id: "<uuid>", key: <index> }（<index>每行遞增 1-200k 的整數）。上還有 btree 索引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'));

第一次查詢（快速）：

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"

具有大偏移量的第二個查詢（慢）：

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"

在 PG 文件中，我發現：

被 OFFSET 子句跳過的行仍然必須在伺服器內部計算；因此，較大的 OFFSET 可能效率低下。

但是他們沒有說如何處理大偏移量。如何改進此查詢？這種行為（大偏移量效率低下）也是所有 RDMS 或僅 PG（使用 9.4）共有的嗎？

為什麼不嘗試 where 子句？如果你索引j.data->'key'，awhere將只是一個 log(n)。

另一個原因可能order by j.data->'key'是計算速度慢。將其編入索引也可以提高您的訂單量（但好處將遠不如 where 子句本身帶來的好處那麼明顯）。

本文可能有助於更好地回答您的問題： https ://use-the-index-luke.com/sql/partial-results/fetch-next-page

在可能的情況下，嘗試記住最後一次看到的值並使用查詢語句where some_sort_key > '40000' LIMIT 100或其他內容。這通常比使用OFFSET.

（或類似的）性能問題適用於所有具有與AFAIKOFFSET功能等效的 SQL 擴展的 RDBMS 。LIMIT/OFFSET

這是一個非常好的展示文稿，詳細解釋了這一點：Pagination Done the Right Way

提高性能的另一個選擇是使用伺服器端游標。參見手冊中的DECLARE和FETCH。

但是請注意，伺服器端游標在請求之間具有任意長延遲的連接池應用程序中表現不佳，因為數據庫會話等待客戶端的下一個請求。-克雷格林格

引用自：https://dba.stackexchange.com/questions/111962