MISSION 260

Using an Index

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.

Mission 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

optimizing-postgres-databases-data-engineering

Course Info:

Intermediate

The median completion time for this course is 4.7 hours. ​View Details​​​

This course is free and includes five missions. It is the second course in the Data Engineer path.

START LEARNING FREE

Take a Look Inside