Matheus Barbosa Silva; Vinicius Santos Andrade

DOI: 10.22167/2675-6528-202601234

Article prepared by the ResumeAI tool, an artificial intelligence solution developed by the Pecege Institute focused on synthesis and writing.

Summary

Query optimization in MongoDB databases was investigated, considering the relevance of large-scale data processing in the NoSQL context. The study aimed to evaluate, through practical experiments, how the structure and configuration of indexes influence read performance and operational write cost. The methodology employed exploratory bibliographic research and quantitative experimental research, which included ten controlled experiments using public databases, such as NYC 311 Service Requests. The results demonstrated that single-field indexes reduced average execution time by almost 100% for high-cardinality attributes, but had limited effectiveness on low-cardinality fields due to selectivity. The determining impact of the prefix rule on compound indexes was verified, and the inflection point where the individual document search cost surpassed sequential scanning was identified. Additionally, it was observed that the excessive accumulation of indexes increased write latency and resource contention in the storage engine. It was concluded that, although indexes are fundamental for scalability, they require careful planning of data distribution to prevent the MongoDB query planner from adopting suboptimal strategies.

Keywords: Performance; Indexes; NoSQL; Execution Plans; Selectivity.

1. Introduction

Non-relational databases, often referred to as NoSQL, have become essential pillars in modern application architectures, especially those dealing with large-scale data storage and processing. Scenarios such as social networks, Internet of Things (IoT) applications, and media consumption platforms generate massive data volumes that require systems capable of managing and querying information with high demand and volume. In this context, efficiency in data insertion and querying is crucial, as it directly impacts user experience and the economy of computational resources. MongoDB, a popular non-relational database management system (DBMS), stands out for its horizontal scalability and support for various index types, making it a relevant object of study for query optimization in Big Data environments (Chopade & Pachghare, 2020).

One of the main strategies for optimizing query performance in non-relational databases, such as MongoDB, is the construction and efficient use of indexes. Indexes are data structures that logically organize one or more attributes of a collection, allowing the system to quickly locate desired documents without the need to exhaustively scan all records. This ability to define a more restricted search scope is fundamental to speeding up read operations, especially on attributes with high cardinality and that are frequently used as filters. Index optimization is a continuous process that aims to maximize the efficiency in the use of computational resources, such as memory and processing power, as pointed out by Marat et al. (2024).

Despite the evident benefits in query acceleration, the implementation and maintenance of indexes are not operations free of costs and complexities. The technical literature warns of a significant trade-off between read performance and the operational cost of writing. Each index added to a collection imposes an additional effort on the storage engine, requiring synchronous maintenance of the B+ tree structure with each data alteration (Chopade & Pachghare, 2020). In environments with high write concurrency, the excessive accumulation of indexes can lead to increased write latency and resource lock contention, impacting the system’s overall performance (Tao et al., 2024). Furthermore, MongoDB’s query plan selection mechanism, based on an empirical approach known as First Past The Post (FPTP), may exhibit a bias that favors fast index plans for the initial results, but are suboptimal for queries returning large volumes of data, which can nullify the expected optimization gains.

To mitigate these challenges and ensure effective optimization, it is crucial to understand theoretical concepts such as query selectivity and the tipping point. Selectivity refers to the proportion of documents returned by a query relative to the total collection, and its low incidence can make the use of indexes less advantageous than a full sequential scan (Tao et al., 2024). The tipping point marks the threshold where the cost of traversing the index and individually retrieving each document exceeds the benefit of indexing. Additionally, the prefix rule in compound indexes is determinant, as MongoDB uses these indexes only when the filter fields form a continuous prefix starting from the first index field (Chopade & Pachghare, 2020). Maximum efficiency is achieved with covered queries, where all filter and projection fields are contained within the index, eliminating the need to access the original documents on disk and minimizing computational cost (Chopade & Pachghare, 2020; Tao et al., 2024).

Given the complexity and multiple factors influencing query optimization in NoSQL databases like MongoDB, an in-depth study on the impact of different index types and configurations becomes imperative. Understanding these internal mechanisms and quantifying their effects allows for the structuring of best practices and the extension of optimization benefits to various application contexts. Thus, this study proposes to analyze the positive and negative influence on query optimization of different types of indexes available in MongoDB databases, with the general objective of evaluating, through practical experiments, how the structure and configuration of indexes influence read performance and operational write cost.

2. Material and Methods

The present research was developed through a mixed methodological approach, which integrated an exploratory bibliographic research with a quantitative experimental research. The exploratory stage aimed to describe and analyze the functioning and particularities of the various types of indexes available in the MongoDB database management system. This theoretical analysis provided the basis for the formulation of practical experiments, allowing the verification of the observed characteristics in a controlled environment.

