postgresql--Index

Index of PostgreSQL

The types of indexes that PostgreSQL provides are: B-tree, hash, gist, and gin. In most cases, b-tree indexes are more commonly used, and users can create an B-tree index using the "CREATE INDEX" command.

1. B-tree Index:

B-tree is suitable for processing data that can be stored sequentially, such as when a field involves comparisons using:<, <=, =, >=, or one of the > operators, it is possible to create an index.

You can also use B-tree Index search to implement the same constructs as the combinations of these operators, such as between and in. Additionally, the is null or is not NULL condition on the index column can be used with the B-tree index.

For queries involving the pattern-matching operator like, the optimizer can also use the B-tree index, if the pattern is constant and anchored to the beginning of the string, such as Col ' foo% ' or col? ' ^ Foo ', but not col like '%bar '. However, if your database does not use a C locale, you will need to create an index with a special operator class to support indexing of pattern-matching queries, as described in section 11.9 below. You can also use the B-tree index for ilike and? *, but only if the pattern starts with non-alphabetic characters (that is, characters that are not affected by the case conversion).

2. Hash index:

A hash index can only handle simple equals comparisons. When a column of an index involves comparisons using the = operator, the Query planner considers using a hash index.

Hash index operations currently do not log wal-log, so if there are no write changes, the hash index may need to be rebuilt with Reindex after the database crashes. Also, after the initial basic backup, the hash index changes are not replicated through streaming or file-based replication, so they give incorrect answers to queries that subsequently use them. For these reasons, the use of hash indexes is not currently encouraged.

3. Gist Index:

The gist index is not a single index type, but rather a schema that implements many different indexing strategies on this architecture. Therefore, specific operator types that can use the gist index are highly dependent on the index policy (operator class)

The gist index is not a single index, but an infrastructure that can implement many different indexing strategies. Therefore, specific operators that can use the gist index vary depending on the index policy (operator Class).

4. Gin Index

The gin index is an inverted index that can handle values (such as arrays) that contain multiple keys. Similar to gist, gin supports user-defined indexing policies, and specific operator types that can use gin indexes vary depending on the indexing strategy.

Design principles for Indexes:

①: Not more indexes are better. If a table has a large number of indexes, it will not only consume a lot of disk space, but also affect the performance of the INSERT, delete, UPDATE, and other statements, because the indexes are adjusted and updated when the data in the table is changed.

②: Avoid too many indexes on frequently updated tables, and as few columns as possible in the index. You should create an index on fields that are frequently used for queries, but avoid adding unnecessary fields.

③: A table with a small amount of data is best not to use an index. When there is less data, the query may take less time than traversing the index, and the index may not have an optimization effect.

④: Indexes are indexed on columns that are frequently used in conditional expressions, and are not indexed on columns with fewer values.

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

⑥: Indexes on columns that are frequently sorted or grouped (for group by or order by operations). If there are multiple columns to sort, you can create a composite index on those columns.

---Common actions: (note: The 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 normal 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: The 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 common storage parameter

5. Specify null values in front

Create index idx_contacts_name on contacts (name desc nulls first);

6. Avoid long-time blocking of index creation, you can increase the concurrently keyword after the index keyword to reduce the blocking time of index

CREATE index concurrently idx_contacts_name on contacts (name DESC);

Note that concurrently is not supported when rebuilding an index, you can create a new index, then delete the old index, and the concurrent index is forced to cancel, potentially leaving an invalid index, which will cause the update to become slower, and if it is a unique index, it will also fail to insert duplicate values.

7. Modify the Index

Index renaming: 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 Index

Drop index if exists idx_emp;

8. Cascade will remove all indexes and dependent index objects.

postgresql--Index