Understanding additional B-tree features

In PostgreSQL, indexing is a large field and covers many aspects of database work. As I’ve outlined in this book already, indexing is the key to performance. There’s no good performance without proper indexing. Therefore, it’s worth inspecting the indexing-related features that we will cover in the following subsections in detail.

Combined indexes

In my job as a professional PostgreSQL support vendor, I’m often asked about the difference between combined and individual indexes. In this section, I’ll try to shed some light on this question.

The general rule is that if a single index can answer your question, it’s usually the best choice. However, you can’t index all of the possible combinations of fields people are filtering with. What you can do instead is use the properties of combined indexes to achieve as much as possible.

Let’s suppose we have a table containing three columns...