- 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.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. Because PostgreSQL allows the user to define operators, PostgreSQL cannot look at the name of an operator (e.g., < or >=) 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 semantics.

The B-tree index method defines five strategies, shown in Table 5-2.

Table 5-2: B-tree Strategies
Operation Strategy Number
less than 1
less than or equal 2
equal 3
greater than or equal 4
greater than 5

Hash indexes express only bitwise equality, and so they use only one strategy, shown in Table 5-3.

Table 5-3: Hash Strategies
Operation Strategy Number
equal 1

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.

Table 5-4: GiST Two-Dimensional “R-tree” Strategies
Operation Strategy Number
strictly left of 1
does not extend to right of 2
overlaps 3
does not extend to left of 4
strictly right of 5
same 6
contains 7
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.

Table 5-5: GIN Array Strategies
Operation Strategy Number
overlap 1
contains 2
is contained by 3
equal 4

Note that all strategy operators return Boolean values. In practice, all operators defined as index method strategies must return type boolean, since they must appear at the top level of a WHERE clause to be used with an index.

By the way, the amorderstrategy column in pg_am tells whether the index method supports ordered scans. Zero means it doesn't; if it does, amorderstrategy is the strategy number that corresponds to the ordering operator. For example, B-tree has amorderstrategy = 1, which is its “less than” strategy number.

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