sort a large table by its primary key and save it back So I have a large table (>50 million rows) with an auto incremented primary key column, which is filled by a script running on multiple cores. Now, when I do a SELECT, entries are returned 'out of order', which, I guess, is the result of the table being filled by several processes in parallel.

I could do a SELECT ... ORDER BY &lt;primary key&gt;, but this select takes much more time than the simple SELECT.

Is there a way to sort the table by its primary key and save it back, without loading all rows into memory? I found a similar question answered here, but I cannot apply it to my case without adding another column, which, given the size of the table, I'm reluctant to.

I'm Running Firebird Superserver

Any advice appreciated.
Unless you specify an ordering using ORDER BY you can never guarantee the order of the output, even if you are very careful what order you put things in.

I'm not familiar with Firebird, but in many databases the difference could be explained by your primary key not being clustered, so when with the ORDER BY present it scans down the index in order then for each row looks up the extra data from the heap, where the version without explicit sorting will just scan the heap. With a clustered index, the index and the heap are one in the same so this extra work is not needed. Another way to avoid extra lookups is to include all the other columns that you need in an index that the DB can use. This is not space efficient though, especially for wide tables. To see if post-scan lookups are your performance problem see how SELECT &lt;pk&gt; FROM &lt;table&gt; ORDER BY &lt;pk&gt; compares to your other timing tests.

The above assumes you have no other filtering clauses, or joins and the large table is not seen as the primary one by the query planner, that complicate matters. If you do have those you should present fuller details of he query and table structure for better help. It may be that adding the ORDER BY is forcing a more complex query to spool the data out to a temporary table (potentially on disk) before rereading in order. Index use hints may help with this sort of problem, but without further details of the query and structures we cant' be more specific on that.