Query optimization while using skip and limit in Atlas search

Question

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 the execution time is 500ms
Skip 30 and limit 10 then the execution time is 700ms
Skip 50 and limit 10 then the execution time is 900ms
Skip 800 and limit 10 then the 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.

score 0 · Answer 1 · answered Nov 11 '22 at 12:57

When you skip "x" number of documents, those documents are still read into the memory, hence you see the query time increasing with increasing the skip count. It's just that the skipped documents are not served back to the driver. A recommended way to optimize is to look for ways which could allow you to implement filter based pagination such as filtering based on timestamp (getting 7 days documents per page or 1 month documents per page), etc, instead of skipping the entire lot. Just ensure that the query arising out of this filter based pagination is supported by an index.

Query optimization while using skip and limit in Atlas search

1 Answers1