Query optimization while using skip and limit in Atlas search

MongoDB Atlas Atlas Search
, ,
1

HI
I am trying to $skip and $limit after $search in the aggregation. Each and every time when I try to increase my skip size the execution time gets longer
Example:

  • Skip 10 and limit 10 then execution time is 500ms
  • Skip 30 and limit 10 then execution time is 700ms
  • Skip 50 and limit 10 then execution time is 900ms
  • Skip 800 and limit 10 then execution time is 20Sec

My code:

db.collection.aggregate([
    {
        $search: {
            "index": 'search',
            "count": { "type": "total" },
            "compound": {
                "must": [{
                    "range": {
                        "path": "timestamp",
                        "gte": ISODate('2020-01-01'),
                        "lte": ISODate()
                    }
                },
                {
                    "text": {
                        "query": '(.*)info(.*)',
                        "path": ['field1', 'field2']
                    },
                },
                {
                    "near": {
                        "path": 'timestamp',
                        "origin": ISODate(),
                        "pivot": 7776000000
                    }
                }
                ],
            }
        }
    },
    { $skip: 10 },
    { $limit: 10 }
])

I need to know if is there any other way to optimise the query to get faster and if is there any way to specify ascending or descending order in the Atlas search index.

1 Like
3

Hi @Nanthakumar_DG - Welcome to the community :wave:

MongoDB still has to iterate over documents to skip them which explains what you were experiencing in the above quote.

Just wanting to understand more of the use case details here - Is the question about pagination of Atlas Search results? Could you provide more details on the intended use case?

Regards,
Jason

4

YEP, That’s about pagination each and every time when I go to the next page in UI response time is increasing as I mentioned in the Example.
I found the solution for that and made a change in the Atlas search index and that works well.
Ref: storing-source-fields
Thanks for the reply @Jason_Tran

1 Like
6

Hello @Nanthakumar_DG ,

Can you share your storedSource configuration please?

I have exactly the same problem with pagination.

7 
{
  "mappings": {
    "dynamic": false,
    "fields": {
      "lvl": {
        "type": "string"
      },
      "data": [
        {
          "dynamic": true,
          "type": "document"
        },
        {
          "type": "string"
        }
      ],
      "time": {
        "type": "date"
      }
    }
  },
  "storedSource": {
    "include": [
      "data",
      "lvl",
      "data"
    ]
  }
}
8

Hey @Nanthakumar_DG , we are working on improving this for Atlas Search currently, what was your expected latency for this query you were hoping to see? Did stored source help?

9

@Elle_Shwer Yes storedSource helps to improve the performance of the query timing but still, I need help in this search index the accuracy of the result is not as expected. I need a full-text search type indexing in my case can anyone help with that this index should also need to be sorted in descending, skip and limit.

10

Hey @Nanthakumar_DG since we posted this we have a sort solution that may also help here: https://www.mongodb.com/docs/atlas/atlas-search/sort/

Pagination is still in progress.

11

Can we vote somewhere for the pagination improvement? It’s becoming a bit of a problem for me (already using stored source etc.)

12

@Ruud_van_Buul you can vote here: Faster Pagination – MongoDB Feedback Engine

13

Hi, @Nanthakumar_DG

When you have a sequence with $skip followed by a $limit, the $limit moves before the $skip. With the reordering, the $limit value increases by the $skip amount.

For example, if the pipeline consists of the following stages:

{ $skip: 10 },
{ $limit: 5 }

During the optimization phase, the optimizer transforms the sequence to the following:

{ $limit: 15 },
{ $skip: 10 }

This optimization allows for more opportunities for $sort + $limit Coalescence, such as with $sort + $skip + $limit sequences. See $sort + $limit Coalescence for details on the coalescence and $sort + $skip + $limit Sequence for an example.

For aggregation operations on sharded collections, this optimization reduces the results returned from each shard.

More

14

Hi @Nanthakumar_DG and @Ruud_van_Buul , Sequential Pagination in Atlas Search is now available on MongoDB clusters 7.0.5+ and 6.0.13+.

Instead of using $skip/$limit, you can:

  1. generate Base64-encoded tokens for each document within your results using the new $meta keyword searchSequenceToken , and
  2. use that token as a point of reference with the new searchBefore and searchAfter options within the $search stage to specify what set of results you want to return

Would love for you to give it a try and get your feedback!

15

Hi @amyjian
Is this feature currently available for the C# driver?

Thanks
Sven