Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Watch 1
Star 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/indexes-ordering.html

64 lines
6.4KB
Raw Permalink Blame History 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.4. Indexes and ORDER BY</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes" /><link rel="next" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.4. Indexes and <code xmlns="http://www.w3.org/1999/xhtml" class="literal">ORDER BY</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="INDEXES-ORDERING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.4. Indexes and <code class="literal">ORDER BY</code></h2></div></div></div><a id="id-1.5.10.7.2" class="indexterm"></a><p>

  3.    In addition to simply finding the rows to be returned by a query,

  4.    an index may be able to deliver them in a specific sorted order.

  5.    This allows a query's <code class="literal">ORDER BY</code> specification to be honored

  6.    without a separate sorting step.  Of the index types currently

  7.    supported by <span class="productname">PostgreSQL</span>, only B-tree

  8.    can produce sorted output — the other index types return

  9.    matching rows in an unspecified, implementation-dependent order.

  10.   </p><p>

  11.    The planner will consider satisfying an <code class="literal">ORDER BY</code> specification

  12.    either by scanning an available index that matches the specification,

  13.    or by scanning the table in physical order and doing an explicit

  14.    sort.  For a query that requires scanning a large fraction of the

  15.    table, an explicit sort is likely to be faster than using an index

  16.    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

  19.    special case is <code class="literal">ORDER BY</code> in combination with

  20.    <code class="literal">LIMIT</code> <em class="replaceable"><code>n</code></em>: an explicit sort will have to process

  21.    all the data to identify the first <em class="replaceable"><code>n</code></em> rows, but if there is

  22.    an index matching the <code class="literal">ORDER BY</code>, the first <em class="replaceable"><code>n</code></em>

  23.    rows can be retrieved directly, without scanning the remainder at all.

  24.   </p><p>

  25.    By default, B-tree indexes store their entries in ascending order

  26.    with nulls last (table TID is treated as a tiebreaker column among

  27.    otherwise equal entries).  This means that a forward scan of an

  28.    index on column <code class="literal">x</code> produces output satisfying <code class="literal">ORDER BY x</code>

  29.    (or more verbosely, <code class="literal">ORDER BY x ASC NULLS LAST</code>).  The

  30.    index can also be scanned backward, producing output satisfying

  31.    <code class="literal">ORDER BY x DESC</code>

  32.    (or more verbosely, <code class="literal">ORDER BY x DESC NULLS FIRST</code>, since

  33.    <code class="literal">NULLS FIRST</code> is the default for <code class="literal">ORDER BY DESC</code>).

  34.   </p><p>

  35.    You can adjust the ordering of a B-tree index by including the

  36.    options <code class="literal">ASC</code>, <code class="literal">DESC</code>, <code class="literal">NULLS FIRST</code>,

  37.    and/or <code class="literal">NULLS LAST</code> when creating the index; for example:

  38. </p><pre class="programlisting">

  39. CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);

  40. CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

  41. </pre><p>

  42.    An index stored in ascending order with nulls first can satisfy

  43.    either <code class="literal">ORDER BY x ASC NULLS FIRST</code> or

  44.    <code class="literal">ORDER BY x DESC NULLS LAST</code> depending on which direction

  45.    it is scanned in.

  46.   </p><p>

  47.    You might wonder why bother providing all four options, when two

  48.    options together with the possibility of backward scan would cover

  49.    all the variants of <code class="literal">ORDER BY</code>.  In single-column indexes

  50.    the options are indeed redundant, but in multicolumn indexes they can be

  51.    useful.  Consider a two-column index on <code class="literal">(x, y)</code>: this can

  52.    satisfy <code class="literal">ORDER BY x, y</code> if we scan forward, or

  53.    <code class="literal">ORDER BY x DESC, y DESC</code> if we scan backward.

  54.    But it might be that the application frequently needs to use

  55.    <code class="literal">ORDER BY x ASC, y DESC</code>.  There is no way to get that

  56.    ordering from a plain index, but it is possible if the index is defined

  57.    as <code class="literal">(x ASC, y DESC)</code> or <code class="literal">(x DESC, y ASC)</code>.

  58.   </p><p>

  59.    Obviously, indexes with non-default sort orderings are a fairly

  60.    specialized feature, but sometimes they can produce tremendous

  61.    speedups for certain queries.  Whether it's worth maintaining such an

  62.    index depends on how often you use queries that require a special

  63.    sort ordering.

  64.   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-multicolumn.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-bitmap-scans.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.3. Multicolumn Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 11.5. Combining Multiple Indexes</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1