1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
<!-- $PostgreSQL: pgsql/doc/src/sgml/btree-gist.sgml,v 1.8 2010/08/17 04:37:20 petere Exp $ -->
<sect1 id="btree-gist">
<title>btree_gist</title>
<indexterm zone="btree-gist">
<primary>btree_gist</primary>
</indexterm>
<para>
<filename>btree_gist</> provides sample GiST operator classes that
implement B-tree equivalent behavior for the data types
<type>int2</>, <type>int4</>, <type>int8</>, <type>float4</>,
<type>float8</>, <type>numeric</>, <type>timestamp with time zone</>,
<type>timestamp without time zone</>, <type>time with time zone</>,
<type>time without time zone</>, <type>date</>, <type>interval</>,
<type>oid</>, <type>money</>, <type>char</>,
<type>varchar</>, <type>text</>, <type>bytea</>, <type>bit</>,
<type>varbit</>, <type>macaddr</>, <type>inet</>, and <type>cidr</>.
</para>
<para>
In general, these operator classes will not outperform the equivalent
standard B-tree index methods, and they lack one major feature of the
standard B-tree code: the ability to enforce uniqueness. However,
they are useful for GiST testing and as a base for developing other
GiST operator classes.
</para>
<para>
In addition to the typical btree search operators, btree_gist also
provides search operators for <literal><></literal> (<quote>not
equals</quote>). This may be useful in combination with an
<link linkend="SQL-CREATETABLE-EXCLUDE">exclusion constraint</link>,
as described below.
</para>
<sect2>
<title>Example usage</title>
<para>
Simple example using btree_gist instead of btree:
</para>
<programlisting>
CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING gist (a);
-- query
SELECT * FROM test WHERE a < 10;
</programlisting>
<para>
Use an <link linkend="SQL-CREATETABLE-EXCLUDE">exclusion
constraint</link> to enforce the rule that a cage at a zoo
can contain only one kind of animal:
</para>
<programlisting>
=> CREATE TABLE zoo (
cage INTEGER,
animal TEXT,
EXCLUDE USING gist (cage WITH =, animal WITH <>)
);
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'lion');
ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
=> INSERT INTO zoo VALUES(124, 'lion');
INSERT 0 1
</programlisting>
</sect2>
<sect2>
<title>Authors</title>
<para>
Teodor Sigaev (<email>teodor@stack.net</email>) ,
Oleg Bartunov (<email>oleg@sai.msu.su</email>), and
Janko Richter (<email>jankorichter@yahoo.de</email>). See
<ulink url="http://www.sai.msu.su/~megera/postgres/gist/"></ulink>
for additional information.
</para>
</sect2>
</sect1>
|
