17.3. Access Method Strategies

The amstrategies column exists to standardize comparisons across data types. For example, B-trees impose a strict ordering on keys, lesser to greater. Since 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. In fact, some access methods don't impose any ordering at all. For example, R-trees express a rectangle-containment relationship, whereas a hashed data structure expresses only bitwise similarity based on the value of a hash function. PostgreSQL needs some consistent way of taking a qualification in your query, looking at the operator, and then deciding if a usable index exists. This implies that PostgreSQL needs to know, for example, that the <= and > operators partition a B-tree. PostgreSQL uses strategies to express these relationships between operators and the way they can be used to scan indexes.

Defining a new set of strategies is beyond the scope of this discussion, but we'll explain how B-tree strategies work because you'll need to know that to add a new B-tree operator class. In the pg_am table, the amstrategies column sets the number of strategies defined for this access method. For B-trees, this number is 5. The meanings of these strategies are shown in Table 17-2.

Table 17-2. B-tree Strategies

OperationIndex
less than1
less than or equal2
equal3
greater than or equal4
greater than5

The idea is that you'll need to add operators corresponding to these strategies to the pg_amop relation (see below). The access method code can use these strategy numbers, regardless of data type, to figure out how to partition the B-tree, compute selectivity, and so on. Don't worry about the details of adding operators yet; just understand that there must be a set of these operators for int2, int4, oid, and all other data types on which a B-tree can operate.