A declarative SQL query describes the result to produce, not the exact physical steps for obtaining it. The database query planner chooses an execution plan from available access methods and join strategies.

Sequential and index scans

A sequential scan reads table data directly and can be efficient when much of the table is needed.

An index scan first uses an index to locate candidate rows. This is attractive when the index eliminates enough unnecessary data to justify its own traversal and row-lookups.

Cost estimation

The planner estimates costs from information such as table size, value distributions and expected selectivity. Statistics that do not represent current data well can lead to poor plans.

EXPLAIN

Database systems commonly provide an EXPLAIN facility that reveals the chosen plan: scans, joins, sorts, estimated row counts and costs.

The plan should be interpreted together with actual workload measurements rather than treating one operator as universally good or bad.

Index design is a feedback loop

Useful indexing therefore follows a cycle:

important query → inspect plan → identify bottleneck
       ↓
consider index or query change
       ↓
measure the resulting plan and workload

An index is successful when it improves the application workload enough to justify its storage and write costs.