Creating an index in both asc and desc directions

Question

Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table has two indexes; each one with a single segment, one in asc order and one in desc order.

Apart from the wtf'ness of having an index for every field in every table, it got me thinking - is there any advantage for single-segment indexes to having two indexes with the same index segments, but one in desc and one in asc? Is there anything to be gained, or would a modern DBMS simple use the asc index and start from the end and work its way backwards if required?

Answer 1

I'd be surprised to hear of a modern database that can't do reverse order index scans.

• SQL Server certainly does and the backward scan is indicated in the execution plan.
• Firebird (search for backward in the page text).
• MySQL

Answer 2

Although Firebird indexes are in theory bidirectional, the engine doesn't actually use the bidirectionality because the reverse direction is not trustworthy because of the write order of pages: when an index page is split, the links between pages are rewritten, if this interleaves with a reverse read it might read a link that is still pointing to the old index page instead of the newly added page, causing it to skip index entries. This is explained in Firebird for the Database Expert: Episode 3 - On Disk Consistency.

So as the bidirectionality of the index is not guaranteed, Firebird only reads an index in its declared direction (either ascending or descending). Now as to why your database has all these indexes, I'd assume that either the person designing the database didn't know what he was doing, or he assumed that adding these indexes would making sorting on any column faster.

Answer 3

Yes, there is a noticeable performance hit (FB 2.5) on a large table when not using a descending index for i.e.:

select first 1 * 
from mytable 
where pk_id >= 200000 
order by pk_id desc

This query is used to find the previous record, based on the value of the primary key field "pk_id" (Integer).