Making Use of Indexes

In Chapter 2, Understanding Transactions and Locking, you learned about concurrency and locking. In this chapter, it is time to attack indexing head on. The importance of this topic cannot be stressed enough—indexing is (and will most likely remain) one of the most important topics in the life of every database engineer.

After 18 years of professional, full-time PostgreSQL consulting and PostgreSQL 24x7 support (www.cybertec-postgresql.com), I can say one thing for sure—bad indexing is the main source of bad performance. Of course, it is important to adjust memory parameters and all that. However, it is all in vain if indexes are not used properly. There is simply no replacement for a missing index. To make my point: there is no way to achieve good performance without proper indexing, so always make it a point to check the indexing if performance is bad.

This is the reason behind dedicating an entire chapter to indexing alone. This will give you as many insights as possible.

In this chapter, we will cover the following topics:

• Understanding simple queries and the cost model
• Improving speed using clustered tables
• Understanding additional B-tree features
• Introducing operator classes
• Understanding PostgreSQL index types
• Achieving better answers with fuzzy searching
• Understanding full-text search

By the end of this chapter, you will understand how indexes can be used beneficially in PostgreSQL.