PostgreSQL Indexes

Essentially, indexes help database systems search tables more efficiently. The concept of indexes is widely supported among all the popular RDBMSs, and PostgreSQL is no exception.

By default, PostgreSQL has support for up to three types of indexes: B-Tree, R-Tree, and hash. During index creation, the specific type of index required can be specified. Each index type is best suited for a specific type of indexing.

A general rule of thumb when using indexes is to determine what queries your database is making consistent use of. Essentially, indexes should exist for every WHERE criteria in frequently used queries.

B-Tree Indexes

The B-Tree index is based on an implementation of the high-concurrency Lehman-Yao B-Trees. The B-Tree ...

Get PostgreSQL Essential Reference now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.