partial unique indexes are being enforced for inserts, but they're not used in find

Question

Hey!

I'm facing difficulties using AWS documentDB - my queries don't use partial/sparse indices for queries that should use them. I have a collection like this:

```
> db.entries.getIndices()
[
  {
    v: 4,
    key: { _id: 1 },
    name: '_id_',
    ns: 'mydb.entries'
  },
  {
    v: 4,
    unique: true,
    key: { uid: 1 },
    name: 'idx_uid',
    ns: 'mydb.entries'
  },
  {
    v: 4,
    key: { lastEventAt: 1 },
    name: 'idx_lastEventAt',
    ns: 'mydb.entries',
    sparse: true
  },
  {
    v: 4,
    unique: true,
    key: { 'gc.idHash': 1 },
    name: 'idx_gc_idHash',
    ns: 'mydb.entries',
    partialFilterExpression: { 'gc.idHash': { '$exists': true } }
  }
]
```

Index is being used for `uid`, which is expected:
```
db.entries.find({ 'uid': "foo" }).explain()
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mydb.entries',
    winningPlan: { stage: 'IXSCAN', indexName: 'idx_uid', direction: 'forward' }
  },
```

Indices are **NOT USED**  for *sparse* `lastEventAt` index and unique subdoc *partial* index `gc.idHash` ; the same queries use index on mongodb.

```
> db.entries.find({ 'gc.IdHash': "foo" }).explain()
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mydb.entries',
    winningPlan: { stage: 'COLLSCAN' }
  },

>  db.entries.find({ 'lastEventAt': "foo" }).explain()
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mydb.entries',
    winningPlan: { stage: 'COLLSCAN' }
  },

> db.entries.find({ 'lastEventAt': { $gt: ISODate("2025-01-02")} }).explain()
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mydb.entries',
    winningPlan: { stage: 'COLLSCAN' }
```

`$eq` and `$gt` should be supported, according to documentation (https://docs.aws.amazon.com/documentdb/latest/developerguide/partial-index.html#partial-index-operators) . Collection is not small, so the `Note` comment in documentation doesn't apply - it contains ~2M records

Trying to use index hint for a query results in a error - partial/sparse indices can't be used in index hints.

Additional info: `gc.idHash` is being enforced during inserts -> no duplicates are allowed. How can I resolve this issue - it's blocking our migration to aws docdb.

Thanks for help

re:Post Agent · Accepted Answer

There are a few key differences in how Amazon DocumentDB handles sparse and partial indexes compared to MongoDB that are causing your issues.

For sparse indexes in Amazon DocumentDB, you must explicitly include the `$exists` clause in your query for the index to be used. Try modifying your query for the lastEventAt field like this:

```
db.entries.find({ 'lastEventAt': { $exists: true, $gt: ISODate("2025-01-02") } }).explain()
```

For partial indexes, there's a case sensitivity issue in your query. Your index is on `gc.idHash` but your query is using `gc.IdHash` (note the capital 'I'). Field names in DocumentDB are case-sensitive, so this mismatch is causing the index to be ignored. Correct your query to:

```
db.entries.find({ 'gc.idHash': "foo" }).explain()
```

Additionally, for the partial index to be used, your query predicate must exactly match or be a logical subset of the partialFilterExpression. Since your partial index has a filter expression of `{ 'gc.idHash': { '$exists': true } }`, you should include this in your query:

```
db.entries.find({ 'gc.idHash': { $exists: true, $eq: "foo" } }).explain()
```

The reason your unique constraints are being enforced during inserts but not used for queries is that DocumentDB separates the uniqueness constraint enforcement from the index usage in query optimization.

These adjustments should help your queries utilize the indexes properly in Amazon DocumentDB.  
**Sources**  
[Functional differences: Amazon DocumentDB and MongoDB - Amazon DocumentDB](https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html)  
[Partial index - Amazon DocumentDB](https://docs.aws.amazon.com/documentdb/latest/developerguide/partial-index.html)

KoG · Answer

Modifying query to `$and: [ { foo: { $exists: true }},  ]` solves the issue, thanks!

This *requirement* is NOT clearly stated in the documentation (https://docs.aws.amazon.com/documentdb/latest/developerguide/partial-index.html#partial-index-query), neither is mentioned in the `Functional differences: Amazon DocumentDB and MongoDB` - only sparse indices are being mentioned - I suggest that you state this it very clearly also for partial indices, because I'm surely not the first, not the last one that is going to bitten by this odd behaviour.

partial unique indexes are being enforced for inserts, but they're not used in find

Add your answer

Relevant content