Alert all the time - Query Targeting: Scanned Objects / Returned has gone above 1000

I keep receiving this error in my email.
The Perfomance Advisor does not give me any recommendation.
My Profiler looks like this: (I dont know exactly if thats good or not).

What is the problem?

It says Keys Examined more than 100k and return 10, but my query is limiting to 10 so thats what I want. Is it a problem?

Hi Foco!

Thank you for reaching out! Without understanding your system more, it’s hard to say exactly what the problem is. However, in general, I’d recommend starting with the Metrics tab and taking a look at the “Query Targeting” graph to get an understanding of how much work your database is doing versus how much work you’re getting out. I would also recommend taking a look at the Query Profiler and setting the Display dropdown in the top left to “Examined:Returned Ratio”. Here, if you see any slow queries with a high ratio, you can click into the individual operation to learn more.
To do this, click on the dot on the scatter plot and then click on the “View More Details” button.

In the View More Details page, you will see some JSON with more information about the slow operation. In this JSON, I typically like to focus in on a few fields.

  • plansummary will show you whether you are running a collection scan or an index scan. If you see COLLSCAN, that’s a good indication that there may be an index that could benefit your read performance.
  • docsexamined
  • nreturned

Performance Advisor will look at the last 24 hours of your slow logs and make index suggestions. These recommendations may take a bit to show in the Performance Advisor page. However, without understanding your system fully, it’s hard to say whether an index will definitely improve your performance. I’d start with the steps above to see if you have any collection scans and dig in deeper from there.
If you have any further questions, please feel free to reach out to me and/or open a support case so we can provide you further support!

Thanks,
Frank

2 Likes

Perfomance advisor advised me to create one new index, but it is not on the collection that is getting this warnings.

Here is my profiler:

But the point is, in my query, Im limiting to return only 10 items. So it is kinda obvious that it will scan a lot of items to return only a few.

Here is my query:

Mint.aggregate([
                {
                    $match: {
                        "createdAt": {
                            $gte: finalUnix
                        }
                    }
                },
                {
                    $project: {
                        contract: 1,
                    }
                },
                {
                    $group: {
                        _id: '$contract',
                        totalMints: { $sum: 1 }
                    }
                },
                {
                    $sort: {
                        "totalMints": -1
                    }
                },
                {
                    $limit: 10
                }
            ])

Hi Foco,

Thanks for your followup! Performance Advisor also factors in how often a query is being run as well as a variety of other factors. This way it doesn’t suggest any extraneous indexes for one-off or infrequent queries. Having too many indexes on a collection can be detrimental and impact write performance. This is also why we also have the “Drop Index Recommendations” capability to catch any unused, duplicate, or hidden indexes.

To address your main point, I understand you’re limiting the query to only return 10 items, which is why the query targeting alert is triggered. In general, query targeting should be kept as low as possible, under the 1k threshold. You could achieve this either by improving the query and/or by adding an effective index to that namespace to bring down the number of documents scanned. My personal recommendation would be to investigate these two options, as it looks like the operational latency for your namespaces is quite high (based on the Profiler screenshot).

Thanks,
Frank

1 Like