[SERVER-25023] no way to index the same fields with two different partial index filters Created: 13/Jul/16  Updated: 22/Jun/22  Resolved: 26/Apr/20

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 4.7.0

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Bernard Gorman
Resolution: Done Votes: 13
Labels: query-44-grooming, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Documented
is documented by DOCS-14281 Investigate changes in SERVER-25023: ... Closed
Duplicate
is duplicated by SERVER-32733 Support same index definition but wit... Closed
is duplicated by SERVER-34841 Cannot create multiple indexes with t... Closed
Problem/Incident
causes PYTHON-2215 Test failure - test_collection.TestCo... Closed
causes SERVER-52814 queryPlanner.winningPlan.inputStage i... Closed
Related
related to SERVER-62636 setFeatureCompatibilityVersion 4.4 su... Closed
related to SERVER-47657 Add 'unique' and 'sparse' parameters ... Closed
related to SERVER-47659 Add 'wildcardProjection' parameter to... Closed
related to SERVER-67446 Ensure consistent wildcardProjection ... Closed
is related to SERVER-24239 Add support for building indices with... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2020-03-09, Query 2020-03-23, Query 2020-04-06, Query 2020-04-20, Query 2020-05-04
Participants:
Case:
Linked BF Score: 48

 Description   

If I have a large data set, I might want to create several indexes on it, each index covering only a subset of the full set of documents. Currently that's not allowed because we don't allow same pattern with different options, and filter is part of the options.



 Comments   
Comment by Githook User [ 25/Apr/20 ]

Author:

{'name': 'Bernard Gorman', 'email': 'bernard.gorman@gmail.com', 'username': 'gormanb'}

Message: SERVER-25023 Allow multiple indexes on the same fields with different partial index filters
Branch: master
https://github.com/mongodb/mongo/commit/085ffeb310e8fed49739cf8443fcb13ea795d867

Comment by Asya Kamsky [ 12/Sep/19 ]

There is an additional use case where you may want a different TTL expiration value depending on the type of document or some other partial filter.
There is no workaround for that unfortunately.

Comment by Michael de Hoog [ 20/Nov/18 ]

The workaround we use is to add a non-existant key to the subsequent indexes to make them unique from the first index.

index({ i: 1, _partial_index_1: 1 }, { partialFilterExpression: { j: { $gte: 10, $lt: 20 } } })
index({ i: 1, _partial_index_2: 1 }, { partialFilterExpression: { j: { $gte: 20, $lt: 30 } } })
index({ i: 1, _partial_index_3: 1 }, { partialFilterExpression: { j: { $gte: 30, $lt: 40 } } })

Comment by João Ferreira [ 07/Nov/18 ]

Additional use case: live index in production that can be made sparse. The ideal solution would be to add the same index, this time sparse, side-by-side; wait until complete; delete initial index. This is not possible due to this bug.

Comment by Coen Smid [ 03/Oct/18 ]

Still waiting for this ticket. 

Adding 'or' or 'not' features to the partial index would work for my use case as well. 

Comment by Ramon Fernandez Marina [ 04/May/18 ]

SERVER-34841 has another interesting use case for this feature.

Comment by Asya Kamsky [ 27/Jan/17 ]

This is important for views where you want to have a single large collection appear as several smaller collections so every query under the hood will start with something like tenant_id='X' and each view is for a specific tenant_id only.

Comment by Asya Kamsky [ 08/Sep/16 ]

That does not address this use case at all. The goal is to split a large index into several smaller indexes.

Comment by Max Hirschhorn [ 02/Sep/16 ]

If we could express a $or in the partialFilterExpression (SERVER-17853), then there wouldn't be a need to create two partial indexes as the OR stage would be able to make use of the same index for each clause.

Generated at Thu Feb 08 04:08:04 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.