From f23a5630ebc797219b62797f566dec9f65090e03 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 8 Apr 2014 15:46:14 -0400 Subject: Add an in-core GiST index opclass for inet/cidr types. This operator class can accelerate subnet/supernet tests as well as btree-equivalent ordered comparisons. It also handles a new network operator inet && inet (overlaps, a/k/a "is supernet or subnet of"), which is expected to be useful in exclusion constraints. Ideally this opclass would be the default for GiST with inet/cidr data, but we can't mark it that way until we figure out how to do a more or less graceful transition from the current situation, in which the really-completely-bogus inet/cidr opclasses in contrib/btree_gist are marked as default. Having the opclass in core and not default is better than not having it at all, though. While at it, add new documentation sections to allow us to officially document GiST/GIN/SP-GiST opclasses, something there was never a clear place to do before. I filled these in with some simple tables listing the existing opclasses and the operators they support, but there's certainly scope to put more information there. Emre Hasegeli, reviewed by Andreas Karlsson, further hacking by me --- doc/src/sgml/func.sgml | 14 +- doc/src/sgml/gin.sgml | 359 ++++++++++++++++++++++++++++++++++++++++++++++ doc/src/sgml/gist.sgml | 178 +++++++++++++++++++++++ doc/src/sgml/indices.sgml | 28 +++- doc/src/sgml/spgist.sgml | 87 +++++++++++ 5 files changed, 661 insertions(+), 5 deletions(-) (limited to 'doc/src') 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 shows the operators available for the cidr and inet types. The operators <<, - <<=, >>, and - >>= test for subnet inclusion. They + <<=, >>, + >>=, and && + 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 << - is contained within + is contained by inet '192.168.1.5' << inet '192.168.1/24' <<= - is contained within or equals + is contained by or equals inet '192.168.1/24' <<= inet '192.168.1/24' @@ -8502,6 +8503,11 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple contains or equals inet '192.168.1/24' >>= inet '192.168.1/24' + + && + contains or is contained by + inet '192.168.1/24' && inet '192.168.1.80/28' + ~ bitwise NOT 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 @@ + + Built-in Operator Classes + + + The core PostgreSQL distribution + includes the GIN operator classes shown in + . + (Some of the optional modules described in + provide additional GIN operator classes.) + + + + Built-in <acronym>GIN</acronym> Operator Classes + + + + Name + Indexed Data Type + Indexable Operators + + + + + _abstime_ops + abstime[] + + && + <@ + = + @> + + + + _bit_ops + bit[] + + && + <@ + = + @> + + + + _bool_ops + boolean[] + + && + <@ + = + @> + + + + _bpchar_ops + character[] + + && + <@ + = + @> + + + + _bytea_ops + bytea[] + + && + <@ + = + @> + + + + _char_ops + "char"[] + + && + <@ + = + @> + + + + _cidr_ops + cidr[] + + && + <@ + = + @> + + + + _date_ops + date[] + + && + <@ + = + @> + + + + _float4_ops + float4[] + + && + <@ + = + @> + + + + _float8_ops + float8[] + + && + <@ + = + @> + + + + _inet_ops + inet[] + + && + <@ + = + @> + + + + _int2_ops + smallint[] + + && + <@ + = + @> + + + + _int4_ops + integer[] + + && + <@ + = + @> + + + + _int8_ops + bigint[] + + && + <@ + = + @> + + + + _interval_ops + interval[] + + && + <@ + = + @> + + + + _macaddr_ops + macaddr[] + + && + <@ + = + @> + + + + _money_ops + money[] + + && + <@ + = + @> + + + + _name_ops + name[] + + && + <@ + = + @> + + + + _numeric_ops + numeric[] + + && + <@ + = + @> + + + + _oid_ops + oid[] + + && + <@ + = + @> + + + + _oidvector_ops + oidvector[] + + && + <@ + = + @> + + + + _reltime_ops + reltime[] + + && + <@ + = + @> + + + + _text_ops + text[] + + && + <@ + = + @> + + + + _time_ops + time[] + + && + <@ + = + @> + + + + _timestamp_ops + timestamp[] + + && + <@ + = + @> + + + + _timestamptz_ops + timestamp with time zone[] + + && + <@ + = + @> + + + + _timetz_ops + time with time zone[] + + && + <@ + = + @> + + + + _tinterval_ops + tinterval[] + + && + <@ + = + @> + + + + _varbit_ops + bit varying[] + + && + <@ + = + @> + + + + _varchar_ops + character varying[] + + && + <@ + = + @> + + + + jsonb_ops + jsonb + + ? + ?& + ?| + @> + + + + jsonb_hash_ops + jsonb + + @> + + + + tsvector_ops + tsvector + + @@ + @@@ + + + + +
+ + + Of the two operator classes for type jsonb, jsonb_ops + is the default. jsonb_hash_ops supports fewer operators but + will work with larger indexed values than jsonb_ops can support. + + + + Extensibility 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 @@ + + Built-in Operator Classes + + + The core PostgreSQL distribution + includes the GiST operator classes shown in + . + (Some of the optional modules described in + provide additional GiST operator classes.) + + + + Built-in <acronym>GiST</acronym> Operator Classes + + + + Name + Indexed Data Type + Indexable Operators + Ordering Operators + + + + + box_ops + box + + && + &> + &< + &<| + >> + << + <<| + <@ + @> + @ + |&> + |>> + ~ + ~= + + + + + + circle_ops + circle + + && + &> + &< + &<| + >> + << + <<| + <@ + @> + @ + |&> + |>> + ~ + ~= + + + + + + inet_ops + inet, cidr + + && + >> + >>= + > + >= + <> + << + <<= + < + <= + = + + + + + + point_ops + point + + >> + >^ + << + <@ + <@ + <@ + <^ + ~= + + + <-> + + + + poly_ops + polygon + + && + &> + &< + &<| + >> + << + <<| + <@ + @> + @ + |&> + |>> + ~ + ~= + + + + + + range_ops + any range type + + && + &> + &< + >> + << + <@ + -|- + = + @> + @> + + + + + + tsquery_ops + tsquery + + <@ + @> + + + + + + tsvector_ops + tsvector + + @@ + + + + + + +
+ + + For historical reasons, the inet_ops operator class is + not the default class for types inet and cidr. + To use it, mention the class name in CREATE INDEX, + for example + +CREATE INDEX ON my_table USING gist (my_inet_column inet_ops); + + + + + Extensibility 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 name ON table (See for the meaning of these operators.) + The GiST operator classes included in the standard distribution are + documented in . Many other GiST operator classes are available in the contrib collection or as separate projects. For more information see . @@ -253,6 +255,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; 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 , operators that can be + used in this way are listed in the column Ordering Operators. @@ -283,6 +287,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; (See for the meaning of these operators.) + The SP-GiST operator classes included in the standard distribution are + documented in . For more information see . @@ -314,6 +320,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; (See for the meaning of these operators.) + The GIN operator classes included in the standard distribution are + documented in . Many other GIN operator classes are available in the contrib collection or as separate projects. For more information see . @@ -1003,7 +1011,9 @@ CREATE INDEX test_index ON test_table (col varchar_pattern_ops); 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; @@ -1020,6 +1030,22 @@ SELECT am.amname AS index_method, associated with any single class within the family. + + This expanded version of the previous query shows the operator family + each operator class belongs to: + +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; + + + This query shows all defined operator families and all the operators included in each family: 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 @@ + + Built-in Operator Classes + + + The core PostgreSQL distribution + includes the SP-GiST operator classes shown in + . + + + + Built-in <acronym>SP-GiST</acronym> Operator Classes + + + + Name + Indexed Data Type + Indexable Operators + + + + + kd_point_ops + point + + << + <@ + <^ + >> + >^ + ~= + + + + quad_point_ops + point + + << + <@ + <^ + >> + >^ + ~= + + + + range_ops + any range type + + && + &< + &> + -|- + << + <@ + = + >> + @> + + + + text_ops + text + + < + <= + = + > + >= + ~<=~ + ~<~ + ~>=~ + ~>~ + + + + +
+ + + Of the two operator classes for type point, + quad_point_ops is the default. kd_point_ops + supports the same operators but uses a different index data structure which + may offer better performance in some applications. + + + + Extensibility -- cgit v1.2.3