PostgreSQL 索引和ORDER BY

除了簡單地查找查詢要返回的行外，一個索引可能還需要將它們以指定的順序傳遞。這使得查詢中的ORDER BY不需要獨立的排序步驟。在PostgreSQL當前支持的索引類型中，只有B-tree可以產(chǎn)生排序后的輸出，其他索引類型會把行以一種沒有指定的且與實現(xiàn)相關的順序返回。

規(guī)劃器會考慮以兩種方式來滿足一個ORDER BY說明：掃描一個符合說明的可用索引，或者先以物理順序掃描表然后再顯式排序。對于一個需要掃描表的大部分的查詢，一個顯式的排序很可能比使用一個索引更快，因為其順序訪問模式使得它所需要的磁盤I/O更少。只有在少數(shù)行需要被取出時，索引才會更有用。一種重要的特殊情況是ORDER BY與LIMIT n 聯(lián)合使用：一個顯式的排序?qū)幚硭械臄?shù)據(jù)來確定最前面的n行，但如果有一個符合ORDER BY的索引，前n行將會被直接獲取且根本不需要掃描剩下的數(shù)據(jù)。

默認情況下，B-tree索引將它的項以升序方式存儲，并將空值放在最后(表TID被處理為其它相等條目之間的分線器列)。這意味著對列x上索引的一次前向掃描將產(chǎn)生滿足ORDER BY x（或者更長的形式：ORDER BY x ASC NULLS LAST）的結(jié)果。索引也可以被后向掃描，產(chǎn)生滿足ORDER BY x DESC（ ORDER BY x DESC NULLS FIRST， NULLS FIRST是ORDER BY DESC的默認情況）。

我們可以在創(chuàng)建B-tree索引時通過ASC、DESC、NULLS FIRST和NULLS LAST選項來改變索引的排序，例如：

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

一個以升序存儲且將空值前置的索引可以根據(jù)掃描方向來支持ORDER BY x ASC NULLS FIRST或 ORDER BY x DESC NULLS LAST。

讀者可能會疑惑為什么要麻煩地提供所有四個選項，因為兩個選項連同可能的后向掃描可以覆蓋所有ORDER BY的變體。在單列索引中這些選項確實有冗余，但是在多列索引中它們卻很有用?？紤](x, y)上的一個兩列索引：它可以通過前向掃描滿足ORDER BY x, y，或者通過后向掃描滿足ORDER BY x DESC, y DESC。但是應用可能需要頻繁地使用 ORDER BY x ASC, y DESC。這樣就沒有辦法從通常的索引中得到這種順序，但是如果將索引定義為(x ASC, y DESC)或者(x DESC, y ASC)就可以產(chǎn)生這種排序。

顯然，具有非默認排序的索引是相當專門的特性，但是有時它們會為特定查詢提供巨大的速度提升。是否值得維護這樣一個索引取決于我們會多頻繁地使用需要特殊排序的查詢。