The experimental research was designed to validate and quantify the optimization potential of different index types and configurations on read performance and write operational cost in MongoDB instances. To this end, ten controlled experiments were conducted, each designed to isolate a specific concept, such as query selectivity, query coverage, or the impact on write cost. This structure allowed for direct comparison between scenarios with and without the indexing feature under investigation.

The experiments were conducted in a single-instance local MongoDB environment, configured without sharding or replica sets, to isolate the behavior of the indexes from variables inherent to distributed topologies. The computational environment consisted of a Windows Subsystem for Linux (WSL2) with the Debian 10 distribution, operating on the 64-bit Windows 11 operating system. MongoDB version 8.0, the most stable at the time, was used.

The equipment used for the execution of the experiments had an AMD Ryzen 7 3700U processor (2.30GHz, 8 cores) and 20 GB of RAM, of which 17.9 GB were available for the operating system. The choice of this environment aimed to replicate a common development and testing scenario, ensuring the reproducibility of the obtained results.

For the experiments, two public databases were used. The main one was the NYC 311 Service Requests dataset, obtained from the NYC Open Data portal (data.cityofnewyork.us). This dataset, with over 20 million public service request records since 2020, featured fields of various natures, including dates, geographic coordinates, and categories of high and low cardinality.

Additionally, for experiments involving array-type fields, the sample_mflix dataset was used, a sample dataset officially distributed by MongoDB. This dataset contains movie documents with list fields, such as genres and cast. The import of both datasets into local MongoDB collections was performed using the `mongoimport` tool.

The measurement protocol was uniform across all experiments. Each query or operation was executed fifty consecutive times under identical conditions, with the exception of write experiments, for which the number of repetitions was thirty, due to the longer duration of these operations. The primary metric collected was the execution time in milliseconds, obtained through MongoDB’s `explain` method, which reports the actual query engine execution time, excluding network latency.

Secondary metrics, such as the number of documents and keys examined, were recorded to aid in interpreting the internal mechanism of each execution plan. The choice of fifty repetitions per condition was determined by the need to stabilize the average estimate, considering the natural variability of executions in a local environment, where factors such as the operating system cache state and process concurrency can introduce fluctuations.

With this volume of observations, it was possible to construct 95% confidence intervals for the mean, using the Student’s t-distribution with n-1 degrees of freedom. This approach is suitable for finite sample sizes without the assumption of population normality. Explicit control of the plan cache state was implemented in each experiment to rigorously distinguish between cold reads and hot reads.

MongoDB’s plan selection mechanism, known as First Past The Post (FPTP), has been a central point of investigation, as described by Tao et al. (2024). This mechanism adopts an empirical approach, where multiple execution plans are tested simultaneously, and the “winner” is the one that returns the first results fastest, which may introduce a bias towards index scans.

The understanding of the B+ tree structure used by MongoDB indexes, which requires continuous and synchronous maintenance with each data change, was fundamental for the analysis of the operational write cost (Chopade & Pachghare, 2020). Each new index added to a collection imposes an additional effort on the storage engine, impacting write latency and lock contention in high concurrency scenarios.

The process of executing and collecting data from the experiments followed a scheme that involved importing external data via `mongoimport` and `mongorestore` to a local instance of MongoDB 8.0. Python 3.11 scripts, using the `pymongo` and `scipy` libraries, were employed to execute queries, collect `executionTimeMillis` metrics, and calculate 95% confidence intervals.

The collected data were organized into CSV files, and the results were visualized through graphs generated with the `matplotlib` library. In total, ten experiments were conducted. Experiments 1 through 8 investigated the impact of different index types on read performance, covering single-field, composite, text, geospatial, and multi-key indexes, as well as the concepts of selectivity, covered queries, and plan cache behavior.

Experiments 9 and 10 focused on the costs associated with index maintenance, measuring the impact on write operations and evaluating the storage savings provided by partial indexes. Data analysis was performed based on execution time metrics and the number of documents and keys examined, without presenting empirical findings or result interpretations in this section.

3. Results and Discussion

Query optimization in MongoDB databases is a central theme for applications dealing with large-scale data processing and storage, as observed in social networks and IoT platforms. In this context, efficiency in data reading and writing is crucial, directly impacting user experience and the economy of computational resources. The use of indexes emerges as a fundamental strategy to speed up read operations, logically organizing attributes of a collection to allow for quick document location, as highlighted by Chopade and Pachghare (2020).

However, the implementation of indexes is not without costs and complexities. There is a significant trade-off between read performance and operational write cost, as each added index requires synchronous maintenance of the B+ tree structure with every data alteration (Chopade & Pachghare, 2020). In environments with high write concurrency, excessive accumulation of indexes can increase latency and resource contention, affecting the overall system performance (Tao et al., 2024). MongoDB’s query plan selection mechanism, based on the empirical First Past The Post (FPTP) approach, may still favor fast plans for the first results, but suboptimal ones for queries returning large volumes of data, thus nullifying the expected gains.

To mitigate these challenges, it is essential to understand concepts such as query selectivity and the inflection point. Selectivity indicates the proportion of documents returned by a query relative to the total collection, and low selectivity can make the use of indexes less advantageous than a sequential scan (Tao et al., 2024). The inflection point marks the threshold where the cost of traversing the index and individually retrieving each document outweighs the benefit of indexing. The prefix rule in compound indexes is also determinant, as MongoDB uses them only when the filter fields form a continuous prefix starting from the first field of the index (Chopade & Pachghare, 2020).

Maximum efficiency is achieved with covered queries, in which all filter and projection fields are contained within the index, eliminating the need for access to original documents on disk and minimizing computational cost (Chopade & Pachghare, 2020; Tao et al., 2024). This study, therefore, analyzed the influence of various types and configurations of indexes in MongoDB databases, evaluating how index structure and configuration impact read performance and operational write cost through practical experiments.

Types of indexes

To optimize query performance, choosing the appropriate index type is fundamental, considering the data structure and the application’s search patterns. MongoDB offers a variety of indexes that use the structure of a B+ tree to locate documents efficiently, avoiding full collection scans, as described by Chopade and Pachghare (2020). Among the main types, the Single Field Index is the most basic form, applied to a single field, being essential for direct searches and automatically created for the collection’s unique identifier (_id).

The Compound Index encompasses multiple fields in a single structure to cover queries with multiple filters. The order of fields is crucial, as MongoDB uses the index only when the filter fields form a continuous prefix starting from the first field of the index. The Multikey Index, on the other hand, is designed to index fields that contain arrays, creating separate index entries for each array element, which improves the efficiency of searches for elements within lists (Chopade & Pachghare, 2020).

For applications that depend on geographic coordinates, the Geospatial Index (2d and 2dsphere types) optimizes proximity queries and inclusion checks in specific areas. The Text Index allows searching for terms within strings, considering linguistic aspects such as word stems, and is always sparse, applying only to documents with values for the indexed fields. Finally, the Hashed Index, applied to a single field, stores the hash of the indexed value and is often used in sharding environments to ensure uniform distribution of documents across cluster nodes.

In addition to these, MongoDB offers Special Property Indexes. TTL (Time-To-Live) Indexes remove documents older than a specified time. Unique Indexes ensure the uniqueness of field values, preventing the insertion of duplicates. Partial Indexes restrict the application of an index to documents that meet specific criteria, being useful for large collections or specific filters as they occupy less storage space.

Plan and index selection mechanism

MongoDB adopts an empirical approach to execution plan selection, unlike relational systems that use statistical cost estimates. This mechanism, described by Tao et al. (2024) as First Past The Post (FPTP), functions as a “race” between different search strategies. When a query is submitted and there is no valid plan in the cache, the optimizer generates multiple candidate plans, such as using different indexes or a collection scan.

These plans are executed simultaneously in a round-robin scheduling system. MongoDB declares the plan that returns a predefined amount of results (usually the first 101 documents) with the least computational “work”, measured by units of internal effort such as disk reads or traversed index entries, as the “winner”. Once selected, the plan is stored in a Query Plan Cache to optimize future executions of queries with identical logical structure, avoiding the cost of the testing phase.

However, the plan cache is invalidated in specific scenarios, such as significant changes in data volume, creation or removal of indexes, or server restart. Tao et al. (2024) identified an optimizer’s “preference bias”: as the winning criterion is based on the first results, MongoDB tends to favor index scans that find the first records quickly. In low-selectivity queries, the total time to traverse an index and fetch each document individually can exceed the time of a sequential scan, demonstrating that the mere existence of an index does not guarantee global optimization.

Analysis of the Trade-off between read performance and write cost

The implementation of indexes in MongoDB is not a cost-free operation. Although the main focus is on reducing query latency, technical literature warns about the collateral impact on data modification operations. Query efficiency is achieved through a B+ tree structure that requires continuous and synchronous maintenance with each change in the dataset, as detailed by Chopade and Pachghare (2020).

Each new index added to a collection imposes an additional effort on the storage engine, such as WiredTiger. When inserting, removing, or updating a document, MongoDB not only modifies the raw data but also needs to synchronously update all corresponding entries in the associated indexes. In high write concurrency scenarios, the accumulation of indexes leads to two main side effects: increased write latency and lock contention.

The increase in write latency occurs because the time required to complete a write operation has a direct proportionality relationship with the number of indexes defined in the collection (Chopade & Pachghare, 2020). The storage engine replicates updates to each structure and index individually, resulting in longer response times for write operations. Lock contention, in turn, occurs when the search engine attempts to simultaneously update several index pages in the memory cache, generating resource contention between different execution threads (Tao et al., 2024).

