Web Development with Clojure, Third Edition: how about sorting by timestamp? Page 245 P1.0

Somehow postgresql returns messages sorted by author given the two queries. Perhaps it’d make sense to add “order by timestamp desc” to the end of both queries.

That would be ideal, and is necessary to do before the application would be ready for production if a consistent order is desired.

See this quote from PostgreSQL’s documentation on sort order.

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. 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.

It’s likely that the relative sizes of your posts and users tables are causing Postgres to use a different query plan that results in using the incidental order of the author table instead of the posts table.

Up until this point we never cared much about the order of the posts, so we didn’t ever add an order by clause. We do eventually introduce a timeline query that sorts explicitly in Chapter 8.