Indexes: a first look
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
know what an index is and how to check it is used.
Planned topics
- An index is a sorted lookup structure (usually a B-tree) that makes searches fast, like a book's index.
- It costs disk space and slows writes, so do not index every column.
- PostgreSQL creates indexes for primary keys and unique constraints automatically, but not for foreign keys: index them yourself.
- Index columns used in
where,join, andorder byfor common queries. EXPLAINshows the plan:Seq Scanreads the whole table,Index Scanuses an index;EXPLAIN ANALYZEalso runs the query and times it.
Draft outline details
Demonstration
create index ix_order_line_order_id on order_line(order_id);
explain select * from order_line where order_id = 42;
-- expect: Index Scan using ix_order_line_order_id (on large tables)Check your understanding
Does PostgreSQL index foreign key columns automatically?
Show answer
No.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.