Does creating Index for the fields which are used in the partial_filter_selector improves query performance?

[raji94]

my index definition

{
	"index": {
		"partial_filter_selector": {
			"objectType":"po" 
		},
		"fields": ["company","product","location"]
	},
	"ddoc": "indexPODoc",
	"name": "indexPO",
	"type": "json"
}

my query:

{
	"selector": {
                "company": "X",
		"location": {
			"$in": ["location1", "location10"]
		},
		"objectType": "po",
		"product": {
			"$in": ["product1", "product10"]
		}
	},
	"use_index": ["indexPODoc", "indexPO"]
}

Do I have to include objectType in fields array in the index definition?

[willholley]

When using json indexes, $in operators cannot be used to narrow the range of the index scan.

The query planner only supports a single range scan of an index when fulfilling a query - it's essentially querying a view with a startkey/endkey parameter. You can see the generated view query if you call the _explain endpoint (or "explain" button in the UI).

Since a single range cannot be inferred from the $in operator in conjunction with other clauses, this filter is applied in memory against each row returned from the index.

If you need more flexibility, you can install clouseau and use text index types.

[raji94]

Thanks for the reply @willholley ,
Do I need to create index for the field which is used in partial_filter_selector, if i'm passing it in the query?

[willholley]

You don't need to index the field in the partial_filter_selector but the presence of partial_filter_selector means that the index will only be considered if you explicitly specify the index (as you have done) using use_index.

[raji94]

I understand
now I changed my query to the following, but the performance is still slow

{
	"selector": {
                "company": "X",
		"location": "location1"
		"objectType": "po",
		"product": "product1"
	},
	"use_index": ["indexPODoc", "indexPO"]
}

if I create index for the field which I mentioned in the partial_filter_selector improves query performance?
like this

{
	"index": {
		"partial_filter_selector": {
			"objectType":"po" 
		},
		"fields": [objectType,"company","product","location"]
	},
	"ddoc": "indexPODoc",
	"name": "indexPO",
	"type": "json"
}

[willholley]

partial_filter_selector ensures that the index only contains documents matching "objectType":"po". There's no point in adding objectType to the index as well, as there's no further filtering that can be done on that field. It would only be worth adding objectType to the index if you had a broader partial_filter_selector.

[raji94]

Alright! thank you @willholley for the explanation, I really appreciate your help.