Reflection not picked up via Arrow Flight, but used via REST API and UI

1

Hi,

I’m encountering an issue where a reflection that works correctly when querying through the Dremio UI or REST API is not picked up when executing the same SQL query via Arrow Flight (Flight SQL).

In the query profile for the Flight execution, under the Acceleration tab, I see this message:

algebraic_matching: Skipping agg join matching due to limit. Number of joins: 33, Limit: 16

This message does not appear in the UI or REST execution of the same query, and in those cases, the reflection is applied successfully with AGGREGATE_REWRITE.

I’m wondering:

  • Why would the optimizer behavior differ between Arrow Flight and other access methods?
  • Is this join limit (16) specific to the execution context used by Flight SQL?
  • Can this limit be configured or increased to allow reflection matching in more complex queries via Flight?
  • Are there any planner settings or context options I should be setting explicitly when using Flight?

It seems like the number of joins is causing the reflection matching logic to skip, but only when using Arrow Flight. I’d need help to know whether this is expected behavior, and how to work around or resolve it.

Profiles:
rest-api-plan-with-reflections.zip (113.8 KB)
arrow-flight-without-reflections.zip (242.4 KB)
arrow-flight-with-reflections.zip (107.4 KB)

Build Info:

  • Build: 25.1.6-202501021803480419-127757a4
  • Edition: AWS Edition (activated)

Thanks in advance!

2

The main issue is that this reflection is missing in the “arrow-flight-without-reflecitons.zip” profile:

  • creative_performance_portfolio_grid (virtual.data.preview.app)
    • Raw Reflection (Type: raw, Refection Id: 18f6d4ec-ccf5-4f28-a509-64ec3b794c9f): considered, matched, chosen.

From the profiles you sent, I can see that the issue first occurred on Flight SQL and then 4 hours later, you ran the same query from the REST API and it successfully picked up the reflection. If you had ran the same query again through Flight SQL at that time, would the issue still reproduce?

You most likely are hitting an issue we fixed in 25.2.7:

Fixed a sporadic issue where a reflection was not used immediately after refresh. Improved performance of reflection management so that materializations are available for use sooner. Added additional reflection debug logging for support.