SQL Execution Model

Consider two queries that return the same result: each customer’s state and the amount of every sale above a thousand.

-- Query A
SELECT state, amount
FROM (
  SELECT s.*, c.*
  FROM sales s
  JOIN customers c ON s.customer_id = c.id
)
WHERE amount > 1000;

-- Query B
SELECT c.state, s.amount
FROM (
  SELECT amount, customer_id
  FROM sales
  WHERE amount > 1000
) s
JOIN customers c ON s.customer_id = c.id;

Which one looks more efficient?

Query B suggests the filter happens before the join and that only the necessary columns are loaded. Query A suggests the opposite: first the join between all sales and customers, then the filter. If each query were an imperative recipe, B would be the more efficient choice.

But SQL isn’t an imperative recipe. It’s a declarative language: it describes the desired result, while the database decides how to obtain it. To answer the opening question, we need to follow the query from text to execution.

The diagrams in this article are didactic models. They explain the planning stages, but they don’t literally reproduce the output of any specific database.

The journey

Answering it means following the query through every stage a database pushes it through before a row moves: text, a structure the parser can reason about, a plan built from relational algebra, that plan rewritten by an optimizer, and finally the physical instructions that run. Every SQL engine does some version of this, even when the boundaries are fuzzier than they are here.

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class SQL,AST,LOGICAL,OPTIMIZED,PHYSICAL muted;

We’ll walk this path with Queries A and B. To track the same operation as it changes shape across diagrams, filters, joins, and column projections receive their own visual treatments; scans and structural nodes that aren’t operators yet remain neutral.

1. SQL

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class AST,LOGICAL,OPTIMIZED,PHYSICAL muted;
    class SQL active;

The first thing that reaches the database is a string: characters forming words like SELECT, FROM, WHERE, and JOIN. It isn’t yet a sequence of instructions for the machine.

Both queries express the same intent; the difference is syntactic — the position of the filter and the column list in the text. That position doesn’t force the database to execute the operations in that order.

Read top to bottom, the two queries already look different:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph SA["SQL text — Query A (reading order)"]
        direction TB
        SA1["SELECT state, amount"] --> SA2["FROM (subquery)"]
        SA2 --> SA3["SELECT s.*, c.*"]
        SA3 --> SA4["JOIN sales s, customers c"]
        SA4 --> SA5["WHERE amount > 1000"]
    end
    subgraph SB["SQL text — Query B (reading order)"]
        direction TB
        SB1["SELECT c.state, s.amount"] --> SB2["FROM (subquery)"]
        SB2 --> SB3["SELECT amount, customer_id"]
        SB3 --> SB4["WHERE amount > 1000"]
        SB4 --> SB5["JOIN customers c"]
    end
    SA ~~~ SB
    class SA1,SA3,SB1,SB3 opProject;
    class SA4,SB5 opJoin;
    class SA5,SB4 opFilter;
    class SA2,SB2 muted;

In Query A, WHERE is the last thing you read; in Query B it’s inside the subquery, ahead of the join. That position — not anything the database has decided — is the entire source of our intuition.

The database first parses the structure of the text. That’s the role of the next stage.

2. AST

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class SQL,LOGICAL,OPTIMIZED,PHYSICAL muted;
    class AST active;

AST stands for Abstract Syntax Tree: an internal structure the parser builds by reading the text and recognizing its grammar. It isn’t just an illustration — it’s a real stage of processing, turning literal text into something the interpreter can analyze. Spaces, line breaks, and indentation stop mattering; what remains is the relationship between SELECT, FROM, subqueries, filters, and joins.

A simplified representation of both ASTs, side by side, makes the difference easy to spot:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph QA["AST — Query A"]
        direction TB
        A1["SelectStatement<br/>(state, amount)"] --> A2["FromClause"]
        A2 --> A3["Subquery"]
        A3 --> A4["Join<br/>(sales s, customers c)"]
        A1 --> A5["Predicate<br/>(amount > 1000)"]
    end
    subgraph QB["AST — Query B"]
        direction TB
        B1["SelectStatement<br/>(c.state, s.amount)"] --> B2["Join"]
        B2 --> B3["Subquery s"]
        B3 --> B4["Predicate<br/>(amount > 1000)"]
        B2 --> B5["TableRef<br/>(customers c)"]
    end
    QA ~~~ QB
    class A1,B1 opProject;
    class A4,B2 opJoin;
    class A5,B4 opFilter;
    class A2,A3,B3,B5 muted;

