Which indexes should I know?
Understand the concept
An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.
Important ideas
-
B-tree handles many equality and range queries; composite indexes contain multiple columns.
-
Partial indexes cover a subset of rows; unique indexes reject duplicate keys.
-
Index order should follow real filters and sorting; extra indexes slow writes.
-
For a query filtering by tenant and sorting by creation time, index column order should reflect those access patterns.
Can you explain it without the notes?
Which indexes should I know?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.
Turn this concept into a clear answer.
- Explain the supported query and the write/storage cost; low cardinality does not make a field universally useless in every index.
Adapted from 05_SQL_POSTGRESQL.md · “Which indexes should I know?”. Examples are study fragments; framework behavior can depend on your version.