1

I have inherited a postgres database where there are two identical indexes except for their order:

CREATE INDEX avail_time_ix ON table (availability_time);
CREATE INDEX avail_time_dc_ix ON table (availability_time DESC);

Is there any benefit to having both the ordered index and the unordered index?

  • 4
    The first one is not "unordered", if you omit the sort order then it defaults to ASC. Except for some very rare corner cases, the sort order doesn't really matter these days. Scanning an index "backwards" is (nearly) as efficient as scanning it "forwards". So I would say: yes, one of them use not needed. – a_horse_with_no_name Dec 12 '18 at 21:27
  • 2
    but you usually want new entries at the end of the index. So if newer availability times are greater than what's in your table, leave it ASC. As a_horse_with_no_name said, the indexes are b-tree, and the engine can navigate them as easily backwards as forwards, so the only reason to have one order over another is to reduce fragmentation. – Jonathan Fite Dec 12 '18 at 22:04
  • 1
    As others commented: ASC (default) or DESC doesn't make much difference - for a single column btree index. It can make a big difference for multicolumn indexes: dba.stackexchange.com/a/39599/3684 – Erwin Brandstetter Dec 13 '18 at 17:57
  • If any of ya'll want the internet points please post as an answer instead of a comment. Your answers check out based on some articles I read as well as actual query plans. – Freiheit Dec 13 '18 at 18:27
  • 1
    @ErwinBrandstetter the link to the other answer you posted is fantastic. That covers both basics of index ordering as well as some good options that I can use to tailor this to my applications specific use cases – Freiheit Dec 13 '18 at 18:30
3

I am combining several good comments into a comprehensive answer.

Order on a mutli-column index could have a bigger impact on efficiency than a single column. In my case it is only one column.

Postgres can scan indexes forwards and backwards just as efficiently. The index which does not have an explicit order is ordered ASC by default.

Specifies ascending sort order (which is the default).

This means that the two indexes are redundant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.