ORDER BY
#
In addition to simply finding the rows to be returned by a query,
an index may be able to deliver them in a specific sorted order.
This allows a query's <literal>ORDER BY</literal> specification to be honored
without a separate sorting step. Of the index types currently
supported by <productname>PostgreSQL</productname>, only B-tree
can produce sorted output — the other index types return
matching rows in an unspecified, implementation-dependent order.
単に問い合わせによって返される行を見つけ出すだけではなく、インデックスは、その行を指定した順番で取り出すことができます。
これにより、別途ソート処理を行うことなく、問い合わせのORDER BY
指定に従うことが可能です。
PostgreSQLが現在サポートするインデックスの種類の中で、B-treeのみがソート出力を行うことができます。
他の種類のインデックスでは指定なし、または、実装固有の順序でマッチした行を返します。
The planner will consider satisfying an <literal>ORDER BY</literal> specification
either by scanning an available index that matches the specification,
or by scanning the table in physical order and doing an explicit
sort. For a query that requires scanning a large fraction of the
table, an explicit sort is likely to be faster than using an index
because it requires
less disk I/O due to following a sequential access pattern. Indexes are
more useful when only a few rows need be fetched. An important
special case is <literal>ORDER BY</literal> in combination with
<literal>LIMIT</literal> <replaceable>n</replaceable>: an explicit sort will have to process
all the data to identify the first <replaceable>n</replaceable> rows, but if there is
an index matching the <literal>ORDER BY</literal>, the first <replaceable>n</replaceable>
rows can be retrieved directly, without scanning the remainder at all.
プランナは、ORDER BY
指定を満足させるために、指定に一致し利用可能なインデックスでスキャンするか、または、テーブルを物理的な順番でスキャンし明示的なソートを行うかを考慮します。
テーブルの大部分のスキャンが必要な問い合わせでは、後に発生するシーケンシャルなアクセスパターンのために要求されるディスクI/Oが少ないため、インデックスを使用するよりも、明示的なソートの方が高速です。
数行を取り出す必要がある場合のみ、インデックスの方が有用になります。
ORDER BY
とLIMIT
n
が組み合わされた場合が、重要かつ特別です。
先頭のn
行を識別するために、明示的なソートを全データに対して行う必要があります。
しかし、もしORDER BY
に合うインデックスが存在すれば、残りの部分をスキャンすることなく、先頭のn
行の取り出しを直接行うことができます。
By default, B-tree indexes store their entries in ascending order
with nulls last (table TID is treated as a tiebreaker column among
otherwise equal entries). This means that a forward scan of an
index on column <literal>x</literal> produces output satisfying <literal>ORDER BY x</literal>
(or more verbosely, <literal>ORDER BY x ASC NULLS LAST</literal>). The
index can also be scanned backward, producing output satisfying
<literal>ORDER BY x DESC</literal>
(or more verbosely, <literal>ORDER BY x DESC NULLS FIRST</literal>, since
<literal>NULLS FIRST</literal> is the default for <literal>ORDER BY DESC</literal>).
デフォルトでは、B-treeインデックスは項目を昇順で格納し、NULLを最後に格納します。
(テーブルTIDはそれ以外が等しいエントリの中で勝ちを決める列として扱われます)。
これは、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
のデフォルトだからです。)を満たす出力を生成します。
You can adjust the ordering of a B-tree index by including the
options <literal>ASC</literal>, <literal>DESC</literal>, <literal>NULLS FIRST</literal>,
and/or <literal>NULLS LAST</literal> when creating the index; for example:
インデックスを作成する時に、以下のように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);
An index stored in ascending order with nulls first can satisfy
either <literal>ORDER BY x ASC NULLS FIRST</literal> or
<literal>ORDER BY x DESC NULLS LAST</literal> depending on which direction
it is scanned in.
昇順かつNULL先頭という順で格納されたインデックスは、スキャンされる方向に依存してORDER BY x ASC NULLS FIRST
またはORDER BY x DESC NULLS LAST
を満たすことができます。
You might wonder why bother providing all four options, when two
options together with the possibility of backward scan would cover
all the variants of <literal>ORDER BY</literal>. In single-column indexes
the options are indeed redundant, but in multicolumn indexes they can be
useful. Consider a two-column index on <literal>(x, y)</literal>: this can
satisfy <literal>ORDER BY x, y</literal> if we scan forward, or
<literal>ORDER BY x DESC, y DESC</literal> if we scan backward.
But it might be that the application frequently needs to use
<literal>ORDER BY x ASC, y DESC</literal>. There is no way to get that
ordering from a plain index, but it is possible if the index is defined
as <literal>(x ASC, y DESC)</literal> or <literal>(x DESC, y ASC)</literal>.
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)
として定義されていれば、取り出すことができます。
Obviously, indexes with non-default sort orderings are a fairly specialized feature, but sometimes they can produce tremendous speedups for certain queries. Whether it's worth maintaining such an index depends on how often you use queries that require a special sort ordering. 明確なことですが、デフォルト以外のソート順を持つインデックスはかなり特殊な機能です。 しかし、特定の問い合わせにおいては恐ろしいほどの速度を向上させることがあります。 こうしたインデックスを維持する価値があるかどうかは、特殊なソート順を要求する問い合わせを使用する頻度に依存します。