codekingpro/portable-devtools
114k
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>64.3. Index Scanning</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 Vsnapshot" /><link rel="prev" href="index-functions.html" title="64.2. Index Access Method Functions" /><link rel="next" href="index-locking.html" title="64.4. Index Locking Considerations" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">64.3. Index Scanning</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="index-functions.html" title="64.2. Index Access Method Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Up</a></td><th width="60%" align="center">Chapter 64. Index Access Method Interface Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="index-locking.html" title="64.4. Index Locking Considerations">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEX-SCANNING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">64.3. Index Scanning <a href="#INDEX-SCANNING" class="id_link">#</a></h2></div></div></div><p>3 In an index scan, the index access method is responsible for regurgitating4 the TIDs of all the tuples it has been told about that match the5 <em class="firstterm">scan keys</em>. The access method is <span class="emphasis"><em>not</em></span> involved in6 actually fetching those tuples from the index's parent table, nor in7 determining whether they pass the scan's visibility test or other8 conditions.9 </p><p>10 A scan key is the internal representation of a <code class="literal">WHERE</code> clause of11 the form <em class="replaceable"><code>index_key</code></em> <em class="replaceable"><code>operator</code></em>12 <em class="replaceable"><code>constant</code></em>, where the index key is one of the columns of the13 index and the operator is one of the members of the operator family14 associated with that index column. An index scan has zero or more scan15 keys, which are implicitly ANDed — the returned tuples are expected16 to satisfy all the indicated conditions.17 </p><p>18 The access method can report that the index is <em class="firstterm">lossy</em>, or19 requires rechecks, for a particular query. This implies that the index20 scan will return all the entries that pass the scan key, plus possibly21 additional entries that do not. The core system's index-scan machinery22 will then apply the index conditions again to the heap tuple to verify23 whether or not it really should be selected. If the recheck option is not24 specified, the index scan must return exactly the set of matching entries.25 </p><p>26 Note that it is entirely up to the access method to ensure that it27 correctly finds all and only the entries passing all the given scan keys.28 Also, the core system will simply hand off all the <code class="literal">WHERE</code>29 clauses that match the index keys and operator families, without any30 semantic analysis to determine whether they are redundant or31 contradictory. As an example, given32 <code class="literal">WHERE x > 4 AND x > 14</code> where <code class="literal">x</code> is a b-tree33 indexed column, it is left to the b-tree <code class="function">amrescan</code> function34 to realize that the first scan key is redundant and can be discarded.35 The extent of preprocessing needed during <code class="function">amrescan</code> will36 depend on the extent to which the index access method needs to reduce37 the scan keys to a <span class="quote">“<span class="quote">normalized</span>”</span> form.38 </p><p>39 Some access methods return index entries in a well-defined order, others40 do not. There are actually two different ways that an access method can41 support sorted output:42 43 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>44 Access methods that always return entries in the natural ordering45 of their data (such as btree) should set46 <code class="structfield">amcanorder</code> to true.47 Currently, such access methods must use btree-compatible strategy48 numbers for their equality and ordering operators.49 </p></li><li class="listitem"><p>50 Access methods that support ordering operators should set51 <code class="structfield">amcanorderbyop</code> to true.52 This indicates that the index is capable of returning entries in53 an order satisfying <code class="literal">ORDER BY</code> <em class="replaceable"><code>index_key</code></em>54 <em class="replaceable"><code>operator</code></em> <em class="replaceable"><code>constant</code></em>. Scan modifiers55 of that form can be passed to <code class="function">amrescan</code> as described56 previously.57 </p></li></ul></div><p>58 </p><p>59 The <code class="function">amgettuple</code> function has a <code class="literal">direction</code> argument,60 which can be either <code class="literal">ForwardScanDirection</code> (the normal case)61 or <code class="literal">BackwardScanDirection</code>. If the first call after62 <code class="function">amrescan</code> specifies <code class="literal">BackwardScanDirection</code>, then the63 set of matching index entries is to be scanned back-to-front rather than in64 the normal front-to-back direction, so <code class="function">amgettuple</code> must return65 the last matching tuple in the index, rather than the first one as it66 normally would. (This will only occur for access67 methods that set <code class="structfield">amcanorder</code> to true.) After the68 first call, <code class="function">amgettuple</code> must be prepared to advance the scan in69 either direction from the most recently returned entry. (But if70 <code class="structfield">amcanbackward</code> is false, all subsequent71 calls will have the same direction as the first one.)72 </p><p>73 Access methods that support ordered scans must support <span class="quote">“<span class="quote">marking</span>”</span> a74 position in a scan and later returning to the marked position. The same75 position might be restored multiple times. However, only one position need76 be remembered per scan; a new <code class="function">ammarkpos</code> call overrides the77 previously marked position. An access method that does not support ordered78 scans need not provide <code class="function">ammarkpos</code> and <code class="function">amrestrpos</code>79 functions in <code class="structname">IndexAmRoutine</code>; set those pointers to NULL80 instead.81 </p><p>82 Both the scan position and the mark position (if any) must be maintained83 consistently in the face of concurrent insertions or deletions in the84 index. It is OK if a freshly-inserted entry is not returned by a scan that85 would have found the entry if it had existed when the scan started, or for86 the scan to return such an entry upon rescanning or backing87 up even though it had not been returned the first time through. Similarly,88 a concurrent delete might or might not be reflected in the results of a scan.89 What is important is that insertions or deletions not cause the scan to90 miss or multiply return entries that were not themselves being inserted or91 deleted.92 </p><p>93 If the index stores the original indexed data values (and not some lossy94 representation of them), it is useful to95 support <a class="link" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">index-only scans</a>, in96 which the index returns the actual data not just the TID of the heap tuple.97 This will only avoid I/O if the visibility map shows that the TID is on an98 all-visible page; else the heap tuple must be visited anyway to check99 MVCC visibility. But that is no concern of the access method's.100 </p><p>101 Instead of using <code class="function">amgettuple</code>, an index scan can be done with102 <code class="function">amgetbitmap</code> to fetch all tuples in one call. This can be103 noticeably more efficient than <code class="function">amgettuple</code> because it allows104 avoiding lock/unlock cycles within the access method. In principle105 <code class="function">amgetbitmap</code> should have the same effects as repeated106 <code class="function">amgettuple</code> calls, but we impose several restrictions to107 simplify matters. First of all, <code class="function">amgetbitmap</code> returns all108 tuples at once and marking or restoring scan positions isn't109 supported. Secondly, the tuples are returned in a bitmap which doesn't110 have any specific ordering, which is why <code class="function">amgetbitmap</code> doesn't111 take a <code class="literal">direction</code> argument. (Ordering operators will never be112 supplied for such a scan, either.)113 Also, there is no provision for index-only scans with114 <code class="function">amgetbitmap</code>, since there is no way to return the contents of115 index tuples.116 Finally, <code class="function">amgetbitmap</code>117 does not guarantee any locking of the returned tuples, with implications118 spelled out in <a class="xref" href="index-locking.html" title="64.4. Index Locking Considerations">Section 64.4</a>.119 </p><p>120 Note that it is permitted for an access method to implement only121 <code class="function">amgetbitmap</code> and not <code class="function">amgettuple</code>, or vice versa,122 if its internal implementation is unsuited to one API or the other.123 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="index-functions.html" title="64.2. Index Access Method Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="index-locking.html" title="64.4. Index Locking Considerations">Next</a></td></tr><tr><td width="40%" align="left" valign="top">64.2. Index Access Method Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 64.4. Index Locking Considerations</td></tr></table></div></body></html>