What Starburst Knows About Your Query Before It Runs a Single Row
Your SQL arrives with a request, but no instructions about the cheapest way to fulfill it. The Starburst query optimizer builds that strategy using the query, available statistics, and the capabilities of the connected sources.
Before execution, it can estimate intermediate results and choose among possible plans. You can inspect that reasoning with EXPLAIN, which shows the plan without running the query.
The interesting word in the title is “knows.” Some information is available beforehand; some is estimated; some arrives only during execution. Keeping those categories separate makes a slow query much easier to investigate.
What the Starburst query optimizer starts with
Start with the planning inputs before studying the chosen plan. For this September 2026 discussion, Enterprise documentation supplies the technical detail; Galaxy documentation confirms the shared distinction between estimated plans and execution results.
The SQL supplies a shape, not a schedule
Starburst’s documented query lifecycle separates parsing, analysis, planning, and execution. Its training material also covers constant folding: some expressions can be simplified before the engine processes your requested data.
That gives you a useful starting question during a review: what work does this statement actually request? Write down its filters, joins, selected columns, and final grouping before debating worker counts or memory settings.
Consider a hypothetical revenue report joining orders, customers, and regions. The business request is simple. Your investigation should still distinguish the requested answer from the sequence of operations proposed to produce it.
For teams building this review process, a practical engagement should cover connector behavior, plan interpretation, and repeatable performance checks. DataCouch’s starburst consulting services page describes support for connector setup and query plan analysis.
Table statistics provide an imperfect preview
Connectors can supply table row counts and column statistics such as distinct counts, null fractions, value ranges, and data size. Availability varies by connector and table. SHOW STATS exposes the statistics available through SQL.
Those details deserve a place beside your SQL when you troubleshoot. A table’s name tells your team what it represents, but it provides a poor basis for guessing how much data a particular operation will handle.
“the query planner cannot reliably deduce information just from table names.”
Piotr Findeisen, Starburst Data technical author, (Introduction to Trino Cost-Based Optimizer)
Here is why those summaries matter: estimated sizes after filtering can change the preferred join order. Findeisen’s example shows how adding an item filter changes the plan’s reasoning about its inputs.
Treat a row estimate as a working hypothesis. If your team expects a seasonal promotion to dominate the selected period, flag that expectation for later comparison. A plausible number deserves checking just as much as an obvious question mark.
How estimates become a physical plan
The Starburst query optimizer uses connector statistics to compare join orders and select the lowest computed cost among the alternatives considered. Large intermediate results matter because subsequent operations must process them.
Join order changes the work that follows
In our hypothetical report, suppose the region filter selects a small customer group. Compare a plan that narrows the customer input early with one that carries a much larger intermediate result into later steps.
Keep the example hypothetical until you inspect your own plan. A tidy explanation in a meeting is useful, but the actual SQL, available statistics, and connector behavior should decide whether that explanation survives review.
Record the estimated output at each important join. Then ask where the plan expects the biggest reduction. That point becomes a useful place to compare estimates with measured results when you run the statement later.
For example, imagine a join estimated to emit 1,000 rows actually emits 1,000,000. That hypothetical thousandfold mismatch is more actionable than describing the whole query as slow. It gives your investigation a specific operation to examine.
Check whether the join condition matches the relationship you intended. A report meant to connect each order to one customer deserves a different review from a report intentionally connecting orders to multiple customer events.
Broadcast joins trade movement for local memory
A broadcast join replicates its build input to participating workers. A partitioned join redistributes inputs by join key, with each worker handling a share. Broadcast requires the filtered build input to fit in each worker’s memory.
Use the following comparison to decide what to inspect, rather than declaring one plan shape the winner. Its entries describe documented behavior; the right choice for your workload still depends on the inputs and settings.
Planning choiceWhat it doesWhat to inspectBroadcast joinReplicates the build inputEstimated filtered build size and worker memoryPartitioned joinDistributes inputs by join keyData redistribution and input sizesAutomatic join ordering without usable costsFalls back to eliminating cross joinsMissing statistics or unavailable cost estimates
The comparison follows Starburst’s [join planning reference]. It also explains why missing statistics deserve attention before you start rearranging table names and hoping the next run behaves better.
Think about it this way: choosing a join strategy resembles planning deliveries. You need an estimate of the load before deciding where to send it. An elegantly drawn route cannot rescue a wildly inaccurate packing list.
Avoid turning a successful experiment into a permanent rule too quickly. If forcing a different distribution improves one query, save the evidence and repeat the comparison with representative inputs before proposing a wider settings change.
Performance matters to users beyond the query plan. In Starburst’s published Optum case study, the company reports ad hoc queries running ten times faster than Hive. That is a customer result, not a forecast for your workload.
“Providing users with one endpoint is so much easier.”
Mike Prior, Principal IO Engineer at Optum, [Starburst customer case study].
The useful lesson from that account is to measure outcomes your users notice. Pair plan analysis with completion times for actual reports. A lower estimated cost is helpful evidence, but it is not the finished business result.
What can stay close to the source
Planning also considers whether a connected source can perform parts of the work. Supported pushdown can move operations into that source, reducing the data returned for further processing. Support depends on the connector and underlying system. [Pushdown reference]
Predicate pushdown needs connector support
Predicate pushdown places supported filtering at the source. Projection pushdown limits the columns read and returned. Aggregation pushdown can also move supported grouping work, subject to the connector, function, and query structure. [Supported pushdown operations]
Get Samantha Blake’s stories in your inbox
Join Medium for free to get updates from this writer.
When reviewing a federated query, list the sources separately. Next to each one, record which work you expect it to perform. That small exercise prevents a capability demonstrated on one connector from becoming an assumption about every other connector.
Keep a small worksheet with the source name, expected filter, observed plan behavior, and follow-up question. Ask a colleague to review it before changing the SQL. Unstated assumptions are harder to challenge than a four-column record.
An aggregate successfully pushed to a source does not appear as a corresponding engine-side aggregate operator in the plan. Inspect the source scan too, so you understand where the work went. [Reading pushdown in a plan]
And keep your review tied to the business question. Removing columns can make a statement easier to inspect, but only if those columns are unnecessary. A faster report that omits required detail is a rather expensive misunderstanding.
Runtime filters learn something new
Fair warning: a planned runtime filter does not mean the engine already knows its values. Starburst’s runtime filtering collects candidate join values from the processed build input and uses them to restrict eligible probe-side work. [Runtime filtering documentation]
The plan can show filter assignments ahead of execution. Their useful values arrive as execution proceeds. Connector support determines how those filters can be applied, including whether they help skip partitions or file regions. [Filter planning and application]
For the revenue example, distinguish a literal region condition written into SQL from customer identifiers discovered after processing an input. Both may reduce later work, but they enter the process at different moments.
That timing distinction is easy to lose in a dashboard presentation. Label your evidence explicitly: planned filter, collected filter, observed reduction. It makes the explanation clearer for colleagues who need to act on the findings.
Published viewpoint, paraphrased: Alberto Miorin, Engineering Lead at Zalando, describes reliability, speed, and stability for S3 data lakes as reasons for selecting Starburst Enterprise.
Zalando’s published case study also reports a 50% reduction in infrastructure costs. Treat that as vendor-published customer evidence; the page does not establish that any single planner feature produced the entire saving.
Use customer stories to frame questions for your own evaluation. Ask which reports become usable, which operational costs change, and how results were measured. Keep those questions separate from claims about a particular filter or join.
How to test what the plan believes
The most useful next step is a comparison you can repeat. Save the SQL, the plan, the statistics you inspected, and the conditions of the run together so another engineer can follow your reasoning.
EXPLAIN estimates are not elapsed seconds
An EXPLAIN plan can show estimated output rows, output bytes, and CPU, memory, and network costs. The latter costs are relative comparison values, not measured resource units or a promised duration. Unknown values appear as ?.
Do not translate a cost number directly into seconds or money. Instead, identify the assumptions that make one candidate plan look preferable. That keeps your explanation aligned with what the displayed values actually represent.
Let me explain the practical sequence: inspect EXPLAIN first, then use EXPLAIN ANALYZE when you intend to execute the statement and collect runtime statistics. The second command runs the query.
Compare the same operator’s estimated and actual output. Start with the earliest large mismatch, then follow its downstream effects. This is a diagnostic approach, not a guarantee that the first mismatch explains the whole slowdown.
Choose comparison queries that represent ordinary usage as well as troublesome cases. Include a narrow reporting period and a broad one, for example. Record the parameters so a later reviewer can reproduce the business request precisely.
Missing statistics call for a specific fix
Statistics can become outdated when another system changes the data. Supported connectors allow collection through ANALYZE; others rely on the source’s statistics management or lack statistics support. Choose the remedy for your connector.
Make each tuning experiment answer one question. Refresh supported statistics, capture the new plan, and compare results under similar conditions. Changing statistics, SQL, and cluster size together may improve performance while leaving you unsure which change mattered.
Define success before the experiment: perhaps a report must finish within its scheduled window, or a dashboard must satisfy an agreed response target. Keep that acceptance criterion visible when discussing a technically interesting but operationally irrelevant plan change.
Some runtime corrections are documented too. With fault-tolerant execution enabled, supported plan adjustments can reorder partitioned join inputs using actual input sizes. That capability uses information discovered during execution.
Better metadata will still need human judgment
The May 29, 2026 Enterprise 480-e LTS release notes state that Iceberg extended statistics are always enabled and remove the earlier enablement properties. Check release-specific instructions before copying configuration advice from an older article.
Published viewpoint, paraphrased: Jitender Aswani, Senior Vice President of Engineering, and Emma Tippet, Head of Product at Starburst, argue that AI agents need explicit business context to select the right data and meaning.
My expectation is that plan review will become more important as teams accept more machine-written SQL. That is an editorial forecast, not a product roadmap. A statement can be executable while still answering the wrong business question.
Prepare by keeping metric definitions beside representative queries and their measured results. Give reviewers a record of what changed and why. Better statistical inputs help planning, while explicit business definitions help people judge whether the requested answer is appropriate.
So what does that mean for you? Read the Starburst query optimizer’s plan as a set of testable decisions. Inspect its inputs, check its estimates, and measure the run before deciding which change deserves to stay.
Questions about plans and statistics
Q: How do I see a Starburst query plan without running it?
A: Prefix the statement with EXPLAIN. It displays the plan and estimates without executing the query. EXPLAIN ANALYZE executes it and adds runtime information.
Q: Why does EXPLAIN show question marks for rows?
A: A question mark means the estimate is unknown. Inspect connector statistics support and available table statistics before assuming the displayed plan is fully costed.
Q: Will ANALYZE fix every slow federated query?
A: No. It collects statistics where supported; it does not make every connector expose the same capabilities. Establish whether missing or outdated statistics explain your problem first.
Q: Can Starburst know runtime filter values beforehand?
A: The plan can include a runtime filter before execution, but values collected from a join input are learned while that input is processed.
