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
Last activity: Mar 7, 2025, 08:41 AM