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, 2018 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, 2018 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, 2018 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, 2018 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, 2018 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.

154k19 gold badges378 silver badges522 bronze badges

answered Dec 17, 2018 at 14:36

Freiheit

2292 silver badges16 bronze badges

Add a comment |

1 Answer 1