I am puzzled about the way Dremio is or is not accelerating queries on the same dataset for the same results, depending on how I am querying, or how the reflections are defined.
The source data is a parquet dataset stored in minio containing the following columns:
numberA: Integer
idA: Integer
numberB: Integer
idB: Integer
start_ts: DateAndTime
end_ts: DateAndTime
unused_int: Text
unused_string: Text
record_type: Text
I have added a VDS on top of this with an added column for partitioning (DATE_TRUNC of start_ts). On top of this VDS, I have two RAW reflections with all but the unused_* columns selected, partitioned on day_date. One sorted on numberA, and one sorted on numberB
I have attached the view definitions and 3 profiles.
All 3 profiles gives the same resultset on the same underlying data.
Profile 1:select_from_grouping_view_not_accelerated: Querying through v_unidirectional. Query is not accelerated
Profile 2:select_from_combined_and_group_accelerated: Same query as above, but querying directly on v_combined, using the query defined in v_unidirectional with an added WHERE clause. Query is accelerated by both RAW reflections
Profile 3:select_from_grouping_view_accelerated_by_a_all_cols_in_reflections: Querying through v_unidirectional. Query is now accelerated by one of the RAW reflections (after including the two unused_* columns in the raw reflections)
These queries are all fairly fast due to the size of the dataset. (Only 20 mio rows). In our actual setup, the difference between the queries from profile 1 and 2 is 25 minutes vs. 8 seconds.
Unfortunately, I can’t share profiles from this setup, so I have spent some time reproducing the issue on a mock dataset.
My questions are:
Why is the query in profile 1 not accelerated when the query in profile 2 is?
Why is the query in profile 3 accelerated when the query in profile 1 is not? The only difference is addition of unused columns to the RAW reflection. Our real dataset has more columns. All columns in the source dataset must be included for the query to be accelerated.
Profile 1 and 2 both show the same number of input rows. How is this possible if the profile 1 query is not accelerated? The source data is not sorted by numberA
How do I tell what makes Dremio choose not to accelerate in Profile 1?
profile 1 - Dremio did match but compared cost of going back to the PDS and the cost estimates came out going to the PDS as less expensive. Now this could be a wrong estimate. If the query SLA ~ 2s is good then you should be fine
Query was NOT accelerated
reflection_use_test (janj)
byNumberB (raw): considered, matched, not chosen.
byNumberA (raw): considered, matched, not chosen.
What you can also do is select all columns of the reflection and see if the reflection gets picked and that is what is happened on the 3rd profile, the default reflection was used which just substitutes the reflection and we can see it is about 1s faster
Thanks for the quick reply @balaji.ramaswamy. I do know that I can see that a reflection was matched but not chosen. I was looking for something more concrete as to why it was not chosen.
2 seconds would be more than fast enough, but as mentioned, on the dataset where I first experienced the issue, the difference between accelerated and non-accelerated execution is 8 seconds vs. 25+ minutes. This was just a mock dataset created to reproduce the issue so I could send you the profiles.
None of the views use all columns of the dataset, so I didn’t understand why it would make a difference to enable all columns. Apparently, the concept of the default reflection is what makes a difference here.
NB! In all three scenarios I had only two reflections on the dataset. The difference is that in scenario 3 I had enabled all columns on both reflections, while in scenario 1 and 2 two unused columns where not enabled on the reflections. So, in 1 and 2, no “default” reflection existed.
Two things still don’t make sense though:
Why this requirement to enable all columns in order for the “default” reflection to be used. It should be enough to enable the columns that are requested in the query. In a true big-data scenario, this can make a big difference to the size of the reflection. Our actual PDS has a lot more columns and is many magnitudes of size larger than the mock dataset I used here.
Why querying through the final view v_unidirectional is not accelerated, while executing the exact same query as in the view, but with the where clause included here instead of querying the view with a where clause is not accelerated.
This means that all users/applications that need the functionality of the final view would need to know the business logic of the view, and would need to be updated if that logic changes.
Is there some more logging that can be enabled to see why the optimizer chooses the path it does, or can you point me to the right place in the code, so I can try to get a better understanding of this.
Unfortunately, this is not the first scenario where I have had to hand hold the optimizer and do trial and error changes to the way we query to get the performance we need. This would be easier to do with a better understanding of how the optimizer work.
@dotjdk For #1 you can create the VDS with less # of columns and then select all columns in the VDS so default can be used
For #2 is it one of the profiles already attached, let me take a look, if not please attach one
As for explaining why you had matched, not chosen, if you turn on planner.verbose_profile it would tell you the cost stimates of each step, then you can compare the best cost replacement plan in the acceleration tab to the logical planning box in the planning tab (the top most line in both boxes) you will see in the logical planning the cumulative cost in terms of cpu/io/memory/network was less
Thanks. We already have verbose_profile enabled. I will try to dig a bit more in the output to get a better understanding.
For #2 … Profile 1 is the one where I am querying through a VDS that has the group by… Profile 2 is the exact same query, except I use the query from the VDS with the group by and add the WHERE clause on that
For #1 … I will try that. It does still seems strange to me that it should be necessary It kind of defeats the point of being able to deselect fields in the reflection UI
Looking at the best cost replacement plans, I can see that
when querying through the VDS (v_unidirectional) which has the GROUP BY, the filter is not pushed down to the FileSystemScanDrel, but applied after the UnionRel (See Profile 1 in the original post)
when querying using the GROUP BY query from that VDS with the WHERE clause applied directly, the filter is pushed down to FileSystemScanDrel on the accelerators, so a lot less rows are returned to the UnionRel (See Profile2 in the original post)
I would expect that the query optimizer would be able to realize that this optimization (pushing the where clause down to the file scans) is possible when querying on the v_unidirectional directly
In the following three queries, only the first one is accelerated, even though they are all semantically doing exactly the same
SELECT "numberA", MAX("idA") AS "idA", "numberB", MAX("idB") AS "idB", "start_ts", "end_ts", "record_type", "day_date"
FROM "janj"."v_combined" where numberA = 1000024970
GROUP BY "numberA", "numberB", "start_ts", "end_ts", "record_type", "day_date"
--SELECT * FROM (
-- SELECT "numberA", MAX("idA") AS "idA", "numberB", MAX("idB") AS "idB", "start_ts", "end_ts", "record_type", "day_date"
-- FROM "janj"."v_combined"
-- GROUP BY "numberA", "numberB", "start_ts", "end_ts", "record_type", "day_date"
--) where numberA = 1000024970
--SELECT * FROM "janj"."v_unidirectional" where numberA = 1000024970
UNLESS all fields from the source PDS are enabled on the reflections, in which case,
the first version is accelerated by both reflections byNumberA and byNumberB
the second and third versions are accelerated by byNumberA only
It kind of defeats the point of being able to deselect fields in the reflection UI