CODEWITHSUNDEEP
couchdbcloudantcouchdb-2.0couchdb-mango
J. Araujo
J. Araujo

Reputation: 89

Cloudant/Mango query selector size limits

In our system we need to know if a reference to a specific document ID exists anywhere in the database. (for example to allow/deny deletion)

This previously worked fine with a view we created but now we have been asked to do the same thing using Cloudant query selectors.

We came up with the solution below where we include all possible paths where a document reference would be found. The query is generated dynamically to include all possible paths for a "foreign key" reference, however this mean it would need to scale to potentially +100 unique paths (ie, the $or operator array might end up with +100 items)

I wonder if such a large query would even work and also the potential performance implications. Also if there's an alternative way we would like to know.

{
   "selector": {
      "$or": [
         {
            "content": {
               "$elemMatch": {
                  "accounts": {
                     "$elemMatch": {
                        "bank": "bank12345"
                     }
                  }
               }
            }
         },
         {
            "content": {
               "$elemMatch": {
                  "partners": {
                     "$elemMatch": {
                        "someEntity": "someReference12345"
                     }
                  }
               }
            }
         }
      ]
   },
   "fields": [
      "_id",
      "_rev"
   ]
}

Upvotes: 0

Views: 562

Answers (2)

J. Araujo
J. Araujo

Reputation: 89

As an update to this discussion I would like to report that we did test a Cloudant query of many conditions and it only took 11 ms to execute, so although that's a lot we still need to learn about the inner working of selectors, it does seem you can do such queries as long as all queried fields are indexes without a significant impact on performance. But since it was a simple test don't quote me on this one. ;-)

Upvotes: 0

xpqz
xpqz

Reputation: 3737

So, let me get this right -- you previously used a view to do a simple lookup, which worked great, was efficient and easy to verify and you've been asked to instead make a CQ selector with hundreds of clauses?

Why? That's just crazy. This is the job for a view.

Leaving that aside for a while, I assume you're using JSON indexes for this. Your performance will almost certainly be atrociously bad, btw. You need to create indexes upfront for all the bits you want to be able to query on, or you may end up with a full DB scan instead of an index lookup.

Without seeing your documents, it's hard to offer more than generic advice. It sounds like your documents are large, and/or contain arrays that you in the worst case scenario update over time. I can't think of any other plausible way why you'd consider using a selector with more than a handful of clauses. Could you split out the partners array into separate documents instead?

Upvotes: 1

Related Questions