Query Plan Analysis in Dremio

1

Hello Team,

I am currently using Dremio version 25.0.5 with MySQL 5.7.32, and I have observed an behavior while executing queries.

When running the following query:

SELECT Cust_Sub_Id, Event_Date
FROM Voice_Seg_Fct
WHERE Event_Date = ‘2024-04-11’;

I noticed that the query plan displayed by Dremio is as follows:

SELECT Cust_Sub_Id
FROM (SELECT Cust_Sub_Id, Event_Date
FROM Voice_Seg_Fct) AS Voice_Seg_Fct
WHERE Event_Date = ‘2024-04-11’;

I have a couple of observations and questions regarding this:

  1. Is it possible to avoid the creation of the derived table as shown in the Dremio query plan?

  2. Could the use of derived tables impact memory usage or performance?

I would appreciate any insights or recommendations you might have on these observations.

Thank you for your assistance.

Best regards,
Ajay Babu Maguluri.

2

@AjayBabuM Seems like the ARP pushdown to MySQL is that. Are you able to share the query profile?

3

Hi @balaji.ramaswamy

Thanks for quick reply, Please find the query profile.
f8b64965-2480-4e59-8ac3-b5c54aa89bc4.zip (18.7 KB)

Best regards,
Ajay Babu Maguluri.

4

@AjayBabuM

The query you have issued on Dremio is

SELECT "Cust_Sub_Id" FROM MySQL69.EDA.Voice_Seg_Fct WHERE "Event_Date" = '2024-04-11'

That is been pushed down to MySQL as

SELECT `Cust_Sub_Id`
FROM (SELECT `Voice_Seg_Fct`.`Cust_Sub_Id`, `Voice_Seg_Fct`.`Event_Date`
FROM `EDA`.`Voice_Seg_Fct`) AS `Voice_Seg_Fct`
WHERE `Event_Date` = '2024-04-11'

Only the column Cust_Sub_Id is projected and Filter is also pushed down. It looks like the query is running super fast.