ORDER BY
単に問い合わせによって返される行を見つけ出すだけではなく、インデックスは、その行を指定した順番で取り出すことができます。
これにより、別途ソート処理を行うことなく、問い合わせのORDER BY
指定に従うことが可能です。
PostgreSQLが現在サポートするインデックスの種類の中で、B-Treeのみがソート出力を行うことができます。
他の種類のインデックスでは指定なし、または、実装固有の順序でマッチした行を返します。
プランナは、ORDER BY
指定を満足させるために、指定に一致し利用可能なインデックスでスキャンするか、または、テーブルを物理的な順番でスキャンし明示的なソートを行うかを考慮します。
テーブルの大部分のスキャンが必要な問い合わせでは、後に発生するシーケンシャルなアクセスパターンのために要求されるディスクI/Oが少ないため、インデックスを使用するよりも、明示的なソートの方が高速です。
数行を取り出す必要がある場合のみ、インデックスの方が有用になります。
ORDER BY
とLIMIT
n
が組み合わされた場合が、重要かつ特別です。
先頭のn
行を識別するために、明示的なソートを全データに対して行う必要があります。
しかし、もしORDER BY
に合うインデックスが存在すれば、残りの部分をスキャンすることなく、先頭のn
行の取り出しを直接行うことができます。
デフォルトでは、B-treeインデックスは項目を昇順で格納し、NULLを最後に格納します。
これは、x
列に対するインデックスの前方方向のスキャンでORDER BY x
(より冗長にいえばORDER BY x ASC NULLS LAST
)を満たす出力を生成することを意味します。
また、インデックスを後方方向にスキャンすることもでき、この場合、ORDER BY x DESC
(より冗長にいえばORDER BY x DESC NULLS FIRST
。NULLS FIRST
がORDER BY DESC
のデフォルトだからです。)を満たす出力を生成します。
インデックスを作成する時に、以下のようにASC
、DESC
、NULLS FIRST
、NULLS LAST
オプションを組み合わせて指定することにより、B-treeインデックスの順序を調整することができます。
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
昇順かつNULL先頭という順で格納されたインデックスは、スキャンされる方向に依存してORDER BY x ASC NULLS FIRST
またはORDER BY x DESC NULLS LAST
を満たすことができます。
4つの全方向を提供する理由が何か、後方方向へのスキャンの可能性があることを考慮した2方向で、すべての種類のORDER BY
を網羅できるのではないかと疑問を持つかもしれません。
単一列に対するインデックスでは、このオプションは実際冗長ですが、複数列に対するインデックスでは有用になります。
(x, y)
という2つの列に対するインデックスを仮定します。
これを前方方向にスキャンすればORDER BY x, y
を満たし、後方方向にスキャンすればORDER BY x DESC, y DESC
を満たします。
しかし、ORDER BY x ASC, y DESC
をよく使用しなければならないアプリケーションが存在する可能性があります。
簡素なインデックスからこの順序を取り出す方法がありません。
しかし、インデックスが(x ASC, y DESC)
または(x DESC, y ASC)
として定義されていれば、取り出すことができます。
明確なことですが、デフォルト以外のソート順を持つインデックスはかなり特殊な機能です。 しかし、特定の問い合わせにおいては恐ろしいほどの速度を向上させることがあります。 こうしたインデックスを維持する価値があるかどうかは、特殊なソート順を要求する問い合わせを使用する頻度に依存します。