In the previous lesson, we introduced the `EXPLAIN` command with its option `ANALYZE`, and demonstrated how to benchmark queries against Postgres databases. Using `EXPLAIN`, we evaluated estimated and actual query plan results, and compared the difference between them. Finally, we ended by benchmarking a `SELECT` query with the `JOIN` clause between two tables to estimate the query speed costs incurred by joins.

In this lesson of our Postgres Database Optimization course, we will follow up on the `JOIN` query, and work through strategies to make it more efficient. To begin, we will learn about different query scans a `SELECT` performs. 

Next, we will introduce the concept of an index, and how Postgres indexes are used to speed up common queries. We've explored indexes before in our Advanced SQL and Databases course but we'll go more in depth in explaining indexes in this lesson. You can also supplement your knowledge of indexes by viewing the anatomy of an index.

As you work through this lesson on Postgres indexes, you’ll get to apply what you’ve learned by writing code inside your browser window in our interactive coding environment, and your work will be checked automatically so that you can easily see whether you’ve gotten something right before you move on to the next screen of the lesson.

Objectives

  • Learn how an index speeds up common queries.
  • Learn how to build an index table.
  • Learn how to benchmark query speeds.

Lesson Outline

1. Alternate Table Scans
2. Index Scan
3. Time Complexity
4. Indexing
5. Comparing the Queries
6. Create an Index
7. Dropping Indexes
8. Index Performance on Joins
9. Next Steps
10. Takeaways