| 1 |
<!-- doc/src/sgml/btree-gist.sgml --> |
| 2 |
|
| 3 |
<sect1 id="btree-gist" xreflabel="btree_gist"> |
| 4 |
<title>btree_gist — GiST operator classes with B-tree behavior</title> |
| 5 |
|
| 6 |
<indexterm zone="btree-gist"> |
| 7 |
<primary>btree_gist</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
<filename>btree_gist</filename> provides GiST index operator classes that |
| 12 |
implement B-tree equivalent behavior for the data types |
| 13 |
<type>int2</type>, <type>int4</type>, <type>int8</type>, <type>float4</type>, |
| 14 |
<type>float8</type>, <type>numeric</type>, <type>timestamp with time zone</type>, |
| 15 |
<type>timestamp without time zone</type>, <type>time with time zone</type>, |
| 16 |
<type>time without time zone</type>, <type>date</type>, <type>interval</type>, |
| 17 |
<type>oid</type>, <type>money</type>, <type>char</type>, |
| 18 |
<type>varchar</type>, <type>text</type>, <type>bytea</type>, <type>bit</type>, |
| 19 |
<type>varbit</type>, <type>macaddr</type>, <type>macaddr8</type>, <type>inet</type>, |
| 20 |
<type>cidr</type>, <type>uuid</type>, <type>bool</type> and all <type>enum</type> types. |
| 21 |
</para> |
| 22 |
|
| 23 |
<para> |
| 24 |
In general, these operator classes will not outperform the equivalent |
| 25 |
standard B-tree index methods, and they lack one major feature of the |
| 26 |
standard B-tree code: the ability to enforce uniqueness. However, |
| 27 |
they provide some other features that are not available with a B-tree |
| 28 |
index, as described below. Also, these operator classes are useful |
| 29 |
when a multicolumn GiST index is needed, wherein some of the columns |
| 30 |
are of data types that are only indexable with GiST but other columns |
| 31 |
are just simple data types. Lastly, these operator classes are useful for |
| 32 |
GiST testing and as a base for developing other GiST operator classes. |
| 33 |
</para> |
| 34 |
|
| 35 |
<para> |
| 36 |
In addition to the typical B-tree search operators, <filename>btree_gist</filename> |
| 37 |
also provides index support for <literal><></literal> (<quote>not |
| 38 |
equals</quote>). This may be useful in combination with an |
| 39 |
<link linkend="sql-createtable-exclude">exclusion constraint</link>, |
| 40 |
as described below. |
| 41 |
</para> |
| 42 |
|
| 43 |
<para> |
| 44 |
Also, for data types for which there is a natural distance metric, |
| 45 |
<filename>btree_gist</filename> defines a distance operator <literal><-></literal>, |
| 46 |
and provides GiST index support for nearest-neighbor searches using |
| 47 |
this operator. Distance operators are provided for |
| 48 |
<type>int2</type>, <type>int4</type>, <type>int8</type>, <type>float4</type>, |
| 49 |
<type>float8</type>, <type>timestamp with time zone</type>, |
| 50 |
<type>timestamp without time zone</type>, |
| 51 |
<type>time without time zone</type>, <type>date</type>, <type>interval</type>, |
| 52 |
<type>oid</type>, and <type>money</type>. |
| 53 |
</para> |
| 54 |
|
| 55 |
<para> |
| 56 |
By default <filename>btree_gist</filename> builds <acronym>GiST</acronym> index with |
| 57 |
<function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in |
| 58 |
much faster index built speed. It is still possible to revert to buffered built strategy |
| 59 |
by using the <literal>buffering</literal> parameter when creating the index. |
| 60 |
</para> |
| 61 |
|
| 62 |
<para> |
| 63 |
This module is considered <quote>trusted</quote>, that is, it can be |
| 64 |
installed by non-superusers who have <literal>CREATE</literal> privilege |
| 65 |
on the current database. |
| 66 |
</para> |
| 67 |
|
| 68 |
<sect2 id="btree-gist-example-usage"> |
| 69 |
<title>Example Usage</title> |
| 70 |
|
| 71 |
<para> |
| 72 |
Simple example using <literal>btree_gist</literal> instead of <literal>btree</literal>: |
| 73 |
</para> |
| 74 |
|
| 75 |
<programlisting> |
| 76 |
CREATE TABLE test (a int4); |
| 77 |
-- create index |
| 78 |
CREATE INDEX testidx ON test USING GIST (a); |
| 79 |
-- query |
| 80 |
SELECT * FROM test WHERE a < 10; |
| 81 |
-- nearest-neighbor search: find the ten entries closest to "42" |
| 82 |
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10; |
| 83 |
</programlisting> |
| 84 |
|
| 85 |
<para> |
| 86 |
Use an <link linkend="sql-createtable-exclude">exclusion |
| 87 |
constraint</link> to enforce the rule that a cage at a zoo |
| 88 |
can contain only one kind of animal: |
| 89 |
</para> |
| 90 |
|
| 91 |
<programlisting> |
| 92 |
=> CREATE TABLE zoo ( |
| 93 |
cage INTEGER, |
| 94 |
animal TEXT, |
| 95 |
EXCLUDE USING GIST (cage WITH =, animal WITH <>) |
| 96 |
); |
| 97 |
|
| 98 |
=> INSERT INTO zoo VALUES(123, 'zebra'); |
| 99 |
INSERT 0 1 |
| 100 |
=> INSERT INTO zoo VALUES(123, 'zebra'); |
| 101 |
INSERT 0 1 |
| 102 |
=> INSERT INTO zoo VALUES(123, 'lion'); |
| 103 |
ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl" |
| 104 |
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra). |
| 105 |
=> INSERT INTO zoo VALUES(124, 'lion'); |
| 106 |
INSERT 0 1 |
| 107 |
</programlisting> |
| 108 |
|
| 109 |
</sect2> |
| 110 |
|
| 111 |
<sect2 id="btree-gist-authors"> |
| 112 |
<title>Authors</title> |
| 113 |
|
| 114 |
<para> |
| 115 |
Teodor Sigaev (<email>[email protected]</email>), |
| 116 |
Oleg Bartunov (<email>[email protected]</email>), |
| 117 |
Janko Richter (<email>[email protected]</email>), and |
| 118 |
Paul Jungwirth (<email>[email protected]</email>). See |
| 119 |
<ulink url="http://www.sai.msu.su/~megera/postgres/gist/"></ulink> |
| 120 |
for additional information. |
| 121 |
</para> |
| 122 |
|
| 123 |
</sect2> |
| 124 |
|
| 125 |
</sect1> |