Timeseries: Poor Performance on Indexed TimeField

Working with Data
, ,
#1

Hello,

I created two collections. One is a timeseries, the other is not.
I added an index to the ts field to both and then wrote 1’000’000 random documents to both.
The code to do that is as follows:

test> db.createCollection("test1", {timeseries: {timeField: "ts", metaField: "md", granularity: "seconds"}})
{ ok: 1 }
test> db.createCollection("test2")
{ ok: 1 }
test> db.test1.createIndex({ts: -1})
ts_-1
test> db.test2.createIndex({ts: -1})
ts_-1
test> for (var i = 1; i <= 100; i++) {
...     const docs = [...Array(10000)].map(_ => ({
.....         ts: new Date(new Date().getTime() - (Math.random() * 172800000)),
.....         md: "",
.....         value: Math.random() * 1000000
.....     }));
...     db.test1.insertMany(docs);
...     db.test2.insertMany(docs);
... }
test> db.test1.countDocuments()
1000000
test> db.test2.countDocuments()
1000000
test> db.test1.find({}).sort({ts: -1}).limit(1)
[
  {
    ts: ISODate("2021-12-20T18:45:43.956Z"),
    md: '',
    _id: ObjectId("61c0cf58435319318616399e"),
    value: 933482.8127467203
  }
]
test> db.test2.find({}).sort({ts: -1}).limit(1)
[
  {
    _id: ObjectId("61c0cf6143531931861660ae"),
    ts: ISODate("2021-12-20T18:45:43.956Z"),
    md: '',
    value: 933482.8127467203
  }
]

Querying the timeseries collection (test1) is MUCH slower than querying the regular collection (test2).

test1 query explain and stats: test> db.test1.find({}).sort({ts: -1}).limit(1).explain(){ explainVersion: - Pastebin.com
test2 query explain and stats: test> db.test2.find({}).sort({ts: -1}).limit(1).explain(){ explainVersion: - Pastebin.com

Is there something wrong with my query? Or the way I’m structuring my data?

I’ve tried using .hint() to force the timeseries collection to use the index but the performance is the same.

Would love any ideas or suggestions.

Thank you

1 Like