# Create Reflection on Id column, filter where Id = x. Reflection won't work. WTF? **URL:** \ **Category:** Uncategorized\ **Created:** [October 28, 2025, 10:07am UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245 "2025-10-28T10:07:26Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![quangbilly79](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/quangbilly79/32/6549_2.png) [@quangbilly79](https://community.dremio.com/u/quangbilly79)\ **Post date:** [October 28, 2025, 10:07am UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245/1 "2025-10-28T10:07:26Z")
I can’t understand how Reflection works. At first, I thought it was just like Index, but no. I already read the Dremio doc about Reflection! Below is an example. Create a **Raw Reflection** on the **ShipmentId** column on **hive\_source.test\_iceberg.factorderrawscspos\_reflection** table ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/119cfd76a17292b2f5a7aab560d59cee667fac00.png) I run a simple query like below select ``` \* ``` from **hive\_source.test\_iceberg.factorderrawscspos\_reflection** where 1 = 1 **and ShipmentId = 3851793** ; But the plan said that **Reflection is not used** because it does **not cover the query**! WTF??? It’s literaly in the Where clause! As I understand, Dremio will save a list of **ShipmentId** with the **pointer to the corresponding row** in the original table. ShipmentId will be saved as a **Binary Tree data structure** for fast searching. When I query for ShipmentId x, Dremio will search in the Tree for the value x, and the row that it points to, then return that row (like searching the Table of Contents in a book). So why does my query not use Reflection? ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b178cec0c385efe8167a4d3a316dbc09a62ed36d.png) Please note that my table is very large (~ 1B of rows), the time is fast since it’s my 3rd query, my 1st query took 10s or something. ---
**Author:** ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)\ **Post date:** [October 29, 2025, 1:58pm UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245/2 "2025-10-29T13:58:50Z")
When you do a `SELECT *` you are selecting all the columns. However, your reflection has only one column `ShipmentId`. To test if the reflection is picked up, change your query to: ```sql SELECT ShipmentId FROM hive_source.test_iceberg.factorderrawscspos_reflection WHERE ShipmentId = 3851793; ``` Or, add the rest of the columns in the reflection (ideally, whichever columns you are actually interested in querying instead of `SELECT *`) ---
**Author:** ![quangbilly79](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/quangbilly79/32/6549_2.png) [@quangbilly79](https://community.dremio.com/u/quangbilly79)\ **Post date:** [October 30, 2025, 1:27am UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245/3 "2025-10-30T01:27:54Z")
Yep, it works, but do I have to create a raw reflection on every column? I mean, I just want to optimize where the query is on 1 or 2 columns. Creating Raw Reflection on every columns is just like create an index on every column, which is not optimized. If it’s the same as the Index, then why, in the case of “select \* where” query, the index works in other SQL databases (mysql, sql server, postgresql,…), but reflection won’t work in Dremio case with the same query? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 4, 2025, 6:21am UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245/4 "2025-11-04T06:21:00Z")
@quangbilly79 We have to look at the dashboards and see commonly queries columns. We also need to see of the queries are more aggregates then an agg reflection would be more effective. If your dashboards and reports say query 50 columns all the time and we have outliers then create a VDS on the 50 columns and create a reflection on the VDS. The outliers will go back to the base table but most of your queries (aim for 95%) will be fast, if there are specific columns we are always grouping by, create an agg reflection on those dimensions and select the measures the dashboards requires like SUM, MAX, MIN etc. Also another thing to look at is if the dashboard need all the data in the base table. Sometimes the table can have say 10 years of data but say 95% queries are hitting the last 2 years then your VDS can be on 50 columns with 2 years of data and queries using the reflection will be even faster as the reflection size will be much less, So all this depends on the nature of dashboards/reports. Thanks, Bali ---
**Author:** ![quangbilly79](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/quangbilly79/32/6549_2.png) [@quangbilly79](https://community.dremio.com/u/quangbilly79)\ **Post date:** [November 4, 2025, 7:04am UTC](https://community.dremio.com/t/create-reflection-on-id-column-filter-where-id-x-reflection-wont-work-wtf/13245/5 "2025-11-04T07:04:55Z")
@balaji.ramaswamy Thanks, but could you tell me more about how the Reflection in Dremio works behind the scenes (how data is stored, the data structure,…)? I mean, every open source DB engine like SQL Server, MySQL, Postgres,… there are many detailed documents on how the Index works, so that people know to optimize the index, which kind of query will take advantage of the index,… themself just by doing some researching. ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/6/6ccc21f2ae431e3bf4689718cc73c62906fd68f2.png) I googled lots about Dremio Reflection, but still can’t understand how it truly works (like the data structure,…) A simple problem like why select \* from tableX where colY = 1 When creating an Index on colY column on SQL Server, it helps the query run faster But when creating a Reflection on colY column on Dremio, it won’t work There are many other complex queries like merge, join,… I need to analyze. So I need to deeply understand how Reflection works behind the scenes. Since Dremio has an open source version, I don’t think it’s much of a unshareable secret or something. If I have time, I can check the source code or sth. But please could you tell me more about Dremio Reflection? Please!