Además de simplemente encontrar las filas que se devolverán en una consulta, un índice puede ser capaz de entregarlas en un orden de clasificación específico. Esto permite cumplir con la especificación ORDER BY de una consulta sin un paso de ordenación independiente. De los tipos de índice actualmente admitidos por PostgreSQL, solo B-tree puede producir salidas ordenadas; los otros tipos de índice devuelven las filas coincidentes en un orden no especificado y dependiente de la implementación.

El planificador considerará satisfacer una especificación ORDER BY ya sea escaneando un índice disponible que coincida con la especificación, o escaneando la tabla en orden físico y realizando una ordenación explícita. Para una consulta que requiere escanear una gran fracción de la tabla, es probable que una ordenación explícita sea más rápida que usar un índice porque requiere menos E/S de disco debido a que sigue un patrón de acceso secuencial. Los índices son más útiles cuando solo se necesitan recuperar unas pocas filas. Un caso especial importante es ORDER BY en combinación con LIMIT n: una ordenación explícita tendrá que procesar todos los datos para identificar las primeras n filas, pero si hay un índice que coincide con el ORDER BY, las primeras n filas se pueden recuperar directamente, sin escanear el resto en absoluto.

Por defecto, los índices B-tree almacenan sus entradas en orden ascendente con los nulos al final (el TID de la tabla se trata como una columna de desempate entre entradas que de otro modo serían iguales). Esto significa que un escaneo hacia adelante de un índice en la columna x produce una salida que satisface a ORDER BY x (o de manera más detallada, ORDER BY x ASC NULLS LAST). El índice también se puede escanear hacia atrás, produciendo una salida que satisface a ORDER BY x DESC (o de manera más detallada, ORDER BY x DESC NULLS FIRST, ya que NULLS FIRST es el valor predeterminado para ORDER BY DESC).

Puedes ajustar la ordenación de un índice B-tree incluyendo las opciones ASC, DESC, NULLS FIRST y/o NULLS LAST al crear el índice; por ejemplo:

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

Un índice almacenado en orden ascendente con nulos al principio puede satisfacer tanto ORDER BY x ASC NULLS FIRST como ORDER BY x DESC NULLS LAST dependiendo de en qué dirección se escanee.

Te preguntarás por qué molestarse en proporcionar las cuatro opciones, cuando dos opciones junto con la posibilidad de escaneo hacia atrás cubrirían todas las variantes de ORDER BY. En índices de una sola columna, las opciones son de hecho redundantes, pero en índices multicolumna pueden ser útiles. Considera un índice de dos columnas en (x, y): este puede satisfacer ORDER BY x, y si escaneamos hacia adelante, o ORDER BY x DESC, y DESC si escaneamos hacia atrás. Pero podría ser que la aplicación con frecuencia necesite usar ORDER BY x ASC, y DESC. No hay forma de obtener esa ordenación a partir de un índice simple, pero es posible si el índice se define como (x ASC, y DESC) o (x DESC, y ASC).

Obviamente, los índices con ordenaciones no predeterminadas son una característica bastante especializada, pero a veces pueden producir aceleraciones tremendas para ciertas consultas. Si vale la pena mantener dicho índice depende de la frecuencia con la que utilices consultas que requieran una ordenación especial.