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?
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.ASC
(default) orDESC
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