Why does select query change order of rows in Postgresql? [duplicate]

Question

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?

Also dba.stackexchange.com/questions/186885/… and dba.stackexchange.com/questions/6051/… and dba.stackexchange.com/questions/300170/… and dba.stackexchange.com/questions/38710/… You could get different results every time you execute the same query, or you could get the same results from both queries. Without ORDER BY all bets are off — Charlieface
– Charlieface, Commented Mar 6 at 16:25
@Charlieface Thanks for your comment, I know in theory select query does not guarantee order, but after clustering the table, I expected it to be ordered by that index. — Amir reza Riahi
– Amir reza Riahi, Commented Mar 6 at 16:51
How the data is stored on disk and how the data is returned from a query without ORDER BY, are two very different things. — Frank Heikens
– Frank Heikens, Commented Mar 6 at 21:33
But why do you expect it? No expectations should exist here. — Charlieface
– Charlieface, Commented Mar 7 at 1:19

Laurenz Albe · Accepted Answer · 2025-03-07 08:41:04Z

You are experiencing the feature “synchronized sequential scans”, which was introduced in PostgreSQL 8.3 and can be turned off by disabling the parameter synchronize_seqscans. The description:

This allows sequential scans of large tables to synchronize with each other, so that concurrent scans read the same block at about the same time and hence share the I/O workload. When this is enabled, a scan might start in the middle of the table and then “wrap around” the end to cover all rows, so as to synchronize with the activity of scans already in progress. This can result in unpredictable changes in the row ordering returned by queries that have no ORDER BY clause. Setting this parameter to off ensures the pre-8.3 behavior in which a sequential scan always starts from the beginning of the table. The default is on.

Different from what you seem to expect, a LIMIT without an ORDER BY will use a sequential scan of the table, and that scan can start in the middle of the table.

Alessandro Vecchio · Accepted Answer · 2025-03-06 18:29:24Z

5

The only thing that CLUSTER guarantee in this scenario, is that rows are physically sorted by values of x inside the table pages but, due to how Postgresql memory optimization works, the order of retrieval of those pages can't be guaranteed. Without an ORDER BY clause, a SELECT * will get you clusters of sorted rows.

edited Mar 6 at 18:29

answered Mar 6 at 18:03

Alessandro Vecchio

864 bronze badges

Add a comment |

Stack Exchange Network

Why does select query change order of rows in Postgresql? [duplicate]

2 Answers 2

Linked

Hot Network Questions

Why does select query change order of rows in Postgresql? [duplicate]

2 Answers 2

Linked

Related

Hot Network Questions