You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@couchdb.apache.org by GitBox <gi...@apache.org> on 2022/04/19 08:36:27 UTC
[GitHub] [couchdb] shrikantgarg17 opened a new issue, #3998: Records not getting retrieved in case of indexed array.
shrikantgarg17 opened a new issue, #3998:
URL: https://github.com/apache/couchdb/issues/3998
Hi Team,
We are facing issues while retrieving records from an indexed array. though we are not sure if indexing is wrong or querying is wrong but it is taking lot of time to get data.
We are using Couch DB. Here are the details.
{
"couchdb": "Welcome",
"version": "3.1.0",
"git_sha": "ff0feea20",
"uuid": "c371bc5f-ad93-11eb-b1fa-968ae7512ef5",
"features": [
"access-ready",
"partitioned",
"pluggable-storage-engines",
"reshard",
"scheduler"
],
"vendor": {
"name": "XXXXX"
}
}
We are storing our records in one database whose structure is like this
{
"_id": "Test:1a90228b1a6347af9452b51c549c3a49",
"_rev": "1-1908817280c7fec3dfdd9982c680bee7",
"shri": {
"tags": [
"java"
],
"Country": [
"US"
],
"status": "compliant"
},
"tags": {}
"status": "active",
"createTime": 1633684718741,
"modifyTime": 0
}
so i want to have index on tags attribute which is an array. below are the index which i have created
{
"_id": "index1",
"_rev": "1-56cbb4c9768fd34749b89dfffd446bf7",
"language": "query",
"views": {
"tags-json-index": {
"map": {
"fields": {
"shri.tags": "asc",
"_id": "asc"
},
"partial_filter_selector": {}
},
"reduce": "_count",
"options": {
"def": {
"fields": [
{
"shri.tags": "asc"
},
{
"_id": "asc"
}
]
}
}
}
}
}
and my selector query is :
{"selector": {"$and": [{"shri.tags": {"$elemMatch": {"$eq": "xyz"}}}, {"_id": {"$gte": "0"}}]}, "fields": ["_id", "shri""], "sort": [{"_id": "asc"}], "limit": 501}
but my query takes lot of time. Not sure if indexing is working properly or not.
Below is snippet when i explain my query:
{
"dbname": "oc-xx-xyz",
"index": {
"ddoc": null,
"name": "_all_docs",
"type": "special",
"def": {
"fields": [
{
"_id": "asc"
}
]
}
},
"partitioned": "undefined",
"selector": {
"$and": [
{
"shri.tags": {
"$elemMatch": {
"": {
"$eq": "xyz"
}
}
}
},
{
"_id": {
"$gte": "0"
}
}
]
},
"opts": {
"use_index": [],
"bookmark": "nil",
"limit": 501,
"skip": 0,
"sort": {
"_id": "asc"
},
"fields": [
"_id",
"shri"
],
"partition": "",
"r": [
49
],
"conflicts": false,
"stale": false,
"update": true,
"stable": false,
"execution_stats": false
},
"limit": 501,
"skip": 0,
"fields": [
"_id",
"shri"
],
"mrargs": {
"include_docs": true,
"view_type": "map",
"reduce": false,
"partition": null,
"start_key": "0",
"end_key": "",
"direction": "fwd",
"stable": false,
"update": true,
"conflicts": "undefined"
}
}
Can anyone please help me on this.
Thanks
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [couchdb] willholley commented on issue #3998: Records not getting retrieved in case of indexed array.
Posted by GitBox <gi...@apache.org>.
willholley commented on issue #3998:
URL: https://github.com/apache/couchdb/issues/3998#issuecomment-1102795535
@shrikantgarg17 array elements are not indexed independently when using a `json` index (see https://github.com/apache/couchdb/issues/1388 for the explanation). At query time, CouchDB will ignore the `$elemMatch` operator when evaluating which indexes can be used. Given the only other clause in your selector is `_id > 0`, you end up with a full database scan.
As a solution, you can either use a [view](https://docs.couchdb.org/en/3.2.0/ddocs/views/index.html) directly and emit each element in the index as a key, or add the [search plugin](https://docs.couchdb.org/en/3.2.0/ddocs/search.html) and use the `text` type index, which can index array elements independently.
The view solution would be a map function like:
```
function(doc) {
if(doc.shri && doc.shri.tags) {
for (var i = 0; i < doc.shri.tags.length; i++) {
emit(doc.shri.tags[i], null);
}
}
}
```
and then all the view using `http://couchdburl/db/_design/my_ddoc/_view?key="xyz"`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [couchdb] big-r81 commented on issue #3998: Records not getting retrieved in case of indexed array.
Posted by GitBox <gi...@apache.org>.
big-r81 commented on issue #3998:
URL: https://github.com/apache/couchdb/issues/3998#issuecomment-1102926714
Moving to discussions...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [couchdb] big-r81 commented on issue #3998: Records not getting retrieved in case of indexed array.
Posted by GitBox <gi...@apache.org>.
big-r81 commented on issue #3998:
URL: https://github.com/apache/couchdb/issues/3998#issuecomment-1102361235
Hi,
I updated your comment for better readability of the json source. Please use code blocks.
Your query isn't using your index, which is shown:
```json
"index": {
"ddoc": null,
"name": "_all_docs",
"type": "special",
"def": {
"fields": [{
"_id": "asc"
}]
}
},
```
It's using the `_all_docs` endpoint for quering your results.
Did you try to explicitly use `use_index` in your `_find` query to use your index?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [couchdb] shrikantgarg17 commented on issue #3998: Records not getting retrieved in case of indexed array.
Posted by GitBox <gi...@apache.org>.
shrikantgarg17 commented on issue #3998:
URL: https://github.com/apache/couchdb/issues/3998#issuecomment-1102641790
Yeah.. that is the problem.. even when i use use_index in my query it is not picking my index and always using _all_docs. Not sure what is the probelm.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [couchdb] big-r81 commented on issue #3998: Records not getting retrieved in case of indexed array.
Posted by GitBox <gi...@apache.org>.
big-r81 commented on issue #3998:
URL: https://github.com/apache/couchdb/issues/3998#issuecomment-1102812121
I think the answer is much simpler:
There is a special defualt index only for `_id` which is for me:
```json
{
"total_rows": 2,
"indexes": [
{
"ddoc": null,
"name": "_all_docs",
"type": "special",
"def": { "fields": [{ "_id": "asc" }] }
},
{
"ddoc": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
"name": "tags-json-index",
"type": "json",
"partitioned": false,
"def": { "fields": [{ "_id": "asc" }, { "shri": "asc" }] }
}
]
}
```
If I only sort for `_id` the "special" index is used. If I use
```json
"sort" : ["_id", "shri"]
```
in the query, with the following ddoc
```json
{
"_id": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
"_rev": "3-bbb25abe2218156af2ad8a5d6648ce1d",
"language": "query",
"views": {
"tags-json-index": {
"map": {
"fields": {
"_id": "asc",
"shri": "asc"
},
"partial_filter_selector": {}
},
"reduce": "_count",
"options": {
"def": {
"fields": [
"_id",
"shri"
]
}
}
}
}
}
```
then the the mango query uses this index...
```json
{
"dbname": "test",
"index": {
"ddoc": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
"name": "tags-json-index",
"type": "json",
"partitioned": false,
"def": {
"fields": [
{
"_id": "asc"
},
{
"shri": "asc"
}
]
}
},
"partitioned": false,
"selector": {
"$and": [
{
"shri.tags": {
"$elemMatch": {
"": {
"$eq": "java"
}
}
}
},
{
"_id": {
"$gte": "0"
}
}
]
},
"opts": {
"use_index": [],
"bookmark": "nil",
"limit": 501,
"skip": 0,
"sort": {
"_id": "asc",
"shri": "asc"
},
"fields": [
"_id"
],
"partition": "",
"r": [
49
],
"conflicts": false,
"stale": false,
"update": true,
"stable": false,
"execution_stats": false
},
"limit": 501,
"skip": 0,
"fields": [
"_id"
],
"mrargs": {
"include_docs": true,
"view_type": "map",
"reduce": false,
"partition": null,
"start_key": [
"0"
],
"end_key": [
"<MAX>"
],
"direction": "fwd",
"stable": false,
"update": true,
"conflicts": "undefined"
}
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@couchdb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org