GMishx opened a new issue, #5756:
URL: https://github.com/apache/couchdb/issues/5756
## Description
I'm encountering a puzzling issue where CouchDB's Mango query planner
refuses to use a specific index, even though the `_explain` output indicates
the index covers the query and seems to meet all criteria. I've tried various
approaches to align the index and query, but the index consistently shows as
`usable: false` with the reason `is_partial`, which is unexpected given the
setup.
I'm seeking assistance to understand this behavior and debug why this index
isn't being utilized, as existing documentation hasn't clarified this
particular scenario.
## Steps to Reproduce
1. Sample documents:
```json
[
{
"_id": "07150a7e11204595bcf050a6d932c82c",
"_rev": "1-6a6e72d7a7473348ee42d394b076f3c1",
"type": "vendor",
"shortname": "Apache",
"fullname": "Apache Software Foundation",
"url": "https://www.apache.org/";
},
{
"_id": "803f49a2968a4d4ba0d2db56663c41fc",
"_rev": "1-896259d1b99ed2c64a7a299bec0fc748",
"type": "vendor",
"shortname": "hp",
"fullname": "Hewlett Packard",
"url": "http://hp.com";
}
]
```
2. Index creation:
```json
{
"index": {
"partial_filter_selector": {
"type": {
"$eq": "vendor"
}
},
"fields": ["type", "fullname", "shortname", "url"]
},
"ddoc" : "VendorsByAllIdx",
"name" : "vendorsByType",
"type" : "json"
}
```
3. Query:
- Aim of using the query is to fetch all Vendor documents which can also
be achieved using views. However, views does not allow sorting and pagination,
but mango queries does.
```json
{
"selector": {
"type": {
"$eq": "vendor"
}
},
"fields": [
"_id",
"type",
"fullname",
"shortname",
"url"
],
"limit": 10,
"skip": 0,
"sort": [
{
"type": "asc"
},
{
"fullname": "asc"
}
],
"use_index": [
"VendorsByAllIdx",
"vendorsByType"
]
}
```
4. Explain output:
```json
{
"dbname": "sw360db",
"index": null,
"partitioned": false,
"selector": {
"type": {
"$eq": "vendor"
}
},
"opts": {
"use_index": [
"VendorsByAllIdx",
"vendorsByType"
],
"bookmark": "nil",
"limit": 10,
"skip": 0,
"sort": {
"type": "asc",
"fullname": "asc"
},
"fields": [
"_id",
"type",
"fullname",
"shortname",
"url"
],
"partition": "",
"r": 1,
"conflicts": false,
"stale": false,
"update": true,
"stable": false,
"execution_stats": false,
"allow_fallback": true
},
"limit": 10,
"skip": 0,
"fields": [
"_id",
"type",
"fullname",
"shortname",
"url"
],
"index_candidates": [
{
"index": {
"ddoc": "_design/VendorsByAllIdx",
"name": "vendorsByType",
"type": "json",
"partitioned": false,
"def": {
"fields": [
{
"type": "asc"
},
{
"fullname": "asc"
},
{
"shortname": "asc"
},
{
"url": "asc"
}
],
"partial_filter_selector": {
"type": {
"$eq": "vendor"
}
}
}
},
"analysis": {
"usable": false,
"reasons": [
{
"name": "is_partial"
}
],
"ranking": 3,
"covering": true
}
},
{
"index": {
"ddoc": "_design/UserBySecondaryDeptIdx",
"name": "bySecondaryDepartmentsAndRoles",
"type": "json",
"partitioned": false,
"def": {
"fields": [
{
"secondaryDepartmentsAndRoles": "asc"
}
]
}
},
"analysis": {
"usable": false,
"reasons": [
{
"name": "field_mismatch"
}
],
"ranking": 1,
"covering": false
}
}
],
"selector_hints": [
{
"type": "json",
"indexable_fields": [
"type"
],
"unindexable_fields": []
},
{
"type": "nouveau",
"indexable_fields": [
"type"
],
"unindexable_fields": []
}
]
}
```
## Expected Behaviour
As the output of explain says, the index covers the query, but still does
not satisfy the query and thus is not used. I put all the possible fields for
the document in the index and in the query in the same order to make sure the
index covers the entire query. Even added the "type" field into sorting,
however irrelevant as it is taken care by the selector and the partial filter
of the index.
I tried to cover all the checkboxes to create this perfect index as per
existing documentation. However, CouchDB still refuses to use it and fails my
query as there are no indexes which can be used.
Are there ways I can debug the index selector to understand better why this
particular index cannot be used to answer the query?
## Your Environment
* CouchDB version used: 3.5.1
* Browser name and version: Mozilla Firefox Developer 146.0b5 (64-bit)
* Operating system and version: Debian GNU/Linux 12 (bookworm)
```json
{
"couchdb": "Welcome",
"version": "3.5.1",
"git_sha": "44f6a43d8",
"uuid": "deb34a557c40d5f658d42ca2c78872e1",
"features": [
"nouveau",
"access-ready",
"partitioned",
"pluggable-storage-engines",
"reshard",
"scheduler"
],
"vendor": {
"name": "The Apache Software Foundation"
}
}
```
## Additional Context
None
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
