AI Glossary / Data engineering and architecture / Intermediate

Indexed access

Definition

Indexed access retrieves data through an index that organizes searchable values and points to, or otherwise identifies, relevant records. It can avoid examining every record when the index suits the query. The phrase describes an access path, not a guarantee of speed. Different index structures support different operations, and a database optimizer may choose another path when it estimates that doing so is more efficient.

What it means

A book index directs a reader to relevant pages; a database index similarly helps locate records matching an indexed condition. The database still has to obtain the required values and apply any remaining checks. An index on customer identifier can help find a particular customer, but it may do little for a query that needs nearly every customer or filters on an unrelated expression.

How it works

The database maintains an index structure alongside or as part of its storage design. A query can search that structure for equality matches, ranges, or other supported predicates, then retrieve candidate records and verify remaining conditions. B-tree and hash indexes illustrate different capabilities: ordered structures can support range access, while ordinary hash indexes focus on equality. Some implementations can answer suitable queries largely from index contents, subject to additional requirements. Inserts, updates, and deletes also require index maintenance, adding cost.

Three practical examples

• Customer service: a system finds a record by a stable account identifier without scanning the whole customer table.
• Reporting: an appropriate ordered index helps retrieve events within a selective time interval.
• AI provenance: a review tool looks up the source record for a particular dataset identifier through an indexed relationship.

Think of it this way

Think of using the index at the back of a reference book. It is helpful for finding a narrow topic, but reading the entire book does not become faster by looking up every page individually. The analogy is limited because database indexes have maintenance costs, multiple structure types, and transaction rules. A database can also combine access paths or retrieve values directly from an index under particular conditions.

Worked example

A toy table contains 100,000 event records, of which 20 match a particular EventID prefix under an indexable query design. A suitable index may narrow access to those candidates instead of testing every row. Now consider a query selecting 90,000 records from the same table. Repeated index-driven record retrieval may be less attractive than scanning a large contiguous portion of the table, depending on storage and caching. These counts illustrate selectivity, not measured read operations or runtime. The actual plan must account for the index type, predicate, selected columns, data distribution, and environment. If a filter applies a transformation not supported by the available index, the apparent presence of an index on the raw column may not provide the expected access path.

Illustrative case study

Fictional example: Spruce Analytics adds an index to support frequent source-record lookups. It verifies that the relevant query uses a suitable plan and measures the lookup workload. The team also measures import performance because each newly loaded record must update the index. A broad monthly export continues to use a scan, which is acceptable for that workload. This invented case illustrates balancing reads and writes; it does not claim a particular speedup or that an unused index necessarily indicates a database fault.

Common misconceptions and mistakes

Do not assume every indexed column is unique or that every query mentioning it will use the index. Do not treat index access as inherently superior to sequential access. Avoid adding indexes without considering storage and write maintenance. Also distinguish logical key constraints from physical access structures: they interact, but not every index is a key and not every relationship declaration automatically creates every helpful index. Product-specific behavior should be checked in the relevant documentation.

Business use

Indexed access matters when response time and resource use depend on finding a selective subset efficiently. Start with actual query patterns, then choose index structure and columns accordingly. Inspect execution plans and measure representative workloads, including writes and broad reads. Keep statistics and maintenance practices appropriate to the database. For AI services, index provenance lookups and filtering needs deliberately, while recognizing that exact-key indexes and approximate vector-search indexes solve different retrieval problems and have different correctness properties.

People also ask

Does creating an index force the database to use it?

Usually not. The optimizer may choose another path based on costs and query compatibility.

Can an index make writes more expensive?

Yes. The database must maintain it when relevant data changes.

Is indexed access the same as sorted output?

No. Some access paths have an order, but a query requiring a defined result order should specify it explicitly.

Check your understanding

A table has an index, but a query returns almost every row through a scan. Is that necessarily wrong?

Answer: No. A scan may be cheaper for a broad result. Examine the actual workload and plan before assuming the index should be forced.

Related terms

• Database index
• Execution plan
• index scan
• table scan
• B-tree
• hash index
• selectivity
• query optimizer
• covering index
• index-only scan

Learn next

compare a selective lookup and a broad query on the same small database. Inspect the access paths before and after an appropriate index is added. Measure both reads and inserts, and explain why the chosen plan depends on more than the existence of an index.

More in Data engineering and architecture

• Implicit parallelism - Implicit parallelism is parallel execution that a compiler, runtime or execution system discovers or manages without the programmer spelling out every...
• Incomplete join - Incomplete join is an informal description of a join whose conditions do not fully specify the intended relationship between its inputs.
• Independent multivalued fact - An independent multivalued fact is a set of values associated with an entity that varies independently of another set associated with that same entity.
• Industry standard data model - An industry standard data model is a shared description of important data concepts, attributes and relationships for a particular sector, established...
• Information governance - Information governance is the coordinated system of responsibilities, policies and oversight through which an organization manages information throughout...
• Information integration - Information integration brings information from different sources into a coherent form or view so that people and applications can use it together.

Sources

Sources: PostgreSQL 18, Indexes Introduction, https://www.postgresql.org/docs/18/indexes-intro.html ; PostgreSQL 18, Index-Only Scans and Covering Indexes, https://www.postgresql.org/docs/18/indexes-index-only-scans.html ; PostgreSQL 15, Index Types, https://www.postgresql.org/docs/15/indexes-types.html . Primary documentation search excerpts consulted for index maintenance, planner choice, supported predicates, and index-only qualifications. Numerical counts are original illustrations, not benchmarks.

Previous: Index number Next: Indexed grammar