Even with modern concurrency management mechanisms, this overhead reduces the efficiency of parallel processing, potentially turning the index into a systemic performance bottleneck in write-intensive environments. Another critical factor is that, for maximum efficiency, indexes must reside in RAM, forming the resident set. Large or excessive indexes can exceed cache capacity, forcing the system to perform read and write operations directly to disk (swapping), which drastically degrades overall performance (Tao et al., 2024).

The impact of physical storage is also relevant, as complex indexes, such as multi-key or text indexes, can occupy more disk space than the original documents themselves. Optimization in MongoDB, therefore, should be seen as a balance of resources, weighing the gain in reading speed against the cost of infrastructure and the agility of write operations. The analysis by Chopade and Pachghare (2020) suggests that it is preferable to consolidate a composite index that serves multiple query patterns, respecting the prefix rule, rather than maintaining several overlapping individual indexes, minimizing the impact on writes and optimizing memory cache usage.

Index maintenance

The dynamic nature of indexes in MongoDB databases, which grow and change with the amount of stored data, makes their maintenance a continuous task. It is essential to analyze and update them recurrently so that they remain relevant in the face of schema updates and database scale growth. Furthermore, the storage space occupied by indexes must be considered, as large indexes not only impact storage but also the speed of write operations, since data updates imply modifications in the indexed structures (Marat et al., 2024).

The size of the indexes maintained by a database is a critical factor for the performance of operations, especially in large-scale contexts, where document-oriented databases are commonly applied. Thus, the definition of indexes must keep pace with updates to the database’s data schema, ensuring that the benefits provided by these structures are not lost. It is desirable that the size occupied by indexes is justified by their use cases, so that the performance loss in write operations is reasonable and justifiable within the application’s context.

Tipping point of index selectivity (Tipping point)

One of the most critical concepts in non-relational database optimization is query selectivity, which defines the proportion of documents returned relative to the total collection. As discussed by Tao et al. (2024), there is a theoretical threshold, called the “tipping point”, beyond which the use of an index ceases to be advantageous and becomes more costly than a full collection scan.

The inefficiency of an index in low selectivity scenarios, where a large volume of data is returned, is justified by the operational cost of the fetch phase. When MongoDB uses an index, it first traverses the B+ tree to locate document references and then fetches each document individually from disk or memory cache. If the query returns a considerable portion of the collection (above 30% or 40%), the effort of repeatedly switching between the index and the storage layer exceeds the time of a full sequential read.

At this stage, the index ceases to be a shortcut and becomes a costly and unnecessary intermediary, consuming additional CPU cycles and I/O operations. Tao et al. (2024) highlight an additional challenge in query optimization in MongoDB due to the First Past The Post (FPTP) criterion, which has an inherent bias in favor of index-based plans. As the plan “race” ends as soon as the first results are found, an index plan generally wins the competition by being agile in locating the initial records in its B+ tree structure.

However, this early win mechanism does not account for the total work required to complete query execution. Thus, the optimizer may cache a plan that is excellent for returning the first 100 documents, but which becomes drastically suboptimal when processing a large volume of data. For this research, the inflection point served as the main test variable in the practical experiment, allowing us to identify situations where optimization may require the removal or deprecation of an index in favor of a natural scan, aiming to reduce the overall execution time.

Influence of plan cache on query execution

The analysis of query performance in MongoDB cannot be dissociated from the functioning of its decision persistence system, the Query Plan Cache. According to Tao et al. (2024), once the FPTP mechanism elects a winning plan, it is stored in cache to optimize future executions of queries with identical structure, i.e., with the same filter format, disregarding the values to be filtered. The immediate benefit of the cache is the elimination of the plan “race” phase in each new query.

However, Tao et al. (2024) emphasize that this stability can hide a disadvantage: if the plan stored in the cache was elected under specific data volume conditions that have changed drastically, MongoDB will continue to use a potentially suboptimal strategy until a cache invalidation occurs. For the methodology of this research, this characteristic demanded a rigorous distinction between cold reads, when the cache is empty and the engine is forced to re-evaluate the indexes, and warm reads, when the plan is already consolidated and saved in the cache, reflecting the real performance of the stable system.

As detailed by Chopade and Pachghare (2020), the plan cache is not permanent, being automatically invalidated by events such as the creation or removal of indexes, server restart, or when the volume of altered documents in the collection reaches a threshold suggesting that the data distribution statistics are obsolete. Therefore, for the results and discussions of this work to be reliable, the practical experiments considered the state of the cache. Ignoring the existence of the cached plan could lead to erroneous conclusions, attributing the speed of a query solely to index efficiency, when, in fact, it would be benefiting from the absence of the plan competition phase in hot reads. This understanding allowed us to isolate the real performance gain provided by indexing in relation to the inherent adaptive behavior of MongoDB’s query plan optimizer.

