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

Index of postgresql

The index types provided by postgresql are: B-tree, hash, gist, and gin. In most cases, B-tree indexes are commonly used, and users can use the create index command to create a B-tree index.

1. B-tree index:

B-tree is suitable for dealing with data that can be stored sequentially, such as for some fields that involve using:

When one of the operators makes a comparison, an index can be established.

You can also use B-tree index search to implement the same construction as a combination of these operators, such as BETWEEN and IN. In addition, IS NULL or IS NOT NULL conditions on index columns can be used with B-tree indexes.

For queries involving the pattern matching operator LIKE, the optimizer can also use B-tree indexes if the pattern is constant and anchored to the beginning of the string, such as col LIKE' foo%' or col?' ^ foo', but not col LIKE'%bar'. However, if your database does not use the C language environment, you need to use special operator classes to create indexes to support indexes on pattern matching queries; see section 11.9 below. You can also use B-tree indexes on ILIKE and? *, but only if the pattern starts with non-alphabetic characters (that is, characters that are not affected by case conversion).

2. Hash index:

Hash indexes can only handle simple comparisons. When the columns of an index involve comparing using the = operator, the query planner considers using hash indexes.

Hash index operations do not currently record WAL-log, so if there are no write changes, the Hash index may need to be rebuilt with REINDEX after the database crash. In addition, changes to Hash indexes are not replicated by streaming or file-based replication after the initial basic backup, so they give the wrong answer to queries that subsequently use them. For these reasons, the use of Hash indexes is currently discouraged.

3. Gist index:

An gist index is not a single index type, but a schema on which many different indexing strategies can be implemented. Therefore, specific types of operators that can be used for gist indexes are highly dependent on index policies (operator classes).

An GiST index is not a single index, but an infrastructure that can implement many different index strategies. Therefore, you can use the specific operators of the GiST index to vary according to the indexing strategy (operator class).

4. GIN index

GIN indexes are inverted indexes that can handle values that contain multiple keys (such as arrays). Like gist, gin supports user-defined indexing policies, and the specific types of operators that can be used for GIN indexes vary depending on the indexing policy.

Design principles of the index:

①: the more indexes, the better. If there are a large number of indexes in a table, it not only takes up a lot of disk space, but also affects the performance of statements such as insert, delete, update, and so on, because the index is also adjusted and updated when the data in the table is changed.

②: avoid indexing tables that are updated frequently and have as few columns in the index as possible. Indexes should be created on fields that are often used in queries, but avoid adding unnecessary fields.

③: it is best not to use indexes for tables with a small amount of data. When there is less data, the query may take less time than traversing the index, and the index may not produce an optimization effect.

④: index on columns with more different values that are often used in conditional expressions, but not on columns with fewer values.

⑤: specifies a unique index when uniqueness is a characteristic of the data itself. Using a unique index ensures the data integrity of the defined columns and improves the query speed.

⑥: index on columns that are sorted or grouped frequently (for group by or order by operations). If you have more than one column to sort, you can build a composite index on those columns.

-Common operations: (note: B-tree index is created by default)

Basic syntax:

Create [unique | fulltext | spatial] index index_name on table_name (col_ name [length],....) [ASC | DESC]

1. Create a general index: B-tree index

Create index idx_contacts_name on contacts (name)

-create a unique index:

Create unique index idx_emp on emp (id)

-- create a composite index:

Create index idx_emp on emp (id,name)

2. Array index

Create index idx_contacts_phone on contacts using gin (phone)

Note: phone is an array type in the contacts table

3. Descending index

Create index idx_contacts_name on contacts (name desc)

4. Specify storage parameters

Create index idx_contacts_name on contacts (name) with (fillfactor=50)

Note: fillfactor is a commonly used storage parameter

5. Specify the null value at the front

Create index idx_contacts_name on contacts (name desc nulls first)

6. To avoid the long-term blocking of index creation, you can add the concurrently keyword after the index keyword to reduce the blocking time of the index.

Create index concurrently idx_contacts_name on contacts (name desc)

Note that concurrently is not supported when rebuilding the index, you can create a new index, and then delete the old index, in addition, the concurrent index is forcibly cancelled, which may leave an invalid index, which will cause the update to slow down, and if it is a unique index, it will also cause the insertion of duplicate values to fail.

7. Modify the index

Index rename: alter index name rename to new_name

Set tablespace: alter index name set tablespace tablespace_name

Set storage parameters: alter index name set (storage_parameter=value [,...])

Reset storage parameters: alter index name reset (storeage_parameter [,...])

8. Delete the index

Drop index if exists idx_emp

8. Cascade deletes the index and all objects that depend on the index

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.