En plus de trouver simplement les lignes à renvoyer à une requête, un index peut être capable de les renvoyer dans un ordre spécifique. Ceci permet le respect d'une clause ORDER BY sans nécessiter une étape de tri séparée. De tous les types d'index actuellement supportés par PostgreSQL™, seuls les B-tree peuvent produire une sortie triée -- les autres types d'index renvoient les lignes correspondantes dans un ordre non spécifié, dépendant de l'implémentation.

Le planificateur considérera qu'une clause ORDER BY est satisfaite soit en parcourant tout index disponible qui correspond à la clause soit en parcourant la table dans l'ordre physique et en réalisant un tri explicite. Pour une requête qui nécessite une fraction importante de la table, le tri explicite a des chances d'être plus rapide car il nécessite moins d'entrées/sorties disque grâce à un modèle d'accès mieux ordonné. Les index sont plus utiles quand seules quelques lignes doivent être récupérées. Un cas spécial important est le ORDER BY en combinaison avec LIMIT n : un tri explicite devra traiter toutes les données pour identifier les n première lignes, mais s'il y a un index correspondant au ORDER BY, alors les n lignes peuvent être récupérées directement sans avoir besoin de parcourir le reste.

Par défaut, les index B-tree stockent leurs entrées dans l'ordre ascendant avec les valeurs NULL en dernier. Cela signifie qu'un parcours en avant d'un index sur une colonne x produira une sortie satisfaisant un ORDER BY x (ou en plus verbeux ORDER BY x ASC NULLS LAST). L'index peut aussi être parcouru en arrière, produisant ainsi une sortie satisfaisant un ORDER BY x DESC (ou en plus verbeux ORDER BY x DESC NULLS FIRST car NULLS FIRST est la valeur par défaut pour un ORDER BY DESC).

Vous pouvez ajuster l'ordre d'un index B-tree en incluant les options ASC, DESC, NULLS FIRST, et/ou NULLS LAST lors de la création de l'index ; par exemple :

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

Un index stocké en ordre ascendant avec les valeurs NULL en premier peut satisfaire soit ORDER BY x ASC NULLS FIRST soit ORDER BY x DESC NULLS LAST suivant la direction du parcours.

Vous vous demandez l'intérêt des quatre options quand deux options ensemble avec la possibilité d'un parcours en sens inverse couvrirait toutes les variantes d'ORDER BY. Dans les index sur une colonne, les options sont en fait redondantes mais dans un index à plusieurs colonnes, elles sont utiles. Pensez à un index à deux colonnes (x, y) : il peut satisfaire une clause ORDER BY x, y si nous parcourons en avant, ou ORDER BY x DESC, y DESC dans un parcours inverse. Mais il se peut que l'application utilise fréquemment ORDER BY x ASC, y DESC. Il n'y a pas moyen d'obtenir cet ordre à partir d'un index standard, mais c'est possible si l'index est défini ainsi : (x ASC, y DESC) or (x DESC, y ASC).

Évidemment, les index avec un ordre autre que celui par défaut sont une fonctionnalité très spécialisée. Quelque fois, elles peuvent apporter des performances conséquentes pour certaines requêtes. Leur intérêt dépend surtout de la fréquence des requêtes nécessitant cet ordre spécial.