Slow response while using $gte and $lte together.

[Fabio Batalha Cunha dos Santos]

Hello people,

I'm having some difficult to figure out why the combined use of $gte and $lte makes the queries extremely SLOOOOW.


1. A count of all records in my collection.
command: count
query: {}
result: 767226
time: 0:00:00.058039


2. A count using 2 indexes (collection, processing_date) using $lt, $gt (until know, everything is as expected and OK)
command: count
query: {'processing_date': {'$lt': datetime.datetime(2014, 12, 31, 0, 0), '$gt': datetime.datetime(2014, 1, 1, 0, 0)}, 'collection': 'scl'}
result: 20267
time: 0:00:00.906559


3. A count using 2 indexes (collection, processing_date) using $lt, $gte (until know, everything is as expected and OK)
command: count
query: {'processing_date': {'$lt': datetime.datetime(2014, 12, 31, 0, 0), '$gte': datetime.datetime(2014, 1, 1, 0, 0)}, 'collection': 'scl'}
result: 20267
time: 0:00:00.304122

4. A count using 2 indexes (collection, processing_date) using $lte, $gt (until know, everything is as expected and OK)
command: count
query: {'processing_date': {'$gt': datetime.datetime(2014, 1, 1, 0, 0), '$lte': datetime.datetime(2014, 12, 31, 0, 0)}, 'collection': 'scl'}
result: 20267
time: 0:00:00.071070

5. A count using 2 indexes (collection, processing_date) using $lte, $gte (NOT OK, Very SLOOW)
command: count
query: {'processing_date': {'$gte': datetime.datetime(2014, 1, 1, 0, 0), '$lte': datetime.datetime(2014, 12, 31, 0, 0)}, 'collection': 'scl'}
result: 20267
time: 0:00:13.859353

5. A explain of the brevious query using 2 indexes (collection, processing_date) using $lte, $gte, collecting the total ['executionStats']['nReturned'] (Strangely faster them the previous query)
command: explain
query: {'processing_date': {'$gte': datetime.datetime(2014, 1, 1, 0, 0), '$lte': datetime.datetime(2014, 12, 31, 0, 0)}, 'collection': 'scl'}
result: 20267
time: 0:00:01.469607


The same not happens if I use just the processing_date filter.

Questions:
1. Why ($gt, $lt), ($gte, $lt) and ($gt, $lte) response times are so different (faster) from $gte, $lte.
2. How can I improve my index to approximate the totalDocsExamined to the nReturned, using 2 indexes (collection, processing_date)?

mongos> db.articles.find({'collection': 'scl', 'processing_date': {'$gte': ISODate('2014-01-01'), '$lte': ISODate('2014-12-31')}}).explain('executionStats')['executionStats']
{
    "nReturned" : 20267,
    "executionTimeMillis" : 1669,
    "totalKeysExamined" : 68043,
    "totalDocsExamined" : 68043,
.....


mongos> db.articles.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "articlemeta.articles"
    }
...
...
    {
        "v" : 1,
        "key" : {
            "collection" : 1
        },
        "name" : "collection_1",
        "ns" : "articlemeta.articles"
    },
    {
        "v" : 1,
        "key" : {
            "processing_date" : 1
        },
        "name" : "processing_date_1",
        "ns" : "articlemeta.articles"
    }
]


Thanks in advance!

[Fabio Batalha Cunha dos Santos]

Answering the second question.

After creating a compounded index the query results become faster enough.

mongods> articles.createIndex({'collection': 1, 'processing_date': 1})

mongos> db.articles.find({'collection': 'scl', 'processing_date': {'$gte': ISODate('2014-01-01'), '$lte': ISODate('2014-12-31')}}).explain('executionStats')['executionStats']
{
    "nReturned" : 20267,

    "executionTimeMillis" : 1165,
    "totalKeysExamined" : 20267,
    "totalDocsExamined" : 20267,


But I'm still insterested to know about the first question.

1. Why ($gt, $lt), ($gte, $lt) and ($gt, $lte) response times are so different (faster) them $gte, $lte.


Regards,