PostgreSQL Index Description

Indexed by: Unique index, primary key index, multi-attribute index, partial index, expression index.

Index type: b-tree,hash,gist,gin and expression index

PostgreSQL all indexes are "subordinate indexes", that is, the index is physically detached from the table file it describes.

The index is also an object and is recorded in the table Pg_class.

The internal structure of the index is related to the Access Method (index type) of the index. All Access methods use the page to organize the internal structure of the index, which allows access to the index using the interface provided by the storage Manager.

Index mode

Multi-Attribute Index: If an index defines one more attribute, it is called a multi-attribute index, which is used to combine queries.

The B-tree,gist,gin in PostgreSQL support multiple attribute indexes. Supports up to 32 properties.

Not only can you use the properties in a table, but you can also use a function or an expression to evaluate the value as an attribute index.

Partial index: An index of a subset established on a table that has an expression definition (the expression is a partial index predicate).

CREATE INDEX Stu_name_inx on STUDENT (NAME) WHERE (ID > Ten and ID <20);

Using partial indexes can reduce the index size and improve the efficiency of index query.

Expression index: You can build on a function or a scalar expression that is computed from one or more properties in a table.

CREATE INDEX stu_low_name_idx on STUDENT (LOWER (NAME));

An expression index that is useful only if the query is using the same expression as when it was created.

The expressions for the partial index predicate and the expression index are stored in the Pg_index table Indexprs property.

When an index is created, the range of actual index values is computed based on the expression, which causes the insertion to be slower than the new speed.

Index type

B-tree: A data structure similar to the B + number that stores the key values of the database.

Support comparison query and scope query (>,=,<).

The Hash:hash index hashes the index keyword using the hash function, and the hash only handles the = operation.

GiST: A universal search tree, which needs to be learned alone.

GIN: Inverted index, which can handle values (such as arrays) that contain multiple keys. Similar to gist.

Index related system tables

Pg_am:

postgres=# Select COUNT (*) from pg_am; Count-------     5 (1 row)

respectively: Btree,hash,gist,gin,spgist.

If you are doing database development two times, you can focus on the following 13 fields, is to provide 13 modules interface functions.

Pg_index:

Create an index, add a record pg_class create one, and add one more pg_index.

The Pg_index is used to record information about the index.

Several important fields are described:

Indexrelid The OID of the index in Pg_class.

Indrelid represents the OID of the base table on which the index depends.

Indisvalid is true, then it can be used for querying, otherwise it means that the index is imperfect and needs to be updated during insert/update operation.

Indkey This is an array that records the index on the base table, and if there is a value of 0 in the array, then the indexed property is an expression on the table property.

Indexprs An expression of an index

Pg_opclass:

Each index type does not directly set the data type to be manipulated by the index of that type. The table indicates that the indexed index method operates on specific data

Type, the set of operations that need to be used.

There are also several system views, which are descriptions of the type of index operations

Pg_opfamily:

PG_AMOP:

Pg_amproc:

　

　　

PostgreSQL Index Description