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 O’Reilly online learning.

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