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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
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.3 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.3 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>
|
