Postgresql returns random rows when using large OFFSET

Question

I'm curious of this behaviour:

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)

Seems that postgres iterates forward with some randomizing rule. Why does large offset "mix" table? After that if we use small offset it returns "stable" value:

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)

`limit` without an order by gives you no guarantees about the rows you will get. It's pretty much senseless — , Aug 04 '21 at 15:02
Related, possibly duplicate: [The order of a SQL Select statement without Order By clause](https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause) — Mark Rotteveel, Aug 04 '21 at 15:17

Jim Jones · Answer 1 · 2021-08-04T15:10:11.773

3

As the table records are not physically ordered, it is imperative to use ORDER BY in a OFFSET .. LIMIT query. Otherwise you might get random results:

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

Demo: db<>fiddle

edited Aug 04 '21 at 15:10

answered Aug 04 '21 at 15:04

Jim Jones

18,404
3
35
44

I know, thanks. But I want to know the reason of this behaviour. There is some nuance, I guess – Slava Nikulin Aug 04 '21 at 15:09
1

@SlavaNikulin: without an `order by` the database is free to return the rows in any order it wants. – Aug 04 '21 at 15:11
1

@SlavaNikulin I see. Well, data inserted into a table aren't ordered by anything. So, if you need to perform an operation in ordered manner, you have to tell the database to **explicitly** do so.. otherwise you might get different results by every request. – Jim Jones Aug 04 '21 at 15:14

score 2 · Accepted Answer · answered Aug 04 '21 at 16:26

PostgreSQL has a feature that tries to get multiple concurrent sequential scans on the same large table to all work on the same part of the table at the same time, so that they can share cache space and don't have to each read the same data off disk individually. A side effect of this is that for partial (like with LIMIT) sequential scans done consecutively, each one starts where the previous one left off.

The synchronization points are always at page boundaries, so with a low OFFSET and a low LIMIT you just keep reading data from the same page (and from that page's beginning) over and over again and getting the same data.

You can turn this off with set synchronize_seqscans TO off; if you need to get more stable results for some internal testing purpose. If you do this you are, as von Neumann might say, living in a state of sin.

Postgresql returns random rows when using large OFFSET

2 Answers2