A Case Study on Improving Miricanvas's Query Performance through Amazon DocumentDB Index Optimization

Miridih's Miricanvas transitioned from MongoDB Atlas to Amazon DocumentDB to efficiently process design data. However, due to differences in how the Query Planner operates between the two services, they experienced unexpected query performance degradation.

The key problem cases and solutions are as follows:

• Partial Index Utilization Issue: Amazon DocumentDB only uses an index when the partialFilterExpression exactly matches the query condition. As with MongoDB, an index may not work with just an $exists condition alone, so indexes need to be redesigned to match query patterns or a regular compound index should be used.
• Absence of SORT_MERGE with the $in Operator: MongoDB efficiently sorts using SORT_MERGE when $or or $in conditions are used, but DocumentDB does not support this. This causes delays as large amounts of data are sorted in memory. To resolve this, a strategy of splitting the query and then merging the results at the application level is used.

For successful operation, it is important to always verify actual index utilization through explain() and to identify behavioral differences with MongoDB in advance.