11.\" This is -*-nroff-*-
22.\" XXX standard disclaimer belongs here....
3- .\" $Header: /cvsroot/pgsql/src/man/Attic/create_operator.l,v 1.1 1996/11/14 10:15:58 scrappy Exp $
3+ .\" $Header: /cvsroot/pgsql/src/man/Attic/create_operator.l,v 1.2 1996/11/30 04:56:18 momjian Exp $
44.TH "CREATE OPERATOR" SQL 11/05/95 Postgres95 Postgres95
55.SH NAME
66create operator\(em define a new user operator
3333is a sequence of up to sixteen punctuation characters. The following
3434characters are valid for single-character operator names:
3535.nf
36+
37+ .ce 1
3638~ ! @ # % ^ & ` ?
39+
3740.fi
3841If the operator name is more than one character long, it may consist
3942of any combination of the above characters or the following additional
4043characters:
4144.nf
45+
46+ .ce 1
4247| $ : + - * / < > =
48+
4349.fi
50+ The operator "!=" is mapped to "<>" on input, and they are
51+ therefore equivalent.
4452.PP
4553At least one of
4654.IR leftarg
@@ -86,22 +94,34 @@ area-greater-than, <<<. Suppose that an operator, area-equal, ===,
8694exists, as well as an area not equal, !==. Hence, the query optimizer
8795could freely convert:
8896.nf
97+
98+ .ce 1
8999"0,0,1,1"::box >>> MYBOXES.description
100+
90101.fi
91102to
92103.nf
104+
105+ .ce 1
93106MYBOXES.description <<< "0,0,1,1"::box
107+
94108.fi
95109This allows the execution code to always use the latter representation
96110and simplifies the query optimizer somewhat.
97111.PP
98112The negator operator allows the query optimizer to convert
99113.nf
100- not MYBOXES.description === "0,0,1,1"::box
114+
115+ .ce 1
116+ NOT MYBOXES.description === "0,0,1,1"::box
117+
101118.fi
102119to
103120.nf
121+
122+ .ce 1
104123MYBOXES.description !== "0,0,1,1"::box
124+
105125.fi
106126If a commutator operator name is supplied, Postgres searches for it in
107127the catalog. If it is found and it does not yet have a commutator
@@ -124,11 +144,17 @@ along the lines of [SHAP86]; however, it must know whether this
124144strategy is applicable. For example, a hash-join algorithm is usable
125145for a clause of the form:
126146.nf
147+
148+ .ce 1
127149MYBOXES.description === MYBOXES2.description
150+
128151.fi
129152but not for a clause of the form:
130153.nf
154+
155+ .ce 1
131156MYBOXES.description <<< MYBOXES2.description.
157+
132158.fi
133159The
134160.BR hashes
@@ -141,7 +167,10 @@ be used to sort the two operand classes. For the === clause above,
141167the optimizer must sort both relations using the operator, <<<. On
142168the other hand, merge-sort is not usable with the clause:
143169.nf
170+
171+ .ce 1
144172MYBOXES.description <<< MYBOXES2.description
173+
145174.fi
146175If other join strategies are found to be practical, Postgres will change
147176the optimizer and run-time system to use them and will require
@@ -153,7 +182,10 @@ be worth the complexity involved.
153182The last two pieces of the specification are present so the query
154183optimizer can estimate result sizes. If a clause of the form:
155184.nf
185+
186+ .ce 1
156187MYBOXES.description <<< "0,0,1,1"::box
188+
157189.fi
158190is present in the qualification, then Postgres may have to estimate the
159191fraction of the instances in MYBOXES that satisfy the clause. The
@@ -162,10 +194,7 @@ defined using
162194.IR " define function"
163195which accepts one argument of the correct data type and returns a
164196floating point number. The query optimizer simply calls this
165- function, passing the parameter
166- .nf
167- "0,0,1,1"
168- .fi
197+ function, passing the parameter "0,0,1,1"
169198and multiplies the result by the relation size to get the desired
170199expected number of instances.
171200.PP
@@ -177,11 +206,17 @@ classes involved to compute the desired expected result size.
177206.PP
178207The difference between the function
179208.nf
209+
210+ .ce 1
180211my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
212+
181213.fi
182214and the operator
183215.nf
216+
217+ .ce 1
184218MYBOXES.description === "0,0,1,1"::box
219+
185220.fi
186221is that Postgres attempts to optimize operators and can decide to use an
187222index to restrict the search space when operators are involved.