4 Indexes
This chapter covers
- Exploring various index types of Postgres
- Understanding query execution plans with the EXPLAIN statement
- Optimizing query performance with single-column and composite indexes
- Reducing the number of table lookups with covering indexes
- Indexing a subset of data with partial indexes
- Indexing the result of a function or expression
Indexes are often the first optimization technique that comes to mind when dealing with a long-running query or a slow database operation. They’ve proven so effective in many scenarios that we sometimes overlook other optimization methods, turning to indexes right away. As a result, indexes may be the second most common database object we create and use, right after tables.
In this chapter, you learn why indexes are such a popular optimization tool and how to leverage them in Postgres. Using a multiplayer online game as an example, you’ll explore various index types and see how and when to apply them effectively in practice.
4.1 Why are indexes so popular?
Suppose we have a table with a hundred records, and we ask Postgres to return a record with an ID equal to 5. The algorithmic complexity of this search operation, expressed in Big O notation, is O(N), where N is the number of records in the table. O(N) describes linear search complexity, meaning that the more records we have, the longer it will take for Postgres to find the requested record or confirm that it doesn’t exist.