Mango index "does not contain a valid index for this query" even when specified manually

Question

I'm trying to efficiently query data via Mango (as that seems to be the only option given my requirements Searching for sub-objects with a date range containing the queried date value), but I can't even get a very simple index/query pair to work: although I specify my index manually for the query, I'm told that my index "was not used because it does not contain a valid index for this query. No matching index found, create an index to optimize query time."

(I'm doing all of this via Fauxton on CouchDB v. 3.0.0)

Let's say my documents look like this:

{
    "tenant": "TNNT_a",
    "$doctype": "JobOpening",
    // a bunch of other fields
}

All documents with a $doctype of "JobOpening" are guaranteed to have a tenant property. The searches I wish to perform will only ever be for documents with $doctype of "JobOpening" and a tenant selector will always be provided when querying.

Here's the test index I've configured:

{
   "index": {
      "fields": [
          "tenant",
          "$doctype"
      ],
      "partial_filter_selector": {
        "\\$doctype": {
          "$eq": "JobOpening"
        }
      }
   },
   "ddoc": "job-openings-doctype-index",
   "type": "json"
}

And here's the query

{
   "selector": {
      "tenant": "TNNT_a",
      "\\$doctype": "JobOpening"
   },
   "use_index": "job-openings-doctype-index"
}

Why isn't the index being used for the query?

I've tried not using a partial index, and I think the $doctype escaping is done properly in the requisite places, but nothing seems to keep CouchDB from performing a full scan.

score 1 · Accepted Answer · answered Mar 22 '21 at 12:12

The index isn't being used because the $doctype field is not being recognized by the query planner as expected.

Changing the fields declaration from $doctype to \\$doctype in the design document solves the issue.

{
   "index": {
      "fields": [
          "tenant",
          "\\$doctype"
      ],
      "partial_filter_selector": {
        "\\$doctype": {
          "$eq": "JobOpening"
        }
      }
   },
   "ddoc": "job-openings-doctype-index",
   "type": "json"
}

After that small refactor, the query

    {
       "selector": {
          "tenant": "TNNT_a",
          "\\$doctype": "JobOpening"
       },
       "use_index": "job-openings-doctype-index"
    }

Returns the expected result, and produces an "explain" which confirms the job-openings-doctype-index was queried:

{
 "dbname": "stack",
 "index": {
  "ddoc": "_design/job-openings-doctype-index",
  "name": "7f5c5cea5acd90f11fffca3e3355b6a03677ad53",
  "type": "json",
  "def": {
   "fields": [
    {
     "tenant": "asc"
    },
    {
     "\\$doctype": "asc"
    }
   ],
   "partial_filter_selector": {
    "\\$doctype": {
     "$eq": "JobOpening"
    }
   }
  }
 },
// etc etc etc

Whether this change is intuitive or not is unclear, however it is consistent - and perhaps reveals leading field names with a "special" character may not be desirable.

Regarding the indexing of the filtered field, as per the documentation regarding partial_filter_selector

Technically, we don’t need to include the filter on the "status" [e.g. $doctype here] field in the query selector ‐ the partial index ensures this is always true - but including it makes the intent of the selector clearer and will make it easier to take advantage of future improvements to query planning (e.g. automatic selection of partial indexes).

Despite that, I would not choose to index a field whose value is constant.

Thanks for answering! I had tried with escaping `\\$doctype` everywhere, but it seems I got tripped up by Fauxton: it appears the first query reports no index was used (because it's being built?) but subsequent queries (event rerunning the same one) do indeed use the index. Unfortunately, it appears that when you index an array attribute and attempt to match an item with `$elemMatch` (which was my main objective https://stackoverflow.com/questions/66674336/searching-for-sub-objects-with-a-date-range-containing-the-queried-date-value) then index ends up being wholly ignored anyway... — David Sulc, Mar 24 '21 at 16:15

Mango index "does not contain a valid index for this query" even when specified manually

1 Answers1