Mango Query _id $in very slow

[csemio]

Hi team,
I'have a problem with a very simple query

I'have a couchDb 3.0.0 with a database with 26 Millions of docs with n=3&q=2
Doc sample :

{
  "_id": "00000018-59A3-4A39-A821-D7D449EC7568",
  "_rev": "1-f5b34e9481d78d955d006765c4f2405a",
  "burnedEnergy": 25,
  "distance": 318,
  "duration": 243,
  "end": "2021-03-21T15:45:00Z",
  "endT1": "2021-03-21T16:45:00+01:00",
  "sensorOff": false,
  "start": "2021-03-21T15:30:00Z",
  "startT1": "2021-03-21T16:30:00+01:00",
  "steps": 400,
  "userID": 2500
}

I'have created an index like this :

{
 "type": "json",
 "partitioned": false,
 "def": {
  "fields": [
   {
    "userID": "asc"
   },
   {
    "_id": "asc"
   }
  ]
 }
}

and my query is :

{
  "userID":28,
  "_id":{"$in":["001D4673-DF53-4B1D-B943-54F9C4CD58EC","0287AB57-5046-4133-932A-7E795E56DB2A"]}
  }
}

I have also tried to add "_id":{"$gte": null}

this query take more than 26 seconds

If i explain it, it's seems to use the index :

{
  "dbname": "activity-logs3",
  "index": {
    "ddoc": "_design/36270c1c4dfbaede78808909216ea66483b84b35",
    "name": "userid-docid-index",
    "type": "json",
    "partitioned": false,
    "def": {
      "fields": [
        {
          "userID": "asc"
        },
        {
          "_id": "asc"
        }
      ]
    }
  },
  "partitioned": false,
  "selector": {
    "$and": [
      {
        "userID": {
          "$eq": 28
        }
      },
      {
        "_id": {
          "$in": [
            "001D4673-DF53-4B1D-B943-54F9C4CD58EC",
            "0287AB57-5046-4133-932A-7E795E56DB2A"
          ]
        }
      }
    ]
  },
  "opts": {
    "use_index": [],
    "bookmark": "nil",
    "limit": 25,
    "skip": 0,
    "sort": {},
    "fields": "all_fields",
    "partition": "",
    "r": [
      49
    ],
    "conflicts": false,
    "stale": false,
    "update": true,
    "stable": false,
    "execution_stats": true
  },
  "limit": 25,
  "skip": 0,
  "fields": "all_fields",
  "mrargs": {
    "include_docs": true,
    "view_type": "map",
    "reduce": false,
    "partition": null,
    "start_key": [
      21699
    ],
    "end_key": [
      21699,
      "<MAX>"
    ],
    "direction": "fwd",
    "stable": false,
    "update": true,
    "conflicts": "undefined"
  }
}

I don't knows what's going wrong.

[natcohen]

You should look at this #1549 and this #1852

Long story short, use Lucene for $or and $in queries, if you can...