Does every query use an index if one exists?

No. The planner may choose another path when the index is unsuitable or estimated to be more expensive.

Are indexes free after creation?

No. They occupy space and relevant data changes require maintenance, which can affect write performance.

Does an index on a column make its values unique?

Only when the index or an associated constraint is specifically defined to enforce uniqueness under the database's rules.

AI Glossary / Data engineering and architecture / Beginner

Database index

Definition

A database index is a structure maintained to help a database locate or organize relevant records efficiently. It usually provides an alternative access path based on selected columns or expressions. An appropriate index can reduce the work needed for a query, but it consumes resources and must be maintained as the underlying data changes.

What it means

Imagine finding one customer's orders among millions of records. Without a useful access path, the engine may inspect a large part of the table. An index organized around customer identifiers can help it locate relevant entries more directly.

Indexes support particular operations, not every possible question about a table. A structure useful for exact matches may differ from one suited to ranges, text components or spatial relationships. The database's planner decides whether an available index is useful for a specific query and dataset.

How it works

When an index is created, the database builds its structure from existing data. A tree-based index, for example, organizes keys so searches can narrow the region to examine. Other designs use different mechanisms. Some index entries identify matching records; some structures can provide requested values without every ordinary table access.

As records are inserted, changed or removed, the database maintains the relevant index information. That work adds storage and processing costs. Creating an index on a busy table may also have operational effects, depending on the system and creation method.

The planner considers query conditions, supported operators and estimates of how much data will match. Reading most of a table sequentially can be cheaper than following many index entries. Consequently, an unused index is not automatically evidence of a planner failure, and an index's presence is not a promise that every query becomes faster.

Three practical examples

1. A customer service application frequently looks up orders by order number. A suitable index can support that selective lookup without scanning the complete order history each time.

2. A reporting system requests records within a date interval. A compatible ordered index may help locate the interval, depending on its size and the surrounding query.

3. An AI knowledge application searches documents for specific indexed text components. It uses an appropriate search structure rather than assuming that an ordinary index on the document identifier supports every content-search operation.

Think of it this way

Think of a library's separate catalog arranged by author. It helps locate relevant books while leaving the books organized by another scheme. The limitation is that a database automatically maintains its index and may choose other access paths. Some indexes also contain enough information for a particular answer, whereas a basic catalog usually sends you to the shelves.

Worked example

Suppose a fictional Orders table contains 1,000,000 rows, and customer C17 has 100 orders. A query asks for those orders. Its matching fraction is 100 divided by 1,000,000, or 0.01%.

A compatible customer index can give the engine an access path to the matching records. That does not imply exactly 100 physical reads: the index itself must be traversed, records occupy storage pages, and caching affects actual work. Measure the execution plan and resource use rather than deriving elapsed time directly from the matching fraction.

Now a different query requests 900,000 orders, or 90% of the table. A sequential scan may be more efficient than accessing most records through an index. Finally, every new order still requires maintenance of relevant indexes. The best choice therefore considers both lookup benefits and the application's write workload, not just the fastest isolated read.

Illustrative case study

Fictional case: IndexMeadow creates an index on every column after several dashboard queries become slow. Some lookups improve, but imports take longer and storage consumption rises. The team discovers that several new indexes do not support any important query.

It reviews representative execution plans and workload measurements. Instead of keeping every structure, it selects indexes that address frequent, consequential access patterns and tests the effects on imports as well as reports. It checks dependencies before removing anything that supports an important constraint.

The lesson is that indexing is a workload decision. The case provides no universal index-count prescription.

Common misconceptions and mistakes

Do not confuse an index with a guarantee of result order. If a query requires a defined order, express that requirement explicitly. An engine may choose a different access path on another execution.

Avoid assuming every index enforces uniqueness. A unique index has a specific role and product-dependent details; ordinary indexes can contain repeated keys. Also avoid treating separate single-column indexes as automatically equivalent to one carefully chosen multicolumn index. Their usefulness depends on the conditions, ordering and engine behavior. Test the actual query rather than relying on the index's name.

Business use

Indexes can improve response times and reduce repeated work, but unnecessary ones create continuing costs. Begin with important user operations and realistic data distributions. Compare query performance, write performance, index size and the operational cost of building the structure.

Maintain useful statistics and review whether changing workloads still justify existing indexes. For AI-generated queries, indexing does not compensate for an incorrect join or an unnecessarily broad request. First establish the intended question and correct query, then optimize the access needed to answer it.

Check your understanding

A query returns almost every row, and the planner chooses a table scan despite an available index. Is that necessarily wrong?

Answer: No. Accessing most of the table through an index may cost more than a sequential scan. Examine the plan and measured workload before changing the design.

Related terms

Execution plan
Inverted index
B-tree
query planner
selectivity
unique constraint
multicolumn index
database table
query optimization
index maintenance

Learn next

Learn execution plans next. Compare a selective lookup and a broad scan on the same sample table, before and after creating a suitable index. Check that results agree and record both read performance and the cost of inserting new rows. Extend the experiment to a query with two filtering columns.

Sources

PostgreSQL, Indexes introduction, explains access paths, planner choices and maintenance costs: https://www.postgresql.org/docs/current/indexes-intro.html

PostgreSQL, Index Types, distinguishes structures and supported operations: https://www.postgresql.org/docs/current/indexes-types.html

SQLite, Query Planning, provides a primary explanation of alternative lookup strategies: https://www.sqlite.org/queryplanner.html

The primary documentation was consulted. The calculations and fictional case are original; index behavior and benefits must be evaluated in the intended database and workload.

Previous: Database engine Next: Database instance