Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit725d52d

Browse files
committed
Create the system catalog infrastructure needed for KNNGIST.
This commit adds columns amoppurpose and amopsortfamily to pg_amop, andcolumn amcanorderbyop to pg_am. For the moment all the entries inamcanorderbyop are "false", since the underlying support isn't there yet.Also, extend the CREATE OPERATOR CLASS/ALTER OPERATOR FAMILY commands with[ FOR SEARCH | FOR ORDER BY sort_operator_family ] clauses to allow the newcolumns of pg_amop to be populated, and create pg_dump support for dumpingthat information.I also added some documentation, although it's perhaps a bit prematuregiven that the feature doesn't do anything useful yet.Teodor Sigaev, Robert Haas, Tom Lane
1 parent4fc09ad commit725d52d

File tree

21 files changed

+913
-475
lines changed

21 files changed

+913
-475
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 66 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -423,7 +423,16 @@
423423
<entry><structfield>amcanorder</structfield></entry>
424424
<entry><type>bool</type></entry>
425425
<entry></entry>
426-
<entry>Does the access method support ordered scans?</entry>
426+
<entry>Does the access method support ordered scans sorted by the
427+
indexed column's value?</entry>
428+
</row>
429+
430+
<row>
431+
<entry><structfield>amcanorderbyop</structfield></entry>
432+
<entry><type>bool</type></entry>
433+
<entry></entry>
434+
<entry>Does the access method support ordered scans sorted by the result
435+
of an operator on the indexed column?</entry>
427436
</row>
428437

429438
<row>
@@ -598,9 +607,13 @@
598607
<para>
599608
The catalog <structname>pg_amop</structname> stores information about
600609
operators associated with access method operator families. There is one
601-
row for each operator that is a member of an operator family. An operator
610+
row for each operator that is a member of an operator family. A family
611+
member can be either a <firstterm>search</> operator or an
612+
<firstterm>ordering</> operator. An operator
602613
can appear in more than one family, but cannot appear in more than one
603-
position within a family.
614+
search position nor more than one ordering position within a family.
615+
(It is allowed, though unlikely, for an operator to be used for both
616+
search and ordering purposes.)
604617
</para>
605618

606619
<table>
@@ -645,6 +658,14 @@
645658
<entry>Operator strategy number</entry>
646659
</row>
647660

661+
<row>
662+
<entry><structfield>amoppurpose</structfield></entry>
663+
<entry><type>char</type></entry>
664+
<entry></entry>
665+
<entry>Operator purpose, either <literal>s</> for search or
666+
<literal>o</> for ordering</entry>
667+
</row>
668+
648669
<row>
649670
<entry><structfield>amopopr</structfield></entry>
650671
<entry><type>oid</type></entry>
@@ -659,10 +680,52 @@
659680
<entry>Index access method operator family is for</entry>
660681
</row>
661682

683+
<row>
684+
<entry><structfield>amopsortfamily</structfield></entry>
685+
<entry><type>oid</type></entry>
686+
<entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry>
687+
<entry>The btree operator family this entry sorts according to, if an
688+
ordering operator; zero if a search operator</entry>
689+
</row>
690+
662691
</tbody>
663692
</tgroup>
664693
</table>
665694

695+
<para>
696+
A <quote>search</> operator entry indicates that an index of this operator
697+
family can be searched to find all rows satisfying
698+
<literal>WHERE</>
699+
<replaceable>indexed_column</>
700+
<replaceable>operator</>
701+
<replaceable>constant</>.
702+
Obviously, such an operator must return boolean, and its left-hand input
703+
type must match the index's column data type.
704+
</para>
705+
706+
<para>
707+
An <quote>ordering</> operator entry indicates that an index of this
708+
operator family can be scanned to return rows in the order represented by
709+
<literal>ORDER BY</>
710+
<replaceable>indexed_column</>
711+
<replaceable>operator</>
712+
<replaceable>constant</>.
713+
Such an operator could return any sortable data type, though again
714+
its left-hand input type must match the index's column data type.
715+
The exact semantics of the <literal>ORDER BY</> are specified by the
716+
<structfield>amopsortfamily</structfield> column, which must reference
717+
a btree operator family for the operator's result type.
718+
</para>
719+
720+
<note>
721+
<para>
722+
At present, it's assumed that the sort order for an ordering operator
723+
is the default for the referenced opfamily, i.e., <literal>ASC NULLS
724+
LAST</>. This might someday be relaxed by adding additional columns
725+
to specify sort options explicitly.
726+
</para>
727+
</note>
728+
666729
<para>
667730
An entry's <structfield>amopmethod</> must match the
668731
<structname>opfmethod</> of its containing operator family (including

‎doc/src/sgml/ref/alter_opfamily.sgml

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> ADD
25-
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> )
25+
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ]
2626
| FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )
2727
} [, ... ]
2828
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> DROP
@@ -154,6 +154,22 @@ ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="
154154
</listitem>
155155
</varlistentry>
156156

