PostgreSQL Index Tuning – Benefits of Ordered and Unordered Index on Same Column

indexindex-tuningpostgresql

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?

Best Answer

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.

Related Question