My Queries Have Begun to Timeout and I See "index": "#sequentialscan" in the Query Plan Text

Understanding the Issue

Sometimes our queries begin to timeout. Usually it happens after some changes have been done in our cluster. However, sometimes queries can timeout right after creation.

Usually, the first diagnostic we should do is taking a look into the query Plan Text. To do so you need to run the relevant query from the Web UI (Query Workbench) in order to collect the Plan Text output from the query. After the query has successfully executed, click the Plan Text button as shown below:

If your queries are timing out and you see "index": "#sequentialscan" in the query Plan Text, it indicates that the Couchbase Query Service is using a sequential scan instead of an index. This behavior was introduced in Couchbase Server 7.6 and allows queries to run even when no primary or secondary indexes are available. However, sequential scans are not optimized for high performance and can lead to significant slowdowns, especially in large collections.

Please, go over Query without Indexes article of our documentation to find out more.

Why Does This Happen?

There are two common scenarios where queries might start timing out due to sequential scans:

1. From the beginning – If no indexes were created for the queried keyspace, the Query Service falls back on a sequential scan.

2. After index management – If indexes were dropped, modified, or became unavailable, queries that previously used indexes may default to sequential scans, leading to increased execution time.

What is a Sequential Scan?

A sequential scan uses a K/V range scan to retrieve document keys when no suitable indexes exist. While this provides an immediate way to access data, it is not intended for high-performance workloads. Instead, sequential scans are best suited for:

• Small collections where maintaining an index is unnecessary.

• Situations where key order is unimportant.

• Cases where creating an index is not justified due to minimal queries.

For larger datasets and optimized query execution, defining the appropriate secondary indexes is strongly recommended. Moreover, we recommend to use Global Secondary Indexes in your Production environment only, leaving Primary Indexes and Sequential Scans for testing or developing purposes only.

Solution: Creating Indexes to Improve Query Performance

To resolve query timeouts caused by sequential scans, you should create an appropriate index. Here are two examples of how to do this in Couchbase:

1. Creating a Primary Index

If your keyspace has no indexes, create a primary index to allow efficient access:

CREATE PRIMARY INDEX ON `my_bucket`;

This will provide basic indexing support but may not be the most efficient option for complex queries. Note! We do not recommend to use primary indexes in production environments.

2. Creating a Secondary Index

For better query optimization, create a secondary index based on the fields used in your query. For example, if you frequently query by status and created_at, create an index like this:

CREATE INDEX idx_status_created_at ON `my_bucket`(`status`, `created_at`);

This allows the Query Service to efficiently retrieve results without scanning the entire keyspace sequentially.

Conclusion

Using indexes instead of sequential scans significantly reduces query execution time and improves overall database performance. If you notice your queries are timing out and see #sequentialscan in the query plan text, check your indexes and create the appropriate ones as needed. The best approach would be to create a covering global secondary index for a particular query, as we do not recommend having primary indexes in Production environments.

If you have created the indexes and your queries are still experiencing timeouts, please, open a ticket with Couchbase Support for further investigation.