A database index gives a SQL engine a separate structure for finding rows without checking every row in a table. For selective queries, that can reduce lookup work; it is not a guaranteed speed boost. Indexes take storage, add work to data changes, and may be ignored when the optimizer estimates that a scan is cheaper.
What a database index does
An index is an additional searchable structure associated with a table. It stores column values in an arrangement that helps the database locate candidate rows, then use row references or the index’s key organization to retrieve the requested data. PostgreSQL describes the benefit this way: “An index allows the database server to find and retrieve specific rows much faster than it could do without an index.” (PostgreSQL: Indexes)
As an Amazon Associate I earn from qualifying purchases.
Many common relational rowstore indexes use balanced tree structures, often called B-trees. A tree helps the engine narrow a search through ordered key values rather than inspect every row. Indexes can also help with joins or ordering when their keys match the query’s needs. The benefit depends on the query, the data, and the database engine; an index does not make every operation faster.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →When an index helps—and when a scan can win
A selective lookup
Imagine a large customer table and a query looking up one customer by a unique email address. If a suitable index exists, the engine may use it to locate the matching row without examining the whole table. This is an illustrative example, not a measured benchmark.
#1 Best Overall
A query that returns many rows
A report that reads most of a table may be cheaper to serve with a sequential or table scan. Following an index and fetching a large share of the table’s rows can cost more than reading the table directly. A small table may also be inexpensive to scan.
The optimizer makes this choice based on estimated costs, which depend on factors such as statistics, data distribution, the query, and the engine. PostgreSQL says its planner uses an index when it estimates that doing so is more efficient than a sequential scan; current statistics help it make informed decisions. (PostgreSQL 17: Introduction to Indexes) An index’s existence alone does not show that it is useful or that a particular query will use it.
Index types and designs
Index terminology and behavior vary by database product. B-tree is a common general-purpose structure, but it is not the only one, and vendor-specific index types should not be treated as interchangeable.
| Database | Documented examples | What to keep in mind |
|---|---|---|
| PostgreSQL | B-tree, hash, GiST, SP-GiST, GIN, and BRIN; also multicolumn, expression, partial, and index-only techniques. | These structures and techniques address different data or query patterns. See the PostgreSQL index documentation. |
| MySQL | PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT are generally stored in B-trees. | The manual notes exceptions, including spatial indexes and MEMORY-table cases. See MySQL: How MySQL Uses Indexes. |
| SQL Server | Clustered and nonclustered rowstore indexes, as well as columnstore indexes. | These are SQL Server-specific design choices; see Microsoft’s index architecture and design guide and clustered and nonclustered index overview. |
Composite indexes
A composite index stores keys from more than one column, in an order chosen for the workload. Column order matters, but the right order depends on the engine’s rules and the predicates and access patterns in the queries you need to support. There is no universal column-order recipe that fits every database and query.
Partial or filtered indexes
In systems that support them, partial or filtered indexes include only rows meeting a condition. They can suit queries that repeatedly target a subset of a table, but their value depends on whether the query and workload match the index’s condition.
Covering indexes
A covering index contains the values a query needs, potentially allowing the engine to satisfy some reads from the index rather than fetch additional table data. PostgreSQL calls the related optimization an index-only scan; whether it can be used depends on visibility and storage behavior as well as the query. (PostgreSQL: Indexes)
What indexes cost
An index uses storage and must be maintained as table data changes. Inserts and deletes can require index updates; changing an indexed value can also require maintenance. MySQL warns that unnecessary indexes waste space and increase the work of choosing an index, while adding cost to inserts, updates, and deletes. (MySQL: How MySQL Uses Indexes) PostgreSQL also notes that indexes add overhead to the database system, and Microsoft frames index design as a balance among query speed, update cost, and storage cost. (PostgreSQL: Indexes; Microsoft: SQL Server index architecture and design guide)
Whether an index is worthwhile depends on the reads it helps, the extra work it adds to writes, its storage footprint, how selective the target queries are, and the effort needed to monitor the workload. Indexing every column is not a sound default, and there is no universal index count or speedup figure that applies to all tables.
Best Value
How to check whether a query uses an index
- Inspect the query plan. In PostgreSQL, use
EXPLAINto see the planned strategy. In SQL Server, inspect an estimated or actual execution plan; Microsoft recommends plans for checking which indexes are used. (PostgreSQL 17: Introduction to Indexes; Microsoft: SQL Server index architecture and design guide) - Check the plan in the context of the query. An index scan is not automatically better than a table or sequential scan. Consider how many rows the query returns and the work needed to retrieve them.
- Use appropriate performance measurements. Compare the relevant workload before and after a proposed index change. A plan shows the optimizer’s chosen strategy; it is not, by itself, proof of an overall performance improvement.
- Keep statistics useful. Planners rely on estimates, and PostgreSQL’s documentation explains that current statistics help the planner make informed choices. If estimates do not reflect the data, the selected plan may not match the real workload well.
Index design is a workload-specific balance, not a switch to turn on everywhere. As Microsoft puts it, “The design of the right indexes for a database and its workload is a complex balancing act between query speed, index update cost, and storage cost.” (Microsoft: SQL Server index architecture and design guide)
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
