Is there any benefit to having both an ordered and unordered index on the same column in postgres?

Question

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?

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
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
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
@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

Erwin Brandstetter · Accepted Answer · 2018-12-18 22:29:35Z

3

I am combining several good comments into a comprehensive answer.

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.

145k19 gold badges345 silver badges488 bronze badges

answered Dec 17 '18 at 14:36

Freiheit

2192 silver badges15 bronze badges

Add a comment |

1 Answer 1