Sophie: postgresql9.6-docs-9.6.17-2.mga7 noarch

postgresql9.6-docs-9.6.17-2.mga7.noarch.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Index Scanning</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.6.17 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Index Access Method Interface Definition"
HREF="indexam.html"><LINK
REL="PREVIOUS"
TITLE="Index Access Method Functions"
HREF="index-functions.html"><LINK
REL="NEXT"
TITLE="Index Locking Considerations"
HREF="index-locking.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2020-02-24T19:56:52"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.6.17 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Index Access Method Functions"
HREF="index-functions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="indexam.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 59. Index Access Method Interface Definition</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Index Locking Considerations"
HREF="index-locking.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="INDEX-SCANNING"
>59.3. Index Scanning</A
></H1
><P
>   In an index scan, the index access method is responsible for regurgitating
   the TIDs of all the tuples it has been told about that match the
   <I
CLASS="FIRSTTERM"
>scan keys</I
>.  The access method is <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> involved in
   actually fetching those tuples from the index's parent table, nor in
   determining whether they pass the scan's time qualification test or other
   conditions.
  </P
><P
>   A scan key is the internal representation of a <TT
CLASS="LITERAL"
>WHERE</TT
> clause of
   the form <TT
CLASS="REPLACEABLE"
><I
>index_key</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>operator</I
></TT
>
   <TT
CLASS="REPLACEABLE"
><I
>constant</I
></TT
>, where the index key is one of the columns of the
   index and the operator is one of the members of the operator family
   associated with that index column.  An index scan has zero or more scan
   keys, which are implicitly ANDed &mdash; the returned tuples are expected
   to satisfy all the indicated conditions.
  </P
><P
>   The access method can report that the index is <I
CLASS="FIRSTTERM"
>lossy</I
>, or
   requires rechecks, for a particular query.  This implies that the index
   scan will return all the entries that pass the scan key, plus possibly
   additional entries that do not.  The core system's index-scan machinery
   will then apply the index conditions again to the heap tuple to verify
   whether or not it really should be selected.  If the recheck option is not
   specified, the index scan must return exactly the set of matching entries.
  </P
><P
>   Note that it is entirely up to the access method to ensure that it
   correctly finds all and only the entries passing all the given scan keys.
   Also, the core system will simply hand off all the <TT
CLASS="LITERAL"
>WHERE</TT
>
   clauses that match the index keys and operator families, without any
   semantic analysis to determine whether they are redundant or
   contradictory.  As an example, given
   <TT
CLASS="LITERAL"
>WHERE x &gt; 4 AND x &gt; 14</TT
> where <TT
CLASS="LITERAL"
>x</TT
> is a b-tree
   indexed column, it is left to the b-tree <CODE
CLASS="FUNCTION"
>amrescan</CODE
> function
   to realize that the first scan key is redundant and can be discarded.
   The extent of preprocessing needed during <CODE
CLASS="FUNCTION"
>amrescan</CODE
> will
   depend on the extent to which the index access method needs to reduce
   the scan keys to a <SPAN
CLASS="QUOTE"
>"normalized"</SPAN
> form.
  </P
><P
>   Some access methods return index entries in a well-defined order, others
   do not.  There are actually two different ways that an access method can
   support sorted output:

    <P
></P
></P><UL
><LI
><P
>       Access methods that always return entries in the natural ordering
       of their data (such as btree) should set
       <TT
CLASS="STRUCTFIELD"
>amcanorder</TT
> to true.
       Currently, such access methods must use btree-compatible strategy
       numbers for their equality and ordering operators.
      </P
></LI
><LI
><P
>       Access methods that support ordering operators should set
       <TT
CLASS="STRUCTFIELD"
>amcanorderbyop</TT
> to true.
       This indicates that the index is capable of returning entries in
       an order satisfying <TT
CLASS="LITERAL"
>ORDER BY</TT
> <TT
CLASS="REPLACEABLE"
><I
>index_key</I
></TT
>
       <TT
CLASS="REPLACEABLE"
><I
>operator</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>constant</I
></TT
>.  Scan modifiers
       of that form can be passed to <CODE
CLASS="FUNCTION"
>amrescan</CODE
> as described
       previously.
      </P
></LI
></UL
><P>
  </P
><P
>   The <CODE
CLASS="FUNCTION"
>amgettuple</CODE
> function has a <TT
CLASS="LITERAL"
>direction</TT
> argument,
   which can be either <TT
