[SERVER-26896] Use knowledge about partial index filter expression to determine whether $exists predicate can be covered Created: 03/Nov/16  Updated: 24/Apr/23

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.10
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Peter Garafano (Inactive) Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 1
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-28889 Partial index shouldn't do fetch on c... Closed
Assigned Teams:
Query Optimization
Participants:
Case:

 Description   

Given documents of the form

{
    _id: ObjectId("..."),
    a: "2016-08-07",
    ...
    b: 2222,
    c: 3333
    ...
},
{
    _id: ObjectId("..."),
    a: "2016-08-07"
    c: 4444,
    ...
},
{
    _id: ObjectId("..."),
    a: "2016-08-07",
    ...
    b: 4444,
    c: 5555
    ...
},

And a partial index of the form

db.foo.createIndex({a: -1, b: 1}, partialFilterExpression: {b: {$exists: true}})

The query planner can use this index to fully cover a query of the following form:

db.foo.find({a: { $lte: '2016-08-11' }, b: {$exists: true}}, {_id: 0, a: 1})



 Comments   
Comment by Milena Ivanova [ 24/Apr/23 ]

The optimization of $exists is only partially addressed by SERVER-28889. It works in case the field in the partial filter expression is not indexed. For instance:

db.coll.createIndex({a: 1}, {name: "a_1_b_exists", "partialFilterExpression": {b: {$exists: true}}});
db.coll.find({a: {$gte: 90}, b: {$exists: true}}, {_id: 0, a: 1});

In this case, the $exists predicate is removed and the fetch stage is not needed.

If the field is also indexed, the $exists predicate is tagged to be satisfied by the index. Since null and missing values are not distinguished in the index, the 'tightness' of the index scan is determined as INEXACT_FETCH, which leads to keeping the predicate in the filter and adding a FETCH stage to the plan.

db.coll.createIndex({a: 1, b:1}, {name: "a_1_b_1_b_exists", "partialFilterExpression": {b: {$exists: true}}});
db.coll.find({a: {$gte: 90}, b: {$exists: true}}, {_id: 0, a: 1});

A possible solution is to have a special treatment for $exists predicate during index analysis and give a priority to the index filter expression over index tagging. This is not covered by SERVER-28889.

Comment by Asya Kamsky [ 14/Mar/19 ]

Related to SERVER-28889

Comment by Asya Kamsky [ 27/Dec/16 ]

is this related to partial index at all? It seems to give the exact same behavior if you have regular index on a and b and query with a<value and b:{$exists:true}

I think because we can never cover exists due to SERVER-12869

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