You can use the UPDATE STATISTICS statement to gather statistics for multiple indexes at once.
Purpose
The UPDATE STATISTICS
statement provides a syntax which enables you to analyze multiple indexes at once.
With this syntax, the statement gathers statistics for all the index key expressions from all specified indexes.
This provides a shorthand so that you do not need to list all the index key expressions explicitly.
If the same index expression is included in multiple indexes, duplicate index expressions are removed, so each index expression is only analyzed once.
Syntax
update-statistics-indexes ::= ( 'UPDATE' 'STATISTICS' 'FOR'? |
'ANALYZE' ( 'KEYSPACE' | 'COLLECTION')? )
keyspace-ref indexes-clause index-using? index-with?
For this syntax, UPDATE STATISTICS
and ANALYZE
are synonyms.
The statement must begin with one of these alternatives.
When using the UPDATE STATISTICS
keywords, the FOR
keyword is optional.
Including this keyword makes no difference to the operation of the statement.
When using the ANALYZE
keyword, the COLLECTION
or KEYSPACE
keywords are optional.
Including either of these keywords makes no difference to the operation of the statement.
keyspace-ref | |
indexes-clause | |
index-using | |
index-with |
Keyspace Reference
keyspace-ref ::= keyspace-path | keyspace-partial
keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
keyspace-partial ::= collection
The simple name or fully-qualified name of the keyspace on which the indexes are built. Refer to the CREATE INDEX statement for details of the syntax.
INDEX Clause
indexes-clause ::= 'INDEX' ( '(' ( index-name ( ',' index-name )* | subquery-expr ) ')' |
'ALL' )
For this syntax, the INDEX
clause enables you to specify a comma-separated list of index names, a subquery which returns an array of index names, or all the indexes in the specified keyspace.
index-name |
A unique name that identifies an index. |
subquery-expr |
USING Clause
index-using ::= 'USING' 'GSI'
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
index-with ::= 'WITH' expr
Use the WITH
clause to specify additional options.
expr |
An object with the following properties:
|
Refer to Distribution Statistics for more information on sample size and resolution.
Examples
UPDATE STATISTICS FOR `travel-sample`.inventory.airport
INDEX (def_inventory_airport_faa, def_inventory_airport_city);
ANALYZE KEYSPACE `travel-sample`.inventory.airport
INDEX (def_inventory_airport_faa, def_inventory_airport_city);
This query is equivalent to the query in Example 1.
UPDATE STATISTICS FOR `travel-sample`.inventory.airport INDEX (( (1)
SELECT RAW name (2)
FROM system:indexes
WHERE state = "online"
AND `using` = "gsi" (3)
AND bucket_id = "travel-sample"
AND scope_id = "inventory"
AND keyspace_id = "airport" ));
1 | One set of parentheses delimits the whole group of index terms, and the other set of parentheses delimits the subquery, leading to a double set of parentheses. |
2 | The RAW keyword forces the subquery to return a flattened array of strings, each of which refers to an index name. |
3 | Since USING is a reserved keyword, you need to surround it in backticks in the query. |
ANALYZE KEYSPACE `travel-sample`.inventory.airport INDEX ((
SELECT RAW name
FROM system:indexes
WHERE state = "online"
AND `using` = "gsi"
AND bucket_id = "travel-sample"
AND scope_id = "inventory"
AND keyspace_id = "airport" ));
This query is equivalent to the query in Example 3.
UPDATE STATISTICS FOR `travel-sample`.inventory.airport INDEX ALL;
ANALYZE KEYSPACE `travel-sample`.inventory.airport INDEX ALL;
This query is equivalent to the query in Example 5.