index with $or operator not working

[brifai]

Description

I am trying to create an index for a query with "$or" operator but the query plan does not recognize the index.
my question is, how to create a working index for the following query ?

{
   "selector": {
      "type": "project",
      "$or": [
         {
            "pmId": "8b1cee44bf16ccced9163adee20008a8"
         },
         {
            "sponsor._id": "8b1cee44bf16ccced9163adee20008a8"
         },
         {
            "AccessMembers": {
               "$elemMatch": {
                  "$and": [
                     {
                        "id": "8b1cee44bf16ccced9163adee20008a8"
                     },
                     {
                        "ACL": {
                           "$in": [
                              "View",
                              "Edit"
                           ]
                        }
                     }
                  ]
               }
            }
         }
      ]
   }
}

Steps to Reproduce

Expected Behaviour

Your Environment

• CouchDB Version used:
  v. 2.1.1
• Browser name and version:
• Operating System and version:
  Windows Server 2016 Datacenter

Additional context

[wohali]

What is your Mango index definition?
What does the `/{db}/_explain' endpoint say when you use it?

I would think in this situation you'd need every single field you're referencing in your index: pmId, sponsor._id, and ACL.

[brifai]

thank you @wohali for the reply.
the index is as following:

{
	"index": {
		"fields": [
			"type",
			"pmId",
			"sponsor._id",
			"AccessMembers.[].id",
			"AccessMembers.[].ACL"
		]
	},
	"name": "pm-projects-index",
	"type": "json"
}

the "explain" result as following:

{
 "dbname": "pm",
 "index": {
  "ddoc": null,
  "name": "_all_docs",
  "type": "special",
  "def": {
   "fields": [
    {
     "_id": "asc"
    }
   ]
  }
 },
 "selector": {
  "$and": [
   {
    "type": {
     "$eq": "project"
    }
   },
   {
    "$or": [
     {
      "pmId": {
       "$eq": "8b1cee44bf16ccced9163adee20008a8"
      }
     },
     {
      "sponsor._id": {
       "$eq": "8b1cee44bf16ccced9163adee20008a8"
      }
     },
     {
      "AccessMembers": {
       "$elemMatch": {
        "$and": [
         {
          "id": {
           "$eq": "8b1cee44bf16ccced9163adee20008a8"
          }
         },
         {
          "ACL": {
           "$in": [
            "View",
            "Edit"
           ]
          }
         }
        ]
       }
      }
     }
    ]
   }
  ]
 },
 "opts": {
  "use_index": [],
  "bookmark": "nil",
  "limit": 25,
  "skip": 0,
  "sort": {},
  "fields": "all_fields",
  "r": [
   49
  ],
  "conflicts": false,
  "stale": false,
  "update": true,
  "stable": false,
  "execution_stats": false
 },
 "limit": 25,
 "skip": 0,
 "fields": "all_fields",
 "mrargs": {
  "include_docs": true,
  "view_type": "map",
  "reduce": false,
  "start_key": null,
  "end_key": "",
  "direction": "fwd",
  "stable": false,
  "update": true,
  "conflicts": "undefined"
 }
}

[garrensmith]

The simplest is just creating an index on type. The add in the execution stats to see how many docs are ignored or not.

…

________________________________ From: brifai <notifications@github.com> Sent: Saturday, June 29, 2019 12:19 PM To: apache/couchdb Cc: Subscribed Subject: Re: [apache/couchdb] index with $or operator not working (#2063) thank you @wohali<https://github.com/wohali> for the reply. the index is as following: { "index": { "fields": [ "type", "pmId", "sponsor._id", "AccessMembers.[].id", "AccessMembers.[].ACL" ] }, "name": "pm-projects-index", "type": "json" } the "explain" result as following: { "dbname": "pm", "index": { "ddoc": null, "name": "_all_docs", "type": "special", "def": { "fields": [ { "_id": "asc" } ] } }, "selector": { "$and": [ { "type": { "$eq": "project" } }, { "$or": [ { "pmId": { "$eq": "8b1cee44bf16ccced9163adee20008a8" } }, { "sponsor._id": { "$eq": "8b1cee44bf16ccced9163adee20008a8" } }, { "AccessMembers": { "$elemMatch": { "$and": [ { "id": { "$eq": "8b1cee44bf16ccced9163adee20008a8" } }, { "ACL": { "$in": [ "View", "Edit" ] } } ] } } } ] } ] }, "opts": { "use_index": [], "bookmark": "nil", "limit": 25, "skip": 0, "sort": {}, "fields": "all_fields", "r": [ 49 ], "conflicts": false, "stale": false, "update": true, "stable": false, "execution_stats": false }, "limit": 25, "skip": 0, "fields": "all_fields", "mrargs": { "include_docs": true, "view_type": "map", "reduce": false, "start_key": null, "end_key": "", "direction": "fwd", "stable": false, "update": true, "conflicts": "undefined" } } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://github.com/apache/couchdb/issues/2063?email_source=notifications&email_token=AABL2AUWVGULUJD6MCB3U3DP44ZKPA5CNFSM4H3Z4I5KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODY3WGBY#issuecomment-506946311>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AABL2ASF4FIGXFGCE3SVY6DP44ZKPANCNFSM4H3Z4I5A>.

[brifai]

Thank you @garrensmith for your input, but can this query be indexed ?

[garrensmith]

If you use an $or operator then all the fields in the index need to be on both sides of the or otherwise it won’t work. That’s why I think keep the index fields to a minimum or use a partial_filter_selector when building the index.

…

________________________________ From: brifai <notifications@github.com> Sent: Saturday, June 29, 2019 2:44 PM To: apache/couchdb Cc: garren smith; Mention Subject: Re: [apache/couchdb] index with $or operator not working (#2063) Thank you @garrensmith<https://github.com/garrensmith> for your input, but can this query be indexed ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<https://github.com/apache/couchdb/issues/2063?email_source=notifications&email_token=AABL2ATFHY6I7F7SHCGPQTTP45KKNA5CNFSM4H3Z4I5KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODY3YENY#issuecomment-506954295>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AABL2AWY4INWQQWLLX7XTBDP45KKNANCNFSM4H3Z4I5A>.

[brifai]

Thank you @garrensmith , @wohali do you have any input about it ?

[wohali]

no, garren is the expert here