|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>>>
MERGES clause, if present, tells the system that
it is permissible to use the merge-join method for a join based on this
MERGES 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.
Merge join is based on the idea of sorting the left- and right-hand tables
into order and then scanning them in parallel. So, both data types must
be capable of being fully ordered, and the join operator must be one
that can only succeed for pairs of values that fall at the
in the sort order. In practice this means that the join operator must
behave like equality. But it is possible to merge-join two
distinct data types so long as they are logically compatible. For
equality operator is merge-joinable.
We only need sorting operators that will bring both data types into a
logically compatible sequence.
To be marked
MERGES, the join operator must appear
as an equality member of a
btree index operator family.
This is not enforced when you create
the operator, since of course the referencing operator family couldn't
exist yet. But the operator will not actually be used for merge joins
unless a matching operator family can be found. The
MERGES flag thus acts as a hint to the planner that
it's worth looking for a matching operator family.
A merge-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
btree 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 merge-joinable operator must be marked immutable or stable. If it is volatile, the system will never attempt to use the operator for a merge join.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|