PostgreSQL’s Indexes

We use the index to increase the performance of the queries. If the required rows are relatively smaller than the table, it can take advantage of performance for using the index. Besides, using the nature properties of indexes (For example, in the following cases, the size of index is very small compared to the scale of a table, the indexes are sorted according to the key values, we can ensure the Uniqueness, etc.) can more improve the performance.

Thus, to improve performance over the index, it is important to understand what kind of index is supported in the database and what they have any features. The indexes also add overhead to operate modification (insert, update, delete). So, we should avoid the use of a senseless index, and make appropriate indexes that are mainly used.

Let’s take a look at the types and features of the index provided by PostgreSQL.

Currently (Based on 9.5 version), the standard version of PostgreSQL provides six indexes as below, and can choose the index according to the development environment. (Through the USING-clause) Types and features of the index provided basically in the PostgreSQL are as follows.

1. B-TREE

If you do not specify when you create the index, B-Tree is generated by default. B-Tree is effective against the exploration of equality and range queries. It supports all data types (built-in), and can also use to retrieve NULL-values. Because it retains sorting data, it is effective when need to retrieve table rows in order.

2. HASH

Hash index is only possible to use equality queries. Currently it should be rebuilt after a crash occurred since it can’t be transaction-safe, and it can’t be replicated. In addition, the performance isn’t honed in comparison with the B-tree index. Therefore, Hash index is not recommended.

(NOTE: pg_hackers is under discussion in order to improve these problems.)

3. GIN (Generalized Inverted Index)

As an Inverted index, GIN index is a structure that stores a set of pairs (key, value list). Way to find the key is similar to the B-tree index, but leaf node is different. B-tree index has only one value on each key entry, whereas GIN index has value list that pointing the whole tuple has the same key.

Due to this feature, if there is a lot of rows that has the same key, GIN index reduce internal nodes of a tree. Therefore, the tree management and traversing cost can be reduced, and the overall performance of the index is increased. So, GIN index is suitable for indexing array values, documents type data.

4. GiST (Generalized Search Tree)

GiST is a balanced tree-structured index access method that can use different index strategies depending on it which Operation Class is applied. For this feature, it can be seen as an infrastructure rather than a type of the index. It provides various GiST operator class such as geometric data, text search document, etc. You can check out the supported operation class at here .

5. SP-GiST (Space Partitioned GiST)

SP-Gist is similar to GiST, but it supports partitioned search trees, which facilitate the development of a wide range of different non-balanced data structures such as quad-trees, k-d trees, and radix trees. The supported operation class can be found at here .

6. BRIN (Block Range Index)

BRIN index is the index constituting the metadata on each page in the table, and it is used to determine whether a page is needed for searching or not. The metadata is summarized a range and null bitmap of the target column of each data page. BRIN index is not storing the index entry to each row, to save the only metadata of each page and maintaining ‘summarized data’ about block range. Therefore, BRIN index is very small sized and maintenance overhead is inexpensive.

BRIN is useful for very large tables and to be stored sort order collates to certain column values.

In the next PostgreSQL 9.6, the extendibility of Access Method will be improved. It will be added the command ‘CREATE ACCESS METHOD’, it is possible to create a new index via writing add-on code. Currently, it’s very difficult to add an index, but the new version will provide an interface that can make index easily. We’ll find out about that in the following post.