diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 14 | ||||
| -rw-r--r-- | doc/src/sgml/gin.sgml | 359 | ||||
| -rw-r--r-- | doc/src/sgml/gist.sgml | 178 | ||||
| -rw-r--r-- | doc/src/sgml/indices.sgml | 28 | ||||
| -rw-r--r-- | doc/src/sgml/spgist.sgml | 87 |
5 files changed, 661 insertions, 5 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 73764f2bedc..0809a6d2e92 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -8434,8 +8434,9 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple <xref linkend="cidr-inet-operators-table"> shows the operators available for the <type>cidr</type> and <type>inet</type> types. The operators <literal><<</literal>, - <literal><<=</literal>, <literal>>></literal>, and - <literal>>>=</literal> test for subnet inclusion. They + <literal><<=</literal>, <literal>>></literal>, + <literal>>>=</literal>, and <literal>&&</literal> + test for subnet inclusion. They consider only the network parts of the two addresses (ignoring any host part) and determine whether one network is identical to or a subnet of the other. @@ -8484,12 +8485,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple </row> <row> <entry> <literal><<</literal> </entry> - <entry>is contained within</entry> + <entry>is contained by</entry> <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry> </row> <row> <entry> <literal><<=</literal> </entry> - <entry>is contained within or equals</entry> + <entry>is contained by or equals</entry> <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry> </row> <row> @@ -8503,6 +8504,11 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry> </row> <row> + <entry> <literal>&&</literal> </entry> + <entry>contains or is contained by</entry> + <entry><literal>inet '192.168.1/24' && inet '192.168.1.80/28'</literal></entry> + </row> + <row> <entry> <literal>~</literal> </entry> <entry>bitwise NOT</entry> <entry><literal>~ inet '192.168.1.6'</literal></entry> diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml index 561608f8fa8..576ad3005aa 100644 --- a/doc/src/sgml/gin.sgml +++ b/doc/src/sgml/gin.sgml @@ -62,6 +62,365 @@ </para> </sect1> +<sect1 id="gin-builtin-opclasses"> + <title>Built-in Operator Classes</title> + + <para> + The core <productname>PostgreSQL</> distribution + includes the <acronym>GIN</acronym> operator classes shown in + <xref linkend="gin-builtin-opclasses-table">. + (Some of the optional modules described in <xref linkend="contrib"> + provide additional <acronym>GIN</acronym> operator classes.) + </para> + + <table id="gin-builtin-opclasses-table"> + <title>Built-in <acronym>GIN</acronym> Operator Classes</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Indexed Data Type</entry> + <entry>Indexable Operators</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>_abstime_ops</></entry> + <entry><type>abstime[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_bit_ops</></entry> + <entry><type>bit[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_bool_ops</></entry> + <entry><type>boolean[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_bpchar_ops</></entry> + <entry><type>character[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_bytea_ops</></entry> + <entry><type>bytea[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_char_ops</></entry> + <entry><type>"char"[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_cidr_ops</></entry> + <entry><type>cidr[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_date_ops</></entry> + <entry><type>date[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_float4_ops</></entry> + <entry><type>float4[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_float8_ops</></entry> + <entry><type>float8[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_inet_ops</></entry> + <entry><type>inet[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_int2_ops</></entry> + <entry><type>smallint[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_int4_ops</></entry> + <entry><type>integer[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_int8_ops</></entry> + <entry><type>bigint[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_interval_ops</></entry> + <entry><type>interval[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_macaddr_ops</></entry> + <entry><type>macaddr[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_money_ops</></entry> + <entry><type>money[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_name_ops</></entry> + <entry><type>name[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_numeric_ops</></entry> + <entry><type>numeric[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_oid_ops</></entry> + <entry><type>oid[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_oidvector_ops</></entry> + <entry><type>oidvector[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_reltime_ops</></entry> + <entry><type>reltime[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_text_ops</></entry> + <entry><type>text[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_time_ops</></entry> + <entry><type>time[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_timestamp_ops</></entry> + <entry><type>timestamp[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_timestamptz_ops</></entry> + <entry><type>timestamp with time zone[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_timetz_ops</></entry> + <entry><type>time with time zone[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_tinterval_ops</></entry> + <entry><type>tinterval[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_varbit_ops</></entry> + <entry><type>bit varying[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>_varchar_ops</></entry> + <entry><type>character varying[]</></entry> + <entry> + <literal>&&</> + <literal><@</> + <literal>=</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>jsonb_ops</></entry> + <entry><type>jsonb</></entry> + <entry> + <literal>?</> + <literal>?&</> + <literal>?|</> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>jsonb_hash_ops</></entry> + <entry><type>jsonb</></entry> + <entry> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>tsvector_ops</></entry> + <entry><type>tsvector</></entry> + <entry> + <literal>@@</> + <literal>@@@</> + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</> + is the default. <literal>jsonb_hash_ops</> supports fewer operators but + will work with larger indexed values than <literal>jsonb_ops</> can support. + </para> + +</sect1> + <sect1 id="gin-extensibility"> <title>Extensibility</title> diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index ed0bc54f52d..0158b1759e8 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -40,6 +40,184 @@ </sect1> +<sect1 id="gist-builtin-opclasses"> + <title>Built-in Operator Classes</title> + + <para> + The core <productname>PostgreSQL</> distribution + includes the <acronym>GiST</acronym> operator classes shown in + <xref linkend="gist-builtin-opclasses-table">. + (Some of the optional modules described in <xref linkend="contrib"> + provide additional <acronym>GiST</acronym> operator classes.) + </para> + + <table id="gist-builtin-opclasses-table"> + <title>Built-in <acronym>GiST</acronym> Operator Classes</title> + <tgroup cols="4"> + <thead> + <row> + <entry>Name</entry> + <entry>Indexed Data Type</entry> + <entry>Indexable Operators</entry> + <entry>Ordering Operators</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>box_ops</></entry> + <entry><type>box</></entry> + <entry> + <literal>&&</> + <literal>&></> + <literal>&<</> + <literal>&<|</> + <literal>>></> + <literal><<</> + <literal><<|</> + <literal><@</> + <literal>@></> + <literal>@</> + <literal>|&></> + <literal>|>></> + <literal>~</> + <literal>~=</> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>circle_ops</></entry> + <entry><type>circle</></entry> + <entry> + <literal>&&</> + <literal>&></> + <literal>&<</> + <literal>&<|</> + <literal>>></> + <literal><<</> + <literal><<|</> + <literal><@</> + <literal>@></> + <literal>@</> + <literal>|&></> + <literal>|>></> + <literal>~</> + <literal>~=</> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>inet_ops</></entry> + <entry><type>inet</>, <type>cidr</></entry> + <entry> + <literal>&&</> + <literal>>></> + <literal>>>=</> + <literal>></> + <literal>>=</> + <literal><></> + <literal><<</> + <literal><<=</> + <literal><</> + <literal><=</> + <literal>=</> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>point_ops</></entry> + <entry><type>point</></entry> + <entry> + <literal>>></> + <literal>>^</> + <literal><<</> + <literal><@</> + <literal><@</> + <literal><@</> + <literal><^</> + <literal>~=</> + </entry> + <entry> + <literal><-></> + </entry> + </row> + <row> + <entry><literal>poly_ops</></entry> + <entry><type>polygon</></entry> + <entry> + <literal>&&</> + <literal>&></> + <literal>&<</> + <literal>&<|</> + <literal>>></> + <literal><<</> + <literal><<|</> + <literal><@</> + <literal>@></> + <literal>@</> + <literal>|&></> + <literal>|>></> + <literal>~</> + <literal>~=</> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>range_ops</></entry> + <entry>any range type</entry> + <entry> + <literal>&&</> + <literal>&></> + <literal>&<</> + <literal>>></> + <literal><<</> + <literal><@</> + <literal>-|-</> + <literal>=</> + <literal>@></> + <literal>@></> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>tsquery_ops</></entry> + <entry><type>tsquery</></entry> + <entry> + <literal><@</> + <literal>@></> + </entry> + <entry> + </entry> + </row> + <row> + <entry><literal>tsvector_ops</></entry> + <entry><type>tsvector</></entry> + <entry> + <literal>@@</> + </entry> + <entry> + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + For historical reasons, the <literal>inet_ops</> operator class is + not the default class for types <type>inet</> and <type>cidr</>. + To use it, mention the class name in <command>CREATE INDEX</>, + for example +<programlisting> +CREATE INDEX ON my_table USING gist (my_inet_column inet_ops); +</programlisting> + </para> + +</sect1> + <sect1 id="gist-extensibility"> <title>Extensibility</title> diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index b1c8f227185..64530a11c86 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -239,6 +239,8 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (See <xref linkend="functions-geometry"> for the meaning of these operators.) + The GiST operator classes included in the standard distribution are + documented in <xref linkend="gist-builtin-opclasses-table">. Many other GiST operator classes are available in the <literal>contrib</> collection or as separate projects. For more information see <xref linkend="GiST">. @@ -253,6 +255,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; </programlisting> which finds the ten places closest to a given target point. The ability to do this is again dependent on the particular operator class being used. + In <xref linkend="gist-builtin-opclasses-table">, operators that can be + used in this way are listed in the column <quote>Ordering Operators</>. </para> <para> @@ -283,6 +287,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; (See <xref linkend="functions-geometry"> for the meaning of these operators.) + The SP-GiST operator classes included in the standard distribution are + documented in <xref linkend="spgist-builtin-opclasses-table">. For more information see <xref linkend="SPGiST">. </para> @@ -314,6 +320,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; (See <xref linkend="functions-array"> for the meaning of these operators.) + The GIN operator classes included in the standard distribution are + documented in <xref linkend="gin-builtin-opclasses-table">. Many other GIN operator classes are available in the <literal>contrib</> collection or as separate projects. For more information see <xref linkend="GIN">. @@ -1003,7 +1011,9 @@ CREATE INDEX test_index ON test_table (col varchar_pattern_ops); <programlisting> SELECT am.amname AS index_method, - opc.opcname AS opclass_name + opc.opcname AS opclass_name, + opc.opcintype::regtype AS indexed_type, + opc.opcdefault AS is_default FROM pg_am am, pg_opclass opc WHERE opc.opcmethod = am.oid ORDER BY index_method, opclass_name; @@ -1021,6 +1031,22 @@ SELECT am.amname AS index_method, </para> <para> + This expanded version of the previous query shows the operator family + each operator class belongs to: +<programlisting> +SELECT am.amname AS index_method, + opc.opcname AS opclass_name, + opf.opfname AS opfamily_name, + opc.opcintype::regtype AS indexed_type, + opc.opcdefault AS is_default + FROM pg_am am, pg_opclass opc, pg_opfamily opf + WHERE opc.opcmethod = am.oid AND + opc.opcfamily = opf.oid + ORDER BY index_method, opclass_name; +</programlisting> + </para> + + <para> This query shows all defined operator families and all the operators included in each family: <programlisting> diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml index a043ffb06c4..56827e520dd 100644 --- a/doc/src/sgml/spgist.sgml +++ b/doc/src/sgml/spgist.sgml @@ -53,6 +53,93 @@ </sect1> +<sect1 id="spgist-builtin-opclasses"> + <title>Built-in Operator Classes</title> + + <para> + The core <productname>PostgreSQL</> distribution + includes the <acronym>SP-GiST</acronym> operator classes shown in + <xref linkend="spgist-builtin-opclasses-table">. + </para> + + <table id="spgist-builtin-opclasses-table"> + <title>Built-in <acronym>SP-GiST</acronym> Operator Classes</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Indexed Data Type</entry> + <entry>Indexable Operators</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>kd_point_ops</></entry> + <entry><type>point</></entry> + <entry> + <literal><<</> + <literal><@</> + <literal><^</> + <literal>>></> + <literal>>^</> + <literal>~=</> + </entry> + </row> + <row> + <entry><literal>quad_point_ops</></entry> + <entry><type>point</></entry> + <entry> + <literal><<</> + <literal><@</> + <literal><^</> + <literal>>></> + <literal>>^</> + <literal>~=</> + </entry> + </row> + <row> + <entry><literal>range_ops</></entry> + <entry>any range type</entry> + <entry> + <literal>&&</> + <literal>&<</> + <literal>&></> + <literal>-|-</> + <literal><<</> + <literal><@</> + <literal>=</> + <literal>>></> + <literal>@></> + </entry> + </row> + <row> + <entry><literal>text_ops</></entry> + <entry><type>text</></entry> + <entry> + <literal><</> + <literal><=</> + <literal>=</> + <literal>></> + <literal>>=</> + <literal>~<=~</> + <literal>~<~</> + <literal>~>=~</> + <literal>~>~</> + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Of the two operator classes for type <type>point</>, + <literal>quad_point_ops</> is the default. <literal>kd_point_ops</> + supports the same operators but uses a different index data structure which + may offer better performance in some applications. + </para> + +</sect1> + <sect1 id="spgist-extensibility"> <title>Extensibility</title> |