Index on TimeSeries collection doesn't change performance

Working with Data
, ,
1

I’m using a timeseries collection to store trades of infrequently traded stocks.
The number of trades for each stock is between 1k to 30k and I’m using the stock ticker as the metadata field.

Sample document:

{
  price: Number,
  ticker: String,
  timestamp: Date()
}

I’ve only created one secondary index on the metadata field(ticker) and the queries are very performant.
For example, when I want to get the $topN orders based on price the responses I get are almost instantaneous even though .explain() suggests that all the documents for that specific ticker are being examined. When I use a normal collection for the same data and indexes, the queries are much slower.

While creating an index on {ticker: 1, price: -1} and sorting documents based on the price reduces the totalDocsExamined significantly, the query response times are not very different. So, I’m just wondering whether having secondary indexes is actually useful in this case or not? Does having them help when there’s more load on the database?

2

Hello @peter_g,

Indexes are created to improve query performance - query filter and/or sort operations. The index can be a Single Field Index or a Compound Index, in general.

Secondary Indexes (more than one) can be created for TimeSeries collection data for this purpose. In your case, all three fields are candidates to be part of an index. The index can be created specifically supporting a query filter and/or sort operation. Also, note that a single index can support multiple queries.

What indexes and what fields mainly depends upon how your queries are built. Another factor is that the order of the fields in a compound index matter; i.e., an index { fieldA: 1, fieldB: 1 } has a different purpose than that of the { fieldB: 1, fieldA: 1 }.

Reference: Add Secondary Indexes on metaField and timeField


So, I’m just wondering whether having secondary indexes is actually useful in this case or not?
Does having them help when there’s more load on the database?

For a small amount of data, it is possible there may not be a need for an index at all for a query to perform well. As the collection size grows and the query runtime increases, you will find creating correct indexes will help with the query performance. The Secondary Indexes are useful, like any other indexes on a regular collection, when properly applied. You already know the usage of explain() and the generated Query Plan can show the usage of a specific index on the filter and sort operations.

Reference: Indexing Strategies


While creating an index on {ticker: 1, price: -1} and sorting documents based on the price…

When you have a query with a sort operation and if you find "stage" : "SORT" in the Query Plan it means that it is an in-memory sort and the index is not applied.

I suggest you post the query and the query plan (with ‘executionStats’) in your reply, and it can help figure if there is any issue.