Small footprint Reflection rejected as 'Too Expensive'

Reflection 1 is a Raw Reflection on our ‘Project’ VDS with a footprint of 1.92MB
Reflection 2 is an Advanced Raw Reflection on our ‘Project’ VDS with 20 Columns and a footprint of 517.23 KB

I have a query that selects the same 20 columns that are found in Reflection 2, however
Reflection 2 is rejected as too expensive and Reflection 1 is selected instead.

I’ve attached the query profile. Can you explain why a Reflection would be rejected as ‘Too Expensive’ when it contains fewer columns and a smaller footprint than the Reflection that was chosen by the optimizer? (156.7 KB)

Has anyone else encountered this issue? I’m not clear on what I’m doing wrong. (using Dremio 3.1.6)

I can see from your profile that there are 3 reflections that match. The costs of using any of those 3 turns out to be identical, so Dremio is somewhat arbitrarily choosing between them.

The reason the cost is different is that the reflection is stored in columnar format. The cost of using a reflection is not affected by the number of columns in the reflection.

What about footprint size? Shouldn’t the reflection with the smallest footprint be selected?

The total footprint size of the reflection is not related to the cost associated with using the reflection in a query, because Dremio is able to efficiently read just the necessary columns, regardless of whether the reflection includes all columns or just a subset.

OK. Thanks for the explanation.