|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.7 Special Features of Operator Classes
There are two special features of operator classes that we have not discussed yet, mainly because they are not useful with the most commonly used index methods.
Normally, declaring an operator as a member of an operator class means
that the index method can retrieve exactly the set of rows
that satisfy a
WHERE condition using the operator. For example,
SELECT * FROM table WHERE integer_column < 4;
can be satisfied exactly by a B-tree index on the integer column.
But there are cases where an index is useful as an inexact guide to
the matching rows. For example, if a GiST index stores only
bounding boxes for objects, then it cannot exactly satisfy a
condition that tests overlap between nonrectangular objects such as
polygons. Yet we could use the index to find objects whose bounding
box overlaps the bounding box of the target object, and then do the
exact overlap test only on the objects found by the index. If this
scenario applies, the index is said to be “lossy” for the
operator, and we add
RECHECK to the
CREATE OPERATOR CLASS command.
RECHECK is valid if the index is guaranteed to return
all the required rows, plus perhaps some additional rows, which
can be eliminated by performing the original operator invocation.
Consider again the situation where we are storing in the index only
the bounding box of a complex object such as a polygon. In this
case there's not much value in storing the whole polygon in the index
entry--we may as well store just a simpler object of type
box. This situation is expressed by the
CREATE OPERATOR CLASS: we'd write something like
CREATE OPERATOR CLASS polygon_ops DEFAULT FOR TYPE polygon USING gist AS ... STORAGE box;
At present, only the GiST and GIN index methods support a
STORAGE type that's different from the column data type.
routines must deal with data-type conversion when
is used. In GIN, the
STORAGE type identifies the type of
the “key” values, which normally is different from the type
of the indexed column--for example, an operator class for
integer array columns might have keys that are just integers. The
routines are responsible for extracting keys from indexed values.
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|