Partial Index not covering OR query filter

Working with Data
,
1

I have a MongoDB collection with documents having fields as we would see below. I’m trying to build a partial index with an expression that would be used in my filter queries.

Here’s the Partial Index creation command

db.reviews.createIndex(
  {
    catalog_id: 1,
    product_id: 1,
    score: -1,
    created_at: -1
  },
  {
    name: "reviews_only_fetch_by_catalog_product",
    partialFilterExpression: {
      $or: [
        { comments: { $exists: true } },
        { images: { $exists: true } },
        { videos: { $exists: true } }
      ]
    }
  }
)

When I run the below query, I was expecting the partial index getting leveraged as the filter expression is a subset of partial index expression.

{
  $and: [
    {
      catalog_id: '100'
    },
    {
      $or: [
        {
          comments: {
            $exists: true
          }
        },
        {
          images: {
            $exists: true
          }
        },
        {
          videos: {
            $exists: true
          }
        }
      ]
    }
  ]
}

But surprisingly, the explain plan makes a COLLSCAN instead of using the index. Why would the $or filter that is exactly as defined in the index definition not work for the query?

While the below query is able to leverage the index.

{
  catalog_id: '100',
  comments: {
    $exists: true
  }
}

MongoDB version - 6.0.4

3

It would help us help you if you share sample documents from your collection. We need sample documents to experiment your use-case on our system.

4

Experiencing the same issue. We have a collection with a compound partial index of which the filter is set as below:

db.conversations.createIndex(
  {
    instance_id: 1,
    contact_id: 1,
    session_id: 1
  },
  {
    name: "open_and_recently_closed_conversations",
    partialFilterExpression: {
      $or: [
       {"closed_at": null},
       {"recently_closed_at": {"$exists": true}}
      ]
    }
  }
)

And here is the query I am expecting to match the index:

db.conversations.find({
  "$or": [
   {
    "closed_at": null,
   },
   {
    "recently_closed_at": {
     "$exists": true
    }
   }
  ],
  "contact_id": ObjectId('006524aa9ee37054d4c2eb00'),
  "instance_id": "id-123",
  "session_id": ObjectId("eed774749d33b29c8ff6858b")
 })

But the Winning Plan shows otherwise (Full explain logs at the bottom):

    winningPlan: {
      stage: 'FETCH',
      filter: {
        '$and': [
          {
            '$or': [
              {
                closed_at: {
                  '$eq': null
                }
              },
              {
                recently_closed_at: {
                  '$exists': true
                }
              }
            ]
          },
          {
            contact_id: {
              '$eq': ObjectId('006524aa9ee37054d4c2eb00')
            }
          },
          {
            session_id: {
              '$eq': ObjectId('eed774749d33b29c8ff6858b')
            }
          }
        ]
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          instance_id: 1
        },
        indexName: 'instance_id',
        isMultiKey: false,
        multiKeyPaths: {
          instance_id: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          instance_id: [
            '["id-123", "id-123"]'
          ]
        }
      }
    },

Surprisingly, the following query leverages the index, even though the partial filter is set on {"$exists": true}:

 db.conversations.find( {
 "session_id": ObjectId("eed774749d33b29c8ff6858b"),
  "contact_id": ObjectId("006524aa9ee37054d4c2eb00"),
  "instance_id": "id-123",
  "closed_at": null,  
  "recently_closed_at": {
      "$exists": false
  }
 })