157+
<varlistentry>
158+
<term><replaceable class="parameter">sort_family_name</replaceable></term>
159+
<listitem>
160+
<para>
161+
The name (optionally schema-qualified) of an existing btree operator
162+
family that describes the sort ordering associated with an ordering
163+
operator.
164+
</para>
165+
166+
<para>
167+
If neither <literal>FOR SEARCH</> nor <literal>FOR ORDER BY</> is
168+
specified, <literal>FOR SEARCH</> is the default.
169+
</para>
170+
</listitem>
171+
</varlistentry>
172+
157173
<varlistentry>
158174
<term><replaceable class="parameter">support_number</replaceable></term>
159175
<listitem>

‎doc/src/sgml/ref/create_opclass.sgml

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable>
2525
USING <replaceable class="parameter">index_method</replaceable> [ FAMILY <replaceable class="parameter">family_name</replaceable> ] AS
26-
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ]
26+
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ] [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ]
2727
| FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )
2828
| STORAGE <replaceable class="parameter">storage_type</replaceable>
2929
} [, ... ]
@@ -180,6 +180,22 @@ CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAUL
180180
</listitem>
181181
</varlistentry>
182182

183+
<varlistentry>
184+
<term><replaceable class="parameter">sort_family_name</replaceable></term>
185+
<listitem>
186+
<para>
187+
The name (optionally schema-qualified) of an existing btree operator
188+
family that describes the sort ordering associated with an ordering
189+
operator.
190+
</para>
191+
192+
<para>
193+
If neither <literal>FOR SEARCH</> nor <literal>FOR ORDER BY</> is
194+
specified, <literal>FOR SEARCH</> is the default.
195+
</para>
196+
</listitem>
197+
</varlistentry>
198+
183199
<varlistentry>
184200
<term><replaceable class="parameter">support_number</replaceable></term>
185201
<listitem>

‎doc/src/sgml/xindex.sgml

Lines changed: 62 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -276,10 +276,13 @@
276276
</table>
277277

278278
<para>
279-
Notice that allstrategy operators return Boolean values. In
280-
practice, all operators defined as index methodstrategies must
279+
Notice that allthe operators listed above return Boolean values. In
280+
practice, all operators defined as index methodsearch operators must
281281
return type <type>boolean</type>, since they must appear at the top
282282
level of a <literal>WHERE</> clause to be used with an index.
283+
(Some index access methods also support <firstterm>ordering operators</>,
284+
which typically don't return Boolean values; that feature is discussed
285+
in <xref linkend="xindex-ordering-ops">.)
283286
</para>
284287
</sect2>
285288

@@ -464,7 +467,7 @@
464467
</table>
465468

466469
<para>
467-
Unlikestrategy operators, support functions return whichever data
470+
Unlikesearch operators, support functions return whichever data
468471
type the particular index method expects; for example in the case
469472
of the comparison function for B-trees, a signed integer. The number
470473
and types of the arguments to each support function are likewise
@@ -921,6 +924,62 @@ ALTER OPERATOR FAMILY integer_ops USING btree ADD
921924
</para>
922925
</sect2>
923926

927+
<sect2 id="xindex-ordering-ops">
928+
<title>Ordering Operators</title>
929+
930+
<para>
931+
Some index access methods (currently, only GiST) support the concept of
932+
<firstterm>ordering operators</>. What we have been discussing so far
933+
are <firstterm>search operators</>. A search operator is one for which
934+
the index can be searched to find all rows satisfying
935+
<literal>WHERE</>
936+
<replaceable>indexed_column</>
937+
<replaceable>operator</>
938+
<replaceable>constant</>.
939+
Note that nothing is promised about the order in which the matching rows
940+
will be returned. In contrast, an ordering operator does not restrict the
941+
set of rows that can be returned, but instead determines their order.
942+
An ordering operator is one for which the index can be scanned to return
943+
rows in the order represented by
944+
<literal>ORDER BY</>
945+
<replaceable>indexed_column</>
946+
<replaceable>operator</>
947+
<replaceable>constant</>.
948+
The reason for defining ordering operators that way is that it supports
949+
nearest-neighbor searches, if the operator is one that measures distance.
950+
For example, a query like
951+
<programlisting><![CDATA[
952+
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
953+
]]>
954+
</programlisting>
955+
finds the ten places closest to a given target point. A GiST index
956+
on the location column can do this efficiently because
957+
<literal>&lt;-&gt;</> is an ordering operator.
958+
</para>
959+
960+
<para>
961+
While search operators have to return Boolean results, ordering operators
962+
usually return some other type, such as float or numeric for distances.
963+
This type is normally not the same as the data type being indexed.
964+
To avoid hard-wiring assumptions about the behavior of different data
965+
types, the definition of an ordering operator is required to name
966+
a B-tree operator family that specifies the sort ordering of the result
967+
data type. As was stated in the previous section, B-tree operator families
968+
define <productname>PostgreSQL</productname>'s notion of ordering, so
969+
this is a natural representation. Since the point <literal>&lt;-&gt;</>
970+
operator returns <type>float8</>, it could be specified in an operator
971+
class creation command like this:
972+
<programlisting><![CDATA[
973+
OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops
974+
]]>
975+
</programlisting>
976+
where <literal>float_ops</> is the built-in operator family that includes
977+
operations on <type>float8</>. This declaration states that the index
978+
is able to return rows in order of increasing values of the
979+
<literal>&lt;-&gt;</> operator.
980+
</para>
981+
</sect2>
982+
924983
<sect2 id="xindex-opclass-features">
925984
<title>Special Features of Operator Classes</title>
926985

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp