Query planner, explain plans and covered queries — researched

How a database decides to use an index: explain plans, index scans versus collection/table scans, covered queries, selectivity and cardinality, the ESR guideline for compound-index key order, hints and in-memory sorts. Child pack of Indexing.

Definitions

Selectivity is a query property that describes the ratio of documents matching the query versus the total number of documents in a collection. The selectivity of an index describes how many documents a unique index key matches. A query or index has high selectivity when proportionally few documents match a query or a given index key. [source]

Structure and components

The order of the indexed fields impacts the effectiveness of a compound index. Compound indexes contain references to documents according to the order of the fields in the index. To create efficient compound indexes, follow the [ESR (Equality, Sort, Range) guideline.](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/#std-label-esr-indexing-guideline) [source]
An index covers a query when the index contains all of the fields scanned by the query. A covered query scans the index and not the collection, which improves query performance. [source]
The best way to filter in Convex is to use indexes. Indexes build a special internal structure in your database to speed up lookups. [source]
Each index that the Performance Advisor suggests contains the following metrics. These metrics apply specifically to queries which would be improved by the index: [source]
| Method | Availability | Description | |---|---|---| | View plan cache statistics | Atlas clusters and self-hosted deployments | The [`$planCacheStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/planCacheStats/#mongodb-pipeline-pipe.-planCacheStats) aggregation stage returns information about a collection's[plan cache.](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) The plan cache contains query plans that the query planner uses to efficiently complete queries. Generally, the plan cache should contain entries for your m [source]
For example, consider a query for `"item": "saccharomyces cerevisiae"` and `"stock": 60`. If the collection contains 10000 documents matching `"item": "saccharomyces cerevisiae"` and only 100 of those documents match `"stock": 60`, the query examines 10000 keys. In the `IXSCAN` stage, the query filters those keys by the `stock` field and only returns 100 results to the next stage. [source]
| Column | Description | | ------- | ------------------------------------------------ | | id | A unique identifier for this step | | parent | The id of the parent step (0 for top-level) | | notused | Reserved for future use (always 0) | | detail | Human-readable description of the execution step | [source]

How it works

- `cursor.hint(index)`- ## Important**mongosh Method**This page documents a [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) method. This is*not* the documentation for a language-specific driver, such as Node.js.For MongoDB API drivers, refer to the language-specific [MongoDB driver documentation.](https://www.mongodb.com/docs/drivers/)Call this method on a query to override MongoDB's default index selection and [query optimization process](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-read-operations-query-optimization) . Use[`db.collection [source]
- [`queryPlanner.winningPlan.queryPlan.inputStage.stage`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.queryPlanner.winningPlan.queryPlan.inputStage) displays`IXSCAN` to indicate index use. - [`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.nReturned) displays`3` to indicate that the winning query plan returns three documents. - [`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalKeysExamin [source]
- The Query Performance Summary shows the execution stats of the query: - Documents Returned displays `3` to indicate that the winning query plan returns three documents. - Index Keys Examined displays `3` to indicate that MongoDB scanned three index entries. The number of keys examined match the number of documents returned, meaning that the[`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) only had to examine index keys to return the results. The[`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) [source]
`CREATE INDEX` builds an index on one or more columns or expressions of a table. Turso uses B-tree indexes in the same format as SQLite. The query planner automatically uses indexes when they can speed up a query -- you do not need to reference an index explicitly in your SQL statements. [source]
Because indexes can have different selectivities depending on the index keys used, ensure that the most selective indexes are available based on the predicates contained in a query. To ensure the most efficient query execution, create indexes that most uniquely match the predicates contained in a query. [source]
The [`.withIndex`](/api/interfaces/server.QueryInitializer.md#withindex) method defines which index to query and how Convex will use that index to select documents. The first argument is the name of the index and the second is an *index range expression*. An index range expression is a description of which documents Convex should consider when running the query. [source]
This query is invalid because the `by_channel` index is ordered by `(channel, _creationTime)` and this query range has a comparison on `_creationTime` without first restricting the range to a single `channel`. Because the index is sorted first by `channel` and then by `_creationTime`, it isn't a useful index for finding messages in all channels created 1-2 minutes ago. The TypeScript types within `withIndex` will guide you through this. [source]
If the index range is not specified, all documents in the index will be considered in the query. [source]
**Index bounds** define the range of index values that MongoDB searches when using an index to fulfill a query. When you specify multiple query predicates on an indexed field, MongoDB attempts to combine the bounds for those predicates to produce an index scan with smaller bounds. Smaller index bounds result in faster queries and reduced resource use. [source]
For a compound index where the index prefix keys are not strings, arrays, and embedded documents, an operation that specifies a different collation can still use the index to support comparisons on the index prefix keys. [source]
- [`$regex`](https://www.mongodb.com/docs/manual/reference/operator/query/regex/#mongodb-query-op.-regex) is a range operator. - When `$in` is used alone, it is an equality operator that performs a series of equality matches. - When `$in` is used with`.sort()` : - If `$in` has fewer than 201 array elements, the elements are expanded and then merged in the sort order specified for the index using a`SORT_MERGE` stage. This improves performance for small arrays. In this case,`$in` is similar to an equality predicate with ESR. - If `$in` has 201 elements or more, the elements are ordered like a ra [source]
When you create an index, you can give the index a custom name. Giving your index a name helps distinguish different indexes on your collection. For example, you can more easily identify the indexes used by a query in the query plan's [explain results](https://www.mongodb.com/docs/manual/reference/explain-results/#std-label-explain-results) if your indexes have distinct names. [source]
- If `totalDocsExamined` has a value much greater than that of`nReturned` , it indicates an ineffective index. That is, MongoDB had to scan the collection in order to filter the results.[Create an index](https://www.mongodb.com/docs/manual/core/indexes/create-index/#std-label-manual-create-an-index) on the filter fields to improve performance. - If `totalDocsExamined` and`nReturned` have the same values, it indicates that MongoDB only examined the documents that it returned. This indicates an effective index. [source]
The `USE INDEX` hint suggests to the optimizer which indexes to consider when processing the query. The optimizer is not forced to use these indexes but will prioritize them if they are suitable. [source]
The ANALYZE statement collects statistics about the contents of tables and indexes. The query optimizer uses these statistics to choose better query plans, particularly when deciding which index to use and how to order joins. [source]
The ANALYZE statement gathers statistics about the distribution of values in indexes and stores the results in the `sqlite_stat1` table (and optionally `sqlite_stat4`). The query optimizer reads these statistics to make better decisions about: [source]
* [**Creating and Altering Tables**](postgres/createtable): Tables are the basic building block of a relational database. Tables (sometimes referred to as relations or tuples) are used to store data in rows and columns. We will cover how to create tables, add columns and define constraints such as primary keys and foreign keys. * [**Data Types**](postgres/datatype/): Postgres supports a wide range of data types for storing different types of data. In a table, each column has a data type, and based on this data type, Postgres allocates storage and allows various operations. We will cover some o [source]
This query is a *full table scan* because it requires Convex to look at every document in the table. The performance of this query is based on the number of books in the library. [source]
One option is to re-sort the entire library by `author`. This will solve our immediate problem but now our original queries for `firstBook` and `lastBook` would become full table scans because we'd need to examine every book to see which was inserted first/last. [source]
The columns passed to `fts_match` must correspond to columns in an existing FTS index. When Turso's query planner detects `fts_match` in a WHERE clause, it routes the query through the FTS index for efficient lookup. [source]
Adding an index to an existing table triggers a full table scan, with one read per existing row. [source]
* Which index to use for a query * The order in which to process tables in a join * Whether to use an index or a full table scan [source]
The index only supports queries on fields included in the `wildcardProjection` object. In this example, MongoDB performs a collection scan for the following query because it includes a field that is not present in the `wildcardProjection` object: [source]
Each index suggestion includes an Average Query Targeting score indicating how many documents were read for every document returned for the index's corresponding query shapes. A score of 1 represents very efficient query shapes because every document read matched the query and was returned with the query results. All suggested indexes represent an opportunity to improve query performance. [source]
in.mongod) did not have to scan all of the documents, and only the three matching documents had to be pulled into memory. This results in a very efficient query. - [`executionStats.totalDocsExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalDocsExamined) display`3` to indicate that MongoDB scanned three documents. [source]
The [`explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) method provides information on how MongoDB plans and executes the given query. You may find this information useful when troubleshooting query performance and planning optimizations. [source]

How-to and procedures

To improve query performance, you can create a [compound text index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/create-text-index/#std-label-compound-text-index-example) and include an equality match in your `$text` queries. If the compound index contains the field used in your equality match, the index scans fewer entries and returns results faster. [source]
To manually compare the performance of a query using more than one index, you can use the [`hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint/#mongodb-method-cursor.hint) method in conjunction with the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) method. [source]
To learn how to create indexes that the Performance Advisor suggests, see [Create Suggested Indexes.](https://www.mongodb.com#std-label-pa-create-suggested-indexes) [source]
You can create [indexes](https://www.mongodb.com/docs/manual/core/indexes/) suggested by the Performance Advisor directly within the Performance Advisor itself. When you create indexes, keep the ratio of reads to writes on the target collection in mind. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. To learn more about indexing strategies, see [Indexing Strategies.](https://www.mongodb.com/docs/manual/applications/indexes/) [source]
To better understand what queries can be run over which indexes, see [Introduction to Indexes and Query Performance](/database/reading-data/indexes/indexes-and-query-perf.md). [source]
To view the query plan statistics, use the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) method: [source]
You can also use index attributes to speed up querying. An additional benefit is that indexed attributes can be used with comparison operators for where queries like `$gt`, `$lt`, `$gte`, and `$lte` and can be used in `order` clauses. [source]
To confirm whether a query used an index, run the query with the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) option. [source]
To force MongoDB to use a particular index, use [cursor.hint() (mongosh method)](https://www.mongodb.com/docs/manual/reference/method/cursor.hint/#std-label-cursor-hint) when testing indexes. [source]
Use **cursor-based pagination** for large datasets or infinite scroll. Cursor-based pagination scales better because it uses indexed columns to find the starting position instead of traversing skipped rows: [source]
Ensure that your queries are designed to take advantage of indexes for row filtering. The absence of suitable indexes forces SQLite to resort to full table scans, incrementally increasing the read count by one for each row in the table. Efficient indexing is key to minimizing this overhead. [source]
Incorporating necessary indexes at the table creation stage is a best practice. Adding indexes to tables that already contain rows triggers a full table scan, with each existing row necessitating one read. Proactive index management is crucial for maintaining optimal database performance. [source]
To view collections with slow queries and see suggested indexes, you must have [`Project Read Only`](https://www.mongodb.com/docs/atlas/reference/user-roles/#mongodb-authrole-Project-Read-Only) access or higher to the project. [source]
You can also adjust the time range the Performance Advisor takes into account when suggesting indexes by using the Time Range dropdown at the top of the Performance Advisor. [source]
To view the query plan selected, chain the [`cursor.explain("executionStats")`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) cursor method to the end of the **find** command: [source]
Check the [`explain.executionStats.executionTimeMillis`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.executionTimeMillis) field to see the execution time in milliseconds. This shows the total time, including the time it takes to build and select a query plan in addition to the time it takes the plan to execute. [source]
Check the [`inputStage.stage`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.executionStages.inputStage) field for each execution stage: [source]
Check the total values for the query and ensure that [`executionStats.totalDocsExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalDocsExamined) does not show a value greater than `executionStats.totalKeysExamined`. [source]
Use `$natural` in conjunction with `cursor.hint()` to perform a collection scan to return documents in [natural order.](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-natural-order) [source]
To see how many documents were scanned to return the query, view the query's `executionStats`: [source]
To avoid an in-memory sort, place the range filter after the sort predicate. For more information on in-memory sorts, see `cursor.allowDiskUse()`. [source]
Run the query you want to evaluate with the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) method: [source]

Measurements and reference values

In this example, the `status` of 99% of documents in the collection is `processed`. If you add an index on `status` and query for documents with the `status` of `processed`, both the index and the query have low selectivity. However, if you want to query for documents that do **not** have the `status` of `processed`, the index and the query have high selectivity because the query only returns 1% of the documents in a collection. [source]
| Metric | Description | |---|---| | Execution Count | Number of queries executed per hour which would be improved. | | Average Execution Time | Current average execution time in milliseconds for affected queries. | | Average Query Targeting | Average number of documents read per document returned by affected queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see [Query Targeting.](https://www.mongodb.com#std-label-query-targeting) | | In Memory Sort | Current number of affected queries per hour that needed to be sorted [source]

Problems, failure modes and limitations

- [`queryPlanner.winningPlan.queryPlan.stage`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.queryPlanner.winningPlan.queryPlan.stage) displays`COLLSCAN` to indicate a collection scan.Collection scans indicate that the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries. - [`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-result [source]
- When an [index filter](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-index-filters) exists for the query shape, MongoDB ignores the[`hint()`.](https://www.mongodb.com#mongodb-method-cursor.hint) - If a query includes a `$text` expression, you cannot use[`hint()`](https://www.mongodb.com#mongodb-method-cursor.hint) to specify which index to use for the query. - If you use [`hint()`](https://www.mongodb.com#mongodb-method-cursor.hint) on a[hidden index](https://www.mongodb.com/docs/manual/core/index-hidden/) or an index that doesn't exist, the operation returns an error. - On [source]
- The Query Performance Summary shows the execution stats of the query: - Documents Returned displays `3` to indicate that the winning query plan returns three documents. - Index Keys Examined displays `0` to indicate that this query is not using an index. - Documents Examined displays `10` to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents. - Below the Query Performance Summary, MongoDB Compass displays the `COLLSCAN` query stage to indicate that a collection scan was used for this query.Collection scans indicate that [source]
Hidden indexes are not visible to the [query planner](https://www.mongodb.com/docs/manual/core/query-plans/) and cannot be used to support a query. [source]
When your query fetches documents from the database, it will scan the rows in the range you specify. If you are using `.collect()`, for instance, it will scan all of the rows in the range. So if you use `withIndex` without a range expression, you will be [scanning the whole table](https://docs.convex.dev/database/indexes/indexes-and-query-perf#full-table-scans), which can be slow when your table has thousands of rows. `.filter()` doesn't affect which documents are scanned. Using `.first()` or `.unique()` or `.take(n)` will only scan rows until it has enough documents. [source]
- The following fields in the `options` document are not available in Stable API V1: - `background` - `bucketSize` - `sparse` - `storageEngine` - [Text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/#std-label-index-type-text) indexes are not available in Stable API V1. - The above unsupported index types are ignored by the [query planner](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) in[strict mode](https://www.mongodb.com/docs/manual/reference/stable-api/#std-label-stable-api-strict-client) . For example, attempting [source]
The `IGNORE INDEX` hint tells the optimizer to avoid using specific indexes for the query. MySQL will consider all other indexes (if any) or perform a full table scan if necessary. [source]
The `FORCE INDEX` hint forces the optimizer to use the specified index(es) for the query. If the specified index cannot be used, MySQL will not fall back to other indexes; it might resort to a full table scan instead. [source]
MongoDB cannot compound the index bounds and the `"ratings.scores.q2"` field is unconstrained during the index scan. [source]
The [`min()`](https://www.mongodb.com#mongodb-method-cursor.min) and [`max()`](https://www.mongodb.com/docs/manual/reference/method/cursor.max/#mongodb-method-cursor.max) methods indicate that the system should avoid normal query planning. They construct an index scan where the index bounds are explicitly specified by the values given in [`min()`](https://www.mongodb.com#mongodb-method-cursor.min) and `max()`. [source]
> [!WARNING] > For databases that don't enforce foreign keys (like PlanetScale), Prisma ORM emulates relations and you should manually add indexes on relation scalar fields to avoid full table scans: > > ```prisma title="prisma/schema.prisma" > model Comment { > postId Int > post Post @relation(fields: [postId], references: [id]) > > @@index([postId]) > } > ``` [source]
- To hide an index, you must have [featureCompatibilityVersion](https://www.mongodb.com/docs/manual/reference/command/setFeatureCompatibilityVersion/#std-label-view-fcv) set to`6.0` or greater. - You cannot hide the `_id` index. - You cannot [`cursor.hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint/#mongodb-method-cursor.hint) a hidden index. [source]
The `ship.coordinates` field contains embedded arrays. Wildcard indexes do not record individual values of embedded arrays. Instead, they record the entire embedded array. As a result, the wildcard index cannot support a match on an embedded array value, and MongoDB fulfills the query with a collection scan. [source]
For document queries that return larger numbers of documents, you'll want to use an [index](/database/reading-data/indexes/.md) to improve the performance. Document queries that use indexes will be [ordered based on the columns in the index](/database/reading-data/indexes/.md#sorting-with-indexes) and can avoid slow table scans. [source]
This is okay, but we're still at risk of having a slow query because too many books have a title of *Foundation*. An even better approach could be to build a *compound* index that indexes both `author` and `title`. Compound indexes are indexes on an ordered list of fields. [source]
- You can't create indexes through the Performance Advisor if [Data Explorer](https://www.mongodb.com/docs/atlas/atlas-ui/#std-label-atlas-ui) is disabled for your project. You can still view the Performance Advisor recommendations, but you must create those indexes from[`mongosh`.](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) - Atlas always creates indexes for entire clusters. If you create an index while viewing the Performance Advisor for a single shard in a sharded cluster, Atlas creates that index for the entire sharded cluster. [source]
- Create queries that your current indexes support to reduce the time needed to search for your results. - Avoid creating documents with large array fields that require a lot of processing to search and index. - Optimize your indexes and remove unused or inefficient indexes. Too many indexes can negatively impact write performance. - Consider the suggested indexes from the [Performance Advisor](https://www.mongodb.com/docs/atlas/performance-advisor/#std-label-performance-advisor) with the highest Impact scores and lowest Average Query Targeting scores. - Create the indexes that the Performance [source]
If you run `$text` queries on a large dataset, a single-field text index may scan a large number of entries to return results, which can result in slow queries. [source]
If MongoDB cannot compound the two bounds, MongoDB constrains the index scan by the bound on the leading field. In this example, the leading field is `temperature`, resulting in a constraint of `temperature: [ [ 80, Infinity ] ]`. [source]
These APIs allow you to efficiently limit your query to a reasonable size without performing a full table scan. [source]
If your Convex table has a small number of documents, this is fine! Full table scans should still be fast if there are a few hundred documents, but if the table has many thousands of documents these queries will become slow. [source]
"Sort" determines the order for results. To avoid in-memory sorts, put sort fields before range in the index. [source]
If you do not add the index manually, queries might require full table scans. This can be slow, and also expensive on database providers that bill per accessed row. To help avoid this, Prisma ORM warns you when your schema contains fields that are used in a `@relation` that does not have an index defined. For example, take the following schema with a relation between the `User` and `Post` models: [source]
However, the following query operation, which by default uses the "simple" binary collator, cannot use the index and requires a `COLLSCAN`. [source]
The [Performance Advisor](https://www.mongodb.com/docs/atlas/performance-advisor/#std-label-performance-advisor) monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. [source]
The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. The threshold for slow queries varies based on the average time of operations on your cluster to provide recommendations pertinent to your workload. [source]
The Performance Advisor can't suggest indexes for MongoDB databases configured to use the `ctime` timestamp format. As a workaround, set the timestamp format for such databases to either `iso8601-utc` or `iso8601-local`. To learn more about timestamp formats, see [mongod --timeStampFormat.](https://www.mongodb.com/docs/manual/reference/program/mongod/#std-option-mongod.--timeStampFormat) [source]
Without the index, the query would scan the whole collection of `10` documents to return `3` matching documents. The query also had to scan the entirety of each document, potentially pulling them into memory. This results in an expensive and potentially slow query operation. [source]
| Method | Availability | Description | |---|---|---| | Use the Atlas Performance Advisor | M10+ Atlas clusters | The Atlas Performance Advisor monitors slow queries and suggests new indexes to improve performance. For more information, see [Monitor and Improve Slow Queries with the Performance Advisor.](https://www.mongodb.com/docs/atlas/performance-advisor/#std-label-performance-advisor) | | Check ongoing operations in Atlas | M10+ Atlas clusters | You can use the [Atlas Real-Time Performance Panel](https://www.mongodb.com/docs/atlas/real-time-performance-panel/#std-label-real-time-metrics-s [source]
Explain output is limited by the maximum [Nested Depth for BSON Documents](https://www.mongodb.com/docs/manual/reference/limits/#mongodb-limit-Nested-Depth-for-BSON-Documents), which is 100 levels of nesting. Explain output that exceeds the limit is truncated. [source]

Comparisons and alternatives

The second compound index, `{ type: 1, quantity: 1 }`, is therefore the more efficient index for supporting the example query, as the MongoDB server only needs to scan `2` [`index keys`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalKeysExamined) to find all matching documents using this index, compared to `5` when when using the compound index `{ quantity: 1, type: 1 }`. [source]
- `COLLSCAN` - Indicates MongoDB performed a collection scan. - `IXSCAN` - Indicates MongoDB performed an index scan. - `FETCH` - Indicates MongoDB fetched full documents from the database. If the query returns a small number of fields and the application is not write intensive on this collection, consider adding indexes to cover the query. This allows MongoDB to fetch the field values from the index rather than reading the full document. For more information, see [Run Covered Queries.](https://www.mongodb.com/docs/manual/core/query-optimization/#std-label-covered-queries) - `PROJECTION` - Ind [source]
If one of the two boundaries is not specified, the query plan will be an index scan that is unbounded on one side. This may degrade performance compared to a query containing neither operator, or one that uses both operators to more tightly constrain the index scan. [source]
Bloom indexes in PostgreSQL are useful for multi-column searches with high-cardinality data. They offer space efficiency but come with some trade-offs, such as potential false positives and lack of range query support. [source]
By hiding an index from the planner, you can evaluate the potential impact of dropping an index without actually dropping the index. If the impact is negative, you can unhide the index instead of having to recreate a dropped index. [source]
`cursor.explain()` defaults to `queryPlanner`, unlike the [`explain`](https://www.mongodb.com/docs/manual/reference/command/explain/#mongodb-dbcommand-dbcmd.explain) command, which defaults to `allPlansExecution`. [source]

Changes and history

| Behavior Starting in MongoDB 7.1 | Behavior in Earlier MongoDB Versions | |---|---| | Index errors found during the collection scan phase, except duplicate key errors, are returned immediately and then the index build stops. Earlier MongoDB versions return errors in the commit phase, which occurs near the end of the index build. MongoDB 7.1 helps you to rapidly diagnose index errors. For example, if an incompatible index value format is found, the error is returned to you immediately. | Index build errors can take a long time to be returned compared to MongoDB 7.1 because the errors are retu [source]

Facts and statements

* **BRIN** stands for **Block Range Index**. * Designed for handling very large tables with columns that have natural correlation to their physical location within the table. * Works in terms of **block ranges** (or "page ranges"). * Each block range groups physically adjacent pages in the table. * Summary information is stored by the index for each block range. * **Lossy**: BRIN indexes can satisfy queries via regular bitmap index scans but are lossy, meaning the query executor rechecks tuples and discards those not matching query conditions. * Size of block range determined at index creation [source]
| Detail Pattern | Meaning | | -------------------------------------------------- | ------------------------------------------------- | | `SCAN table` | Full table scan (no index used) | | `SEARCH table USING INDEX idx (col=?)` | Index lookup on the specified column | | `SEARCH table USING INTEGER PRIMARY KEY (rowid=?)` | Direct rowid lookup | | `USE TEMP B-TREE FOR ORDER BY` | A temporar [source]
* [DROP INDEX](/sql-reference/statements/drop-index) for removing indexes * [REINDEX](/sql-reference/statements/reindex) for rebuilding indexes * [CREATE TABLE](/sql-reference/statements/create-table) for inline UNIQUE and PRIMARY KEY constraints * [EXPLAIN](/sql-reference/statements/explain) for verifying index usage in query plans [source]
4. **Query Using Index**: [source]
1. By default Convex queries are *full table scans*. This is appropriate for prototyping and querying small tables. 2. As your tables grow larger, you can improve your query performance by adding *indexes*. Indexes are separate data structures that order your documents for fast querying. 3. In Convex, queries use the *`withIndex`* method to express the portion of the query that uses the index. The performance of a query is based on how many documents are in the index range expression. 4. Convex also supports *compound indexes* that index multiple fields. [source]
The number of index keys examined is indicated in the [`totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalKeysExamined) field. Queries that examine more index keys generally take longer to complete. [source]
MongoDB scanned `5` index keys ([`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalKeysExamined)) to return `2` matching documents ([`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.nReturned)). [source]
MongoDB scanned `2` index keys ([`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalKeysExamined)) to return `2` matching documents ([`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.nReturned)). [source]
If the number of keys examined is much lower than the number of documents examined, check each stage in the [`executionStages`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.executionStages) field, comparing the `keysExamined` and `docsExamined` to determine which stage failed to use the index. Then, [create an index](https://www.mongodb.com/docs/manual/core/indexes/create-index/#std-label-manual-create-an-index) to accommodate the query at that stage. [source]
The choice of index both affects how you write the index range expression and what order the results are returned in. For instance, by making both a `by_channel` and `by_channel_user` index, we can get results within a channel ordered by `_creationTime` or by `user`, respectively. If you were to use the `by_channel_user` index like this: [source]
1. 0 or more equality expressions defined with [`.eq`](/api/interfaces/server.IndexRangeBuilder.md#eq). 2. \[Optionally] A lower bound expression defined with [`.gt`](/api/interfaces/server.IndexRangeBuilder.md#gt) or [`.gte`](/api/interfaces/server.IndexRangeBuilder.md#gte). 3. \[Optionally] An upper bound expression defined with [`.lt`](/api/interfaces/server.IndexRangeBuilder.md#lt) or [`.lte`](/api/interfaces/server.IndexRangeBuilder.md#lte). [source]
Picking a good index range [source]
For performance, define index ranges that are as specific as possible! If you are querying a large table and you're unable to add any equality conditions with `.eq`, you should consider defining a new index. [source]
`.withIndex` is designed to only allow you to specify ranges that Convex can efficiently use your index to find. For all other filtering you can use the [`.filter`](/api/interfaces/server.Query.md#filter) method. [source]
Wildcard indexes can support a [covered query](https://www.mongodb.com/docs/manual/core/query-optimization/#std-label-covered-queries) only if **all** of the following conditions are true: [source]
**You must step through fields in index order.** [source]
Each equality expression must compare a different index field, starting from the beginning and in order. The upper and lower bounds must follow the equality expressions and compare the next field. [source]
`DROP INDEX` removes a previously created index. After the index is dropped, the query planner can no longer use it to optimize queries. The table data is unchanged. [source]
MongoDB offers the ability to hide or unhide indexes from the query planner. By hiding an index from the planner, you can evaluate the potential impact of dropping an index without actually dropping the index. [source]
1. Make sure that your mutations only read the data they need. Consider reducing the amount of data read by using indexed queries with [selective index range expressions](https://docs.convex.dev/database/indexes/). 2. Make sure you are not calling a mutation an unexpected number of times, perhaps from an action inside a loop. 3. Design your data model such that it doesn't require making many writes to the same document. [source]
If the specified `lastName` is never an array, MongoDB can use the `$**` wildcard index to support a covered query. [source]
* [ANALYZE](/sql-reference/statements/analyze) for collecting statistics that improve query plans * [CREATE INDEX](/sql-reference/statements/create-index) for creating indexes to speed up queries [source]
1. Full table scans: Queries created with [fullTableScan](/api/interfaces/server.QueryInitializer.md#fulltablescan) which iterate over all of the documents in the table in insertion order. 2. Indexed Queries: Queries created with [withIndex](/api/interfaces/server.QueryInitializer.md#withindex) which iterate over an index range in index order. [source]
- The query planner selects the wildcard index to fulfill the query predicate. - The query predicate specifies *exactly* one field covered by the wildcard index. - The query projection explicitly excludes `_id` and includes*only* the query field. - The specified query field is never an array. [source]
If you query for `{ "status": 2, "product_type": "grocery" }`, MongoDB only reads one document matching the index key, indicating the index is highly selective. By using this index, you can receive a query response more efficiently, since MongoDB must only further filter one document matching the index value. In this case, the filter also matches, and the query only returns one document. [source]
Although this example's query on `status` equality is more selective, a query such as `{ "status": { $gt: 5 }, "product_type": "grocery" }` still needs to read four documents if you use the same index on `status`. However, if you create a compound index on `product_type` and `status`, MongoDB can more efficiently answer a query for `{"status": { $gt: 5 }, "product_type": "grocery" }` via the compound index, as the query returns only one matching document. [source]
iagnostic logs.](https://www.mongodb.com/docs/manual/reference/log-messages/#std-label-log-messages-ref) Check the diagnostic logs to identify problematic queries and see which queries would benefit from indexes. | | View explain results | Atlas clusters and self-hosted deployments | Query explain results show information on the query plan and execution statistics. You can use explain results to determine the following information about a query: The amount of time a query took to execute Whether the query used an index The number of documents and index keys scanned to fulfill a query To view e [source]
In relational databases that use foreign key constraints, the database usually also implicitly creates an index for the foreign key columns. For example, [MySQL will create an index on all foreign key columns](https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html#:~\:text=MySQL%20requires%20that%20foreign%20key%20columns%20be%20indexed%3B%20if%20you%20create%20a%20table%20with%20a%20foreign%20key%20constraint%20but%20no%20index%20on%20a%20given%20column%2C%20an%20index%20is%20created.). This is to allow foreign key checks to run fast and not require a table scan. [source]
rforming the query. Specify the index either by the index name or by the index specification document. You can also specify `{ $natural : 1 }` to force the query to perform a forwards collection scan, or`{ $natural : -1 }` for a reverse collection scan. [source]
The [`cursor.explain("executionStats")`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) and the [`db.collection.explain("executionStats")`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) methods provide statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index. See [`db.collection.explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) for deta [source]
{ queryPlanner: { ... winningPlan: { queryPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { quantity: 1 }, ... } } }, rejectedPlans: [ ] }, executionStats: { executionSuccess: true, nReturned: 3, executionTimeMillis: 0, totalKeysExamined: 3, totalDocsExamined: 3, executionStages: { ... }, ... }, ... } [source]
{ queryPlanner: { ... winningPlan: { queryPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { quantity: 1, type: 1 }, ... } } } }, rejectedPlans: [ ] }, executionStats: { executionSuccess: true, nReturned: 2, executionTimeMillis: 0, totalKeysExamined: 5, totalDocsExamined: 2, executionStages: { ... } }, ... } [source]
{ queryPlanner: { ... queryPlan: { winningPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { type: 1, quantity: 1 }, ... } } }, rejectedPlans: [ ] }, executionStats: { executionSuccess: true, nReturned: 2, executionTimeMillis: 0, totalKeysExamined: 2, totalDocsExamined: 2, executionStages: { ... } }, ... } [source]
Utilize the [`EXPLAIN QUERY PLAN`](https://www.sqlite.org/eqp.html) statement to gain insights into your query's execution plan. This tool is invaluable for identifying whether your query is performing a full table scan and if it's leveraging the most efficient index to reduce unnecessary reads. [source]
* Over-fetching data * Missing indexes * Not caching repeated queries * Full table scans [source]
A query for "messages in `channel` created 1-2 minutes ago" over the `by_channel` index would look like: [source]
In this case the performance of this query will be based on how many messages are in the channel. Convex will consider each message in the channel and only return the messages where the `user` field doesn't match `myUserId`. [source]
- If avoiding in-memory sorts is critical, place sort fields before range fields (ESR) - If your range predicate in the query is very selective, then put it before sort fields (ERS) [source]
If the query optimizer considered more than one plan, [`executionStats`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats) information also includes the *partial* execution information captured during the [plan selection phase](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) for both the winning and rejected candidate plans. [source]
Even with this optimization you are still just looping over the table to find the first post that matches and may hit your function limits. Using indexes is still the way to go. You can read a [detailed discussion of how to handle tags with indexes](https://stack.convex.dev/complex-filters-in-convex#optimize-with-indexes). [source]
{ queryPlanner: { ... winningPlan: { queryPlan: { stage: 'COLLSCAN', ... } } }, executionStats: { executionSuccess: true, nReturned: 3, executionTimeMillis: 0, totalKeysExamined: 0, totalDocsExamined: 10, executionStages: { stage: 'COLLSCAN', ... }, ... }, ... } [source]
mmary, MongoDB Compass displays the query stages `FETCH` and`IXSCAN` .`IXSCAN` indicates that the[`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) used an index to satisfy the query before executing the`FETCH` stage and retrieving the documents. [source]
This query is saying "look through all of the books, left-to-right, and collect the ones where the `author` field is Jane Austen." To do this the librarian will need to look through the entire shelf and check the author of every book. [source]
MongoDB's indexing strategy eliminates any need to arrange exact match fields in a particular order. However, if the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index. For more information, see [Sort and Non-prefix Subset of an Index.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/#std-label-sort-index-nonprefix-subset) [source]
Collect statistics about indexes to help the query optimizer [source]
s. Index hints don't affect [query shape.](https://www.mongodb.com/docs/manual/core/query-shapes/#std-label-query-shapes)For more information about hints and query settings, see [Query Settings Syntax.](https://www.mongodb.com/docs/manual/reference/command/setQuerySettings/#std-label-setQuerySettings-syntax) [source]
Unless the [`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find/#mongodb-method-db.collection.find) query is an equality condition on the `_id` field `{ _id: <value> }`, you must explicitly specify the index with the [`hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint/#mongodb-method-cursor.hint) method to run `min()`. [source]
MongoDB Compass provides an [Explain Plan](https://www.mongodb.com/docs/compass/current/query-plan/) tab, which displays statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index. [source]
The more selective the equality matches, the more efficient the indexed query. [source]
Queries lacking index support perform a full table scan, incurring a row scan for each table row. [source]
SQL updates read (and write) each row they modify. Absent an index for row filtering, a full table scan is performed, adding a read for each table row, plus a write for each updated row. [source]
Queries that perform filter or sort operations that show a `COLLSCAN` stage would benefit from an index. [source]
Recommended indexes are accompanied by sample queries, grouped by [query shape](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-query-shape), that were run against a collection that would benefit from the suggested index. The Performance Advisor doesn't negatively affect the performance of your Atlas clusters. [source]
The Performance Advisor ranks the indexes according to their Impact, which is based on the total wasted bytes read by the associated operations. To learn more about how the Performance Advisor ranks indexes, see [Review Index Ranking.](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking/#std-label-pa-index-ranking) [source]
For each suggested index, the Performance Advisor shows the most commonly executed query shapes that the index would improve. For each query shape, the Performance Advisor displays the following metrics: [source]
By default, the Performance Advisor suggests indexes for all clusters in the deployment. To only show suggested indexes from a specific collection, use the Collection dropdown at the top of the Performance Advisor. [source]
The Performance Advisor includes a user feedback button for Index Suggestions on dedicated clusters. [source]
The `EXPLAIN QUERY PLAN` prefix provides a high-level description of the strategy the query optimizer chose for executing a statement. This output is more useful than raw EXPLAIN for understanding query performance. [source]
* [Experimental Features](/sql-reference/experimental-features) for enabling in-place `VACUUM` * [PRAGMAs](/sql-reference/pragmas) for `auto_vacuum`, `journal_mode`, `query_only`, and `wal_checkpoint` * [ATTACH DATABASE](/sql-reference/statements/attach-database) for attaching a schema that can be targeted by `VACUUM INTO` * [ANALYZE](/sql-reference/statements/analyze) for refreshing query planner statistics after a vacuum [source]
- `cursor.explain(verbosity)`- ## Important**mongosh Method**This page documents a [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) method. This is*not* the documentation for a language-specific driver, such as Node.js.For MongoDB API drivers, refer to the language-specific [MongoDB driver documentation.](https://www.mongodb.com/docs/drivers/)Provides information on the query plan for the [`db.collection.find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find/#mongodb-method-db.collection.find) method.The `explain()` method has the fo [source]
MongoDB runs the [query optimizer](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-read-operations-query-optimization) to choose the winning plan for the operation under evaluation. [`cursor.explain()`](https://www.mongodb.com#mongodb-method-cursor.explain) returns the [`queryPlanner`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.queryPlanner) information for the evaluated method. [source]
MongoDB runs the [query optimizer](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-read-operations-query-optimization) to choose the winning plan, executes the winning plan to completion, and returns statistics describing the execution of the winning plan. [source]
MongoDB runs the [query optimizer](https://www.mongodb.com/docs/manual/core/query-plans/) to choose the winning plan and executes the winning plan to completion. In `"allPlansExecution"` mode, MongoDB returns statistics describing the execution of the winning plan as well as statistics for the other candidate plans captured during [plan selection.](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) [source]
If you run `cursor.explain()` against a database that does not exist on a sharded cluster, the execution stage reaches the end-of-stream and the operation does not create the database. For more information on end-of-stream execution stats, see `explain.executionStats.executionStages.isEOF`. [source]
The verbosity mode (i.e. `queryPlanner`, `executionStats`, `allPlansExecution`) determines whether the results include [`executionStats`](https://www.mongodb.com/docs/manual/reference/explain-results/#std-label-executionStats) and whether [`executionStats`](https://www.mongodb.com/docs/manual/reference/explain-results/#std-label-executionStats) includes data captured during [plan selection.](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) [source]
The following example runs [`cursor.explain()`](https://www.mongodb.com#mongodb-method-cursor.explain) in ["executionStats"](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#std-label-explain-method-executionStats) verbosity mode to return the query planning and execution information for the specified [`db.collection.find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find/#mongodb-method-db.collection.find) operation: [source]
.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalDocsExamined) displays`10` to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents. [source]
If the range predicate in your query is very selective, place it before the sort fields to reduce the number of sorted documents and allow an in-memory sort. [source]
Queries can execute in several stages. At each stage, MongoDB collects documents from the previous stage to perform the next set of operations. [`explain.executionStats.executionStages`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.executionStages) provides information on each execution stage, where each level of the [`inputStage`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.executionStages.inputStage) field shows how MongoDB selected documents for the stage. [source]
Queries that use filters to specify the results may have issues. To identify an inefficient filter, compare the value on the [`executionStats.totalDocsExamined`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.totalDocsExamined) field to that of the [`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats.nReturned) field. [source]
Familiarizing yourself with the [SQLite query planner](https://www.sqlite.org/queryplanner.html) can significantly enhance your understanding of how your queries are executed. This knowledge is pivotal in optimizing query efficiency. [source]
The EXPLAIN statement displays information about how Turso executes a SQL statement. There are two forms: `EXPLAIN` shows the virtual machine bytecode, and `EXPLAIN QUERY PLAN` shows the high-level query execution strategy. [source]
` , MongoDB interprets`true` as`allPlansExecution` and`false` as`queryPlanner` .For more information on the modes, see [Verbosity Modes.](https://www.mongodb.com#std-label-explain-cursor-method-verbosity)The [`explain()`](https://www.mongodb.com#mongodb-method-cursor.explain) method returns a document with the query plan and, optionally, the execution statistics. [source]
Using `explain` ignores all existing plan cache entries and prevents the MongoDB query planner from creating a new plan cache entry. [source]
[`db.collection.explain().find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) is similar to [`db.collection.find().explain()`](https://www.mongodb.com#mongodb-method-cursor.explain) with the following key differences: [source]
- The [`db.collection.explain().find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) construct allows for the additional chaining of query modifiers. For list of query modifiers, see[db.collection.explain().find().help().](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#std-label-explain-method-help) - The [`db.collection.find().explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) returns the`explain()` information on the que [source]
[`cursor.explain()`](https://www.mongodb.com#mongodb-method-cursor.explain) operations can return information regarding: [source]
tps://www.mongodb.com/docs/manual/reference/explain-results/#std-label-executionStats) , which details the execution of the winning plan and the rejected plans. - [`serverInfo`](https://www.mongodb.com/docs/manual/reference/explain-results/#std-label-serverInfo) , which provides information on the MongoDB instance. - `serverParameters` , which details internal parameters. [source]
Explain plan results for queries are subject to change between MongoDB versions. [source]
1. Click the Explain Plan tab for the `test.inventory` collection. 2. Click Explain. [source]
Return to the Explain Plan tab for the `inventory` collection and re-run the query from the previous step: [source]
The [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain/#mongodb-method-cursor.explain) method returns the following output: [source]
For more information on optimizing queries, see [`explain`](https://www.mongodb.com/docs/manual/reference/command/explain/#mongodb-dbcommand-dbcmd.explain) and [Query Plans.](https://www.mongodb.com/docs/manual/core/query-plans/#std-label-query-plans-query-optimization) [source]
This task runs the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain/#mongodb-method-db.collection.explain) method on a sample query in an attempt to identify performance issues. In practice, it may be difficult to run `explain()` on every query your application runs. [source]

Related concepts

explain plan — is a related of Query planner, explain plans and covered queries
IXSCAN — is a abbreviation of Query planner, explain plans and covered queries
collection scan — is a contrast of Query planner, explain plans and covered queries
query planner — is a whole of Query planner, explain plans and covered queries
totalKeysExamined — is a measure of Query planner, explain plans and covered queries
index build — is a dependent of Query planner, explain plans and covered queries
index hint — is a related of Query planner, explain plans and covered queries
slow query — is a related of Query planner, explain plans and covered queries
index definition — is a part of Query planner, explain plans and covered queries
withIndex — is a instance of Query planner, explain plans and covered queries; Convex index API
query optimization — is a hypernym of Query planner, explain plans and covered queries
selectivity — is a measure of Query planner, explain plans and covered queries
compound index — is a hyponym of Query planner, explain plans and covered queries; MongoDB 'compound' = SQL 'composite'/'multicolumn'
vector index — is a hyponym of Query planner, explain plans and covered queries; pgvector / Atlas Vector Search / Convex vector index — an index type, not the Pinecone sense
hidden index — is a hyponym of Query planner, explain plans and covered queries
covered query — is a dependent of Query planner, explain plans and covered queries
blocking sort — is a problem of Query planner, explain plans and covered queries
wildcard index — is a hyponym of Query planner, explain plans and covered queries
B-tree — is a hyponym of Query planner, explain plans and covered queries
ESR rule — is a dependent of Query planner, explain plans and covered queries