Slow inner join with where clause

1

Hello,
The push-down works pretty well but I have discovered a basic select where the push-down could be optimized.

SELECT U.LastName, T.Name
FROM deva.Data0001.dbo.Users U
inner join dev.Data0001.dbo.Trainings T ON U.User_ID = T.Lector_ID
where U.User_ID < 100

Dremio fetches all rows from both sources, then merges them and applies filter. It is not necessary to fetch all rows from table Users because the where clause can be pushed-down and the query will be much faster. Dremio fetches 22549 rows from table Users but needs only 30 rows.
bde45cb0-3781-4ae6-8789-1f96a200ffa9.zip (6.0 KB)

2

You Can modify like this:

SELECT U.LastName, T.Name
FROM deva.Data0001.dbo.Users U
inner join dev.Data0001.dbo.Trainings T ON U.User_ID = T.Lector_ID and U.User_ID < 100 and T.Lector_ID < 100