- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

5.14.3 Index Method Support Routines

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.

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 “support function numbers”.

B-trees require a single support function, shown in Table 5-6.

Table 5-6: B-tree Support Functions
Function Support Number
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. 1

Hash indexes likewise require one support function, shown in Table 5-7.

Table 5-7: Hash Support Functions
Function Support Number
Compute the hash value for a key 1

GiST indexes require seven support functions, shown in Table 5-8.

Table 5-8: GiST Support Functions
Function Support Number
consistent - determine whether key satisfies the query qualifier 1
union - compute union of a set of keys 2
compress - compute a compressed representation of a key or value to be indexed 3
decompress - compute a decompressed representation of a compressed key 4
penalty - compute penalty for inserting new key into subtree with given subtree's key 5
picksplit - determine which entries of a page are to be moved to the new page and compute the union keys for resulting pages 6
equal - compare two keys and return true if they are equal 7

GIN indexes require four support functions, shown in Table 5-9.

Table 5-9: GIN Support Functions
Function Support Number
compare - 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 1
extractValue - extract keys from a value to be indexed 2
extractQuery - extract keys from a query condition 3
consistent - determine whether value matches query condition 4

Unlike strategy 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.

ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition