NuGet queries against asset attributes can be slow due to non-optimized indexes

Certain NuGet query/download/update requests from visual studio 2015 are reported to be slow in Nexus 3.1

Nexus 2.14 does not exhibit the slowness.

The following NuGet related log message from a customer indicates slow query performance:

2017-02-07 11:03:13,165-0600 INFO [qtp1490888539-26206] *UNKNOWN com.orientechnologies.common.profiler.OProfilerStub - $ANSI{green {db=component}} [TIP] Query 'SELECT FROM asset WHERE (LOWER(attributes.nuget.id) = "runtime.native.system" ) AND (bucket = #11:82 OR bucket = #11:83 OR bucket = #11:84 OR bucket = #11:86 OR bucket = #11:87 OR bucket = #11:88 OR bucket = #11:89 OR bucket = #11:90 ) ORDER BY attributes.nuget.download_count DESC, id ASC, version ASC LIMIT 40' fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index

asset.attributes is a distinct data structure from other properties like asset.name, asset.format etc. [ code reference|https://github.com/sonatype/nexus-internal/blob/e28b4254688e26ea6daae74b67ad85c3936db85e/components/nexus-repository/src/main/java/org/sonatype/nexus/repository/storage/MetadataNodeEntityAdapter.java#L53] - therefore this excludes this issue being a duplicate of NEXUS-12310 where a missing index on component name was the problem.

'attributes.nuget.id' is not handled with an index

Also there are no indices around attributes.nuget.id in AssetEntityAdapter, so these kind of queries are non-performant because they do table scans.

For NuGet, users can essentially write their own queries where they can do pretty much anything non-performant, but we should ensure the sunny day uses cases by common clients are supported in a performant manner.