Unordered result using order by query

Question

I'm using pgr_astar function of PostgreSQL to get the optimal path, and I mentioned that the result should be ordered by seq:

cur.execute("create table %s as SELECT a.seq, a.id1 AS node, a.id2 AS edge, 
b.source, b.target, b.cost, b.reverse_cost, b.km, b.%s, b.%s, b.estimated_time as time, b.ang_elev, b.geom_way
FROM pgr_astar ('SELECT id, source, target, cost as cost, x1, y1, x2, y2, reverse_cost FROM chicago_2po_4pgr', %s, %s, true, true) as a
LEFT JOIN chicago_2po_4pgr as b  ON a.id2 = b.id order by seq    
 " %(nom_table,colonne_cost,colonne_cost_reverse,depart,destination)) 

And I'm getting this result (sequences are not ordered)

0
.
.
124
125
135
136
137
138
139
140
129
130
131
132
133
134

Any suggestion please? How can I modify this query to get an ordered result.

Answer 1

How did you query rows from the table created with your displayed statement?

While the ORDER BY in the displayed query should do its job properly, that's just sorting physical rows in the newly created table. But the physical order of rows is subject to change. VACUUM and other background processes can reorder rows freely. Any write access to the table will do so, too.

Plus, more importantly, SELECT * FROM tbl is not bound to return rows in current physical order. While returning rows in physical order is typically the fastest way, and Postgres will typically just do that, nothing of the sort is guaranteed. You have to append another ORDER BY to get sorted rows (which defies the purpose of testing the current physical sort order).

Test the current physical order of rows like this:

SELECT ctid, * FROM tbl ORDER BY ctid;

Explanation for ctid:

• How do I decompose ctid into page and row numbers?

And verify the original SELECT in the question by running it without CREATE TABLE AS, and you'll see perfectly sorted rows ...

Related:

• Subtract two records of the same column in a table
• Select last n rows without use of order by clause