tiangolo opened a new issue #1218: _find with query ranges uses (or not) the index depending on order of JSON selector object keys URL: https://github.com/apache/couchdb/issues/1218 <!--- Provide a general summary of the issue in the Title above --> The order of the fields in a JSON selector make it use (or not) an index declared for those fields. It seems to be only for queries with more than one operator (`$gt`, `$lt`, etc) that are in the index. ## Expected Behavior <!--- If you're describing a bug, tell us what should happen --> I would expect the query planner to be able to execute more than one operator for indexed fields. For example, to be able to select more than one range. If that's not possible, I would expect it to use the index for one range and execute the rest in memory, as it seems to be doing when there are fields that are not in the index, instead of not using any index whatsoever. If that's intended behavior and there can only be one operator per field in the index except for one, I would expect the query planner to find out which of the indexed fields in the selector have more than one operator instead of requiring it to be the last field in the JSON selector. If that's somehow impossible, I would expect it to be documented, explaining it. <!--- If you're suggesting a change/improvement, tell us how it should work --> ## Current Behavior <!--- If describing a bug, tell us what happens instead of the expected behavior --> It seems like this: * There can be fields in the selector that are not in the index. * Any field in the selector that is NOT in the index, can have more than one operator. * Each field in the selector that is in the index can have at most one operator, but... * There can be at most one field in the selector that is in the index with more than one operator, but, it has to be the last key in the JSON selector. So, the query has to be constructed in an order like: * fields that are not in the index with more than one operator * intermixed with fields that are in the index with only one operator * only one field that is in the index and has more than one operator <!--- If suggesting a change/improvement, explain the difference from current behavior --> ## Possible Solution <!--- Not obligatory, but suggest a fix/reason for the bug, --> <!--- or ideas how to implement the addition or change --> I interleaved my ideas of what could be done in the "Expected Behavior" section, depending on the possible actual current states. ## Steps to Reproduce (for bugs) <!--- Provide a link to a live example, or an unambiguous set of steps to --> <!--- reproduce this bug. Include code to reproduce, if relevant --> With a test database, create an index with: ```json { "index": { "fields": [ "type", "timestamp" ] }, "name": "type_timestamp", "type": "json" } ``` Then run (explain) a query with: ```json { "selector": { "type": "measure", "timestamp": { "$gt": "2015", "$lt": "2016" } } } ``` It works and it uses the index. Now, change the order of the JSON fields: ```json { "selector": { "timestamp": { "$gt": "2015", "$lt": "2016" }, "type": "measure" } } ``` It is the same query. If you take into account the fact that a JSON dictionary is supposed to represent an unordered map, it IS the same query. But it no longer uses the index. Try to force it to use the index: ```json { "selector": { "timestamp": { "$gt": "2015", "$lt": "2016" }, "type": "measure" }, "use_index": "type_timestamp" } ``` There's an error with: ```json {"error":"no_usable_index","reason":"No index matches the index specified with \"use_index\""} ``` That's the simplest case to reproduce the bug. --- ### Now, more data about how it behaves with some experiments. From the working example, add a non-indexed field in the query, but in the middle of the others, even with a range: ```json { "selector": { "type": "measure", "value": { "$gt": 0, "$lt": 10 }, "timestamp": { "$gt": "2015", "$lt": "2016" } } } ``` It uses the index. But change that non-indexed value to the end of the selector: ```json { "selector": { "type": "measure", "timestamp": { "$gt": "2015", "$lt": "2016" }, "value": { "$gt": 0, "$lt": 10 } } } ``` And it no longer uses the index. --- It happens with any two operators, not only `$gt` and `$lt`. For example: ```json { "selector": { "type": "measure", "timestamp": { "$gt": "2015", "$gte": "2016" } } } ``` works and uses the index. But: ```json { "selector": { "timestamp": { "$gt": "2015", "$gte": "2016" }, "type": "measure" } } ``` Doesn't use the index. --- It's possible to have several indexed fields in the selector as long as they don't use more than one operator. And the operator can be an inequality (`$gt`, `$lt`, etc), it doesn't have to be an equality (`$eq`). Create an index with: ```json { "index": { "fields": [ "type", "value", "latitude", "longitude", "timestamp" ] }, "name": "type_value_latitude_longitude_timestamp", "type": "json" } ``` Then you can query with: ```json { "selector": { "type": { "$gt": "measure" }, "timestamp": { "$gt": "2018" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 } } } ``` And it uses the index: `type_value_latitude_longitude_timestamp`. Notice that the order of the selectors is not the same as in the index creation. But if you specify the index by hand: ```json { "selector": { "type": { "$gt": "measure" }, "timestamp": { "$gt": "2018" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 } }, "use_index": "type_value_latitude_longitude_timestamp" } ``` For some weird reason, it complains with an error: ```json {"error":"no_usable_index","reason":"No index matches the index specified with \"use_index\""} ``` And if you put the fields in the selector in the original order of the index: ```json { "selector": { "type": { "$gt": "measure" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 }, "timestamp": { "$gt": "2018" } }, "use_index": "type_value_latitude_longitude_timestamp" } ``` It still errors out: ```json {"error":"no_usable_index","reason":"No index matches the index specified with \"use_index\""} ``` So, specifying the index deterministically as suggested on the docs ends up not working, while letting it figure it out seems to work. I discovered this as I was writing this issue. I'm not sure if that would be a different bug. Now, with the working case that uses the index, you can add more non-indexed fields, even with several operators, even at the end: ```json { "selector": { "type": { "$gt": "measure" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 }, "timestamp": { "$gt": "2018" }, "foo": { "$gt": 0, "$lt": 10 } } } ``` and it still uses the index. But if you add more than one operator to one of the indexed fields without moving it to the end (the `timestamp`): ```json { "selector": { "type": { "$gt": "measure" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 }, "timestamp": { "$gt": "2018", "$lt": "2020" }, "foo": { "$gt": 0, "$lt": 10 } } } ``` It no longer uses the index. But if you the indexed field with more than one operator (the `timestamp`) to the end of the selector, it uses the index again: ```json { "selector": { "type": { "$gt": "measure" }, "value": { "$gt": 0 }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 }, "foo": { "$gt": 0, "$lt": 10 }, "timestamp": { "$gt": "2018", "$lt": "2020" } } } ``` However, there can only be one indexed field with two operators in the selector, see how the `value` has two operators now: ```json { "selector": { "type": { "$gt": "measure" }, "latitude": { "$gt": 10 }, "longitude": { "$gt": 10 }, "foo": { "$gt": 0, "$lt": 10 }, "value": { "$gt": 0, "$lt": 10 }, "timestamp": { "$gt": "2018", "$lt": "2020" } } } ``` In this case, it no longer uses the index we just created `type_value_latitude_longitude_timestamp` but uses the previously created index `type_timestamp`. If we remove the index `type_timestamp` it doesn't use any index at all. ## Context <!--- How has this issue affected you? What are you trying to accomplish? --> I was trying to use `_find` to do query ranges over several fields, for a time range, a latitude range and a longitude range. Just as described above. That is not a geosearch, I know it is not supported. That is just a bounding box, 3 ranges, 3 slices of the data. <!--- Providing context helps us come up with a solution that is most useful in the real world --> ## Your Environment <!--- Include as many relevant details about the environment you experienced the bug in --> * Version used: CouchDB 2.1.1 (Docker image [`couchdb:2`](https://hub.docker.com/_/couchdb/)) * Browser Name and version: Chrome Version 63.0.3239.108 (Official Build) (64-bit) * Operating System and version (desktop or mobile): Ubuntu 16.04 * Link to your project: :man_shrugging: ...I'll have one if I figure this out.
---------------------------------------------------------------- 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
