SQL describes which result you want, not the exact sequence of storage operations that produces it. A database parses the statement, checks names and types, transforms it into an internal representation, and selects a physical plan from alternatives.
The plan is a tree of work
For a query joining orders to customers, a plan might scan one relation, scan or index the other, join matching rows, and then sort or aggregate. Each node consumes rows from child nodes and emits rows to its parent. A sequential scan may be the right choice when most of a table is needed. An index scan may be better when a selective predicate finds few rows, but it adds index traversal and row-fetch work.
The planner estimates row counts and costs using statistics and configuration. If estimates are wrong, a locally plausible choice can produce a poor overall plan. Data distribution, stale statistics, parameter values, and correlation between columns can all influence the decision.
Indexes trade write work for read paths
A B-tree index keeps keys ordered in pages so the engine can narrow a search without scanning every row. Composite indexes have an ordering, so leading columns matter for many lookups. A covering index may satisfy a query from index entries alone, depending on engine details and visibility requirements. Every index also consumes storage and adds work to inserts, updates, vacuuming, and backup.
Execution meets storage
The executor runs the chosen operators. It asks the buffer manager for database pages; a page can already be cached or require storage I/O. Transactions add visibility checks, locks or version checks, and logging so the engine can provide its advertised isolation and durability behavior.
Use EXPLAIN to inspect the chosen plan and EXPLAIN ANALYZE to compare estimates with observed execution. The latter runs the statement and adds measurement overhead, so use it carefully for statements with side effects. PostgreSQL's EXPLAIN guide shows how to read plan trees and their estimates.