|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
5.14.2 Index Method Strategies
The operators associated with an operator class are identified by
“strategy numbers”, 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 “less than” and “greater than or equal
to” are interesting with respect to a B-tree.
PostgreSQL allows the user to define operators,
PostgreSQL cannot look at the name of an operator
>=) and tell what kind of
comparison it is. Instead, the index method defines a set of
“strategies”, 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
The B-tree index method defines five strategies, shown in Table 5-2.
|Operation|| Strategy Number
|less than|| 1
|less than or equal|| 2
|greater than or equal|| 4
Hash indexes express only bitwise equality, and so they use only one strategy, shown in Table 5-3.
GiST indexes are even more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” 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 “R-tree” strategies shown in Table 5-4. 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.
|Operation|| Strategy Number
|strictly left of|| 1
|does not extend to right of|| 2
|does not extend to left of|| 4
|strictly right of|| 5
|contained by|| 8
|does not extend above|| 9
|strictly below|| 10
|strictly above|| 11
|does not extend below||12|
GIN 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 arrays are shown in Table 5-5.
Note that all strategy operators return Boolean values. In
practice, all operators defined as index method strategies must
boolean, since they must appear at the top
level of a
WHERE clause to be used with an index.
By the way, the
pg_am tells whether
the index method supports ordered scans. Zero means it doesn't; if it
amorderstrategy is the strategy
number that corresponds to the ordering operator. For example, B-tree
amorderstrategy = 1, which is its
“less than” strategy number.
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|