diff options
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/html/xindex.html | 772 |
1 files changed, 772 insertions, 0 deletions
diff --git a/doc/src/sgml/html/xindex.html b/doc/src/sgml/html/xindex.html new file mode 100644 index 0000000..9a5ccec --- /dev/null +++ b/doc/src/sgml/html/xindex.html @@ -0,0 +1,772 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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>38.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="38.15. Operator Optimization Information" /><link rel="next" href="extend-extensions.html" title="38.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">38.16. Interfacing Extensions to Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xoper-optimization.html" title="38.15. Operator Optimization Information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-extensions.html" title="38.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">38.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">38.16.1. Index Methods and Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-STRATEGIES">38.16.2. Index Method Strategies</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-SUPPORT">38.16.3. Index Method Support Routines</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-EXAMPLE">38.16.4. An Example</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPFAMILY">38.16.5. Operator Classes and Operator Families</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-DEPENDENCIES">38.16.6. System Dependencies on Operator Classes</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-ORDERING-OPS">38.16.7. Ordering Operators</a></span></dt><dt><span class="sect2"><a href="xindex.html#XINDEX-OPCLASS-FEATURES">38.16.8. Special Features of Operator Classes</a></span></dt></dl></div><a id="id-1.8.3.19.2" class="indexterm"></a><p> + The procedures described thus far let you define new types, new + functions, and new operators. However, we cannot yet define an + index on a column of a new data type. To do this, we must define an + <em class="firstterm">operator class</em> for the new data type. Later in this + section, we will illustrate this concept in an example: a new + operator class for the B-tree index method that stores and sorts + complex numbers in ascending absolute value order. + </p><p> + Operator classes can be grouped into <em class="firstterm">operator families</em> + to show the relationships between semantically compatible classes. + When only a single data type is involved, an operator class is sufficient, + so we'll focus on that case first and then return to operator families. + </p><div class="sect2" id="XINDEX-OPCLASS"><div class="titlepage"><div><div><h3 class="title">38.16.1. Index Methods and Operator Classes <a href="#XINDEX-OPCLASS" class="id_link">#</a></h3></div></div></div><p> + The <code class="classname">pg_am</code> table contains one row for every + index method (internally known as access method). Support for + regular access to tables is built into + <span class="productname">PostgreSQL</span>, but all index methods are + described in <code class="classname">pg_am</code>. It is possible to add a + new index access method by writing the necessary code and + then creating an entry in <code class="classname">pg_am</code> — but that is + beyond the scope of this chapter (see <a class="xref" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Chapter 64</a>). + </p><p> + The routines for an index method do not directly know anything + about the data types that the index method will operate on. + Instead, an <em class="firstterm">operator + class</em><a id="id-1.8.3.19.5.3.2" class="indexterm"></a> + identifies the set of operations that the index method needs to use + to work with a particular data type. Operator classes are so + called because one thing they specify is the set of + <code class="literal">WHERE</code>-clause operators that can be used with an index + (i.e., can be converted into an index-scan qualification). An + operator class can also specify some <em class="firstterm">support + function</em> that are needed by the internal operations of the + index method, but do not directly correspond to any + <code class="literal">WHERE</code>-clause operator that can be used with the index. + </p><p> + It is possible to define multiple operator classes for the same + data type and index method. By doing this, multiple + sets of indexing semantics can be defined for a single data type. + For example, a B-tree index requires a sort ordering to be defined + for each data type it works on. + It might be useful for a complex-number data type + to have one B-tree operator class that sorts the data by complex + absolute value, another that sorts by real part, and so on. + Typically, one of the operator classes will be deemed most commonly + useful and will be marked as the default operator class for that + data type and index method. + </p><p> + The same operator class name + can be used for several different index methods (for example, both B-tree + and hash index methods have operator classes named + <code class="literal">int4_ops</code>), but each such class is an independent + entity and must be defined separately. + </p></div><div class="sect2" id="XINDEX-STRATEGIES"><div class="titlepage"><div><div><h3 class="title">38.16.2. Index Method Strategies <a href="#XINDEX-STRATEGIES" class="id_link">#</a></h3></div></div></div><p> + The operators associated with an operator class are identified by + <span class="quote">“<span class="quote">strategy numbers</span>”</span>, which serve to identify the semantics of + each operator within the context of its operator class. + For example, B-trees impose a strict ordering on keys, lesser to greater, + 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 + to</span>”</span> are interesting with respect to a B-tree. + Because + <span class="productname">PostgreSQL</span> allows the user to define operators, + <span class="productname">PostgreSQL</span> cannot look at the name of an operator + (e.g., <code class="literal"><</code> or <code class="literal">>=</code>) and tell what kind of + comparison it is. Instead, the index method defines a set of + <span class="quote">“<span class="quote">strategies</span>”</span>, which can be thought of as generalized operators. + Each operator class specifies which actual operator corresponds to each + strategy for a particular data type and interpretation of the index + semantics. + </p><p> + The B-tree index method defines five strategies, shown in <a class="xref" href="xindex.html#XINDEX-BTREE-STRAT-TABLE" title="Table 38.3. B-Tree Strategies">Table 38.3</a>. + </p><div class="table" id="XINDEX-BTREE-STRAT-TABLE"><p class="title"><strong>Table 38.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> + Hash indexes support only equality comparisons, and so they use only one + strategy, shown in <a class="xref" href="xindex.html#XINDEX-HASH-STRAT-TABLE" title="Table 38.4. Hash Strategies">Table 38.4</a>. + </p><div class="table" id="XINDEX-HASH-STRAT-TABLE"><p class="title"><strong>Table 38.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> + GiST indexes are more flexible: they do not have a fixed set of + strategies at all. Instead, the <span class="quote">“<span class="quote">consistency</span>”</span> support routine + of each particular GiST operator class interprets the strategy numbers + however it likes. As an example, several of the built-in GiST index + operator classes index two-dimensional geometric objects, providing + the <span class="quote">“<span class="quote">R-tree</span>”</span> strategies shown in + <a class="xref" href="xindex.html#XINDEX-RTREE-STRAT-TABLE" title="Table 38.5. GiST Two-Dimensional “R-tree” Strategies">Table 38.5</a>. Four of these are true + two-dimensional tests (overlaps, same, contains, contained by); + four of them consider only the X direction; and the other four + provide the same tests in the Y direction. + </p><div class="table" id="XINDEX-RTREE-STRAT-TABLE"><p class="title"><strong>Table 38.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> + SP-GiST indexes are similar to GiST indexes in flexibility: they don't have + a fixed set of strategies. Instead the support routines of each operator + class interpret the strategy numbers according to the operator class's + definition. As an example, the strategy numbers used by the built-in + operator classes for points are shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-POINT-STRAT-TABLE" title="Table 38.6. SP-GiST Point Strategies">Table 38.6</a>. + </p><div class="table" id="XINDEX-SPGIST-POINT-STRAT-TABLE"><p class="title"><strong>Table 38.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> + GIN indexes are similar to GiST and SP-GiST indexes, in that they don't + have a fixed set of strategies either. Instead the support routines of + each operator class interpret the strategy numbers according to the + operator class's definition. As an example, the strategy numbers used by + the built-in operator class for arrays are shown in + <a class="xref" href="xindex.html#XINDEX-GIN-ARRAY-STRAT-TABLE" title="Table 38.7. GIN Array Strategies">Table 38.7</a>. + </p><div class="table" id="XINDEX-GIN-ARRAY-STRAT-TABLE"><p class="title"><strong>Table 38.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> + BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they + don't have a fixed set of strategies either. Instead the support routines + of each operator class interpret the strategy numbers according to the + operator class's definition. As an example, the strategy numbers used by + the built-in <code class="literal">Minmax</code> operator classes are shown in + <a class="xref" href="xindex.html#XINDEX-BRIN-MINMAX-STRAT-TABLE" title="Table 38.8. BRIN Minmax Strategies">Table 38.8</a>. + </p><div class="table" id="XINDEX-BRIN-MINMAX-STRAT-TABLE"><p class="title"><strong>Table 38.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> + Notice that all the operators listed above return Boolean values. In + practice, all operators defined as index method search operators must + return type <code class="type">boolean</code>, since they must appear at the top + level of a <code class="literal">WHERE</code> clause to be used with an index. + (Some index access methods also support <em class="firstterm">ordering operators</em>, + which typically don't return Boolean values; that feature is discussed + in <a class="xref" href="xindex.html#XINDEX-ORDERING-OPS" title="38.16.7. Ordering Operators">Section 38.16.7</a>.) + </p></div><div class="sect2" id="XINDEX-SUPPORT"><div class="titlepage"><div><div><h3 class="title">38.16.3. Index Method Support Routines <a href="#XINDEX-SUPPORT" class="id_link">#</a></h3></div></div></div><p> + Strategies aren't usually enough information for the system to figure + out how to use an index. In practice, the index methods require + additional support routines in order to work. For example, the B-tree + index method must be able to compare two keys and determine whether one + is greater than, equal to, or less than the other. Similarly, the + hash index method must be able to compute hash codes for key values. + These operations do not correspond to operators used in qualifications in + SQL commands; they are administrative routines used by + the index methods, internally. + </p><p> + Just as with strategies, the operator class identifies which specific + functions should play each of these roles for a given data type and + semantic interpretation. The index method defines the set + of functions it needs, and the operator class identifies the correct + functions to use by assigning them to the <span class="quote">“<span class="quote">support function numbers</span>”</span> + specified by the index method. + </p><p> + Additionally, some opclasses allow users to specify parameters which + control their behavior. Each builtin index access method has an optional + <code class="function">options</code> support function, which defines a set of + opclass-specific parameters. + </p><p> + B-trees require a comparison support function, + and allow four additional support functions to be + supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-BTREE-SUPPORT-TABLE" title="Table 38.9. B-Tree Support Functions">Table 38.9</a>. + The requirements for these support functions are explained further in + <a class="xref" href="btree-support-funcs.html" title="67.3. B-Tree Support Functions">Section 67.3</a>. + </p><div class="table" id="XINDEX-BTREE-SUPPORT-TABLE"><p class="title"><strong>Table 38.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> + Compare two keys and return an integer less than zero, zero, or + greater than zero, indicating whether the first key is less than, + equal to, or greater than the second + </td><td>1</td></tr><tr><td> + Return the addresses of C-callable sort support function(s) + (optional) + </td><td>2</td></tr><tr><td> + Compare a test value to a base value plus/minus an offset, and return + true or false according to the comparison result (optional) + </td><td>3</td></tr><tr><td> + Determine if it is safe for indexes that use the operator + class to apply the btree deduplication optimization (optional) + </td><td>4</td></tr><tr><td> + Define options that are specific to this operator class + (optional) + </td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p> + Hash indexes require one support function, and allow two additional ones to + be supplied at the operator class author's option, as shown in <a class="xref" href="xindex.html#XINDEX-HASH-SUPPORT-TABLE" title="Table 38.10. Hash Support Functions">Table 38.10</a>. + </p><div class="table" id="XINDEX-HASH-SUPPORT-TABLE"><p class="title"><strong>Table 38.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> + Compute the 64-bit hash value for a key given a 64-bit salt; if + the salt is 0, the low 32 bits of the result must match the value + that would have been computed by function 1 + (optional) + </td><td>2</td></tr><tr><td> + Define options that are specific to this operator class + (optional) + </td><td>3</td></tr></tbody></table></div></div><br class="table-break" /><p> + GiST indexes have eleven support functions, six of which are optional, + as shown in <a class="xref" href="xindex.html#XINDEX-GIST-SUPPORT-TABLE" title="Table 38.11. GiST Support Functions">Table 38.11</a>. + (For more information see <a class="xref" href="gist.html" title="Chapter 68. GiST Indexes">Chapter 68</a>.) + </p><div class="table" id="XINDEX-GIST-SUPPORT-TABLE"><p class="title"><strong>Table 38.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 + 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 + to be indexed (optional)</td><td>3</td></tr><tr><td><code class="function">decompress</code></td><td>compute a decompressed representation of a + 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 + 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 + 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 + 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 + (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 + (optional)</td><td>11</td></tr></tbody></table></div></div><br class="table-break" /><p> + SP-GiST indexes have six support functions, one of which is optional, as + shown in <a class="xref" href="xindex.html#XINDEX-SPGIST-SUPPORT-TABLE" title="Table 38.12. SP-GiST Support Functions">Table 38.12</a>. + (For more information see <a class="xref" href="spgist.html" title="Chapter 69. SP-GiST Indexes">Chapter 69</a>.) + </p><div class="table" id="XINDEX-SPGIST-SUPPORT-TABLE"><p class="title"><strong>Table 38.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 + query</td><td>4</td></tr><tr><td><code class="function">leaf_consistent</code></td><td>determine whether key satisfies the + 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 + (optional)</td><td>6</td></tr></tbody></table></div></div><br class="table-break" /><p> + GIN indexes have seven support functions, four of which are optional, + as shown in <a class="xref" href="xindex.html#XINDEX-GIN-SUPPORT-TABLE" title="Table 38.13. GIN Support Functions">Table 38.13</a>. + (For more information see <a class="xref" href="gin.html" title="Chapter 70. GIN Indexes">Chapter 70</a>.) + </p><div class="table" id="XINDEX-GIN-SUPPORT-TABLE"><p class="title"><strong>Table 38.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> + compare two keys and return an integer less than zero, zero, + or greater than zero, indicating whether the first key is less than, + equal to, or greater than the second + </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> + determine whether value matches query condition (Boolean variant) + (optional if support function 6 is present) + </td><td>4</td></tr><tr><td><code class="function">comparePartial</code></td><td> + compare partial key from + query and key from index, and return an integer less than zero, zero, + or greater than zero, indicating whether GIN should ignore this index + entry, treat the entry as a match, or stop the index scan (optional) + </td><td>5</td></tr><tr><td><code class="function">triConsistent</code></td><td> + determine whether value matches query condition (ternary variant) + (optional if support function 4 is present) + </td><td>6</td></tr><tr><td><code class="function">options</code></td><td> + define options that are specific to this operator class + (optional) + </td><td>7</td></tr></tbody></table></div></div><br class="table-break" /><p> + BRIN indexes have five basic support functions, one of which is optional, + as shown in <a class="xref" href="xindex.html#XINDEX-BRIN-SUPPORT-TABLE" title="Table 38.14. BRIN Support Functions">Table 38.14</a>. Some versions of + the basic functions require additional support functions to be provided. + (For more information see <a class="xref" href="brin-extensibility.html" title="71.3. Extensibility">Section 71.3</a>.) + </p><div class="table" id="XINDEX-BRIN-SUPPORT-TABLE"><p class="title"><strong>Table 38.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> + return internal information describing the indexed columns' + summary data + </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> + compute union of two summary tuples + </td><td>4</td></tr><tr><td><code class="function">options</code></td><td> + define options that are specific to this operator class + (optional) + </td><td>5</td></tr></tbody></table></div></div><br class="table-break" /><p> + Unlike search operators, support functions return whichever data + type the particular index method expects; for example in the case + of the comparison function for B-trees, a signed integer. The number + and types of the arguments to each support function are likewise + dependent on the index method. For B-tree and hash the comparison and + hashing support functions take the same input data types as do the + operators included in the operator class, but this is not the case for + most GiST, SP-GiST, GIN, and BRIN support functions. + </p></div><div class="sect2" id="XINDEX-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">38.16.4. An Example <a href="#XINDEX-EXAMPLE" class="id_link">#</a></h3></div></div></div><p> + Now that we have seen the ideas, here is the promised example of + creating a new operator class. + (You can find a working copy of this example in + <code class="filename">src/tutorial/complex.c</code> and + <code class="filename">src/tutorial/complex.sql</code> in the source + distribution.) + The operator class encapsulates + operators that sort complex numbers in absolute value order, so we + choose the name <code class="literal">complex_abs_ops</code>. First, we need + a set of operators. The procedure for defining operators was + discussed in <a class="xref" href="xoper.html" title="38.14. User-Defined Operators">Section 38.14</a>. For an operator class on + B-trees, the operators we require are: + + </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> + </p><p> + The least error-prone way to define a related set of comparison operators + is to write the B-tree comparison support function first, and then write the + other functions as one-line wrappers around the support function. This + reduces the odds of getting inconsistent results for corner cases. + Following this approach, we first write: + +</p><pre class="programlisting"> +#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y) + +static int +complex_abs_cmp_internal(Complex *a, Complex *b) +{ + double amag = Mag(a), + bmag = Mag(b); + + if (amag < bmag) + return -1; + if (amag > bmag) + return 1; + return 0; +} + +</pre><p> + + Now the less-than function looks like: + +</p><pre class="programlisting"> +PG_FUNCTION_INFO_V1(complex_abs_lt); + +Datum +complex_abs_lt(PG_FUNCTION_ARGS) +{ + Complex *a = (Complex *) PG_GETARG_POINTER(0); + Complex *b = (Complex *) PG_GETARG_POINTER(1); + + PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0); +} + +</pre><p> + + The other four functions differ only in how they compare the internal + function's result to zero. + </p><p> + Next we declare the functions and the operators based on the functions + to SQL: + +</p><pre class="programlisting"> +CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool + AS '<em class="replaceable"><code>filename</code></em>', 'complex_abs_lt' + LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR < ( + leftarg = complex, rightarg = complex, procedure = complex_abs_lt, + commutator = > , negator = >= , + restrict = scalarltsel, join = scalarltjoinsel +); +</pre><p> + It is important to specify the correct commutator and negator operators, + as well as suitable restriction and join selectivity + functions, otherwise the optimizer will be unable to make effective + use of the index. + </p><p> + Other things worth noting are happening here: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + There can only be one operator named, say, <code class="literal">=</code> + and taking type <code class="type">complex</code> for both operands. In this + case we don't have any other operator <code class="literal">=</code> for + <code class="type">complex</code>, but if we were building a practical data + type we'd probably want <code class="literal">=</code> to be the ordinary + equality operation for complex numbers (and not the equality of + the absolute values). In that case, we'd need to use some other + operator name for <code class="function">complex_abs_eq</code>. + </p></li><li class="listitem"><p> + Although <span class="productname">PostgreSQL</span> can cope with + functions having the same SQL name as long as they have different + argument data types, C can only cope with one global function + having a given name. So we shouldn't name the C function + something simple like <code class="filename">abs_eq</code>. Usually it's + a good practice to include the data type name in the C function + name, so as not to conflict with functions for other data types. + </p></li><li class="listitem"><p> + We could have made the SQL name + of the function <code class="filename">abs_eq</code>, relying on + <span class="productname">PostgreSQL</span> to distinguish it by + argument data types from any other SQL function of the same name. + To keep the example simple, we make the function have the same + names at the C level and SQL level. + </p></li></ul></div><p> + </p><p> + The next step is the registration of the support routine required + by B-trees. The example C code that implements this is in the same + file that contains the operator functions. This is how we declare + the function: + +</p><pre class="programlisting"> +CREATE FUNCTION complex_abs_cmp(complex, complex) + RETURNS integer + AS '<em class="replaceable"><code>filename</code></em>' + LANGUAGE C IMMUTABLE STRICT; +</pre><p> + </p><p> + Now that we have the required operators and support routine, + we can finally create the operator class: + +</p><pre class="programlisting"> +CREATE OPERATOR CLASS complex_abs_ops + DEFAULT FOR TYPE complex USING btree AS + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 complex_abs_cmp(complex, complex); + +</pre><p> + </p><p> + And we're done! It should now be possible to create + and use B-tree indexes on <code class="type">complex</code> columns. + </p><p> + We could have written the operator entries more verbosely, as in: +</p><pre class="programlisting"> + OPERATOR 1 < (complex, complex) , +</pre><p> + but there is no need to do so when the operators take the same data type + we are defining the operator class for. + </p><p> + The above example assumes that you want to make this new operator class the + default B-tree operator class for the <code class="type">complex</code> data type. + If you don't, just leave out the word <code class="literal">DEFAULT</code>. + </p></div><div class="sect2" id="XINDEX-OPFAMILY"><div class="titlepage"><div><div><h3 class="title">38.16.5. Operator Classes and Operator Families <a href="#XINDEX-OPFAMILY" class="id_link">#</a></h3></div></div></div><p> + So far we have implicitly assumed that an operator class deals with + only one data type. While there certainly can be only one data type in + a particular index column, it is often useful to index operations that + compare an indexed column to a value of a different data type. Also, + if there is use for a cross-data-type operator in connection with an + operator class, it is often the case that the other data type has a + related operator class of its own. It is helpful to make the connections + between related classes explicit, because this can aid the planner in + optimizing SQL queries (particularly for B-tree operator classes, since + the planner contains a great deal of knowledge about how to work with them). + </p><p> + To handle these needs, <span class="productname">PostgreSQL</span> + uses the concept of an <em class="firstterm">operator + family</em><a id="id-1.8.3.19.9.3.3" class="indexterm"></a>. + An operator family contains one or more operator classes, and can also + contain indexable operators and corresponding support functions that + belong to the family as a whole but not to any single class within the + family. We say that such operators and functions are <span class="quote">“<span class="quote">loose</span>”</span> + within the family, as opposed to being bound into a specific class. + Typically each operator class contains single-data-type operators + while cross-data-type operators are loose in the family. + </p><p> + All the operators and functions in an operator family must have compatible + semantics, where the compatibility requirements are set by the index + method. You might therefore wonder why bother to single out particular + subsets of the family as operator classes; and indeed for many purposes + the class divisions are irrelevant and the family is the only interesting + grouping. The reason for defining operator classes is that they specify + how much of the family is needed to support any particular index. + If there is an index using an operator class, then that operator class + cannot be dropped without dropping the index — but other parts of + the operator family, namely other operator classes and loose operators, + could be dropped. Thus, an operator class should be specified to contain + the minimum set of operators and functions that are reasonably needed + to work with an index on a specific data type, and then related but + non-essential operators can be added as loose members of the operator + family. + </p><p> + As an example, <span class="productname">PostgreSQL</span> has a built-in + B-tree operator family <code class="literal">integer_ops</code>, which includes operator + classes <code class="literal">int8_ops</code>, <code class="literal">int4_ops</code>, and + <code class="literal">int2_ops</code> for indexes on <code class="type">bigint</code> (<code class="type">int8</code>), + <code class="type">integer</code> (<code class="type">int4</code>), and <code class="type">smallint</code> (<code class="type">int2</code>) + columns respectively. The family also contains cross-data-type comparison + operators allowing any two of these types to be compared, so that an index + on one of these types can be searched using a comparison value of another + type. The family could be duplicated by these definitions: + +</p><pre class="programlisting"> +CREATE OPERATOR FAMILY integer_ops USING btree; + +CREATE OPERATOR CLASS int8_ops +DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS + -- standard int8 comparisons + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 btint8cmp(int8, int8) , + FUNCTION 2 btint8sortsupport(internal) , + FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) , + FUNCTION 4 btequalimage(oid) ; + +CREATE OPERATOR CLASS int4_ops +DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS + -- standard int4 comparisons + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 btint4cmp(int4, int4) , + FUNCTION 2 btint4sortsupport(internal) , + FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) , + FUNCTION 4 btequalimage(oid) ; + +CREATE OPERATOR CLASS int2_ops +DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS + -- standard int2 comparisons + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 btint2cmp(int2, int2) , + FUNCTION 2 btint2sortsupport(internal) , + FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) , + FUNCTION 4 btequalimage(oid) ; + +ALTER OPERATOR FAMILY integer_ops USING btree ADD + -- cross-type comparisons int8 vs int2 + OPERATOR 1 < (int8, int2) , + OPERATOR 2 <= (int8, int2) , + OPERATOR 3 = (int8, int2) , + OPERATOR 4 >= (int8, int2) , + OPERATOR 5 > (int8, int2) , + FUNCTION 1 btint82cmp(int8, int2) , + + -- cross-type comparisons int8 vs int4 + OPERATOR 1 < (int8, int4) , + OPERATOR 2 <= (int8, int4) , + OPERATOR 3 = (int8, int4) , + OPERATOR 4 >= (int8, int4) , + OPERATOR 5 > (int8, int4) , + FUNCTION 1 btint84cmp(int8, int4) , + + -- cross-type comparisons int4 vs int2 + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2) , + + -- cross-type comparisons int4 vs int8 + OPERATOR 1 < (int4, int8) , + OPERATOR 2 <= (int4, int8) , + OPERATOR 3 = (int4, int8) , + OPERATOR 4 >= (int4, int8) , + OPERATOR 5 > (int4, int8) , + FUNCTION 1 btint48cmp(int4, int8) , + + -- cross-type comparisons int2 vs int8 + OPERATOR 1 < (int2, int8) , + OPERATOR 2 <= (int2, int8) , + OPERATOR 3 = (int2, int8) , + OPERATOR 4 >= (int2, int8) , + OPERATOR 5 > (int2, int8) , + FUNCTION 1 btint28cmp(int2, int8) , + + -- cross-type comparisons int2 vs int4 + OPERATOR 1 < (int2, int4) , + OPERATOR 2 <= (int2, int4) , + OPERATOR 3 = (int2, int4) , + OPERATOR 4 >= (int2, int4) , + OPERATOR 5 > (int2, int4) , + FUNCTION 1 btint24cmp(int2, int4) , + + -- cross-type in_range functions + FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) , + FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ; + +</pre><p> + + Notice that this definition <span class="quote">“<span class="quote">overloads</span>”</span> the operator strategy and + support function numbers: each number occurs multiple times within the + family. This is allowed so long as each instance of a + particular number has distinct input data types. The instances that have + both input types equal to an operator class's input type are the + primary operators and support functions for that operator class, + and in most cases should be declared as part of the operator class rather + than as loose members of the family. + </p><p> + In a B-tree operator family, all the operators in the family must sort + compatibly, as is specified in detail in <a class="xref" href="btree-behavior.html" title="67.2. Behavior of B-Tree Operator Classes">Section 67.2</a>. + For each + operator in the family there must be a support function having the same + two input data types as the operator. It is recommended that a family be + complete, i.e., for each combination of data types, all operators are + included. Each operator class should include just the non-cross-type + operators and support function for its data type. + </p><p> + To build a multiple-data-type hash operator family, compatible hash + support functions must be created for each data type supported by the + family. Here compatibility means that the functions are guaranteed to + return the same hash code for any two values that are considered equal + by the family's equality operators, even when the values are of different + types. This is usually difficult to accomplish when the types have + different physical representations, but it can be done in some cases. + Furthermore, casting a value from one data type represented in the operator + family to another data type also represented in the operator family via + an implicit or binary coercion cast must not change the computed hash value. + Notice that there is only one support function per data type, not one + per equality operator. It is recommended that a family be complete, i.e., + provide an equality operator for each combination of data types. + Each operator class should include just the non-cross-type equality + operator and the support function for its data type. + </p><p> + GiST, SP-GiST, and GIN indexes do not have any explicit notion of + cross-data-type operations. The set of operators supported is just + whatever the primary support functions for a given operator class can + handle. + </p><p> + In BRIN, the requirements depends on the framework that provides the + operator classes. For operator classes based on <code class="literal">minmax</code>, + the behavior required is the same as for B-tree operator families: + all the operators in the family must sort compatibly, and casts must + not change the associated sort ordering. + </p><div class="note"><h3 class="title">Note</h3><p> + Prior to <span class="productname">PostgreSQL</span> 8.3, there was no concept + of operator families, and so any cross-data-type operators intended to be + used with an index had to be bound directly into the index's operator + class. While this approach still works, it is deprecated because it + makes an index's dependencies too broad, and because the planner can + handle cross-data-type comparisons more effectively when both data types + have operators in the same operator family. + </p></div></div><div class="sect2" id="XINDEX-OPCLASS-DEPENDENCIES"><div class="titlepage"><div><div><h3 class="title">38.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> + <span class="productname">PostgreSQL</span> uses operator classes to infer the + properties of operators in more ways than just whether they can be used + with indexes. Therefore, you might want to create operator classes + even if you have no intention of indexing any columns of your data type. + </p><p> + In particular, there are SQL features such as <code class="literal">ORDER BY</code> and + <code class="literal">DISTINCT</code> that require comparison and sorting of values. + To implement these features on a user-defined data type, + <span class="productname">PostgreSQL</span> looks for the default B-tree operator + class for the data type. The <span class="quote">“<span class="quote">equals</span>”</span> member of this operator + class defines the system's notion of equality of values for + <code class="literal">GROUP BY</code> and <code class="literal">DISTINCT</code>, and the sort ordering + imposed by the operator class defines the default <code class="literal">ORDER BY</code> + ordering. + </p><p> + If there is no default B-tree operator class for a data type, the system + will look for a default hash operator class. But since that kind of + operator class only provides equality, it is only able to support grouping + not sorting. + </p><p> + When there is no default operator class for a data type, you will get + errors like <span class="quote">“<span class="quote">could not identify an ordering operator</span>”</span> if you + try to use these SQL features with the data type. + </p><div class="note"><h3 class="title">Note</h3><p> + In <span class="productname">PostgreSQL</span> versions before 7.4, + sorting and grouping operations would implicitly use operators named + <code class="literal">=</code>, <code class="literal"><</code>, and <code class="literal">></code>. The new + behavior of relying on default operator classes avoids having to make + any assumption about the behavior of operators with particular names. + </p></div><p> + Sorting by a non-default B-tree operator class is possible by specifying + the class's less-than operator in a <code class="literal">USING</code> option, + for example +</p><pre class="programlisting"> +SELECT * FROM mytable ORDER BY somecol USING ~<~; +</pre><p> + Alternatively, specifying the class's greater-than operator + in <code class="literal">USING</code> selects a descending-order sort. + </p><p> + Comparison of arrays of a user-defined type also relies on the semantics + defined by the type's default B-tree operator class. If there is no + default B-tree operator class, but there is a default hash operator class, + then array equality is supported, but not ordering comparisons. + </p><p> + Another SQL feature that requires even more data-type-specific knowledge + is the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em> + <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code> framing option + 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>). + For a query such as +</p><pre class="programlisting"> +SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) + FROM mytable; +</pre><p> + it is not sufficient to know how to order by <code class="literal">x</code>; + the database must also understand how to <span class="quote">“<span class="quote">subtract 5</span>”</span> or + <span class="quote">“<span class="quote">add 10</span>”</span> to the current row's value of <code class="literal">x</code> + to identify the bounds of the current window frame. Comparing the + resulting bounds to other rows' values of <code class="literal">x</code> is + possible using the comparison operators provided by the B-tree operator + class that defines the <code class="literal">ORDER BY</code> ordering — but + addition and subtraction operators are not part of the operator class, so + which ones should be used? Hard-wiring that choice would be undesirable, + because different sort orders (different B-tree operator classes) might + need different behavior. Therefore, a B-tree operator class can specify + an <em class="firstterm">in_range</em> support function that encapsulates the + addition and subtraction behaviors that make sense for its sort order. + It can even provide more than one in_range support function, in case + there is more than one data type that makes sense to use as the offset + in <code class="literal">RANGE</code> clauses. + If the B-tree operator class associated with the window's <code class="literal">ORDER + BY</code> clause does not have a matching in_range support function, + the <code class="literal">RANGE</code> <em class="replaceable"><code>offset</code></em> + <code class="literal">PRECEDING</code>/<code class="literal">FOLLOWING</code> + option is not supported. + </p><p> + Another important point is that an equality operator that + appears in a hash operator family is a candidate for hash joins, + hash aggregation, and related optimizations. The hash operator family + is essential here since it identifies the hash function(s) to use. + </p></div><div class="sect2" id="XINDEX-ORDERING-OPS"><div class="titlepage"><div><div><h3 class="title">38.16.7. Ordering Operators <a href="#XINDEX-ORDERING-OPS" class="id_link">#</a></h3></div></div></div><p> + Some index access methods (currently, only GiST and SP-GiST) support the concept of + <em class="firstterm">ordering operators</em>. What we have been discussing so far + are <em class="firstterm">search operators</em>. A search operator is one for which + the index can be searched to find all rows satisfying + <code class="literal">WHERE</code> + <em class="replaceable"><code>indexed_column</code></em> + <em class="replaceable"><code>operator</code></em> + <em class="replaceable"><code>constant</code></em>. + Note that nothing is promised about the order in which the matching rows + will be returned. In contrast, an ordering operator does not restrict the + set of rows that can be returned, but instead determines their order. + An ordering operator is one for which the index can be scanned to return + rows in the order represented by + <code class="literal">ORDER BY</code> + <em class="replaceable"><code>indexed_column</code></em> + <em class="replaceable"><code>operator</code></em> + <em class="replaceable"><code>constant</code></em>. + The reason for defining ordering operators that way is that it supports + nearest-neighbor searches, if the operator is one that measures distance. + For example, a query like +</p><pre class="programlisting"> +SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; + +</pre><p> + finds the ten places closest to a given target point. A GiST index + on the location column can do this efficiently because + <code class="literal"><-></code> is an ordering operator. + </p><p> + While search operators have to return Boolean results, ordering operators + usually return some other type, such as float or numeric for distances. + This type is normally not the same as the data type being indexed. + To avoid hard-wiring assumptions about the behavior of different data + types, the definition of an ordering operator is required to name + a B-tree operator family that specifies the sort ordering of the result + data type. As was stated in the previous section, B-tree operator families + define <span class="productname">PostgreSQL</span>'s notion of ordering, so + this is a natural representation. Since the point <code class="literal"><-></code> + operator returns <code class="type">float8</code>, it could be specified in an operator + class creation command like this: +</p><pre class="programlisting"> +OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops + +</pre><p> + where <code class="literal">float_ops</code> is the built-in operator family that includes + operations on <code class="type">float8</code>. This declaration states that the index + is able to return rows in order of increasing values of the + <code class="literal"><-></code> operator. + </p></div><div class="sect2" id="XINDEX-OPCLASS-FEATURES"><div class="titlepage"><div><div><h3 class="title">38.16.8. Special Features of Operator Classes <a href="#XINDEX-OPCLASS-FEATURES" class="id_link">#</a></h3></div></div></div><p> + There are two special features of operator classes that we have + not discussed yet, mainly because they are not useful + with the most commonly used index methods. + </p><p> + Normally, declaring an operator as a member of an operator class + (or family) means that the index method can retrieve exactly the set of rows + that satisfy a <code class="literal">WHERE</code> condition using the operator. For example: +</p><pre class="programlisting"> +SELECT * FROM table WHERE integer_column < 4; +</pre><p> + can be satisfied exactly by a B-tree index on the integer column. + But there are cases where an index is useful as an inexact guide to + the matching rows. For example, if a GiST index stores only bounding boxes + for geometric objects, then it cannot exactly satisfy a <code class="literal">WHERE</code> + condition that tests overlap between nonrectangular objects such as + polygons. Yet we could use the index to find objects whose bounding + box overlaps the bounding box of the target object, and then do the + exact overlap test only on the objects found by the index. If this + scenario applies, the index is said to be <span class="quote">“<span class="quote">lossy</span>”</span> for the + operator. Lossy index searches are implemented by having the index + method return a <em class="firstterm">recheck</em> flag when a row might or might + not really satisfy the query condition. The core system will then + test the original query condition on the retrieved row to see whether + it should be returned as a valid match. This approach works if + the index is guaranteed to return all the required rows, plus perhaps + some additional rows, which can be eliminated by performing the original + operator invocation. The index methods that support lossy searches + (currently, GiST, SP-GiST and GIN) allow the support functions of individual + operator classes to set the recheck flag, and so this is essentially an + operator-class feature. + </p><p> + Consider again the situation where we are storing in the index only + the bounding box of a complex object such as a polygon. In this + case there's not much value in storing the whole polygon in the index + entry — we might as well store just a simpler object of type + <code class="type">box</code>. This situation is expressed by the <code class="literal">STORAGE</code> + option in <code class="command">CREATE OPERATOR CLASS</code>: we'd write something like: + +</p><pre class="programlisting"> +CREATE OPERATOR CLASS polygon_ops + DEFAULT FOR TYPE polygon USING gist AS + ... + STORAGE box; +</pre><p> + + At present, only the GiST, SP-GiST, GIN and BRIN index methods support a + <code class="literal">STORAGE</code> type that's different from the column data type. + The GiST <code class="function">compress</code> and <code class="function">decompress</code> support + routines must deal with data-type conversion when <code class="literal">STORAGE</code> + is used. SP-GiST likewise requires a <code class="function">compress</code> + support function to convert to the storage type, when that is different; + if an SP-GiST opclass also supports retrieving data, the reverse + conversion must be handled by the <code class="function">consistent</code> function. + In GIN, the <code class="literal">STORAGE</code> type identifies the type of + the <span class="quote">“<span class="quote">key</span>”</span> values, which normally is different from the type + of the indexed column — for example, an operator class for + integer-array columns might have keys that are just integers. The + GIN <code class="function">extractValue</code> and <code class="function">extractQuery</code> support + routines are responsible for extracting keys from indexed values. + BRIN is similar to GIN: the <code class="literal">STORAGE</code> type identifies the + type of the stored summary values, and operator classes' support + procedures are responsible for interpreting the summary values + correctly. + </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="38.15. Operator Optimization Information">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-extensions.html" title="38.17. Packaging Related Objects into an Extension">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.15. Operator Optimization Information </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.17. Packaging Related Objects into an Extension</td></tr></table></div></body></html>
\ No newline at end of file |