# Query Pushdown in Joins **URL:** \ **Category:** Uncategorized\ **Created:** [August 26, 2018, 7:02am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715 "2018-08-26T07:02:44Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![HLNA](https://avatars.discourse-cdn.com/v4/letter/h/f1d935/32.png) [@HLNA](https://community.dremio.com/u/HLNA)\ **Post date:** [August 26, 2018, 7:02am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/1 "2018-08-26T07:02:45Z")
I tested a scenario with two tables (in RedShift & MySQL) joined on a key to return single row. Both of the following queries involved full-table scan. Are we supposed to make any settings? **Qry-01:** SELECT a.\* FROM ds\_redshift a JOIN ds\_mysql b ON a.id = b.id WHERE a.id = 123 and b.id = 123 **Qry-02:** SELECT a.\* FROM ds\_redshift a JOIN ds\_mysql b ON a.id = b.id WHERE a.id = 123 ---
**Author:** ![desidero](https://avatars.discourse-cdn.com/v4/letter/d/7bcc69/32.png) [@desidero](https://community.dremio.com/u/desidero)\ **Post date:** [August 27, 2018, 4:16am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/2 "2018-08-27T04:16:34Z")
Do you have the query profiles? If so, can you attach them? ---
**Author:** ![jduong](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jduong/32/21_2.png) [@jduong](https://community.dremio.com/u/jduong)\ **Post date:** [August 27, 2018, 4:28am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/3 "2018-08-27T04:28:09Z")
@HLNA This is a known issue that will be fixed in an upcoming release. In the mean time, would you be able to restructure your query so that the filters explicitly happen before the join using sub-queries? Eg for query 1: SELECT a.\* from (SELECT \* from ds\_redshift where id = 123) a join (SELECT \* FROM ds\_mysql where ID = 123) b ON a.id = b.id query 2: SELECT a.\* from (SELECT \* FROM ds\_redshift where id = 123) a join ds\_mysql b on a.id = b.id ---
**Author:** ![HLNA](https://avatars.discourse-cdn.com/v4/letter/h/f1d935/32.png) [@HLNA](https://community.dremio.com/u/HLNA)\ **Post date:** [August 27, 2018, 5:57am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/4 "2018-08-27T05:57:14Z")
@jduong I don’t have it handy… but, the query I shared is exactly the one being executed. I confirmed on both DB Server consoles that full table scan is happening. While I will try to fetch query profile next time, I am not sure why full table scan is initiated for this query as it it not complex and the predicate is clearly on a single id column 🤔 ---
**Author:** ![jduong](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jduong/32/21_2.png) [@jduong](https://community.dremio.com/u/jduong)\ **Post date:** [August 27, 2018, 6:48am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/5 "2018-08-27T06:48:38Z")
The reason is that the join won’t get pushed down (since this is across two different sources), but the planner is not pushing the filter to each join operand (this is being fixed). ---
**Author:** ![HLNA](https://avatars.discourse-cdn.com/v4/letter/h/f1d935/32.png) [@HLNA](https://community.dremio.com/u/HLNA)\ **Post date:** [August 27, 2018, 6:43pm UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/6 "2018-08-27T18:43:57Z")
Thanks @desidero and @jduong I am eagerly waiting for 2.1. Will test it. ---
**Author:** ![HLNA](https://avatars.discourse-cdn.com/v4/letter/h/f1d935/32.png) [@HLNA](https://community.dremio.com/u/HLNA)\ **Post date:** [August 27, 2018, 10:46pm UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/7 "2018-08-27T22:46:40Z")
hi @jduong I tested with sub-queries on the same large tables, which returned “No Data”. I created two test tables (across two datasources) and they returned the result (ds\_redshift+ ds\_mysql worked fine, which is test table with 3 or 4 records) ![26%20PM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/e8b55b916baa3622b866d4866dcc125806bc53a3.png) ![35%20PM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/cf24662dc7fc9675d27f3a296dd55b08dc031bf3.png) ---
**Author:** ![sudhko](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@sudhko](https://community.dremio.com/u/sudhko)\ **Post date:** [September 5, 2018, 6:12am UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/8 "2018-09-05T06:12:13Z")
I think the later query works, because the key in join operation happens to the first member of the select…this took us few iterations to figure out Not sure if this is getting addressed in the upcoming 2.1 release? ---
**Author:** ![Rahul](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@Rahul](https://community.dremio.com/u/Rahul)\ **Post date:** [July 29, 2019, 3:11pm UTC](https://community.dremio.com/t/query-pushdown-in-joins/1715/9 "2019-07-29T15:11:07Z")
Dears any update on this?