Here’s where our intuition comes from: in Query B’s tree, the predicate node really does sit lower, nested inside the subquery below the join. In Query A, the same predicate hangs directly off the outer statement, above the join. Even so, the AST only shows the query’s grammatical structure, not the final sequence of work over the data. To get there, the database converts the tree into operators.

3. Logical plan

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class SQL,AST,OPTIMIZED,PHYSICAL muted;
    class LOGICAL active;

The logical plan translates the AST into relational algebra operators — the mathematical operations that describe how relations (tables and their sets of rows) combine and transform, and one of SQL’s conceptual foundations.

Every familiar part of the query finds a parallel in that algebra:

In SQL	In the logical plan	What it does
FROM sales	scan	Reads the sales relation.
WHERE amount > 1000	filter or selection	Keeps only the rows that pass the condition.
JOIN … ON …	join	Combines relations by the join condition.
SELECT state, amount	project or projection	Keeps or computes the output columns.

Now the query stops being a tree of keywords and becomes a tree of operations over data:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph LA["Logical plan — Query A"]
        direction TB
        LA1["Project<br/>(state, amount)"] --> LA2["Filter<br/>(amount > 1000)"]
        LA2 --> LA3["Join<br/>(s.customer_id = c.id)"]
        LA3 --> LA4["Scan (sales)"]
        LA3 --> LA5["Scan (customers)"]
    end
    subgraph LB["Logical plan — Query B"]
        direction TB
        LB1["Project<br/>(state, amount)"] --> LB2["Join<br/>(s.customer_id = c.id)"]
        LB2 --> LB3["Project<br/>(amount, customer_id)"]
        LB3 --> LB4["Filter<br/>(amount > 1000)"]
        LB4 --> LB5["Scan (sales)"]
        LB2 --> LB6["Scan (customers)"]
    end
    LA ~~~ LB
    class LA1,LB1,LB3 opProject;
    class LA3,LB2 opJoin;
    class LA2,LB4 opFilter;
    class LA4,LA5,LB5,LB6 muted;

At this point, the opening question is still open. Both logical plans reflect the position of the clauses in the SQL. Before choosing how to execute either one, the optimizer can still rewrite these operations.

4. Optimization

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class SQL,AST,LOGICAL,PHYSICAL muted;
    class OPTIMIZED active;

Optimizing, in this context, means transforming the logical plan into another plan that produces the same result with less work, or with work better positioned. The change isn’t made to the original SQL text; it’s made to the operator tree.

There are many optimization rules. For this example, two of the most important are predicate pushdown and projection pushdown.

Predicate pushdown

A predicate is a condition that evaluates to true or false. In SQL, the condition of a WHERE clause is a predicate. Predicate pushdown tries to move that filter down in the plan, closer to the data source.

In Query A, the predicate s.amount > 1000 depends only on sales. Since the example uses an inner join, a sale with an amount less than or equal to a thousand couldn’t appear in the result anyway. So the filter can be applied before the join:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph BEFOREP["Query A — before predicate pushdown"]
        direction TB
        P1["Filter<br/>(amount > 1000)"] --> P2["Join"]
        P2 --> P3["Scan (sales)"]
        P2 --> P4["Scan (customers)"]
    end
    subgraph AFTERP["Query A — after predicate pushdown"]
        direction TB
        Q1["Join"] --> Q2["Filter<br/>(amount > 1000)"]
        Q1 --> Q3["Scan (customers)"]
        Q2 --> Q4["Scan (sales)"]
    end
    BEFOREP ~~~ AFTERP
    class P2,Q1 opJoin;
    class P1,Q2 opFilter;
    class P3,P4,Q3,Q4 muted;

Query B’s logical plan already had the filter positioned below the join (see the comparison above), so this rewrite is a no-op for it — there’s nothing left to push down.

