Shulou(Shulou.com)06/01 Report--

Pg_am has been briefly mentioned in the previous chapter. Taking hash as an example, we introduce that hash index cannot support related operations such as greater than or equal to, less than or equal to. We know that we need information to confirm which data types and operators are accepted by the access method.

[pg12@localhost ~] $psql-d testdbpsql (12beta1) Type "help" for help.testdb=# select * from pg_am Oid | amname | amhandler | amtype-+-+-2 | heap | heap_tableam_handler | t 403 | btree | bthandler | I 405 | hash | hashhandler | I 783 | | gist | gisthandler | I 2742 | gin | ginhandler | I 4000 | spgist | spghandler | I 3580 | brin | brinhandler | I 24597 | blackhole_am | blackhole_am_handler | t (8 rows) |

On this basis, this section introduces Operator classes and families.

Basic knowledge

PostgreSQL provides the concepts of operator class and operator family to describe which data types and operators are accepted by access methods. Operator class contains the smallest set of operators for a specific data type of an index operation, and operator class is classified as operator family, that is, or a family may contain multiple operator class.

Testdb=# select * from pg_opfamily Oid | opfmethod | opfname | opfnamespace | opfowner-+-+-- 397 | 403 | array_ops | 11 | 10,627 | 405 | array _ ops | 11 | 10,423 | 403 | bit_ops | 11 | 10,424 | 403 | bool_ops | 11 | 10426 | 403 | bpchar_ops | 11 | 10,427 | 405 | bpchar_ops | 11 | 10,428 | 403 | bytea_ops | 11 | 10. (107 rows)

Above are all the opfamily that exists in the system, including the array array_ops/ bit _ ops/ string, bpchar_ops/, integer integer_ops and other data types such as op family. For example, integer_ops family includes int8_ops, int4_ops, and and int2_ops classes, corresponding to bigint, integer, and smallint, respectively.

The opfmethod field in pg_opfamily is associated with pg_am.oid, such as querying opfamily and opclass of hash am:

Testdb=# select am.amname,opfname, opcname, opcintype::regtypetestdb-# from pg_opclass opc, pg_opfamily opf, pg_am amtestdb-# where opc.opcfamily = opf.oidtestdb-# and opf.opfmethod = am.oidtestdb-# and am.amname = 'hash'testdb-# order by opf.opfname Amname | opfname | opcname | opcintype-+-hash | aclitem_ops | aclitem_ Ops | aclitem hash | array_ops | array_ops | anyarray hash | bool_ops | bool_ops | boolean hash | bpchar_ops | bpchar_ops | character hash | bpchar_pattern_ops | bpchar_pattern_ops | character hash | bytea_ops | bytea_ops | bytea hash | char_ops | char _ ops | "char" hash | cid_ops | cid_ops | cid hash | date_ops | date_ops | date hash | enum_ops | enum_ops | anyenum hash | float_ops | float8_ops | double precision hash | float_ops | float4_ops | real hash | | integer_ops | int2_ops | smallint hash | integer_ops | int8_ops | bigint hash | integer_ops | int4_ops | integer hash | interval_ops | interval_ops | interval hash | jsonb_ops | jsonb_ops | jsonb hash | macaddr8_ops | macaddr8_ops | macaddr8 hash | macaddr | _ ops | macaddr_ops | macaddr hash | network_ops | cidr_ops | inet hash | network_ops | inet_ops | inet hash | numeric_ops | numeric_ops | numeric hash | oid_ops | oid_ops | oid hash | oidvector_ops | oidvector_ops | oidvector hash | pg_lsn _ ops | pg_lsn_ops | pg_lsn hash | range_ops | range_ops | anyrange hash | text_ops | text_ops | text hash | text_ops | name_ops | name hash | text_ops | varchar_ops | text hash | text_pattern_ops | text_pattern_ Ops | text hash | text_pattern_ops | varchar_pattern_ops | text hash | tid_ops | tid_ops | tid hash | time_ops | time_ops | time without time zone hash | timestamp_ops | timestamp_ops | timestamp without time zone hash | timestamptz_ops | timestamptz_ops | timestamp with time zone hash | timetz_ops | timetz_ Ops | time with time zone hash | uuid_ops | uuid_ops | uuid hash | xid_ops | xid_ops | xid (38 rows)

As you can see, for integer_ops opfamily, several types of op clas such as int2_ops/int4_ops/int8_ops can be supported, and the type is smallint/integer/bigint.

Op family can include additional operators to compare different types of values, classified as the same family because when using index, predicates can adapt to different data types (such as smallint/integer/bigint, etc.). In most cases, you don't need to know op family and op class, just create an index and use it. However, you can explicitly specify op lcass.

System catalog

The following is the relevant system directory diagram for op family and op class:

Through the above picture, we can find out the relevant information.

Find out the data types that can be processed by AM

Testdb=# select am.amname,opcname, opcintype::regtypetestdb-# from pg_opclass opc, pg_am am testdb-# where opc.opcmethod = am.oidtestdb-# and am.amname = 'hash'testdb-# order by opcintype::regtype::text Amname | opcname | opcintype-+-- hash | aclitem_ops | aclitem hash | array_ops | anyarray hash | enum_ops | anyenum hash | | range_ops | anyrange hash | int8_ops | bigint hash | bool_ops | boolean hash | bytea_ops | bytea hash | char_ops | "char" hash | bpchar_pattern_ops | character hash | bpchar_ops | character hash | cid_ops | cid hash | date_ops | date hash | float8_ops | | | double precision hash | cidr_ops | inet hash | inet_ops | inet hash | int4_ops | integer hash | interval_ops | interval hash | jsonb_ops | jsonb hash | macaddr_ops | macaddr hash | macaddr8_ops | macaddr8 hash | name_ops | name hash | numeric_ops | numeric hash | | | oid_ops | oid hash | oidvector_ops | oidvector hash | pg_lsn_ops | pg_lsn hash | float4_ops | real hash | int2_ops | smallint hash | text_ops | text hash | varchar_ops | text hash | text_pattern_ops | text hash | varchar_pattern_ops | text hash | tid_ops | | | tid hash | timestamp_ops | timestamp without time zone hash | timestamptz_ops | timestamp with time zone hash | time_ops | time without time zone hash | timetz_ops | time with time zone hash | uuid_ops | uuid hash | xid_ops | xid (38 rows) |

Which op is in op class? (index accesses predicates that can be used for this operator)

Testdb=# select amop.amopopr::regoperatortestdb-# from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amoptestdb-# where opc.opcname = 'int8_ops'testdb-# and opf.oid = opc.opcfamilytestdb-# and am.oid = opf.opfmethodtestdb-# and amop.amopfamily = opc.opcfamilytestdb-# and am.amname =' hash'testdb-# and amop.amoplefttype = opc.opcintype Amopopr-= (bigint,bigint) = (bigint,smallint) = (bigint,integer) (3 rows)

references

Indexes in PostgreSQL-2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.