Module 05 of 10

Indexes & Query Performance

Lessons

About This Module

A query that feels instant on a small table can crawl once the data grows. This module is about understanding why, and how to fix it. An index is a separate structure that helps PostgreSQL find rows without reading the whole table, much like the index at the back of a book.

You'll learn how indexes work, starting with the most common type, the B-tree, and when each kind of index is worth creating. Then you'll learn to read what PostgreSQL is actually doing with EXPLAIN and EXPLAIN ANALYZE, spot slow steps in a query plan, and apply practical query tuning techniques.

Once your queries are fast, you'll be ready for the next module, Transactions & Concurrency, where you learn how PostgreSQL keeps data correct when many people use it at the same time.

Lessons

4 videos
01

PostgreSQL Indexes Explained

A from-scratch introduction to indexes in PostgreSQL: what an index is, why it makes SELECT queries faster, and when you should use one.

02

PostgreSQL Indexes and B-Trees (and an Introduction to EXPLAIN)

An overview of how indexes make queries faster and enforce constraints, what B-trees are as the most common index type, and a first look at EXPLAIN.

03

Reading PostgreSQL EXPLAIN ANALYZE Output

Learn to read an execution plan node by node and look past cost estimates to find the real bottlenecks in a query.

04

Best Practices for Tuning Slow Postgres Queries

A conference talk on lessons learned from tuning slow Postgres queries over the years. Watch it once you are comfortable with indexes and query plans.