begriffs open source - ai-pg/blob - full-docs/txt/indexes-ordering.txt

   1
   2 11.4. Indexes and ORDER BY #
   3
   4    In addition to simply finding the rows to be returned by a query, an
   5    index may be able to deliver them in a specific sorted order. This
   6    allows a query's ORDER BY specification to be honored without a
   7    separate sorting step. Of the index types currently supported by
   8    PostgreSQL, only B-tree can produce sorted output — the other index
   9    types return matching rows in an unspecified, implementation-dependent
  10    order.
  11
  12    The planner will consider satisfying an ORDER BY specification either
  13    by scanning an available index that matches the specification, or by
  14    scanning the table in physical order and doing an explicit sort. For a
  15    query that requires scanning a large fraction of the table, an explicit
  16    sort is likely to be faster than using an index because it requires
  17    less disk I/O due to following a sequential access pattern. Indexes are
  18    more useful when only a few rows need be fetched. An important special
  19    case is ORDER BY in combination with LIMIT n: an explicit sort will
  20    have to process all the data to identify the first n rows, but if there
  21    is an index matching the ORDER BY, the first n rows can be retrieved
  22    directly, without scanning the remainder at all.
  23
  24    By default, B-tree indexes store their entries in ascending order with
  25    nulls last (table TID is treated as a tiebreaker column among otherwise
  26    equal entries). This means that a forward scan of an index on column x
  27    produces output satisfying ORDER BY x (or more verbosely, ORDER BY x
  28    ASC NULLS LAST). The index can also be scanned backward, producing
  29    output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC
  30    NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).
  31
  32    You can adjust the ordering of a B-tree index by including the options
  33    ASC, DESC, NULLS FIRST, and/or NULLS LAST when creating the index; for
  34    example:
  35 CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
  36 CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
  37
  38    An index stored in ascending order with nulls first can satisfy either
  39    ORDER BY x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on
  40    which direction it is scanned in.
  41
  42    You might wonder why bother providing all four options, when two
  43    options together with the possibility of backward scan would cover all
  44    the variants of ORDER BY. In single-column indexes the options are
  45    indeed redundant, but in multicolumn indexes they can be useful.
  46    Consider a two-column index on (x, y): this can satisfy ORDER BY x, y
  47    if we scan forward, or ORDER BY x DESC, y DESC if we scan backward. But
  48    it might be that the application frequently needs to use ORDER BY x
  49    ASC, y DESC. There is no way to get that ordering from a plain index,
  50    but it is possible if the index is defined as (x ASC, y DESC) or (x
  51    DESC, y ASC).
  52
  53    Obviously, indexes with non-default sort orderings are a fairly
  54    specialized feature, but sometimes they can produce tremendous speedups
  55    for certain queries. Whether it's worth maintaining such an index
  56    depends on how often you use queries that require a special sort
  57    ordering.