|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
HASHES clause, if present, tells the system that
it is permissible to use the hash join method for a join based on this
HASHES only makes sense for a binary operator that
boolean, and in practice the operator must represent
equality for some data type or pair of data types.
The assumption underlying hash join is that the join operator can
only return true for pairs of left and right values that hash to the
same hash code. If two values get put in different hash buckets, the
join will never compare them at all, implicitly assuming that the
result of the join operator must be false. So it never makes sense
HASHES for operators that do not represent
some form of equality. In most cases it is only practical to support
hashing for operators that take the same data type on both sides.
However, sometimes it is possible to design compatible hash functions
for two or more data types; that is, functions that will generate the
same hash codes for “equal” values, even though the values
have different representations. For example, it's fairly simple
to arrange this property when hashing integers of different widths.
To be marked
HASHES, the join operator must appear
in a hash index operator family. This is not enforced when you create
the operator, since of course the referencing operator family couldn't
exist yet. But attempts to use the operator in hash joins will fail
at run time if no such operator family exists. The system needs the
operator family to find the data-type-specific hash function(s) for the
operator's input data type(s). Of course, you must also create suitable
hash functions before you can create the operator family.
Care should be exercised when preparing a hash function, because there
are machine-dependent ways in which it might fail to do the right thing.
For example, if your data type is a structure in which there might be
uninteresting pad bits, you cannot simply pass the whole structure to
hash_any. (Unless you write your other operators and
functions to ensure that the unused bits are always zero, which is the
Another example is that on machines that meet the IEEE
floating-point standard, negative zero and positive zero are different
values (different bit patterns) but they are defined to compare equal.
If a float value might contain negative zero then extra steps are needed
to ensure it generates the same hash value as positive zero.
A hash-joinable operator must have a commutator (itself if the two operand data types are the same, or a related equality operator if they are different) that appears in the same operator family. If this is not the case, planner errors might occur when the operator is used. Also, it is a good idea (but not strictly required) for a hash operator family that supports multiple data types to provide equality operators for every combination of the data types; this allows better optimization.
Note: The function underlying a hash-joinable operator must be marked immutable or stable. If it is volatile, the system will never attempt to use the operator for a hash join.
Note: If a hash-joinable operator has an underlying function that is marked strict, the function must also be complete: that is, it should return true or false, never null, for any two nonnull inputs. If this rule is not followed, hash-optimization of
INoperations might generate wrong results. (Specifically,
INmight return false where the correct answer according to the standard would be null; or it might yield an error complaining that it wasn't prepared for a null result.)
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|