= Database index
A <database index> is an auxiliary <data structure> that helps a <database management system> locate records satisfying selected conditions. It stores information organized for an access pattern, such as searching a column value, without requiring every <database tuple> to be inspected. The <database query> still specifies the result; the index supplies a possible route to it.<Introduction to Indexes in the PostgreSQL documentation>[§11.1, indexed lookup example and maintenance costs]{ref}
For example, a <database relation> with a million samples can have an index on `sample_id`. Looking for one identifier can follow a search structure instead of scanning all rows. A condition matching most rows may still favor a scan, since many separate lookups and row fetches can cost more. The index occupies storage and must be maintained on relevant updates. These costs explain why indexing every column is not automatically the best design.
Back to article page