Maximum optimization through covered queries

At the upper limit of indexing efficiency lies the concept of covered queries. According to Chopade and Pachghare (2020), a query is considered “covered” when all fields requested in the search projection, as well as the filter criteria, are part of the same composite index. The technical relevance of this type of query lies in the complete elimination of the fetch phase, which is the retrieval of the original document from disk or data cache, making it faster. As the index residing in RAM already contains all the necessary information to return a response to the client, the search engine can avoid accessing the raw data storage.

As discussed by Tao et al. (2024) when analyzing work units, the cost of a covered query is drastically lower, as it reduces I/O utilization and minimizes network and processing latency. For the practical experiment of this work, the identification of covered queries was done through the metric “totalDocsExamined: 0” in the query execution explanation. Finding and pointing out covered query scenarios allowed us to demonstrate that index optimization aims not only to locate documents faster, but also to completely eliminate the need to access them, in an ideal scenario that allows achieving the highest possible level of optimization provided by an index in MongoDB databases.

Environment and configuration of the experiments

The experimental stage of this research aimed to measure the impact of different types of indexes and configurations on the read and write performance in MongoDB databases. To ensure the reproducibility of the results and the statistical validity of the confidence intervals, the computational environment, the database, and the measurement protocol were precisely described. The experiments were conducted in a single-instance local environment of MongoDB, version 8.0, running on the Windows Subsystem for Linux (WSL2) with Debian 10 distribution, on a 64-bit Windows 11 operating system.

The equipment used had an AMD Ryzen 7 3700U processor (2.30GHz, 8 cores) and 20GB of RAM, with 17.9GB available for the Operating System. The main dataset was the NYC 311 Service Requests dataset, from the NYC Open Data portal (data.cityofnewyork.us), containing over 20 million public service request records since 2020. This dataset, with fields of various natures (dates, coordinates, high and low cardinality categories, free text), was suitable for applying a wide range of index types.

For experiments that required array-type fields, the sample_mflix dataset was used, a sample dataset officially distributed by MongoDB, containing movie documents with list fields such as genres and cast. In both cases, the data were imported into local MongoDB collections using the mongoimport tool. The measurement protocol adopted in all experiments was uniform: each query or operation was executed fifty consecutive times under the same conditions, except for write experiments, which had thirty repetitions due to the longer duration.

The average execution time was recorded in milliseconds, obtained through the `executionTimeMillis` metric of the MongoDB `explain` method. This volume of repetitions allowed for the construction of 95% confidence intervals for each measurement, as established in the Methodology. Additionally, the plan cache state was explicitly controlled in each experiment to ensure a rigorous distinction between cold and hot reads, where such distinction was relevant. Ten experiments were conducted, investigating the impact of single-field, compound, text, geospatial, and multikey indexes, in addition to the concepts of selectivity, covered queries, plan cache behavior, and index maintenance costs.

Experiment 1: Collection scan vs. single field index

The first experiment established the fundamental impact of indexing, measuring the average execution time of 50 identical queries on the NYC 311 collection. Two conditions were compared: with and without a single field index, applied to fields of distinct cardinalities: `borough` (low cardinality, with 5 values) and `created_date` (high cardinality). The plan cache was cleared before each series to isolate the effect of the index from the influence of the cache.

For the `created_date` field, the performance gain was expressive: the average execution time dropped from 6.658 ± 90 ms (COLLSCAN) to 0.1 ms with the index, representing a reduction of almost 100%. This result demonstrates the expected behavior of an index on a high-cardinality field, where the organization of entries in a B+ tree structure allows MongoDB to locate the target document directly, eliminating the need to scan over 5 million records in the collection.

For the `borough` field, with low cardinality, the scenario was different. The index reduced the average time from 10.280 ± 1.110 ms to 5.677 ± 107 ms, a reduction of 44.8%. However, the gain was substantially smaller than in the high cardinality field. As discussed by Chopade and Pachghare (2020), the effectiveness of an index is directly associated with the cardinality of the indexed field: fields with few distinct values have low selectivity, meaning that each index entry still points to a large number of documents in the collection. In the examined dataset, one of the possible values for the `borough` field is associated with more than 1 million documents, which imposes a high cost for individually fetching each record. This behavior anticipates the concept of an inflection point, explored in detail in experiment 3.

Experiment 2: Prefix rule in composite indexes

Experiment 2 measured the impact of the field order of a composite index on query performance with different filter combinations. Two indexes were created: BCD ({borough, complaint_type, created_date}) and DCB ({created_date, complaint_type, borough}). 50 queries were executed per scenario with variations of the fields present in the search filter in relation to the prefix of each index.

