How can i improve the execution time of my CouchDB query?

Question

I am storing a simple class consisting of the following data in my CouchDB. The Definition class just contains a list of points and additional basic data.

public class Geometry : CouchDocument
{
    public Guid SyncId { get; set; }
    public DateTimeOffset CreatedOn { get; set; }
    public Definition Definition { get; set; }
}

The SyncId in this example, is a unique id, which i use to identify geometries within different micro services of my software. So i use it as primary key for the documents.

I created an index like this:

{
   "index": {
      "fields": [
         "syncId"
      ]
   },
   "name": "sync-id-index",
   "type": "json"
}

When i now try to run a query on the CouchDB using the $In-operator or even just doing a syncid=X1 OR syncid=X2 etc, it uses the index i created. However, it takes 16 seconds for the query to finish. If i delete the index, it takes only 4 seconds.

{
   "selector": {
      "syncId": {
         "$in": [
            "ca7be6e4-dc11-4ddf-99f3-c97f544bf998",
            "716726b9-5493-498c-b207-d4b7e63f1ef3",
            "cb6c4941-7b33-445b-8988-361930f9b39a",
            "564fc2d5-3713-4b2b-b2e5-7dd79ef4509c",
            "6c9845e3-39fa-4a3f-acb7-86a362665a13",
            "15bb9836-3bd1-42b3-b12c-5a1025490d20",
            "a0e15e75-292f-4c76-959f-8adc5e569a31",
            "39b056bf-4ff9-4ada-9a44-9552801b52c4",
            "20d9e3bf-3e32-4426-850a-86422771897a",
            "9f262c8c-e493-4bec-9871-ed612a698a8c"
         ]
      }
   }
}

How can i improve the index or this query to improve the performance and lower the execution time?

Answer 1

So i use it as primary key for the documents.

If syncId is your primary key, consider making it the _id field in CouchDB. That would be by far the most efficient way to query the documents. You can then post to the _all_docs endpoint and specify which keys you want returned, which will be very efficient. Remember to also set "include_docs": true to get the actual documents and not only the revisions.

Something like this:

POST /geometry/_all_docs HTTP/1.1
Accept: application/json
Content-Type: application/json
Host: localhost:5984

{
    "include_docs": true,
    "keys" : [
        "ca7be6e4-dc11-4ddf-99f3-c97f544bf998",
        "716726b9-5493-498c-b207-d4b7e63f1ef3",
        "cb6c4941-7b33-445b-8988-361930f9b39a",
        "564fc2d5-3713-4b2b-b2e5-7dd79ef4509c",
        "6c9845e3-39fa-4a3f-acb7-86a362665a13",
        "15bb9836-3bd1-42b3-b12c-5a1025490d20",
        "a0e15e75-292f-4c76-959f-8adc5e569a31",
        "39b056bf-4ff9-4ada-9a44-9552801b52c4",
        "20d9e3bf-3e32-4426-850a-86422771897a",
        "9f262c8c-e493-4bec-9871-ed612a698a8c"
    ]
}

Some more information on _all_docs