In some engines, including Trino, this movement continues all the way to the data source, through what Trino calls a connector — the component linking the engine to a specific data system, such as a relational database, a Data Lake, or a queue. When the connector supports predicate pushdown, it sends the restriction down and the source eliminates rows while reading; support varies with the connector and source. Trino’s documentation describes this condition.

Projection pushdown

Projection pushdown applies the same logic to columns. In the sales table, the example only needs amount and customer_id: amount takes part in the filter and the result; customer_id takes part in the join. In customers, only id and state are needed.

The optimizer inserts or moves projections so only these columns are loaded along the way. Connectors that support this can read and return just the necessary columns, reducing I/O and data transfer. In Trino, this shows up in the columns of the TableScan in the plan. The pushdown documentation explains how to check for it.

Applied to Query A’s plan, after predicate pushdown:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph BEFOREJ["Query A — before projection pushdown"]
        direction TB
        R1["Project<br/>(state, amount)"] --> R2["Join"]
        R2 --> R3["Filter<br/>(amount > 1000)"]
        R3 --> R4["Scan (sales)"]
        R2 --> R5["Scan (customers)"]
    end
    subgraph AFTERJ["Query A — after projection pushdown"]
        direction TB
        S1["Project<br/>(state, amount)"] --> S2["Join"]
        S2 --> S3["Filter<br/>(amount > 1000)"]
        S3 --> S4["Project<br/>(amount, customer_id)"]
        S4 --> S5["Scan (sales)"]
        S2 --> S6["Project<br/>(state, id)"]
        S6 --> S7["Scan (customers)"]
    end
    BEFOREJ ~~~ AFTERJ
    class R1,S1,S4,S6 opProject;
    class R2,S2 opJoin;
    class R3,S3 opFilter;
    class R4,R5,S5,S7 muted;

After both rules, Queries A and B converge to the same optimized logical plan:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opFilter fill:#FBE4E2,stroke:#C0392B,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    subgraph CA["Query A — optimized"]
        direction TB
        CA1["Project<br/>(state, amount)"] --> CA2["Join"]
        CA2 --> CA3["Filter<br/>(amount > 1000)"]
        CA3 --> CA4["Project<br/>(amount, customer_id)"]
        CA4 --> CA5["Scan (sales)"]
        CA2 --> CA6["Project<br/>(state, id)"]
        CA6 --> CA7["Scan (customers)"]
    end
    subgraph CB["Query B — optimized"]
        direction TB
        CB1["Project<br/>(state, amount)"] --> CB2["Join"]
        CB2 --> CB3["Filter<br/>(amount > 1000)"]
        CB3 --> CB4["Project<br/>(amount, customer_id)"]
        CB4 --> CB5["Scan (sales)"]
        CB2 --> CB6["Project<br/>(state, id)"]
        CB6 --> CB7["Scan (customers)"]
    end
    CA ~~~ CB
    class CA1,CA4,CA6,CB1,CB4,CB6 opProject;
    class CA2,CB2 opJoin;
    class CA3,CB3 opFilter;
    class CA5,CA7,CB5,CB7 muted;

Query B isn’t automatically more efficient just because its text suggests it filters first: with the same semantics and valid transformations, the optimizer can arrive at the same sequence of operators for both.

This isn’t a universal guarantee. Outer joins, limits, ordering, aggregations, non-deterministic functions, null values, statistics, and the source’s capabilities can all change what can be rewritten or the cost of each plan.

5. Physical plan

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart LR
    classDef active fill:#FFF4DB,stroke:#B45309,color:#1F2937,stroke-width:2px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    SQL[1. SQL] --> AST[2. AST] --> LOGICAL[3. Logical plan] --> OPTIMIZED[4. Optimized plan] --> PHYSICAL[5. Physical plan]
    class SQL,AST,LOGICAL,OPTIMIZED muted;
    class PHYSICAL active;