The results confirmed the prefix rule described by Chopade and Pachghare (2020): MongoDB uses a composite index only when the filter fields form a continuous prefix starting from the first field of the index. Queries that respected the BCD index prefix obtained progressively lower times as more fields were added to the filter. For example, the query with only `borough` took 43.890 ms, while with an additional filter of `complaint_type` the time dropped to 0.3 ms, due to the smaller number of documents returned.

Queries that violated the prefix rule, such as filtering only by `complaint_type` without including `borough`, resulted in COLLSCAN with 6912 ms, confirming that the index was entirely ignored by the optimizer. A result that deserves attention is the 43,890 ms time for the query with the `borough` prefix. Although the BCD index was correctly used (IXSCAN), the `borough` filter has low selectivity, which forced the search engine to traverse over 1 million index entries and then fetch each of the corresponding documents individually from disk in the fetch stage. This accumulated cost made the operation with the index 4.3 times slower than a direct sequential scan.

This phenomenon is precisely the bias described by Tao et al. (2024) in the FPTP mechanism: the optimizer chose the IXSCAN because it returned the first 101 documents quickly during the plan-running phase, without accounting for the total cost of the complete execution. This result reinforces that the existence of an index and adherence to the prefix rule are not always sufficient to guarantee efficiency; field selectivity is determinant. Finally, the DCB index completely ignored the query for `borough` (COLLSCAN, 8.312 ms), but handled it in 0.0 ms when the three fields were provided with the correct prefix. This demonstrates that the order of fields in the index should reflect the application’s actual query patterns, and that a poorly ordered index can be completely useless for the most frequent use cases.

Experiment 3: Selectivity inflection point

Experiment 3 investigated the tipping point described by Tao et al. (2024), which is the selectivity threshold beyond which the use of an index ceases to be advantageous compared to a sequential scan of the collection. For this purpose, eight types of complaints were selected from the NYC 311 database with selectivities ranging from 0.25% to 15.38% of the collection. For each, 50 executions were performed under three conditions: optimizer’s free decision, forced IXSCAN (via hint), and forced COLLSCAN (via $natural).

At low selectivities, IXSCAN was expressively superior: for 0.25% selectivity, IXSCAN completed the query in 40 ± 2 ms, versus 7,206 ± 244 ms for COLLSCAN, a difference of 180 times. As selectivity increased, IXSCAN’s advantage progressively decreased. From approximately 2% selectivity onwards, the times for IXSCAN and COLLSCAN began to converge, but still with an advantage for execution with the index.

The underlying mechanism revealed that the number of documents examined by IXSCAN grows linearly with selectivity, while COLLSCAN always examines the entire collection (over 5 million documents), regardless of the filter. The intersection of the two time curves occurs precisely in the interval where the accumulated cost of traversing the B+ tree and individually searching each document on disk exceeds the cost of a sequential read. This finding reinforces the importance of considering selectivity for the indexing decision.

Experiment 4: Covered Queries

Experiment 4 evaluated the impact of eliminating the fetch stage through covered queries. After ensuring the clean state of the collection (without indexes), 50 executions were measured under three conditions for the query {borough: “MANHATTAN”, complaint_type: “Noise – Residential”}: without index (COLLSCAN), with a composite index {borough, complaint_type, created_date} and uncovered projection (IXSCAN + FETCH), and with the same index and covered projection (fields returned restricted to those present in the index).

The COLLSCAN condition completed the query in 9.968 ms, scanning all 5 million documents in the collection. With an index and a non-covered projection, the time dropped to 3.338 ms, which represents a significant reduction, although the search engine still needed to individually access the documents that satisfied the filter in the fetch stage to obtain the values of the non-covered fields. The covered query, in turn, completed execution in just 119 ms, which represents a 96% reduction compared to IXSCAN with fetch.

The mechanism responsible for this difference was confirmed by the `totalDocsExamined` metric, which was zero for the covered query, while in the query with IXSCAN and uncovered fields in the index, it was necessary to examine 88,627 documents to obtain the values of the uncovered fields. As explained by Chopade and Pachghare (2020), a covered query is one whose filter and projection fields are entirely present in the same composite index, allowing the search engine to respond directly to the search without needing additional access to document storage. Tao et al. (2024) identify this scenario as having the lowest possible cost in terms of work units, completely eliminating the I/O operations associated with retrieving raw data. Therefore, identifying scenarios conducive to covered queries constitutes the most efficient form of indexing optimization available in MongoDB.

Experiment 5: Plan cache – cold vs. hot reads

Experiment 5 isolated the impact of plan caching on query runtime, differentiating three series of 50 executions: cold reads (cache cleared before each execution), hot reads (cache cleared once before the entire series), and a post-invalidation series (cache warmed up and deliberately invalidated by creating a new index). The cold reads curve showed a significant peak in the first executions: the first query took 7,476 ms, double the stabilized value, followed by an abrupt drop to the level of approximately 3,600 ms from the third execution onwards.

