- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
ISBN 9781906966065
RRP £14.95 ($19.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” specified by the index method.

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 Description 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
comparePartial (optional method) 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 5

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. The number and types of the arguments to each support function are likewise dependent on the index method. For B-tree and hash the 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 GIN and GiST support functions.

ISBN 9781906966065The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming GuideSee the print edition