Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

Update Statistics for a Single Index

  • reference
February 23, 2025
+ 12
You can use the UPDATE STATISTICS statement to gather statistics on a single index.

Purpose

The UPDATE STATISTICS statement provides a syntax which enables you to analyze a single index. With this syntax, the statement gathers statistics for all the index key expressions in the specified index. This provides a shorthand so that you do not need to list all the index key expressions explicitly.

Syntax

ebnf
ViewCopy
update-statistics-index ::= ( 'UPDATE' 'STATISTICS' 'FOR' | 'ANALYZE' )
                              index-clause index-using?  index-with?
Syntax diagram: refer to source code listing

For this syntax, UPDATE STATISTICS FOR and ANALYZE are synonyms. The statement must begin with one of these alternatives.

index-clause

INDEX Clause

index-using

USING Clause

index-with

WITH Clause

INDEX Clause

ebnf
ViewCopy
index-clause ::= 'INDEX' ( index-path '.' index-name | index-name 'ON' keyspace-ref )
Syntax diagram: refer to source code listing

For this syntax, the INDEX clause enables you to specify the index name and a keyspace.

index-name

A unique name that identifies the index.
index-path

Index Path

keyspace-ref

Keyspace Reference

Index Path

ebnf
ViewCopy
index-path ::= keyspace-full | keyspace-prefix | keyspace-partial
Syntax diagram: refer to source code listing 
ebnf
ViewCopy
keyspace-full ::= namespace ':' bucket '.' scope '.' collection
Syntax diagram: refer to source code listing 
ebnf
ViewCopy
keyspace-prefix ::= ( namespace ':' )? bucket
Syntax diagram: refer to source code listing 
ebnf
ViewCopy
keyspace-partial ::= collection
Syntax diagram: refer to source code listing

You can use a dotted notation to specify the index and the keyspace on which the index is built. Refer to the ALTER INDEX or DROP INDEX statements for details of the syntax.

Keyspace Reference

ebnf
ViewCopy
keyspace-ref ::= keyspace-path | keyspace-partial
Syntax diagram: refer to source code listing 
ebnf
ViewCopy
keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
Syntax diagram: refer to source code listing 
ebnf
ViewCopy
keyspace-partial ::= collection
Syntax diagram: refer to source code listing

Alternatively, you can use the index name with the ON keyword and a keyspace reference to specify the keyspace on which the index is built. Refer to the ALTER INDEX or DROP INDEX statements for details of the syntax.

USING Clause

ebnf
ViewCopy
index-using ::= 'USING' 'GSI'
Syntax diagram: refer to source code listing

In Couchbase Server 6.5 and later, the index type for a secondary index must be Global Secondary Index (GSI). The USING GSI keywords are optional and may be omitted.

WITH Clause

ebnf
ViewCopy
index-with ::= 'WITH' expr
Syntax diagram: refer to source code listing

Use the WITH clause to specify additional options.

expr

An object with the following properties:

sample_size

[Optional] An integer specifying the sample size to use for distribution statistics. A minimum sample size is also calculated. If the specified sample size is smaller than the minimum sample size, the minimum sample size is used instead.

resolution

[Optional] A float representing the percentage of documents to store in each distribution bin. If omitted, the default value is 1.0, meaning each distribution bin contains 1% of the documents, and therefore 100 bins are required. The minimum resolution is 0.02 (5000 distribution bins) and the maximum is 5.0 (20 distribution bins).

update_statistics_timeout

[Optional] A number representing a duration in seconds. The command times out when this timeout period is reached. If omitted, a default timeout value is calculated based on the number of samples used.

batch_size

[Optional] Only applies when processing multiple index expressions at once. If there is a large number of index expressions to process, the cost-based optimizer deals with them in batches. This option is an integer specifying the maximum number of index expressions in each batch. If omitted, the default value is 10. You can specify a different value based on the memory availability of the system. Note that when index expressions are processed in batches, the update_statistics_timeout value (above) applies to each batch.

Refer to Distribution Statistics for more information on sample size and resolution.

Result

The statement returns an empty array.

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Example 1. UPDATE STATISTICS with index identifier
sql++
ViewCopy
UPDATE STATISTICS FOR
INDEX airport.def_inventory_airport_city;
Example 2. UPDATE STATISTICS with ON clause
sql++
ViewCopy
UPDATE STATISTICS FOR
INDEX def_inventory_airport_city ON airport;

This query is equivalent to the query in Example 1.

Example 3. ANALYZE with index identifier
sql++
ViewCopy
ANALYZE INDEX airport.def_inventory_airport_city;

This query is equivalent to the query in Example 1.

Example 4. ANALYZE with ON clause
sql++
ViewCopy
ANALYZE INDEX def_inventory_airport_city ON airport;

This query is equivalent to the query in Example 1.