Timeseries: Very poor query performance although having index

It appears that indexing on Timeseries Metadata object field has issues.

I have these c# models:

 class Measurement
        private Metadata Metadata { get; set; }

        double Value { get; set; }

  public class Metadata
        public string SensorId { get; set; }
        public string SourceId { get; set; }


I queried a specific data set (created earlier) before and after inserting 10^9 documents in timeseries (different sensors).

The query uses an index on Metadata.SensorId:

myCollection.Indexes.CreateOne(new CreateIndexModel(Builders.IndexKeys.Ascending(x=>x.Metadata.SensorId ), new CreateIndexOptions()), new CreateOneIndexOptions());

I also tried to create an index using compass which produced exactly the same results.

The very same query that took ms before now runs for minutes!

Note that this only occurs if I use a “nested Index” on Metadata.SensorId*.

If I define a property SensorId as metaField and create an index for SensorId,
query performance is fine!

There really seems to be an issue with indexing Metadata objects!

Hi @Kay_Zander would you mind please sharing some additional information? Can you please share your query, the output of the explainPlan, your granularity setting, and a rough estimate of the cardinality of the inserted data set as it relates to Metadata, e.g. how many unique combinations do you expect there are for your Metadata object.


My bad:
I just found out why I experienced bad performance.
I forgot to update my filters to filter on **Metadata.**SensorId

   var filter = Builders<Measurement>.Filter.And(
            Builders<DataPoint>.Filter.Eq(eq => eq.Metadata.SensorId , sensorId )       
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.