Sample Header Ad - 728x90

Why does select query change order of rows in Postgresql?

2 votes
2 answers
238 views
test=# create table r (id serial primary key, x int, y int);
CREATE TABLE
test=# insert into r (x, y) select random() * 1000, random() * 1000 from generate_series(0, 1000000);
INSERT 0 1000001
test=# 
test=# create index r_x_idx on r using btree (x);
CREATE INDEX
test=# cluster r using r_x_idx ;
CLUSTER
test=# select * from r limit 5;
  id  | x |  y  
------+---+-----
   79 | 0 | 556
 5997 | 0 | 774
 6104 | 0 |  75
 6937 | 0 | 818
 7859 | 0 | 598
(5 rows)

test=# select * from r limit 5 offset 10000;
   id   | x  |  y  
--------+----+-----
 483314 | 10 | 842
 484136 | 10 | 741
 484568 | 10 | 729
 488499 | 10 | 311
 489022 | 10 | 613
(5 rows)

test=# select * from r limit 5;
   id   | x |  y  
--------+---+-----
 330361 | 9 | 614
 330928 | 9 |  48
 331658 | 9 | 712
 332175 | 9 | 448
 332818 | 9 | 920
(5 rows)
As shown in the above, I created table r, then created a b+tree index on column x, and then clustered the table using that index. As expected, when I run select * from r limit 5; I got records with least x value, because the physical order of records are now based on the x column (more precisely, based on the b+tree index on column x). But after running select * from r limit 5 offset 10000;, the order of records are changed, without any insertion. Can someone explain this behavior?
Asked by Amir reza Riahi (155 rep)
Mar 6, 2025, 04:16 PM
Last activity: Mar 7, 2025, 08:41 AM