[SERVER-23808] Implement Index Choice based on Partial Filter Expression Created: 19/Apr/16  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 3.2.5
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: William Cross Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-29247 COUNT_SCAN not used for partial indexes Closed
related to SERVER-26580 allow using partial index on query wh... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

Request for index selection based solely on the partialFilterExpression.

Currently, the query optimizer decides on an index based on the match and sort arguments of a query. With partial indexes, it will also consider the partialFilterExpression, but as implemented, it can still miss an index that would be performant on the query.

Here's an example where the current implementation is sub-optimal:

db.foo.dropIndex();
db.foo.createIndex( { a : 1 }, { partialFilterExpression : { b : { $gte : 5 } } } );
for (i=1; i<=10; i++) { db.foo.insertOne( { a : i, b : i } ) };
db.foo.find( { b : { $gte : 5 } }, { _id : 0, a : 1 } ).explain()

Desired result: The query uses the index, and it is a covered query
Current result: the query performs a collection scan
Variation: db.foo.find( { a : { $exists: true }, b : { $gte : 5 } }, { _id : 0, a : 1 } ).explain() results in an index scan, but does not cover the query even though the index could cover this query.



 Comments   
Comment by William Cross [ 03/Jan/17 ]

I think this ticket is different in scope than SERVER-26896. Per your reference to SERVER-12869 (Index null values and missing values differently), I'm removing the mention of expecting a covered query for { a :

{ $exists : true }

}.

Comment by Asya Kamsky [ 27/Dec/16 ]

same as SERVER-26896?

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