This initial peak corresponds to the “run” phase of plans of the FPTP mechanism described by Tao et al. (2024): without a valid plan in the cache, the optimizer executes multiple candidates in parallel to elect the winner, which imposes an additional cost compared to subsequent executions, given that in the next queries the plan is already stored. After the cache warmup effect, the distributions of the three series were statistically similar, with overlapping confidence intervals, indicating that, once the cache is stabilized, its state does not materially alter the average runtime for this specific query.

The relevant outcome is the transient behavior, where applications that perform infrequent queries on a given collection, or that constantly invalidate the cache, are affected by the cost of the plan selection phase. This highlights the importance of considering the cache lifecycle and query frequency when designing indexing strategies, especially in production environments with variable workloads.

Experiment 6: Text index vs. regular expression

Experiment 6 compared two approaches for textual searches in the NYC 311 collection: the `$regex` operator with sequential scan and the `$text` operator with a text index. 50 runs per condition were performed on the `resolution_description` field, which contains textual descriptions. The results demonstrated an expressive performance difference. The `$regex` search performed a COLLSCAN on all over 5 million documents and completed each run in 15,590 ± 185 ms.

In contrast, the search with `$text` used the index and completed the same search in 730 ± 30 ms, being 21 times faster. The search excluding terms, which requires additional evaluation by the textual engine to filter results, took 1,791 ms ± 47 ms with the index, still 8.7 times faster than `$regex`. These results demonstrate the superiority of the text index for complex textual searches.

However, this optimization comes with a significant storage cost: the text index occupied 656.3MB on disk, which is approximately 12 times larger than the size of the default (_id) index. This storage cost can, in some cases, even exceed the size of the documents themselves and should be considered when deciding to create the index. For this experiment, the text index for a single field occupied about 13% of the total space consumed by the collection, with over 5 million documents.

Experiment 7: Geospatial index

Experiment 7 evaluated the performance of geospatial queries with MongoDB’s 2dsphere index. For this, the `location_geo` field was created in the documents of the NYC 311 collection in GeoJSON format. 50 executions per condition were performed with the `$near` operator for searches with radii ranging from 500m to 10km, in addition to a series with the `$geoWithin` operator applied to a polygon corresponding to the island of Manhattan.

The most immediate result was that the `$near` operation executed all searches for different radii in statistically equivalent average times, ranging from 17.5 ± 4.1 ms for 500 m to 15.9 ± 0.7 ms for 10 km, with no noticeable growth as the radius increased. This behavior is explained by the limit of 1000 returned documents configured in the experiment: regardless of the radius, the index returns the 1000 documents closest to the searched reference point, so the cost remained constant, demonstrating the efficiency of the 2dsphere index for proximity queries with limited returns.

In contrast, the `$geoWithin` operator with the Manhattan polygon presented opposite behavior: without limiting the returned documents, the search engine needed to examine all 1,206,246 records with coordinates within the polygon, resulting in an average time of 51,797 ± 377 ms. This contrast highlights that the use of `$geoWithin` should be accompanied by additional filters or explicit return limits in applications requiring low latency, as searching large areas, even with an index, can be costly.

Experiment 8: Multikey index

Experiment 8 evaluated the effectiveness of the multikey index for searches in array-type fields. The `sample_mflix.movies` collection was used with the `genres` field, which stores an array with one to five genres per movie. 50 runs were performed for each genre with and without an index, in addition to queries with the `$all` operator searching for multiple elements simultaneously.

The gain provided by the multi-key index was strongly dependent on the selectivity of the searched genre. For “Western” (1.1% of the collection, 242 films), the index reduced the time from 20 ms to 3 ms. For “Romance” (15.5% of the collection, 3318 films), the operation went from 19.4 ms to 6.3 ms. This pattern confirms that the multi-key index operates under the same principles as the single-field index: it creates individual entries for each field of the array, such that the smaller the proportion of documents covered, the greater the benefit provided by the index compared to sequential scanning.

The limit of the multi-key index manifested itself in high selectivity genres. For “Drama” (58% of the collection, 12,385 films), the index resulted in an average time of 20.5 ms, slightly higher than the 18.6 ms without an index. This result reinforces the concept of an inflection point observed in experiment 3, now on a reduced scale: to return many documents, a COLLSCAN can be more advantageous than an IXSCAN + fetch in some scenarios, indicating that selectivity is a critical factor even for multi-key indexes.

Experiment 9: Impact of indexes on writing performance

