Search code examples
databasepostgresqlindexingquery-optimization

Is sorting a table by a time field (where auto_now_add=True), equivalent to sorting it by the said table's primary key ID?

Imagine a database table with a time_of_insert attribute, which is auto-filled by the current time for every INSERT (e.g. in a Django model's example, the attribute has auto_now_add=True).

In that case, is sorting the said table by time_of_insert equivalent to sorting it by each row's ID (primary key)?

Background: I ask because I have a table where I have an auto created time_of_insert attribute. I'm currently sorting the said table by time_of_insert; this field isn't indexed. I feel I can simply sort it by id, instead of indexing time_of_insert - that way I get fast results AND I don't have to incur the over-head of indexing one more table column. My DB is postgres.

What am I missing?

Solution

  • Now it's not.

    id guarantees uniqueness. And your datetime column does not.

    So in case if there are 2 rows with the same time_of_insert value - then the result set order is not guaranteed.