Hello Dremio Community,
I am experiencing a performance issue when querying a MySQL database from Dremio. My query is designed to select first and last names from a user_profile table where the state is ‘New York’. Here is the query:
SELECT firstname, lastname FROM user_profile WHERE state = 'New York'
After examining the job profile in Dremio, I observed that a subquery is being run during the JDBC scan phase. However, the filter condition pertaining to the state doesn’t appear to be applied within this subquery, which I suspect is negatively affecting the performance. The subquery as extracted from the job profile appears as follows:
Jdbc(sql=[SELECT `firstname`, `lastname` FROM (SELECT `user_profile`.`firstname`, `user_profile`.`lastname`, `user_profile`.`state` FROM `airflow_rnd`.`user_profile`) AS `user_profile` WHERE `state` = 'New York'])
As seen, the filter condition on the state is applied only at the outer level and not within the inner query, leading to unnecessary data retrieval that could potentially degrade performance.
I executed the same query on both Dremio Community Edition 24.3 and Dremio Community Edition 13.1, and the query execution plans were identical in these two versions. I am including the query profiles for both.
f476a3ed-0922-4601-b2a6-b49e9dff0801.zip (11.2 KB)
21645488-7fb3-4fbb-baef-2f9d14f4875c.zip (8.5 KB)
Has anyone encountered a similar issue or can provide insights into why the filter condition is not being pushed down as expected? Any suggestions on how to optimize this query or Dremio settings to improve the pushdown behavior would be greatly appreciated.
Thank you for your assistance!