An index is simply an organized list of values that appear in one or more columns in a table. The idea is that if you only want a subset of the rows in that table, a query can use the index to determine which rows match, rather than examining every row. Because an index has an order to it, it can also be used to speed up situations where a section of a table has to be sorted in order to return its values.
Indexes help the database cut down on the amount of data it needs to look at in order to execute a query. It's hard to write about indexes without knowing how queries are executed, and it's hard to discuss query execution without knowing what indexes do. This chapter tries to break that explanation deadlock by using simple index examples, where the associated query plans should make some sense even without the query tuning background covered in the next one.
Indexes should not be relied upon to force ordering of a query. If you want a query to be ordered a particular way, use ORDER BY to request it. The existence of an index with that order is more likely to be selected to execute that query, as it will skip a sorting step to do so. But just because a query normally uses an index that happens to have an order in it to return results, that does not mean you'll always receive them ordered that way. Query plans will change over time, to best match the underlying data as it too changes.
The main new thing to know to get started here is that if you put EXPLAIN ANALYZE in front of a SELECT statement, the database will show you how much work it expected that query to take, as well as how much it actually did. Reading EXPLAIN output is covered in more detail in the next chapter. You might discover you get more out of this chapter if you return to it again after reading the next.
Topics we will cover in this chapter are:
- Index creation and maintenance
- Combining indexes
- Discussing different types of indexes and their use