# Optimized subselect for Mongo **URL:** \ **Category:** Uncategorized\ **Created:** [November 16, 2020, 4:04am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598 "2020-11-16T04:04:28Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![pazaan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/pazaan/32/2932_2.png) [@pazaan](https://community.dremio.com/u/pazaan)\ **Post date:** [November 16, 2020, 4:04am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/1 "2020-11-16T04:04:28Z")
I’m wanting to use Dremio to be able to make and use reflections that help me optimize the underlying queries to Mongo (by using the indexes I have on our operational database). For example, I’d like to have a table with a reflection for all of our user IDs. I have this set up, and no problem so far. I would like to use one of the indexes on the Mongo collection that is based on userID. So, I thought that I’d be clever and do something like: `SELECT field1, field2 FROM source.db.collection WHERE userId IN (select userId from Test.“users”) AND type = ‘data\_type’", hoping that Dremio would evaluate the subselect first and use Mongo’s “$in” operator, or even better - parallelize the requests to Mongo. What happens is that Dremio does a full collection scan on the Mongo collection, because it’s only requesting the “type” query to Mongo (it must be doing the subselect matching on the Dremio side). Is there a different way that I can achieve this in Dremio? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 17, 2020, 4:04am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/2 "2020-11-17T04:04:50Z")
@pazaan Great question. Can you please share the profile so we can suggest? > **[How To Share A Query Profile](https://www.dremio.com/tutorials/share-query-profile-dremio/)** > > Tutorial explaining how to share a query profile in Dremio. ---
**Author:** ![pazaan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/pazaan/32/2932_2.png) [@pazaan](https://community.dremio.com/u/pazaan)\ **Post date:** [November 18, 2020, 2:55am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/3 "2020-11-18T02:55:59Z")
Sure! [9513b997-e7f0-4ef8-add2-037cf88234cc.zip](https://community.dremio.com/uploads/short-url/bITsIaATdlY0JUqVfzSKnl3JZXr.zip) (51.7 KB) ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 18, 2020, 5:26am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/4 "2020-11-18T05:26:42Z")
@pazaan It does look like the FILTER is getting push down to Mongo > MongoScan(table=[[QA2, data, deviceData]], MongoQuery=[use data; db.deviceData.find({“$and”: [{“$and”: [{“type”: {“$ne”: null}}, {“type”: {“$eq”: “upload”}}]}, {“\_active”: true}]}, {“type”: 1, “\_active”: 1, “uploadId”: 1, “\_userId”: 1})]) Query ran for 2m49s out of which 2m30s was spent waiting for Mongo to return records. I see that the plan is in a single phase and hence single threaded (Phase 0 is single threaded). Is 2m49s for the background refresh not acceptable? Thanks Bali ---
**Author:** ![pazaan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/pazaan/32/2932_2.png) [@pazaan](https://community.dremio.com/u/pazaan)\ **Post date:** [November 24, 2020, 5:32am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/5 "2020-11-24T05:32:42Z")
That’s passing down the projection for the `_userId`, but what I had _hoped_ to see is something like: ```auto use data; db.deviceData.find({"$and": [{"$and": [{“type”: {"$ne": null}}, {“type”: {"$eq": “upload”}}]}, {"_active": true}, { "_userId": {"$in": ["userId1", "userId2"]}}]}, {“type”: 1, “_active”: 1, “uploadId”: 1, “_userId”: 1})]) ``` ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 24, 2020, 8:00am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/6 "2020-11-24T08:00:42Z")
@pazaan I do not find a filter for userid but only find it for type and active, am I missing something? ---
**Author:** ![pazaan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/pazaan/32/2932_2.png) [@pazaan](https://community.dremio.com/u/pazaan)\ **Post date:** [November 26, 2020, 12:32am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/7 "2020-11-26T00:32:54Z")
Right. Hence my initial comment asking whether there was a way for that filter to come from the subselect statement: `SELECT field1, field2 FROM source.db.collection WHERE userId IN (select userId from Test.“users”) AND type = ‘data_type’` ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 26, 2020, 6:17am UTC](https://community.dremio.com/t/optimized-subselect-for-mongo/6598/8 "2020-11-26T06:17:59Z")
@pazaan If you run the push down in the way you are suggesting, does the query run faster ? \< 3 minutes. If the performance is much better then that would require a code change so wondering what is the performance impact of the current push down? Thanks Bali