Browse Teach Curate

SELECT * Costs You the Index-Only Scan

Asking for every column means the database must visit the table row even when the index already held everything the query needed, and it ships wide columns over the network that nobody reads. Naming the three columns you use is what makes a covering index possible and turns an Index Scan into an Index Only Scan. It also stops a schema change from silently changing what your application receives.

Questions this Concept answers

Why does `SELECT *` cost you the index-only scan?

jeremy Video

How a PostgreSQL Index Replaces a Full Table Scan and When LIKE Patterns Defeat It

A database index is an auxiliary data structure (typically a B-tree, or an LSM tree in write-optimized engines) built over one or more columns of a table, which converts a linear search of the heap i…