Postgresql在使用大偏移时返回随机行

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

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

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

Demo:db<>fiddle

Answer 2

PostgreSQL有一个特性，它尝试在同一个大表上获得多个并发顺序扫描，所有扫描都同时在表的同一部分上工作，这样它们就可以共享缓存空间，而不必分别从磁盘上读取相同的数据。这样做的一个副作用是，对于连续执行的部分(如LIMIT)顺序扫描，每次扫描都从前一次扫描的结束位置开始。

同步点总是在页边界，所以使用低OFFSET和低LIMIT，您可以一遍又一遍地从同一页(以及该页的开头)读取数据并获得相同的数据。

如果您需要为某些内部测试目的获得更稳定的结果，则可以使用set synchronize_seqscans TO off;关闭此功能。如果你这样做，你就像冯·诺伊曼所说的那样，生活在罪恶的状态中。