Here is the full explain log of the query which is failing to match the index:

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'chat',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        {
          '$or': [
            {
              closed_at: {
                '$eq': null
              }
            },
            {
              recently_closed_at: {
                '$exists': true
              }
            }
          ]
        },
        {
          contact_id: {
            '$eq': ObjectId('006524aa9ee37054d4c2eb00')
          }
        },
        {
          instance_id: {
            '$eq': 'id-123'
          }
        },
        {
          session_id: {
            '$eq': ObjectId('eed774749d33b29c8ff6858b')
          }
        }
      ]
    },
    queryHash: 'D50C3328',
    planCacheKey: 'AD3F3223',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      filter: {
        '$and': [
          {
            '$or': [
              {
                closed_at: {
                  '$eq': null
                }
              },
              {
                recently_closed_at: {
                  '$exists': true
                }
              }
            ]
          },
          {
            contact_id: {
              '$eq': ObjectId('006524aa9ee37054d4c2eb00')
            }
          },
          {
            session_id: {
              '$eq': ObjectId('eed774749d33b29c8ff6858b')
            }
          }
        ]
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          instance_id: 1
        },
        indexName: 'instance_id',
        isMultiKey: false,
        multiKeyPaths: {
          instance_id: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          instance_id: [
            '["id-123", "id-123"]'
          ]
        }
      }
    },
    rejectedPlans: [
      {
        stage: 'FETCH',
        filter: {
          '$and': [
            {
              '$or': [
                {
                  closed_at: {
                    '$eq': null
                  }
                },
                {
                  recently_closed_at: {
                    '$exists': true
                  }
                }
              ]
            },
            {
              contact_id: {
                '$eq': ObjectId('006524aa9ee37054d4c2eb00')
              }
            },
            {
              session_id: {
                '$eq': ObjectId('eed774749d33b29c8ff6858b')
              }
            }
          ]
        },
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: {
            instance_id: 1,
            start_article_id: 1,
            start_widget_id: 1
          },
          indexName: 'instance_id_start_article_id_start_widget_id',
          isMultiKey: false,
          multiKeyPaths: {
            instance_id: [],
            start_article_id: [],
            start_widget_id: []
          },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            instance_id: [
              '["id-123", "id-123"]'
            ],
            start_article_id: [
              '[MinKey, MaxKey]'
            ],
            start_widget_id: [
              '[MinKey, MaxKey]'
            ]
          }
        }
      }
    ]
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 0,
    executionTimeMillis: 0,
    totalKeysExamined: 0,
    totalDocsExamined: 0,
    executionStages: {
      stage: 'FETCH',
      filter: {
        '$and': [
          {
            '$or': [
              {
                closed_at: {
                  '$eq': null
                }
              },
              {
                recently_closed_at: {
                  '$exists': true
                }
              }
            ]
          },
          {
            contact_id: {
              '$eq': ObjectId('006524aa9ee37054d4c2eb00')
            }
          },
          {
            session_id: {
              '$eq': ObjectId('eed774749d33b29c8ff6858b')
            }
          }
        ]
      },
      nReturned: 0,
      executionTimeMillisEstimate: 0,
      works: 2,
      advanced: 0,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      docsExamined: 0,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 0,
        executionTimeMillisEstimate: 0,
        works: 1,
        advanced: 0,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: {
          instance_id: 1
        },
        indexName: 'instance_id',
        isMultiKey: false,
        multiKeyPaths: {
          instance_id: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          instance_id: [
            '["id-123", "id-123"]'
          ]
        },
        keysExamined: 0,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    },
    allPlansExecution: [
      {
        nReturned: 0,
        executionTimeMillisEstimate: 0,
        totalKeysExamined: 0,
        totalDocsExamined: 0,
        score: 2.0002,
        executionStages: {
          stage: 'FETCH',
          filter: {
            '$and': [
              {
                '$or': [
                  {
                    closed_at: {
                      '$eq': null
                    }
                  },
                  {
                    recently_closed_at: {
                      '$exists': true
                    }
                  }
                ]
              },
              {
                contact_id: {
                  '$eq': ObjectId('006524aa9ee37054d4c2eb00')
                }
              },
              {
                session_id: {
                  '$eq': ObjectId('eed774749d33b29c8ff6858b')
                }
              }
            ]
          },
          nReturned: 0,
          executionTimeMillisEstimate: 0,
          works: 1,
          advanced: 0,
          needTime: 0,
          needYield: 0,
          saveState: 0,
          restoreState: 0,
          isEOF: 1,
          docsExamined: 0,
          alreadyHasObj: 0,
          inputStage: {
            stage: 'IXSCAN',
            nReturned: 0,
            executionTimeMillisEstimate: 0,
            works: 1,
            advanced: 0,
            needTime: 0,
            needYield: 0,
            saveState: 0,
            restoreState: 0,
            isEOF: 1,
            keyPattern: {
              instance_id: 1
            },
            indexName: 'instance_id',
            isMultiKey: false,
            multiKeyPaths: {
              instance_id: []
            },
            isUnique: false,
            isSparse: false,
            isPartial: false,
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              instance_id: [
                '["id-123", "id-123"]'
              ]
            },
            keysExamined: 0,
            seeks: 1,
            dupsTested: 0,
            dupsDropped: 0
          }
        }
      },
      {
        nReturned: 0,
        executionTimeMillisEstimate: 0,
        totalKeysExamined: 0,
        totalDocsExamined: 0,
        score: 2.0002,
        executionStages: {
          stage: 'FETCH',
          filter: {
            '$and': [
              {
                '$or': [
                  {
                    closed_at: {
                      '$eq': null
                    }
                  },
                  {
                    recently_closed_at: {
                      '$exists': true
                    }
                  }
                ]
              },
              {
                contact_id: {
                  '$eq': ObjectId('006524aa9ee37054d4c2eb00')
                }
              },
              {
                session_id: {
                  '$eq': ObjectId('eed774749d33b29c8ff6858b')
                }
              }
            ]
          },
          nReturned: 0,
          executionTimeMillisEstimate: 0,
          works: 1,
          advanced: 0,
          needTime: 0,
          needYield: 0,
          saveState: 1,
          restoreState: 0,
          isEOF: 1,
          docsExamined: 0,
          alreadyHasObj: 0,
          inputStage: {
            stage: 'IXSCAN',
            nReturned: 0,
            executionTimeMillisEstimate: 0,
            works: 1,
            advanced: 0,
            needTime: 0,
            needYield: 0,
            saveState: 1,
            restoreState: 0,
            isEOF: 1,
            keyPattern: {
              instance_id: 1,
              start_article_id: 1,
              start_widget_id: 1
            },
            indexName: 'instance_id_start_article_id_start_widget_id',
            isMultiKey: false,
            multiKeyPaths: {
              instance_id: [],
              start_article_id: [],
              start_widget_id: []
            },
            isUnique: false,
            isSparse: false,
            isPartial: false,
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              instance_id: [
                '["id-123", "id-123"]'
              ],
              start_article_id: [
                '[MinKey, MaxKey]'
              ],
              start_widget_id: [
                '[MinKey, MaxKey]'
              ]
            },
            keysExamined: 0,
            seeks: 1,
            dupsTested: 0,
            dupsDropped: 0
          }
        }
      }
    ]
  },
  command: {
    find: 'conversations',
    filter: {
      '$or': [
        {
          closed_at: null
        },
        {
          recently_closed_at: {
            '$exists': true
          }
        }
      ],
      contact_id: ObjectId('006524aa9ee37054d4c2eb00'),
      instance_id: 'id-123',
      session_id: ObjectId('eed774749d33b29c8ff6858b')
    },
    '$db': 'talk'
  },
  serverInfo: {
    host: '....net',
    port: 20,
    version: '6.0.13',
    gitVersion: '...'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {}
}

Output of getIndexes query on conversations collection:

[
  {
    v: 2,
    key: { instance_id: 1 },
    name: 'instance_id',
    background: true
  },
  {
    v: 2,
    key: { instance_id: 1 },
    name: 'open_conversations',
    background: true,
    partialFilterExpression: { closed_at: null }
  },
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { subject_generate_at: 1 },
    name: 'subject_generate_at',
    background: true,
    sparse: true
  },
  {
    v: 2,
    key: { planned_es_updated_at: 1 },
    name: 'planned_es_updated_at',
    background: true,
    sparse: true
  },
  {
    v: 2,
    key: { instance_id: 1, start_article_id: 1, start_widget_id: 1 },
    name: 'instance_id_start_article_id_start_widget_id'
  },
  {
    v: 2,
    key: { instance_id: 1, contact_id: 1, session_id: 1 },
    name: 'open_and_recently_closed_conversations',
    partialFilterExpression: { '$or': [Array] }
  },
  {
    v: 2,
    key: { recently_closed_at: 1 },
    name: 'recently_closed_at',
    sparse: true
  }
]

Mongo Version : 6.0.13