Consider a read-only table T, the query pattern of this table is:
select some_columns
from T
where some_conditions
order by fixed_col1, fixed_col2
Is there a way to let me omit the order by clause at the runtime, and presort the table offline and store the sorted rows in T, so that when I select without order by, the selected rows are already sorted?
PS: the behaviour has to be documented somewhere, or a common sense to the PG community.
You can use a CLUSTER command, or you can just create new table by command CREATE TABLE xxx AS SELECT ... ORDER BY. Still you should to use ORDER BY statement because PostgreSQL has optimized reading of bigger tables and try to use synchronize reading for more processes. This synchronization can do so reading of table for one process starts in 1/3 of table - read to end, back to start and read first 1/3. So Postgres doesn't ensure reading data in physical order too.
Is there a way to let me omit the order by clause at the runtime.
No, not if you want a guaranteed sort order.
The only way to get a guaranteed sort order in a query is to use order by. there is no alternative. Relational tables represent unordered sets, so there is no such thing as "sorted rows" in a table.
You will have to use order by to get a guaranteed sort order.
the behaviour has to be documented somewhere, or a common sense to the PG community.
If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.
(emphasis mine)