Covering Indexes and Index-Only Scans
Discover how covering indexes can eliminate table access and enable highly efficient index-only scans.
What are Covering Indexes?
Welcome to our lesson on Covering Indexes and Index-Only Scans! These advanced indexing techniques can dramatically boost your PostgreSQL query performance.
A regular index helps PostgreSQL quickly find rows based on certain column values. Think of it like a book's index: it tells you which page to go to for a topic.
The Cost of Table Access
When you use a standard index, PostgreSQL first finds the row's location (its tuple ID or CTID) in the index. Then, it has to go to the actual table to fetch the rest of the data for that row.
This extra step, called a table lookup or heap fetch, can be slow, especially if many rows are involved or if the table data isn't in memory.
All lessons in this course
- Hash, GIN, and GiST Indexes
- Partial and Expression Indexes
- Covering Indexes and Index-Only Scans
- BRIN Indexes for Large Sequential Data