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>36.16. Interfacing Extensions to Indexes</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="xoper-optimization.html" title="36.15. Operator Optimization Information" /><link rel="next" href="extend-extensions.html" title="36.17. Packaging Related Objects into an Extension" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">36.16. Interfacing Extensions to Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xoper-optimization.html" title="36.15. Operator Optimization Information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 36. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-extensions.html" title="36.17. Packaging Related Objects into an Extension">Next</a></td></tr></table><hr /></div><div class="sect1" id="XINDEX"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.16. Interfacing Extensions to Indexes <a href="#XINDEX" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS">36.16.1. Index Methods and Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-STRATEGIES">36.16.2. Index Method Strategies</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-SUPPORT">36.16.3. Index Method Support Routines</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-EXAMPLE">36.16.4. An Example</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPFAMILY">36.16.5. Operator Classes and Operator Families</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-DEPENDENCIES">36.16.6. System Dependencies on Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-ORDERING-OPS">36.16.7. Ordering Operators</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-FEATURES">36.16.8. Special Features of Operator Classes</a></span></dt></dl></div><a id="id-1.8.3.19.2" class="indexterm"></a><p>
3 The procedures described thus far let you define new types, new
4 functions, and new operators. However, we cannot yet define an
5 index on a column of a new data type. To do this, we must define an
6 <em class="firstterm">operator class</em> for the new data type. Later in this
7 section, we will illustrate this concept in an example: a new
8 operator class for the B-tree index method that stores and sorts
9 complex numbers in ascending absolute value order.
11 Operator classes can be grouped into <em class="firstterm">operator families</em>
12 to show the relationships between semantically compatible classes.
13 When only a single data type is involved, an operator class is sufficient,
14 so we'll focus on that case first and then return to operator families.
15 </p><div class="sect2" id="XINDEX-OPCLASS"><div class="titlepage"><div><div><h3 class="title">36.16.1. Index Methods and Operator Classes <a href="#XINDEX-OPCLASS" class="id_link">#</a></h3></div></div></div><p>
16 Operator classes are associated with an index access method, such
17 as <a class="link" href="btree.html" title="65.1. B-Tree Indexes">B-Tree</a>
18 or <a class="link" href="gin.html" title="65.4. GIN Indexes">GIN</a>. Custom index access method may be
19 defined with <a class="xref" href="sql-create-access-method.html" title="CREATE ACCESS METHOD"><span class="refentrytitle">CREATE ACCESS METHOD</span></a>. See
20 <a class="xref" href="indexam.html" title="Chapter 63. Index Access Method Interface Definition">Chapter 63</a> for details.
22 The routines for an index method do not directly know anything
23 about the data types that the index method will operate on.
24 Instead, an <em class="firstterm">operator
25 class</em><a id="id-1.8.3.19.5.3.2" class="indexterm"></a>
26 identifies the set of operations that the index method needs to use
27 to work with a particular data type. Operator classes are so
28 called because one thing they specify is the set of
29 <code class="literal">WHERE</code>-clause operators that can be used with an index
30 (i.e., can be converted into an index-scan qualification). An
31 operator class can also specify some <em class="firstterm">support
32 function</em> that are needed by the internal operations of the
33 index method, but do not directly correspond to any
34 <code class="literal">WHERE</code>-clause operator that can be used with the index.
36 It is possible to define multiple operator classes for the same
37 data type and index method. By doing this, multiple
38 sets of indexing semantics can be defined for a single data type.
39 For example, a B-tree index requires a sort ordering to be defined
40 for each data type it works on.
41 It might be useful for a complex-number data type
42 to have one B-tree operator class that sorts the data by complex
43 absolute value, another that sorts by real part, and so on.
44 Typically, one of the operator classes will be deemed most commonly
45 useful and will be marked as the default operator class for that
46 data type and index method.
48 The same operator class name
49 can be used for several different index methods (for example, both B-tree
50 and hash index methods have operator classes named
51 <code class="literal">int4_ops</code>), but each such class is an independent
52 entity and must be defined separately.
53 </p></div><div class="sect2" id="XINDEX-STRATEGIES"><div class="titlepage"><div><div><h3 class="title">36.16.2. Index Method Strategies <a href="#XINDEX-STRATEGIES" class="id_link">#</a></h3></div></div></div><p>
54 The operators associated with an operator class are identified by
55 <span class="quote">“<span class="quote">strategy numbers</span>”</span>, which serve to identify the semantics of
56 each operator within the context of its operator class.
57 For example, B-trees impose a strict ordering on keys, lesser to greater,
58 and so operators like <span class="quote">“<span class="quote">less than</span>”</span> and <span class="quote">“<span class="quote">greater than or equal
59 to</span>”</span> are interesting with respect to a B-tree.
61 <span class="productname">PostgreSQL</span> allows the user to define operators,
62 <span class="productname">PostgreSQL</span> cannot look at the name of an operator
63 (e.g., <code class="literal"><</code> or <code class="literal">>=</code>) and tell what kind of
64 comparison it is. Instead, the index method defines a set of
65 <span class="quote">“<span class="quote">strategies</span>”</span>, which can be thought of as generalized operators.
66 Each operator class specifies which actual operator corresponds to each
67 strategy for a particular data type and interpretation of the index
70 The B-tree index method defines five strategies, shown in <a class="xref" href="xindex.html#XINDEX-BTREE-STRAT-TABLE" title="Table 36.3. B-Tree Strategies">Table 36.3</a>.
71 </p><div class="table" id="XINDEX-BTREE-STRAT-TABLE"><p class="title"><strong>Table 36.3. B-Tree Strategies</strong></p><div class="table-contents"><table class="table" summary="B-Tree Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>less than</td><td>1</td></tr><tr><td>less than or equal</td><td>2</td></tr><tr><td>equal</td><td>3</td></tr><tr><td>greater than or equal</td><td>4</td></tr><tr><td>greater than</td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p>
72 Hash indexes support only equality comparisons, and so they use only one
73 strategy, shown in <a class="xref" href="xindex.html#XINDEX-HASH-STRAT-TABLE" title="Table 36.4. Hash Strategies">Table 36.4</a>.
74 </p><div class="table" id="XINDEX-HASH-STRAT-TABLE"><p class="title"><strong>Table 36.4. Hash Strategies</strong></p><div class="table-contents"><table class="table" summary="Hash Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>equal</td><td>1</td></tr></tbody></table></div></div><br class="table-break" /><p>
75 GiST indexes are more flexible: they do not have a fixed set of
76 strategies at all. Instead, the <span class="quote">“<span class="quote">consistency</span>”</span> support routine
77 of each particular GiST operator class interprets the strategy numbers
78 however it likes. As an example, several of the built-in GiST index
79 operator classes index two-dimensional geometric objects, providing
80 the <span class="quote">“<span class="quote">R-tree</span>”</span> strategies shown in
81 <a class="xref" href="xindex.html#XINDEX-RTREE-STRAT-TABLE" title="Table 36.5. GiST Two-Dimensional “R-tree” Strategies">Table 36.5</a>. Four of these are true
82 two-dimensional tests (overlaps, same, contains, contained by);
83 four of them consider only the X direction; and the other four
84 provide the same tests in the Y direction.
85 </p><div class="table" id="XINDEX-RTREE-STRAT-TABLE"><p class="title"><strong>Table 36.5. GiST Two-Dimensional <span class="quote">“<span class="quote">R-tree</span>”</span> Strategies</strong></p><div class="table-contents"><table class="table" summary="GiST Two-Dimensional R-tree Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>strictly left of</td><td>1</td></tr><tr><td>does not extend to right of</td><td>2</td></tr><tr><td>overlaps</td><td>3</td></tr><tr><td>does not extend to left of</td><td>4</td></tr><tr><td>strictly right of</td><td>5</td></tr><tr><td>same</td><td>6</td></tr><tr><td>contains</td><td>7</td></tr><tr><td>contained by</td><td>8</td></tr><tr><td>does not extend above</td><td>9</td></tr><tr><td>strictly below</td><td>10</td></tr><tr><td>strictly above</td><td>11</td></tr><tr><td>does not extend below</td><td>12</td></tr></tbody></table></div></div><br class="table-break" /><p>
86 SP-GiST indexes are similar to GiST indexes in flexibility: they don't have
87 a fixed set of strategies. Instead the support routines of each operator
88 class interpret the strategy numbers according to the operator class's
89 definition. As an example, the strategy numbers used by the built-in
90 operator classes for points are shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-POINT-STRAT-TABLE" title="Table 36.6. SP-GiST Point Strategies">Table 36.6</a>.
91 </p><div class="table" id="XINDEX-SPGIST-POINT-STRAT-TABLE"><p class="title"><strong>Table 36.6. SP-GiST Point Strategies</strong></p><div class="table-contents"><table class="table" summary="SP-GiST Point Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>strictly left of</td><td>1</td></tr><tr><td>strictly right of</td><td>5</td></tr><tr><td>same</td><td>6</td></tr><tr><td>contained by</td><td>8</td></tr><tr><td>strictly below</td><td>10</td></tr><tr><td>strictly above</td><td>11</td></tr></tbody></table></div></div><br class="table-break" /><p>
92 GIN indexes are similar to GiST and SP-GiST indexes, in that they don't
93 have a fixed set of strategies either. Instead the support routines of
94 each operator class interpret the strategy numbers according to the
95 operator class's definition. As an example, the strategy numbers used by
96 the built-in operator class for arrays are shown in
97 <a class="xref" href="xindex.html#XINDEX-GIN-ARRAY-STRAT-TABLE" title="Table 36.7. GIN Array Strategies">Table 36.7</a>.
98 </p><div class="table" id="XINDEX-GIN-ARRAY-STRAT-TABLE"><p class="title"><strong>Table 36.7. GIN Array Strategies</strong></p><div class="table-contents"><table class="table" summary="GIN Array Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>overlap</td><td>1</td></tr><tr><td>contains</td><td>2</td></tr><tr><td>is contained by</td><td>3</td></tr><tr><td>equal</td><td>4</td></tr></tbody></table></div></div><br class="table-break" /><p>
99 BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they
100 don't have a fixed set of strategies either. Instead the support routines
101 of each operator class interpret the strategy numbers according to the
102 operator class's definition. As an example, the strategy numbers used by
103 the built-in <code class="literal">Minmax</code> operator classes are shown in
104 <a class="xref" href="xindex.html#XINDEX-BRIN-MINMAX-STRAT-TABLE" title="Table 36.8. BRIN Minmax Strategies">Table 36.8</a>.
105 </p><div class="table" id="XINDEX-BRIN-MINMAX-STRAT-TABLE"><p class="title"><strong>Table 36.8. BRIN Minmax Strategies</strong></p><div class="table-contents"><table class="table" summary="BRIN Minmax Strategies" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operation</th><th>Strategy Number</th></tr></thead><tbody><tr><td>less than</td><td>1</td></tr><tr><td>less than or equal</td><td>2</td></tr><tr><td>equal</td><td>3</td></tr><tr><td>greater than or equal</td><td>4</td></tr><tr><td>greater than</td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p>
106 Notice that all the operators listed above return Boolean values. In
107 practice, all operators defined as index method search operators must
108 return type <code class="type">boolean</code>, since they must appear at the top
109 level of a <code class="literal">WHERE</code> clause to be used with an index.
110 (Some index access methods also support <em class="firstterm">ordering operators</em>,
111 which typically don't return Boolean values; that feature is discussed
112 in <a class="xref" href="xindex.html#XINDEX-ORDERING-OPS" title="36.16.7. Ordering Operators">Section 36.16.7</a>.)
113 </p></div><div class="sect2" id="XINDEX-SUPPORT"><div class="titlepage"><div><div><h3 class="title">36.16.3. Index Method Support Routines <a href="#XINDEX-SUPPORT" class="id_link">#</a></h3></div></div></div><p>
114 Strategies aren't usually enough information for the system to figure
115 out how to use an index. In practice, the index methods require
116 additional support routines in order to work. For example, the B-tree
117 index method must be able to compare two keys and determine whether one
118 is greater than, equal to, or less than the other. Similarly, the
119 hash index method must be able to compute hash codes for key values.
120 These operations do not correspond to operators used in qualifications in
121 SQL commands; they are administrative routines used by
122 the index methods, internally.
124 Just as with strategies, the operator class identifies which specific
125 functions should play each of these roles for a given data type and
126 semantic interpretation. The index method defines the set
127 of functions it needs, and the operator class identifies the correct
128 functions to use by assigning them to the <span class="quote">“<span class="quote">support function numbers</span>”</span>
129 specified by the index method.
131 Additionally, some opclasses allow users to specify parameters which
132 control their behavior. Each builtin index access method has an optional
133 <code class="function">options</code> support function, which defines a set of
134 opclass-specific parameters.
136 B-trees require a comparison support function,
137 and allow four additional support functions to be
138 supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-BTREE-SUPPORT-TABLE" title="Table 36.9. B-Tree Support Functions">Table 36.9</a>.
139 The requirements for these support functions are explained further in
140 <a class="xref" href="btree.html#BTREE-SUPPORT-FUNCS" title="65.1.3. B-Tree Support Functions">Section 65.1.3</a>.
141 </p><div class="table" id="XINDEX-BTREE-SUPPORT-TABLE"><p class="title"><strong>Table 36.9. B-Tree Support Functions</strong></p><div class="table-contents"><table class="table" summary="B-Tree Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Function</th><th>Support Number</th></tr></thead><tbody><tr><td>
142 Compare two keys and return an integer less than zero, zero, or
143 greater than zero, indicating whether the first key is less than,
144 equal to, or greater than the second
145 </td><td>1</td></tr><tr><td>
146 Return the addresses of C-callable sort support function(s)
148 </td><td>2</td></tr><tr><td>
149 Compare a test value to a base value plus/minus an offset, and return
150 true or false according to the comparison result (optional)
151 </td><td>3</td></tr><tr><td>
152 Determine if it is safe for indexes that use the operator
153 class to apply the btree deduplication optimization (optional)
154 </td><td>4</td></tr><tr><td>
155 Define options that are specific to this operator class
157 </td><td>5</td></tr><tr><td>
158 Return the addresses of C-callable skip support function(s)
160 </td><td>6</td></tr></tbody></table></div></div><br class="table-break" /><p>
161 Hash indexes require one support function, and allow two additional ones to
162 be supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-HASH-SUPPORT-TABLE" title="Table 36.10. Hash Support Functions">Table 36.10</a>.
163 </p><div class="table" id="XINDEX-HASH-SUPPORT-TABLE"><p class="title"><strong>Table 36.10. Hash Support Functions</strong></p><div class="table-contents"><table class="table" summary="Hash Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Function</th><th>Support Number</th></tr></thead><tbody><tr><td>Compute the 32-bit hash value for a key</td><td>1</td></tr><tr><td>
164 Compute the 64-bit hash value for a key given a 64-bit salt; if
165 the salt is 0, the low 32 bits of the result must match the value
166 that would have been computed by function 1
168 </td><td>2</td></tr><tr><td>
169 Define options that are specific to this operator class
171 </td><td>3</td></tr></tbody></table></div></div><br class="table-break" /><p>
172 GiST indexes have twelve support functions, seven of which are optional,
173 as shown in <a class="xref" href="xindex.html#XINDEX-GIST-SUPPORT-TABLE" title="Table 36.11. GiST Support Functions">Table 36.11</a>.
174 (For more information see <a class="xref" href="gist.html" title="65.2. GiST Indexes">Section 65.2</a>.)
175 </p><div class="table" id="XINDEX-GIST-SUPPORT-TABLE"><p class="title"><strong>Table 36.11. GiST Support Functions</strong></p><div class="table-contents"><table class="table" summary="GiST Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">consistent</code></td><td>determine whether key satisfies the
176 query qualifier</td><td>1</td></tr><tr><td><code class="function">union</code></td><td>compute union of a set of keys</td><td>2</td></tr><tr><td><code class="function">compress</code></td><td>compute a compressed representation of a key or value
177 to be indexed (optional)</td><td>3</td></tr><tr><td><code class="function">decompress</code></td><td>compute a decompressed representation of a
178 compressed key (optional)</td><td>4</td></tr><tr><td><code class="function">penalty</code></td><td>compute penalty for inserting new key into subtree
179 with given subtree's key</td><td>5</td></tr><tr><td><code class="function">picksplit</code></td><td>determine which entries of a page are to be moved
180 to the new page and compute the union keys for resulting pages</td><td>6</td></tr><tr><td><code class="function">same</code></td><td>compare two keys and return true if they are equal</td><td>7</td></tr><tr><td><code class="function">distance</code></td><td>determine distance from key to query value (optional)</td><td>8</td></tr><tr><td><code class="function">fetch</code></td><td>compute original representation of a compressed key for
181 index-only scans (optional)</td><td>9</td></tr><tr><td><code class="function">options</code></td><td>define options that are specific to this operator class
182 (optional)</td><td>10</td></tr><tr><td><code class="function">sortsupport</code></td><td>provide a sort comparator to be used in fast index builds
183 (optional)</td><td>11</td></tr><tr><td><code class="function">translate_cmptype</code></td><td>translate compare types to strategy numbers
184 used by the operator class (optional)</td><td>12</td></tr></tbody></table></div></div><br class="table-break" /><p>
185 SP-GiST indexes have six support functions, one of which is optional, as
186 shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-SUPPORT-TABLE" title="Table 36.12. SP-GiST Support Functions">Table 36.12</a>.
187 (For more information see <a class="xref" href="spgist.html" title="65.3. SP-GiST Indexes">Section 65.3</a>.)
188 </p><div class="table" id="XINDEX-SPGIST-SUPPORT-TABLE"><p class="title"><strong>Table 36.12. SP-GiST Support Functions</strong></p><div class="table-contents"><table class="table" summary="SP-GiST Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">config</code></td><td>provide basic information about the operator class</td><td>1</td></tr><tr><td><code class="function">choose</code></td><td>determine how to insert a new value into an inner tuple</td><td>2</td></tr><tr><td><code class="function">picksplit</code></td><td>determine how to partition a set of values</td><td>3</td></tr><tr><td><code class="function">inner_consistent</code></td><td>determine which sub-partitions need to be searched for a
189 query</td><td>4</td></tr><tr><td><code class="function">leaf_consistent</code></td><td>determine whether key satisfies the
190 query qualifier</td><td>5</td></tr><tr><td><code class="function">options</code></td><td>define options that are specific to this operator class
191 (optional)</td><td>6</td></tr></tbody></table></div></div><br class="table-break" /><p>
192 GIN indexes have seven support functions, four of which are optional,
193 as shown in <a class="xref" href="xindex.html#XINDEX-GIN-SUPPORT-TABLE" title="Table 36.13. GIN Support Functions">Table 36.13</a>.
194 (For more information see <a class="xref" href="gin.html" title="65.4. GIN Indexes">Section 65.4</a>.)
195 </p><div class="table" id="XINDEX-GIN-SUPPORT-TABLE"><p class="title"><strong>Table 36.13. GIN Support Functions</strong></p><div class="table-contents"><table class="table" summary="GIN Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">compare</code></td><td>
196 compare two keys and return an integer less than zero, zero,
197 or greater than zero, indicating whether the first key is less than,
198 equal to, or greater than the second
199 </td><td>1</td></tr><tr><td><code class="function">extractValue</code></td><td>extract keys from a value to be indexed</td><td>2</td></tr><tr><td><code class="function">extractQuery</code></td><td>extract keys from a query condition</td><td>3</td></tr><tr><td><code class="function">consistent</code></td><td>
200 determine whether value matches query condition (Boolean variant)
201 (optional if support function 6 is present)
202 </td><td>4</td></tr><tr><td><code class="function">comparePartial</code></td><td>
203 compare partial key from
204 query and key from index, and return an integer less than zero, zero,
205 or greater than zero, indicating whether GIN should ignore this index
206 entry, treat the entry as a match, or stop the index scan (optional)
207 </td><td>5</td></tr><tr><td><code class="function">triConsistent</code></td><td>
208 determine whether value matches query condition (ternary variant)
209 (optional if support function 4 is present)
210 </td><td>6</td></tr><tr><td><code class="function">options</code></td><td>
211 define options that are specific to this operator class
213 </td><td>7</td></tr></tbody></table></div></div><br class="table-break" /><p>
214 BRIN indexes have five basic support functions, one of which is optional,
215 as shown in <a class="xref" href="xindex.html#XINDEX-BRIN-SUPPORT-TABLE" title="Table 36.14. BRIN Support Functions">Table 36.14</a>. Some versions of
216 the basic functions require additional support functions to be provided.
217 (For more information see <a class="xref" href="brin.html#BRIN-EXTENSIBILITY" title="65.5.3. Extensibility">Section 65.5.3</a>.)
218 </p><div class="table" id="XINDEX-BRIN-SUPPORT-TABLE"><p class="title"><strong>Table 36.14. BRIN Support Functions</strong></p><div class="table-contents"><table class="table" summary="BRIN Support Functions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Support Number</th></tr></thead><tbody><tr><td><code class="function">opcInfo</code></td><td>
219 return internal information describing the indexed columns'
221 </td><td>1</td></tr><tr><td><code class="function">add_value</code></td><td>add a new value to an existing summary index tuple</td><td>2</td></tr><tr><td><code class="function">consistent</code></td><td>determine whether value matches query condition</td><td>3</td></tr><tr><td><code class="function">union</code></td><td>
222 compute union of two summary tuples
223 </td><td>4</td></tr><tr><td><code class="function">options</code></td><td>
224 define options that are specific to this operator class
226 </td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p>
227 Unlike search operators, support functions return whichever data
228 type the particular index method expects; for example in the case
229 of the comparison function for B-trees, a signed integer. The number
230 and types of the arguments to each support function are likewise
231 dependent on the index method. For B-tree and hash the comparison and
232 hashing support functions take the same input data types as do the
233 operators included in the operator class, but this is not the case for
234 most GiST, SP-GiST, GIN, and BRIN support functions.
235 </p></div><div class="sect2" id="XINDEX-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">36.16.4. An Example <a href="#XINDEX-EXAMPLE" class="id_link">#</a></h3></div></div></div><p>
236 Now that we have seen the ideas, here is the promised example of
237 creating a new operator class.
238 (You can find a working copy of this example in
239 <code class="filename">src/tutorial/complex.c</code> and
240 <code class="filename">src/tutorial/complex.sql</code> in the source
242 The operator class encapsulates
243 operators that sort complex numbers in absolute value order, so we
244 choose the name <code class="literal">complex_abs_ops</code>. First, we need
245 a set of operators. The procedure for defining operators was
246 discussed in <a class="xref" href="xoper.html" title="36.14. User-Defined Operators">Section 36.14</a>. For an operator class on
247 B-trees, the operators we require are:
249 </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem">absolute-value less-than (strategy 1)</li><li class="listitem">absolute-value less-than-or-equal (strategy 2)</li><li class="listitem">absolute-value equal (strategy 3)</li><li class="listitem">absolute-value greater-than-or-equal (strategy 4)</li><li class="listitem">absolute-value greater-than (strategy 5)</li></ul></div><p>
251 The least error-prone way to define a related set of comparison operators
252 is to write the B-tree comparison support function first, and then write the
253 other functions as one-line wrappers around the support function. This
254 reduces the odds of getting inconsistent results for corner cases.
255 Following this approach, we first write:
257 </p><pre class="programlisting">
258 #define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y)
261 complex_abs_cmp_internal(Complex *a, Complex *b)
263 double amag = Mag(a),
275 Now the less-than function looks like:
277 </p><pre class="programlisting">
278 PG_FUNCTION_INFO_V1(complex_abs_lt);
281 complex_abs_lt(PG_FUNCTION_ARGS)
283 Complex *a = (Complex *) PG_GETARG_POINTER(0);
284 Complex *b = (Complex *) PG_GETARG_POINTER(1);
286 PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
291 The other four functions differ only in how they compare the internal
292 function's result to zero.
294 Next we declare the functions and the operators based on the functions
297 </p><pre class="programlisting">
298 CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
299 AS '<em class="replaceable"><code>filename</code></em>', 'complex_abs_lt'
300 LANGUAGE C IMMUTABLE STRICT;
302 CREATE OPERATOR < (
303 leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
304 commutator = > , negator = >= ,
305 restrict = scalarltsel, join = scalarltjoinsel
308 It is important to specify the correct commutator and negator operators,
309 as well as suitable restriction and join selectivity
310 functions, otherwise the optimizer will be unable to make effective
313 Other things worth noting are happening here:
315 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
316 There can only be one operator named, say, <code class="literal">=</code>
317 and taking type <code class="type">complex</code> for both operands. In this
318 case we don't have any other operator <code class="literal">=</code> for
319 <code class="type">complex</code>, but if we were building a practical data
320 type we'd probably want <code class="literal">=</code> to be the ordinary
321 equality operation for complex numbers (and not the equality of
322 the absolute values). In that case, we'd need to use some other
323 operator name for <code class="function">complex_abs_eq</code>.
324 </p></li><li class="listitem"><p>
325 Although <span class="productname">PostgreSQL</span> can cope with
326 functions having the same SQL name as long as they have different
327 argument data types, C can only cope with one global function
328 having a given name. So we shouldn't name the C function
329 something simple like <code class="filename">abs_eq</code>. Usually it's
330 a good practice to include the data type name in the C function
331 name, so as not to conflict with functions for other data types.
332 </p></li><li class="listitem"><p>
333 We could have made the SQL name
334 of the function <code class="filename">abs_eq</code>, relying on
335 <span class="productname">PostgreSQL</span> to distinguish it by
336 argument data types from any other SQL function of the same name.
337 To keep the example simple, we make the function have the same
338 names at the C level and SQL level.
339 </p></li></ul></div><p>
341 The next step is the registration of the support routine required
342 by B-trees. The example C code that implements this is in the same
343 file that contains the operator functions. This is how we declare
346 </p><pre class="programlisting">
347 CREATE FUNCTION complex_abs_cmp(complex, complex)
349 AS '<em class="replaceable"><code>filename</code></em>'
350 LANGUAGE C IMMUTABLE STRICT;
353 Now that we have the required operators and support routine,
354 we can finally create the operator class:
356 </p><pre class="programlisting">
357 CREATE OPERATOR CLASS complex_abs_ops
358 DEFAULT FOR TYPE complex USING btree AS
364 FUNCTION 1 complex_abs_cmp(complex, complex);
368 And we're done! It should now be possible to create
369 and use B-tree indexes on <code class="type">complex</code> columns.
371 We could have written the operator entries more verbosely, as in:
372 </p><pre class="programlisting">
373 OPERATOR 1 < (complex, complex) ,
375 but there is no need to do so when the operators take the same data type
376 we are defining the operator class for.
378 The above example assumes that you want to make this new operator class the
379 default B-tree operator class for the <code class="type">complex</code> data type.
380 If you don't, just leave out the word <code class="literal">DEFAULT</code>.
381 </p></div><div class="sect2" id="XINDEX-OPFAMILY"><div class="titlepage"><div><div><h3 class="title">36.16.5. Operator Classes and Operator Families <a href="#XINDEX-OPFAMILY" class="id_link">#</a></h3></div></div></div><p>
382 So far we have implicitly assumed that an operator class deals with
383 only one data type. While there certainly can be only one data type in
384 a particular index column, it is often useful to index operations that
385 compare an indexed column to a value of a different data type. Also,
386 if there is use for a cross-data-type operator in connection with an
387 operator class, it is often the case that the other data type has a
388 related operator class of its own. It is helpful to make the connections
389 between related classes explicit, because this can aid the planner in
390 optimizing SQL queries (particularly for B-tree operator classes, since
391 the planner contains a great deal of knowledge about how to work with them).
393 To handle these needs, <span class="productname">PostgreSQL</span>
394 uses the concept of an <em class="firstterm">operator
395 family</em><a id="id-1.8.3.19.9.3.3" class="indexterm"></a>.
396 An operator family contains one or more operator classes, and can also
397 contain indexable operators and corresponding support functions that
398 belong to the family as a whole but not to any single class within the
399 family. We say that such operators and functions are <span class="quote">“<span class="quote">loose</span>”</span>
400 within the family, as opposed to being bound into a specific class.
401 Typically each operator class contains single-data-type operators
402 while cross-data-type operators are loose in the family.
404 All the operators and functions in an operator family must have compatible
405 semantics, where the compatibility requirements are set by the index
406 method. You might therefore wonder why bother to single out particular
407 subsets of the family as operator classes; and indeed for many purposes
408 the class divisions are irrelevant and the family is the only interesting
409 grouping. The reason for defining operator classes is that they specify
410 how much of the family is needed to support any particular index.
411 If there is an index using an operator class, then that operator class
412 cannot be dropped without dropping the index — but other parts of
413 the operator family, namely other operator classes and loose operators,
414 could be dropped. Thus, an operator class should be specified to contain
415 the minimum set of operators and functions that are reasonably needed
416 to work with an index on a specific data type, and then related but
417 non-essential operators can be added as loose members of the operator
420 As an example, <span class="productname">PostgreSQL</span> has a built-in
421 B-tree operator family <code class="literal">integer_ops</code>, which includes operator
422 classes <code class="literal">int8_ops</code>, <code class="literal">int4_ops</code>, and
423 <code class="literal">int2_ops</code> for indexes on <code class="type">bigint</code> (<code class="type">int8</code>),
424 <code class="type">integer</code> (<code class="type">int4</code>), and <code class="type">smallint</code> (<code class="type">int2</code>)
425 columns respectively. The family also contains cross-data-type comparison
426 operators allowing any two of these types to be compared, so that an index
427 on one of these types can be searched using a comparison value of another
428 type. The family could be duplicated by these definitions:
430 </p><pre class="programlisting">
431 CREATE OPERATOR FAMILY integer_ops USING btree;
433 CREATE OPERATOR CLASS int8_ops
434 DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS
435 -- standard int8 comparisons
441 FUNCTION 1 btint8cmp(int8, int8) ,
442 FUNCTION 2 btint8sortsupport(internal) ,
443 FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ,
444 FUNCTION 4 btequalimage(oid) ,
445 FUNCTION 6 btint8skipsupport(internal) ;
447 CREATE OPERATOR CLASS int4_ops
448 DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS
449 -- standard int4 comparisons
455 FUNCTION 1 btint4cmp(int4, int4) ,
456 FUNCTION 2 btint4sortsupport(internal) ,
457 FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ,
458 FUNCTION 4 btequalimage(oid) ,
459 FUNCTION 6 btint4skipsupport(internal) ;
461 CREATE OPERATOR CLASS int2_ops
462 DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS
463 -- standard int2 comparisons
469 FUNCTION 1 btint2cmp(int2, int2) ,
470 FUNCTION 2 btint2sortsupport(internal) ,
471 FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ,
472 FUNCTION 4 btequalimage(oid) ,
473 FUNCTION 6 btint2skipsupport(internal) ;
475 ALTER OPERATOR FAMILY integer_ops USING btree ADD
476 -- cross-type comparisons int8 vs int2
477 OPERATOR 1 < (int8, int2) ,
478 OPERATOR 2 <= (int8, int2) ,
479 OPERATOR 3 = (int8, int2) ,
480 OPERATOR 4 >= (int8, int2) ,
481 OPERATOR 5 > (int8, int2) ,
482 FUNCTION 1 btint82cmp(int8, int2) ,
484 -- cross-type comparisons int8 vs int4
485 OPERATOR 1 < (int8, int4) ,
486 OPERATOR 2 <= (int8, int4) ,
487 OPERATOR 3 = (int8, int4) ,
488 OPERATOR 4 >= (int8, int4) ,
489 OPERATOR 5 > (int8, int4) ,
490 FUNCTION 1 btint84cmp(int8, int4) ,
492 -- cross-type comparisons int4 vs int2
493 OPERATOR 1 < (int4, int2) ,
494 OPERATOR 2 <= (int4, int2) ,
495 OPERATOR 3 = (int4, int2) ,
496 OPERATOR 4 >= (int4, int2) ,
497 OPERATOR 5 > (int4, int2) ,
498 FUNCTION 1 btint42cmp(int4, int2) ,
500 -- cross-type comparisons int4 vs int8
501 OPERATOR 1 < (int4, int8) ,
502 OPERATOR 2 <= (int4, int8) ,
503 OPERATOR 3 = (int4, int8) ,
504 OPERATOR 4 >= (int4, int8) ,
505 OPERATOR 5 > (int4, int8) ,
506 FUNCTION 1 btint48cmp(int4, int8) ,
508 -- cross-type comparisons int2 vs int8
509 OPERATOR 1 < (int2, int8) ,
510 OPERATOR 2 <= (int2, int8) ,
511 OPERATOR 3 = (int2, int8) ,
512 OPERATOR 4 >= (int2, int8) ,
513 OPERATOR 5 > (int2, int8) ,
514 FUNCTION 1 btint28cmp(int2, int8) ,
516 -- cross-type comparisons int2 vs int4
517 OPERATOR 1 < (int2, int4) ,
518 OPERATOR 2 <= (int2, int4) ,
519 OPERATOR 3 = (int2, int4) ,
520 OPERATOR 4 >= (int2, int4) ,
521 OPERATOR 5 > (int2, int4) ,
522 FUNCTION 1 btint24cmp(int2, int4) ,
524 -- cross-type in_range functions
525 FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) ,
526 FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) ,
527 FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) ,
528 FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;
532 Notice that this definition <span class="quote">“<span class="quote">overloads</span>”</span> the operator strategy and
533 support function numbers: each number occurs multiple times within the
534 family. This is allowed so long as each instance of a
535 particular number has distinct input data types. The instances that have
536 both input types equal to an operator class's input type are the
537 primary operators and support functions for that operator class,
538 and in most cases should be declared as part of the operator class rather
539 than as loose members of the family.
541 In a B-tree operator family, all the operators in the family must sort
542 compatibly, as is specified in detail in <a class="xref" href="btree.html#BTREE-BEHAVIOR" title="65.1.2. Behavior of B-Tree Operator Classes">Section 65.1.2</a>.
544 operator in the family there must be a support function having the same
545 two input data types as the operator. It is recommended that a family be
546 complete, i.e., for each combination of data types, all operators are
547 included. Each operator class should include just the non-cross-type
548 operators and support function for its data type.
550 To build a multiple-data-type hash operator family, compatible hash
551 support functions must be created for each data type supported by the
552 family. Here compatibility means that the functions are guaranteed to
553 return the same hash code for any two values that are considered equal
554 by the family's equality operators, even when the values are of different
555 types. This is usually difficult to accomplish when the types have
556 different physical representations, but it can be done in some cases.
557 Furthermore, casting a value from one data type represented in the operator
558 family to another data type also represented in the operator family via
559 an implicit or binary coercion cast must not change the computed hash value.
560 Notice that there is only one support function per data type, not one
561 per equality operator. It is recommended that a family be complete, i.e.,
562 provide an equality operator for each combination of data types.
563 Each operator class should include just the non-cross-type equality
564 operator and the support function for its data type.
566 GiST, SP-GiST, and GIN indexes do not have any explicit notion of
567 cross-data-type operations. The set of operators supported is just
568 whatever the primary support functions for a given operator class can
571 In BRIN, the requirements depends on the framework that provides the
572 operator classes. For operator classes based on <code class="literal">minmax</code>,
573 the behavior required is the same as for B-tree operator families:
574 all the operators in the family must sort compatibly, and casts must
575 not change the associated sort ordering.
576 </p><div class="note"><h3 class="title">Note</h3><p>
577 Prior to <span class="productname">PostgreSQL</span> 8.3, there was no concept
578 of operator families, and so any cross-data-type operators intended to be
579 used with an index had to be bound directly into the index's operator
580 class. While this approach still works, it is deprecated because it
581 makes an index's dependencies too broad, and because the planner can
582 handle cross-data-type comparisons more effectively when both data types
583 have operators in the same operator family.
584 </p></div></div><div class="sect2" id="XINDEX-OPCLASS-DEPENDENCIES"><div class="titlepage"><div><div><h3 class="title">36.16.6. System Dependencies on Operator Classes <a href="#XINDEX-OPCLASS-DEPENDENCIES" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.19.10.2" class="indexterm"></a><p>
585 <span class="productname">PostgreSQL</span> uses operator classes to infer the
586 properties of operators in more ways than just whether they can be used
587 with indexes. Therefore, you might want to create operator classes
588 even if you have no intention of indexing any columns of your data type.
590 In particular, there are SQL features such as <code class="literal">ORDER BY</code> and
591 <code class="literal">DISTINCT</code> that require comparison and sorting of values.
592 To implement these features on a user-defined data type,
593 <span class="productname">PostgreSQL</span> looks for the default B-tree operator
594 class for the data type. The <span class="quote">“<span class="quote">equals</span>”</span> member of this operator
595 class defines the system's notion of equality of values for
596 <code class="literal">GROUP BY</code> and <code class="literal">DISTINCT</code>, and the sort ordering
597 imposed by the operator class defines the default <code class="literal">ORDER BY</code>
600 If there is no default B-tree operator class for a data type, the system
601 will look for a default hash operator class. But since that kind of
602 operator class only provides equality, it is only able to support grouping
605 When there is no default operator class for a data type, you will get
606 errors like <span class="quote">“<span class="quote">could not identify an ordering operator</span>”</span> if you
607 try to use these SQL features with the data type.
608 </p><div class="note"><h3 class="title">Note</h3><p>
609 In <span class="productname">PostgreSQL</span> versions before 7.4,
610 sorting and grouping operations would implicitly use operators named
611 <code class="literal">=</code>, <code class="literal"><</code>, and <code class="literal">></code>. The new
612 behavior of relying on default operator classes avoids having to make
613 any assumption about the behavior of operators with particular names.
615 Sorting by a non-default B-tree operator class is possible by specifying
616 the class's less-than operator in a <code class="literal">USING</code> option,
618 </p><pre class="programlisting">
619 SELECT * FROM mytable ORDER BY somecol USING ~<~;
621 Alternatively, specifying the class's greater-than operator
622 in <code class="literal">USING</code> selects a descending-order sort.
624 Comparison of arrays of a user-defined type also relies on the semantics
625 defined by the type's default B-tree operator class. If there is no
626 default B-tree operator class, but there is a default hash operator class,
627 then array equality is supported, but not ordering comparisons.
629 Another SQL feature that requires even more data-type-specific knowledge
630 is the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em>
631 <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code> framing option
632 for window functions (see <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>).
634 </p><pre class="programlisting">
635 SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)
638 it is not sufficient to know how to order by <code class="literal">x</code>;
639 the database must also understand how to <span class="quote">“<span class="quote">subtract 5</span>”</span> or
640 <span class="quote">“<span class="quote">add 10</span>”</span> to the current row's value of <code class="literal">x</code>
641 to identify the bounds of the current window frame. Comparing the
642 resulting bounds to other rows' values of <code class="literal">x</code> is
643 possible using the comparison operators provided by the B-tree operator
644 class that defines the <code class="literal">ORDER BY</code> ordering — but
645 addition and subtraction operators are not part of the operator class, so
646 which ones should be used? Hard-wiring that choice would be undesirable,
647 because different sort orders (different B-tree operator classes) might
648 need different behavior. Therefore, a B-tree operator class can specify
649 an <em class="firstterm">in_range</em> support function that encapsulates the
650 addition and subtraction behaviors that make sense for its sort order.
651 It can even provide more than one in_range support function, in case
652 there is more than one data type that makes sense to use as the offset
653 in <code class="literal">RANGE</code> clauses.
654 If the B-tree operator class associated with the window's <code class="literal">ORDER
655 BY</code> clause does not have a matching in_range support function,
656 the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em>
657 <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code>
658 option is not supported.
660 Another important point is that an equality operator that
661 appears in a hash operator family is a candidate for hash joins,
662 hash aggregation, and related optimizations. The hash operator family
663 is essential here since it identifies the hash function(s) to use.
664 </p></div><div class="sect2" id="XINDEX-ORDERING-OPS"><div class="titlepage"><div><div><h3 class="title">36.16.7. Ordering Operators <a href="#XINDEX-ORDERING-OPS" class="id_link">#</a></h3></div></div></div><p>
665 Some index access methods (currently, only GiST and SP-GiST) support the concept of
666 <em class="firstterm">ordering operators</em>. What we have been discussing so far
667 are <em class="firstterm">search operators</em>. A search operator is one for which
668 the index can be searched to find all rows satisfying
669 <code class="literal">WHERE</code>
670 <em class="replaceable"><code>indexed_column</code></em>
671 <em class="replaceable"><code>operator</code></em>
672 <em class="replaceable"><code>constant</code></em>.
673 Note that nothing is promised about the order in which the matching rows
674 will be returned. In contrast, an ordering operator does not restrict the
675 set of rows that can be returned, but instead determines their order.
676 An ordering operator is one for which the index can be scanned to return
677 rows in the order represented by
678 <code class="literal">ORDER BY</code>
679 <em class="replaceable"><code>indexed_column</code></em>
680 <em class="replaceable"><code>operator</code></em>
681 <em class="replaceable"><code>constant</code></em>.
682 The reason for defining ordering operators that way is that it supports
683 nearest-neighbor searches, if the operator is one that measures distance.
684 For example, a query like
685 </p><pre class="programlisting">
686 SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
689 finds the ten places closest to a given target point. A GiST index
690 on the location column can do this efficiently because
691 <code class="literal"><-></code> is an ordering operator.
693 While search operators have to return Boolean results, ordering operators
694 usually return some other type, such as float or numeric for distances.
695 This type is normally not the same as the data type being indexed.
696 To avoid hard-wiring assumptions about the behavior of different data
697 types, the definition of an ordering operator is required to name
698 a B-tree operator family that specifies the sort ordering of the result
699 data type. As was stated in the previous section, B-tree operator families
700 define <span class="productname">PostgreSQL</span>'s notion of ordering, so
701 this is a natural representation. Since the point <code class="literal"><-></code>
702 operator returns <code class="type">float8</code>, it could be specified in an operator
703 class creation command like this:
704 </p><pre class="programlisting">
705 OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops
708 where <code class="literal">float_ops</code> is the built-in operator family that includes
709 operations on <code class="type">float8</code>. This declaration states that the index
710 is able to return rows in order of increasing values of the
711 <code class="literal"><-></code> operator.
712 </p></div><div class="sect2" id="XINDEX-OPCLASS-FEATURES"><div class="titlepage"><div><div><h3 class="title">36.16.8. Special Features of Operator Classes <a href="#XINDEX-OPCLASS-FEATURES" class="id_link">#</a></h3></div></div></div><p>
713 There are two special features of operator classes that we have
714 not discussed yet, mainly because they are not useful
715 with the most commonly used index methods.
717 Normally, declaring an operator as a member of an operator class
718 (or family) means that the index method can retrieve exactly the set of rows
719 that satisfy a <code class="literal">WHERE</code> condition using the operator. For example:
720 </p><pre class="programlisting">
721 SELECT * FROM table WHERE integer_column < 4;
723 can be satisfied exactly by a B-tree index on the integer column.
724 But there are cases where an index is useful as an inexact guide to
725 the matching rows. For example, if a GiST index stores only bounding boxes
726 for geometric objects, then it cannot exactly satisfy a <code class="literal">WHERE</code>
727 condition that tests overlap between nonrectangular objects such as
728 polygons. Yet we could use the index to find objects whose bounding
729 box overlaps the bounding box of the target object, and then do the
730 exact overlap test only on the objects found by the index. If this
731 scenario applies, the index is said to be <span class="quote">“<span class="quote">lossy</span>”</span> for the
732 operator. Lossy index searches are implemented by having the index
733 method return a <em class="firstterm">recheck</em> flag when a row might or might
734 not really satisfy the query condition. The core system will then
735 test the original query condition on the retrieved row to see whether
736 it should be returned as a valid match. This approach works if
737 the index is guaranteed to return all the required rows, plus perhaps
738 some additional rows, which can be eliminated by performing the original
739 operator invocation. The index methods that support lossy searches
740 (currently, GiST, SP-GiST and GIN) allow the support functions of individual
741 operator classes to set the recheck flag, and so this is essentially an
742 operator-class feature.
744 Consider again the situation where we are storing in the index only
745 the bounding box of a complex object such as a polygon. In this
746 case there's not much value in storing the whole polygon in the index
747 entry — we might as well store just a simpler object of type
748 <code class="type">box</code>. This situation is expressed by the <code class="literal">STORAGE</code>
749 option in <code class="command">CREATE OPERATOR CLASS</code>: we'd write something like:
751 </p><pre class="programlisting">
752 CREATE OPERATOR CLASS polygon_ops
753 DEFAULT FOR TYPE polygon USING gist AS
758 At present, only the GiST, SP-GiST, GIN and BRIN index methods support a
759 <code class="literal">STORAGE</code> type that's different from the column data type.
760 The GiST <code class="function">compress</code> and <code class="function">decompress</code> support
761 routines must deal with data-type conversion when <code class="literal">STORAGE</code>
762 is used. SP-GiST likewise requires a <code class="function">compress</code>
763 support function to convert to the storage type, when that is different;
764 if an SP-GiST opclass also supports retrieving data, the reverse
765 conversion must be handled by the <code class="function">consistent</code> function.
766 In GIN, the <code class="literal">STORAGE</code> type identifies the type of
767 the <span class="quote">“<span class="quote">key</span>”</span> values, which normally is different from the type
768 of the indexed column — for example, an operator class for
769 integer-array columns might have keys that are just integers. The
770 GIN <code class="function">extractValue</code> and <code class="function">extractQuery</code> support
771 routines are responsible for extracting keys from indexed values.
772 BRIN is similar to GIN: the <code class="literal">STORAGE</code> type identifies the
773 type of the stored summary values, and operator classes' support
774 procedures are responsible for interpreting the summary values
776 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xoper-optimization.html" title="36.15. Operator Optimization Information">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-extensions.html" title="36.17. Packaging Related Objects into an Extension">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.15. Operator Optimization Information </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.17. Packaging Related Objects into an Extension</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob