Trino SQL Interview Questions: Cross-Source Joins, Pushdown, and Query Plans

A Trino SQL interview answer should explain where a query's work happens. A query can read two catalogs, filter each input, join in Trino, and aggregate afterward. Calling the whole operation “pushed down” hides the distinction that matters for network traffic, source load, and debugging.

Follow each operator across the source boundary, then separate plan evidence from runtime evidence. A filter written in SQL is not proof that the source skipped reading every excluded row. A smaller result is not automatically a faster query.

This guide uses official documentation labeled Trino 483 when checked. The queries and plan comparisons are original preparation exercises, not candidate reports. We verified small-data SQL results locally with SQLite. The plan sketches are explicitly illustrative: no Trino cluster, PostgreSQL connector, or Iceberg connector was executed, and no performance improvement was measured.

How a Cross-Source Query Runs in Trino

Consider two hypothetical catalogs: lake, configured for orders in Iceberg, and pg, configured for customers in PostgreSQL. The names are examples, not built-in catalog names.

SELECT o.order_id, o.amount
FROM lake.sales.orders AS o
JOIN pg.crm.customers AS c
  ON o.customer_id = c.customer_id
WHERE c.region = 'NA'
  AND o.amount >= 30
ORDER BY o.order_id;

Official boundary: pushdown depends on the connector and underlying source. Trino's documented join-pushdown conditions include both tables belonging to the same catalog. This cross-catalog example therefore should not be described as one join delegated wholesale to either source. Pushdown documentation

Practice explanation: locate the two scans, ask what each source can filter or project, then locate the join and any exchange inside Trino. A source-side filter can reduce one input without moving the join itself into the source.

Ask about the source data's meaning before optimizing. Is customer_id unique in the customer table? Are the two systems describing compatible points in time? Which records should survive if a customer has not yet reached the reference source? Federation makes the query expressible; it does not answer those correctness questions.

The selected columns matter too. Carry only the columns needed for output, filtering, and joining. However, explain the expected reduction as a hypothesis to verify. Do not claim a particular amount of saved I/O from the SQL text alone.

Verify the Result Before Discussing the Plan

Our original fixture contains six orders:

order_id	customer_id	amount
101	1	10
102	2	20
103	1	30
104	3	40
105	4	50
106	3	60

The customer rows are (1, NA), (2, EU), and (3, NA). There is no customer 4.

Observed local result: the equivalent SQLite join returned (103, 30), (104, 40), and (106, 60). The three amounts sum to 130. Order 105 was excluded because its customer did not match; order 101 matched the region but failed the amount condition.

The local check used ordinary tables containing the same rows, not external catalogs. It establishes this fixture's relational result, not Trino type handling, connector behavior, cross-source consistency, or an execution plan.

Interview follow-up: what happens if customer 3 occurs twice?

An ordinary join can produce more rows for that key. Diagnose whether the source violates the expected uniqueness contract before adding DISTINCT. A deduplication step might be appropriate, but it needs a rule for choosing the intended customer record. Otherwise, it can hide an unresolved modeling issue.

This is a useful way to resist premature optimization: write down the expected output and the grain of each input before proposing a different join order or a pushdown setting.

Plan Exercise One: Where Does the Filter Run?

For the cross-catalog query, compare these schematic plan-reading cards. They are not copied or generated EXPLAIN output, and they omit exchanges, projections, and implementation details.

Card A
  Trino join
    orders scan
    Trino filter: region = 'NA'
      customers scan

Card B
  Trino join
    orders scan
    customers scan with source constraint: region = 'NA'

Question: what changed, and what did not?

The cards describe different locations for the customer-region filter. Both still place the cross-catalog join in Trino. Card B is not evidence that the entire query runs in PostgreSQL.

Official reading guidance: Trino documents checking the plan for the relevant operation rather than assuming pushdown from query syntax. Successful predicate pushdown removes the corresponding filtering work from Trino's plan; projection evidence appears in the columns accessed by the scan. Pushdown guide

Practice answer: inspect the actual scan constraint or generated source query and any residual filter for this predicate. A plan can push part of a condition while retaining another part. Identify the particular expression you are discussing instead of saying “there is still a filter, so nothing pushed down.”

Also avoid the opposite mistake: an operator missing from a simplified diagram proves nothing about a real deployment. Obtain the real plan from the relevant query and environment. These cards teach the distinction you should look for; they are not a substitute for that evidence.

Plan Exercise Two: Who Computes the Aggregate?

Use a second original query entirely within the PostgreSQL catalog:

SELECT region, COUNT(*) AS customers
FROM pg.crm.customers
GROUP BY region
ORDER BY region;

Observed logical result in the local fixture: EU has one customer and NA has two. This checks the aggregation's answer, not its execution location.

Compare two more illustrative cards:

Card C: Trino aggregate(region, count)
          customer rows from source

Card D: Source computes grouping and count
          Trino receives grouped result

Official connector context: the PostgreSQL connector documents aggregate pushdown support, including count, but the actual decision still depends on the operation and query. Its documentation also emphasizes preserving correctness when deciding whether an operation can be pushed down. PostgreSQL connector

Practice answer: in the real plan, identify whether Trino performs the corresponding aggregation or receives an already aggregated source result. Keep this separate from sorting, final output formatting, and any other operation that remains in Trino.

The result can be identical under both cards. That is exactly why output comparison alone does not establish pushdown. Conversely, a different physical plan does not justify a different result. Preserve the query's semantics while investigating where its work occurs.

Do not infer a percentage speedup from the smaller grouped result. Source execution cost, transfer cost, concurrency, and data distribution still matter. A tiny fixture is useful for correctness and nearly useless for estimating a production performance gain.

How Dynamic Filtering Changes a Join

Official mechanism: dynamic filtering collects information from a join's build side during execution and uses it to reduce eligible probe-side work. Support and effectiveness depend on join conditions and connectors. Trino documents plan annotations and runtime statistics for investigating the feature. Dynamic filtering

Original exercise: after selecting North American customers, the matching customer IDs in our fixture are 1 and 3. A useful runtime filter could restrict candidate orders to those IDs, subject to the actual plan and connector's capabilities.

This differs from the static o.amount >= 30 condition, which is available directly from the query. The candidate customer IDs arise from the other input. Neither concept authorizes changing the logical join result.

We checked three logical variants locally:

Query variant	Joined rows and amount total
No region or amount condition	5 rows; total 160
North American customers only	4 rows; total 140
North American customers and amount at least 30	3 rows; total 130

These are SQLite result counts, not Trino scan counts or dynamic-filtering benchmarks. The table shows how explicit predicates change the requested answer. Enabling a correct physical optimization for one unchanged query should preserve that query's answer.

Follow-up: why might an available dynamic filter produce little improvement?

As a preparation hypothesis, consider a build side that selects almost every key, a filter that arrives after substantial work has begun, or a source layout that cannot skip much work. The actual explanation needs runtime evidence. Do not claim one of those possibilities occurred merely because the query was slow.

Read EXPLAIN Without Inventing Runtime Facts

Official command distinction: EXPLAIN shows a plan; its distributed form exposes fragments and exchanges between Trino nodes. EXPLAIN ANALYZE executes the statement and adds runtime information. The latter is an execution, not a harmless synonym for displaying a plan. EXPLAIN, EXPLAIN ANALYZE

For the read-only practice query, a real-environment investigation can begin with:

EXPLAIN (TYPE DISTRIBUTED)
SELECT region, COUNT(*)
FROM pg.crm.customers
GROUP BY region;

This command is provided for preparation; it was not executed against Trino for this article. Start at the source scans and follow the required columns, predicates, exchanges, join, and aggregation toward the output. Explain each substantial movement of data in plain language.

Then separate estimates from observations. A planned row count is not a measured row count. Missing statistics can make an estimate uncertain; it does not mean the table contains no rows.

When execution evidence is available, compare rows and bytes at the expensive boundaries, elapsed time, blocked time, memory, and uneven work across tasks. Choose the measurements that test your hypothesis rather than listing every metric on a dashboard.

For example, if your hypothesis concerns excessive cross-source transfer, a reduction in output rows alone is insufficient. Look for evidence about data read and transferred, while confirming the result remains correct. If elapsed time changes without that evidence, state the observation without attributing a cause prematurely.

Which Optimization Would You Try First?

A good interview response selects a reversible, evidence-driven change. If unnecessary columns cross the source boundary, simplify the projection. If the filter cannot move because of expression semantics, inspect whether an equivalent supported predicate exists. If the join multiplies rows unexpectedly, fix the data contract before tuning execution.

Official connector example: PostgreSQL pushdown support distinguishes text equality from text range predicates because source string ordering can differ from Trino's semantics. This is a correctness constraint, not merely a missing performance feature. PostgreSQL connector reference

Preparation guidance: do not rewrite an expression just to make the plan look smaller. Check null behavior, type conversion, duplicate keys, and outer-join preservation. Moving a condition from ON to WHERE can change which unmatched rows survive; a faster query with the wrong answer is not an optimization.

For a fair comparison, hold the query result, relevant data snapshot, connector configuration, and measurement conditions as stable as practical. Record changes in concurrency or cache state instead of presenting noisy single-run timings as a definitive engine comparison.

End your proposed experiment with a falsifiable expectation: “If source-side filtering is the bottleneck, this equivalent rewrite should reduce the relevant transferred input while preserving the expected rows.” Then say what result would make you reconsider.

Practice the Underlying SQL Reasoning

Practice question	Preparation focus
Optimize a SQL query plan tree	Locate operators and explain the evidence for moving work.
Reason About Composite Join Keys and Predicate Placement	Preserve semantics during a rewrite.
Illustrate SQL Join Results with Duplicate Keys	Predict row multiplication before interpreting performance.
Debug row loss after SQL joins	Explain unmatched records and filtering effects.
Write left-join queries with tricky filters	Distinguish a valid optimization from a changed answer.

Start with the query-plan-tree exercise, then narrate the two plan comparisons. State what runs in the source, what remains in Trino, what the result should be, and which runtime observation is still needed.

Sources and Further Reading

• Trino: Pushdown
• Trino: Dynamic Filtering
• Trino: EXPLAIN
• Trino: EXPLAIN ANALYZE
• Trino: PostgreSQL Connector