fungiboletus opened a new issue #1852: The $or operator in _find makes the queries very slow URL: https://github.com/apache/couchdb/issues/1852 I would like to use the `$or` operator in _find, but it is surprisingly very slow. ## Expected Behavior Great performances thanks to the indexes. ## Current Behavior The indexes seem ignored when the selector uses the `$or` operator. ## Possible Solution ? ## Steps to Reproduce I have a test database with about 10 000 documents. A simple `GET /{database}/{id}` is pretty fast and the latency is mostly due to the network (45ms). A simple `POST /{database}/_find` is also fast, with an acceptable overhead (65ms) and the `total_docs_examined` is 1. ```json { "selector": { "_id": "abc" }, "limit": 1, "execution_stats": true } ``` Now, when I use the `$or` operator, things goes slower (~2s) and `total_docs_examined` is quite high depending on the id I query (often around ~1000). ```json { "selector": { "_id": { "$or": [ "abc" ] } }, "limit": 1, "execution_stats": true } ``` I tried to different syntax with the same result: ```json "selector": { "$or": [ { "_id": "abc" } ] } ``` ```json "selector": { "_id": { "$or": [ { "$eq": "abc" } ] } }, ``` Interestingly, I get the following warning `"warning": "no matching index found, create an index to optimize query time"` but when I create an index on the `_id`, the warning goes away but it's even slower (~4s) ```json { "index": { "fields": [ "_id" ] }, "name": "id-json-index", "type": "json" } ``` ## Context I would like to query the database using the `$or` operator while keeping good performances. The querying on the `_id` field is just as an example here, I couldn't fetch the database using many requests to simulate the same behaviour with good performances client side. The following request is an example of a request that would be cumbersome to do client side, especially with pagination. ```json { "selector": { "a": "example", "b": { "$or": [1,2,3]}, "c": { "$or": [1,2,3]}, "d": { "$or": [1,2,3]} } } ``` ## Your Environment [Official Docker `couchdb` image](https://github.com/apache/couchdb-docker/blob/ce1679b4c1312203df2af8936c367c7027d2e888/2.3.0/Dockerfile). Cluster of 3 CouchDB nodes deployed in a Kubernetes cluster.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
