Couchdb 2 _find query not using index

Question

I'm struggling with something that should be easy but it's making no sense to me, I have these 2 documents in a database:

{ "name": "foo", "type": "typeA" },
{ "name": "bar", "type": "typeB" }

And I'm posting this to _find:

{
    "selector": {
        "type": "typeA"
    },
    "sort": ["name"]
}

Which works as expected but I get a warning that there's no matching index, so I've tried posting various combinations of the following to _index which makes no difference:

{
    "index": {
        "fields": ["type"]
    }
}
{
    "index": {
        "fields": ["name"]
    }
}
{
    "index": {
        "fields": ["name", "type"]
    }
}

If I remove the sort by name and only index the type it works fine except it's not sorted, is this a limitation with couchdbs' mango implementation or am I missing something?

Using a view and map function works fine but I'm curious what mango is/isn't doing here.

lossleader · Answer 1 · 2017-09-25T12:25:38.053

With just the type index, I think it will normally be almost as efficient unless you have many documents of each type (as it has to do the sorting stage in memory.)

But since fields are ordered, it would be necessary to do:

{
    "index": {
        "fields": ["type", "name"]
    }
}

to have a contiguous slice of this index for each type that is already ordered by name. But the query planner may not determine that this index applies.

As an example, the current pouchdb-find (which should be similar) needs the more complicated but equivalent query:

  {
    selector: {type: 'typeA', name: {$gte: null} },
    sort: ['type','name']
  }

to choose this index and build a plan that doesn't resort to building in memory for any step.

With that index I get the error "No index exists for this sort, try indexing by the sort fields.", which is the same error I get for everything I've tried except indexing on just the name. — Julian, Sep 24 '17 at 17:04

Couchdb 2 _find query not using index

1 Answers1