CLASS="LITERAL"
>ForwardScanDirection</TT
> (the normal case)
   or  <TT
CLASS="LITERAL"
>BackwardScanDirection</TT
>.  If the first call after
   <CODE
CLASS="FUNCTION"
>amrescan</CODE
> specifies <TT
CLASS="LITERAL"
>BackwardScanDirection</TT
>, then the
   set of matching index entries is to be scanned back-to-front rather than in
   the normal front-to-back direction, so <CODE
CLASS="FUNCTION"
>amgettuple</CODE
> must return
   the last matching tuple in the index, rather than the first one as it
   normally would.  (This will only occur for access
   methods that set <TT
CLASS="STRUCTFIELD"
>amcanorder</TT
> to true.)  After the
   first call, <CODE
CLASS="FUNCTION"
>amgettuple</CODE
> must be prepared to advance the scan in
   either direction from the most recently returned entry.  (But if
   <TT
CLASS="STRUCTFIELD"
>amcanbackward</TT
> is false, all subsequent
   calls will have the same direction as the first one.)
  </P
><P
>   Access methods that support ordered scans must support <SPAN
CLASS="QUOTE"
>"marking"</SPAN
> a
   position in a scan and later returning to the marked position.  The same
   position might be restored multiple times.  However, only one position need
   be remembered per scan; a new <CODE
CLASS="FUNCTION"
>ammarkpos</CODE
> call overrides the
   previously marked position.  An access method that does not support ordered
   scans need not provide <CODE
CLASS="FUNCTION"
>ammarkpos</CODE
> and <CODE
CLASS="FUNCTION"
>amrestrpos</CODE
>
   functions in <TT
CLASS="STRUCTNAME"
>IndexAmRoutine</TT
>; set those pointers to NULL
   instead.
  </P
><P
>   Both the scan position and the mark position (if any) must be maintained
   consistently in the face of concurrent insertions or deletions in the
   index.  It is OK if a freshly-inserted entry is not returned by a scan that
   would have found the entry if it had existed when the scan started, or for
   the scan to return such an entry upon rescanning or backing
   up even though it had not been returned the first time through.  Similarly,
   a concurrent delete might or might not be reflected in the results of a scan.
   What is important is that insertions or deletions not cause the scan to
   miss or multiply return entries that were not themselves being inserted or
   deleted.
  </P
><P
>   If the index stores the original indexed data values (and not some lossy
   representation of them), it is useful to
   support <A
HREF="indexes-index-only-scans.html"
>index-only scans</A
>, in
   which the index returns the actual data not just the TID of the heap tuple.
   This will only avoid I/O if the visibility map shows that the TID is on an
   all-visible page; else the heap tuple must be visited anyway to check
   MVCC visibility.  But that is no concern of the access method's.
  </P
><P
>   Instead of using <CODE
CLASS="FUNCTION"
>amgettuple</CODE
>, an index scan can be done with
   <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
> to fetch all tuples in one call.  This can be
   noticeably more efficient than <CODE
CLASS="FUNCTION"
>amgettuple</CODE
> because it allows
   avoiding lock/unlock cycles within the access method.  In principle
   <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
> should have the same effects as repeated
   <CODE
CLASS="FUNCTION"
>amgettuple</CODE
> calls, but we impose several restrictions to
   simplify matters.  First of all, <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
> returns all
   tuples at once and marking or restoring scan positions isn't
   supported. Secondly, the tuples are returned in a bitmap which doesn't
   have any specific ordering, which is why <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
> doesn't
   take a <TT
CLASS="LITERAL"
>direction</TT
> argument.  (Ordering operators will never be
   supplied for such a scan, either.)
   Also, there is no provision for index-only scans with
   <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
>, since there is no way to return the contents of
   index tuples.
   Finally, <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
>
   does not guarantee any locking of the returned tuples, with implications
   spelled out in <A
HREF="index-locking.html"
>Section 59.4</A
>.
  </P
><P
>   Note that it is permitted for an access method to implement only
   <CODE
CLASS="FUNCTION"
>amgetbitmap</CODE
> and not <CODE
CLASS="FUNCTION"
>amgettuple</CODE
>, or vice versa,
   if its internal implementation is unsuited to one API or the other.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="index-functions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="index-locking.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Index Access Method Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="indexam.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Index Locking Considerations</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>