Database Reference
In-Depth Information
The implicit index
An index that is created automatically by the database is called an implicit index.
The primary key or unique constraint implicitly creates an index on that column.
The implicit index has already been discussed in the
The unique index
section earlier
in this chapter.
The concurrent index
Building an index locks the table from writing or inserting anything in the table.
During the creation process, a process table can be read without an issue, but
write operations block till the end of the index building process. We have already
discussed that index creation on a table is a very expensive operation, and on a
sizeably huge table, it can take hours to build an index. This can cause dificulty in
regards to performing any write operations. To solve this issue, PostgreSQL has the
concurrent index, which is useful when you need to add indexes in a live database.
The syntax of a concurrent index is as follows:
CREATE INDEX CONCURRENTLY index_name ON table_name using
btree(column);
The concurrent index is slower than the normal index because it
completes index building in two parts. This can be explained with the
help of the following example:
Time taken in creating a normal index
idx_id
using
CREATE INDEX
:
warehouse_db=# CREATE INDEX idx_id ON item (item_id);
Time: 8265.473 ms
Time taken in creating a concurrent index
idx_id
using
CREATE
INDEX CONCURRENTLY
:
warehouse_db=# CREATE INDEX CONCURRENTLY idx_id ON
item (item_id);
Time: 51887.942 ms
Index types
PostgreSQL supports the B-tree, hash, GiST, and GIN index methods. The index
method or type can be selected via the
USING
method. Different types of indexes
have different purposes, for example, the B-tree index is effectively used when a
query involves the range and equality operators and the hash index is effectively
used when the equality operator is used in a query.