So far we’ve talked about what needs to be computed. The physical plan defines how those operations will be executed with real resources:

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#F7F7F5",
    "primaryTextColor": "#1F2937",
    "primaryBorderColor": "#D6D3D1",
    "secondaryColor": "#EEEDE8",
    "tertiaryColor": "#FFF4DB",
    "lineColor": "#6B7280",
    "clusterBkg": "#EEEDE8",
    "clusterBorder": "#D6D3D1",
    "fontFamily": "Inter, Segoe UI, Helvetica Neue, Arial, sans-serif",
    "fontSize": "15px"
  },
  "flowchart": {
    "curve": "basis",
    "nodeSpacing": 28,
    "rankSpacing": 36,
    "padding": 8
  }
}}%%
flowchart TD
    classDef opProject fill:#E3F3EF,stroke:#2F9E8F,color:#1F2937,stroke-width:1.5px;
    classDef opJoin fill:#E4ECF7,stroke:#3B6FA0,color:#1F2937,stroke-width:1.5px;
    classDef muted fill:#F7F7F5,stroke:#D6D3D1,color:#4B5563,stroke-width:1px;
    PR["Project<br/>(state, amount)"] --> HJ["BroadcastHashJoin<br/>(build: customers)<br/>customer_id = id"]
    HJ --> FS1["FileScan sales — format: parquet<br/>predicate pushdown: amount > 1000<br/>projection pushdown: amount, customer_id"]
    HJ --> FS2["FileScan customers — format: parquet<br/>projection pushdown: id, state"]
    class PR opProject;
    class HJ opJoin;
    class FS1,FS2 muted;

This is where the database decides, for example, which join algorithm to use, how many tasks to run in parallel, where to keep data in memory, and when to shuffle data over the network. In distributed engines, this representation also shows up as a distributed plan. In Trino, the default EXPLAIN shows this plan in fragments, indicating where the work happens and how data is distributed across nodes. The EXPLAIN documentation describes fragments such as SOURCE, HASH, BROADCAST, and SINGLE.

Physical decisions depend on characteristics that aren’t visible in the SQL alone: data volume, partitioning, location, file format, available memory, and statistics. For example, Trino can use statistics provided by the connector to estimate costs, choose a join order, and decide between broadcast and partitioned distribution. The documentation on cost-based optimizations details these decisions.

That’s why the optimized logical plan doesn’t end the performance investigation — it tells you what will be done, not how the environment will execute it.

Checking the plan

Now we return to the opening question with a practical tool. Instead of rewriting a query just because it looks like it executes in a different order, use EXPLAIN to see the plan the engine produced:

EXPLAIN (TYPE DISTRIBUTED)
SELECT c.state, s.amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE s.amount > 1000;

When reading the result, try to answer:

1. Which columns are being read from each source?
2. Where is the filter being applied?
3. At what point is data shuffled between nodes?
4. Which strategy was used for the join?
5. Do the estimates make sense for the data you know?

EXPLAIN shows the planned plan. When you need to observe an actual execution, some engines offer tools equivalent to EXPLAIN ANALYZE. In Trino, it executes the query and shows metrics per operation, so it isn’t a cost-free inspection. The EXPLAIN ANALYZE documentation explains its metrics and limitations.

What this means for you

• Readability over micro-optimization. Use well-named CTEs and clear structures; the optimizer takes care of performance. Readable code is easier to maintain and debug.
• Trust the optimizer. Don’t try to “outsmart” the engine with convoluted rewrites just to force a filter order. Modern optimizers — Catalyst in Spark, the cost-based optimizer in Trino — are good at recognizing intent.
• Reach for EXPLAIN often. When performance is in doubt, look at the real plan instead of guessing. Check whether predicate pushdown and projection pushdown actually happened.
• Know your data. Final performance depends on data distribution, partitioning, table statistics, and file format (Parquet, ORC). Optimizing the plan is only half the battle.

The answer

SQL starts as text. The parser turns it into an AST; the AST becomes a logical plan based on relational algebra operators; the optimizer rewrites that plan whenever it finds valid transformations; finally, the database produces a physical plan to execute the work.

Queries A and B help us remember that the way SQL looks isn’t enough to predict performance. Write the form that best communicates your intent and preserves the semantics. When there’s a real question about cost or latency, follow the same path the database followed: look at the plan.