'Is ordering by PK a good choice in postgres

I have a table in postgres with following 3 columns: pk:primary key, name::text, date1::date

which sql will be faster if there are more than 1M rows in my postgres db

  • select * from t1 order by pk
  • select * from t1 order by date1
  • select * from t1 order by name
postgresql


Solution 1:[1]

The ORDER BY clauses in each query require some kind of logical sort, to order records according to one of the columns. For the case of ordering by the pk column, Postgres should be able to use the index that already exists on this column to sort in logarithmic time. For the other two columns, if unindexed, Postgres will be forced to scan the entire table once.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen

Related Questions