MISSION 262

Advanced Indexing

In the previous lesson on indexing, we learned that creating an index will speed up commonly executed queries. An index creates a b-tree structure on a column, separate from the table, which allows filtered queries to perform binary search. Indexes reduce query speeds from O(n) complexity to O(log n).

In this advanced indexing lesson, we will expand on the concept of indexing, and we will dive into Postgres's advanced indexing features to improve the querying speeds of Postgres databases. The features we will investigate are multiple column indexes, different types of indexes, and partial indexes. You can think of these concepts as adding options to an index.

By the end of this lesson, you will be familiar with the additional options we can add to indexes. You will also be familiar with the trade-offs of adding multiple indexes, as well as the cost of disk space to query operations. If, after this lesson, you want to learn more about indexing, you can read about how Postgres handles indexing here.

As you get familiar with the concepts in this advanced indexing lesson, you’ll get to apply what you’ve learned from within your browser so that there's no need to use your own machine to do the exercises. The Python environment inside of this course includes answer checking so you can ensure that you've fully mastered each concept before learning the next concept.

Objectives

  • Learn how to create a multiple column index.
  • Learn the tradeoffs between different types of indexes.
  • Learn when to order your index columns.

Mission Outline

1. Querying with Multiple Filters
2. Bitmap Heap Scan
3. Adding Another Index
4. Multiple Indexes
5. The Tradeoff of Using Indexes
6. Order By Index
7. Index on Expressions
8. Partial Indexes
9. Building a Multi-Column Index
10. Next Steps
11. 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