Experiment 9 measured the relationship between the number of indexes maintained by a collection and the latency of write operations. To isolate this effect, a temporary collection with synthetic documents in the NYC 311 format was used. Scenarios with 0 to 5 additional indexes to the default `_id` were tested, and for each scenario, 30 batch insertions of 1000 documents (insertMany) and 30 bulk update operations (updateMany) were performed.

The results confirmed the direct proportionality relationship between the number of indexes and write latency described by Chopade and Pachghare (2020). For insertion operations, the average time grew from 19.4 ± 1 ms (with only the `_id` index) to 32.3 ± 2.9 ms with 5 additional indexes, an accumulated overhead of 66%. The linear trend was visible throughout the entire curve. For bulk updates, the pattern was similar: from 135.7 ± 20.3 ms without additional indexes to 207.6 ± 50.1 ms with five indexes, an overhead of 53%.

The observed trend justifies Chopade and Pachghare’s (2020) recommendation to consolidate composite indexes that serve multiple query patterns instead of maintaining individual overlapping indexes. This approach aims to minimize the impact on write operations without compromising read performance, optimizing server resource utilization and ensuring system scalability.

Experiment 10: Partial index

Experiment 10 evaluated the partial index, which restricts its coverage to a subset of collection documents according to a filter expression. A full index on `complaint_type` and a partial index on the same field with a filter to cover only documents with status “Open” were compared, measuring both query performance and storage space occupied by each index.

The size of the partial index occupied only 0.42 MB compared to 26.46 MB of the full index, representing a saving of 98.4%. This proportion directly reflects the fraction of documents that meet the status filter in the collection (only 1.4% of the total), as the partial index creates entries only for documents that satisfy the filter. In the performance panel, the behavior of the partial index proved to be accurate in covering its scope. For the “Open” status query with a filter that should not return any documents, the partial index completed the execution in approximately 0 ms with no documents examined.

The same search without an index (COLLSCAN) took 11.461 ± 1.329 ms to reach the same conclusion after scanning over 5 million documents. For a query not covered by the index, i.e., a search for status “Closed”, the results with and without the index were equivalent, as expected, given that the index was not applied because it did not meet the filter. This set of results demonstrates that the partial index is a highly specific optimization tool: it provides maximum gain for the subset of documents covered, creates no maintenance cost for write operations outside the filter, and occupies a smaller fraction of storage compared to the full index.

In summary, the experiments demonstrated that the effectiveness of indexing in MongoDB is intrinsically linked to attribute cardinality, the correct application of the prefix rule in composite indexes, and query selectivity, which defines the inflection point where the index benefit can be outweighed by the sequential scan cost. Covered queries represent the apex of optimization, eliminating disk access, while plan caching influences transient performance. However, the accumulation of indexes increases write latency and resource contention, and specific indexes like text and geospatial, although powerful, demand consideration of their storage costs and application limits. Careful planning of data distribution and index configuration is, therefore, fundamental to prevent suboptimal strategies of MongoDB’s query planner and ensure scalability.

4. Conclusion

This study evaluated the influence of index structure and configuration on read performance and write operational cost in MongoDB databases. It was found that single-field indexes provided execution time reductions of almost 100% for high-cardinality attributes, although their effectiveness was limited in low-cardinality fields due to selectivity. The prefix rule in compound indexes proved to be determinant, and an inflection point was identified where the individual document lookup cost surpassed sequential scanning. Additionally, it was observed that covered queries represent the pinnacle of optimization, eliminating the need for disk access. The experiments also revealed the bias of MongoDB’s First Past The Post (FPTP) mechanism, which, by prioritizing the first results, can fix suboptimal plans for queries returning large volumes of data.

In contrast, the excessive accumulation of indexes increased write latency and resource contention in the storage engine, highlighting a crucial trade-off. Specific indexes, such as text and geospatial ones, proved powerful for their purposes but with significant storage costs, while partial indexes offered targeted optimization and space savings. The main contribution of this work lies in providing a solid foundation for decision-making in high-volume data architectures, where query optimization is strategic. Therefore, careful planning of data distribution and index configuration is recommended to mitigate the risks of suboptimal strategies and ensure system scalability.

Bibliographic References

Chopade, R.; Pachghare, V. 2020. MongoDB Indexing for Performance Improvement. In: ICT Systems and Sustainability, 2020, Goa, Índia. Anais… p. 529-539.

data.cityofnewyork.us [Referência completa não encontrada no documento original]

Marat et al., 2024 [Referência completa não encontrada no documento original]

Tao, D.; Enqi, L.; Kadupitige, S. R.; Cahill, M.; Fekete, A.; Röhm, U. 2024. First past the post: evaluating query optimization in MongoDB. In: Australian Database Conference, 2024, Gold Coast, Australia. Anais… p. 99-113.

Article originating from the Final Course Work of the Specialization in Software Engineering of the MBA USP/Esalq

To learn more about the course, click here and access the MBX Academy platform