sql - Postgresql 在使用大 OFFSET 时返回随机行

Question

我对这种行为很好奇:

test_db=# create table test_table(id bigint);
test_db=# insert into test_table(id) select * from generate_series(1, 1000000);
test_db=# select * from test_table offset 100000 limit 1;

   id
-------
 87169
(1 row)

test_db=# select * from test_table offset 100000 limit 1;

   id
--------
 186785
 (1 row)

test_db=# select * from test_table offset 100000 limit 1;

  id
--------
 284417
(1 row)

似乎 postgres 使用一些随机化规则向前迭代。为什么大偏移量会“混”表？之后，如果我们使用小偏移量，它会返回“稳定”值:

test_db=# select * from test_table offset 1 limit 1;
    id
  --------
   282050
  (1 row)

test_db=# select * from test_table offset 1 limit 1;
     id
  --------
   282050
   (1 row)

Answer 1

由于表记录在物理上没有排序，因此必须使用 ORDER BY在OFFSET .. LIMIT询问。否则你可能会得到随机结果:

SELECT * 
FROM test_table 
ORDER BY id 
OFFSET 100000 
LIMIT 1;        

演示: db<>fiddle