A newer version of this documentation is available.

View Latest

Keyspace Hints

  • reference
  • Couchbase Server 7.1
February 16, 2025
+ 12
Keyspace hints apply to a specific keyspace.

A keyspace hint is a type of optimizer hint. Keyspace hints include index hints, which enable you to specify indexes, and join hints, which enable you to specify join methods.

For each keyspace hint, you must specify the keyspace or keyspaces that the hint applies to. If a keyspace is given an explicit alias in the query, then the hint must refer to the explicit alias, not the keyspace name. This is to avoid confusion in situations where the same keyspace can be used multiple times (with different aliases) in the same query.

If the keyspace is not given an explicit alias in the query, the hint must refer to the keyspace using the keyspace name. (If the keyspace name is a dotted path, the hint must refer to the keyspace using its implicit alias, which is the last component in the keyspace path.)

There are two possible formats for each optimizer hint: simple syntax and JSON syntax. Note that you cannot mix simple syntax and JSON syntax in the same hint comment.

INDEX

This hint directs the optimizer to consider one or more specified secondary indexes. If not specified, the optimizer selects the optimal available index.

Simple Syntax

gsi-hint-simple ::= 'INDEX' '(' keyspace index* ')'
Syntax diagram: refer to source code listing

With the simple syntax, this hint specifies a single keyspace expression, and zero, one, or more indexes. You can use this hint multiple times within the hint comment to specify hints for more than one keyspace.

Arguments

keyspace

The keyspace or alias to which this hint applies.

index

A secondary index that the optimizer should consider for the given keyspace. This argument is optional; if omitted, the optimizer considers all secondary indexes available in the given keyspace.

JSON Syntax

gsi-hint-json ::= '"index"' ':' ( index-array | index-object )
Syntax diagram: refer to source code listing

With the JSON syntax, this hint takes the form of an index property. You may only use this property once within the hint comment. The value of this property may be an Index Array or an Index Object.

Index Array

index-array ::= '[' index-object ( ',' index-object )* ']'
Syntax diagram: refer to source code listing

Use this array to specify indexes for multiple keyspaces. Each element must be an Index Object.

Index Object

index-object ::= '{' keyspace-property ',' indexes-property '}'
Syntax diagram: refer to source code listing

Use this object to specify indexes for a single keyspace. It must contain a Keyspace Property and an Indexes Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'
Syntax diagram: refer to source code listing

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Indexes Property

indexes-property ::= '"indexes"' ':' ( 'null'
                                     | '"' index '"'
                                     | '[' '"' index '"' ( ',' '"' index '"' )* ']' )
Syntax diagram: refer to source code listing

The value of this property may be:

null

The optimizer considers all secondary indexes available in the given keyspace.

An index string

A secondary index that the optimizer should consider for the given keyspace.

An array of index strings

An array of secondary indexes that the optimizer should consider for the given keyspace.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

INDEX hint

This example uses the index def_inventory_route_route_src_dst_day, which is installed with the travel-sample bucket.

The following query hints that the optimizer should select the specified index for the keyspace `travel-sample`.inventory.route.

Query
SELECT /*+ INDEX (route def_inventory_route_route_src_dst_day) */ id (1)
FROM `travel-sample`.inventory.route (2)
WHERE sourceairport = "SFO"
LIMIT 1;
1 The keyspace is not given an explicit alias in the query. You must therefore refer to the keyspace using the keyspace name or implicit alias.
2 The implicit alias is the last element in the keyspace path — in this case, route.

If you examine the plan for this query, you can see that the query uses the suggested index.

Explain plan
"scan": {
  "#operator": "IndexScan3",
  "bucket": "travel-sample",
  "index": "def_inventory_route_route_src_dst_day",
Optimized index selection

For comparison, the following query is equivalent to the one in the INDEX hint example, but without the index hint.

Query
SELECT id
FROM `travel-sample`.inventory.route
WHERE sourceairport = "SFO"
LIMIT 1;

If you examine the plan for this query, you can see that the optimizer has selected a different index: def_inventory_route_sourceairport.

Explain plan
{
  "#operator": "IndexScan3",
  "bucket": "travel-sample",
  "index": "def_inventory_route_sourceairport",

Legacy Equivalent

This hint is equivalent to the legacy USE INDEX (USING GSI) clause. For more details, refer to USE INDEX Clause.

Note that you cannot use a hint comment and the USE clause to specify optimizer hints on the same keyspace. If you do this, the hint comment and the USE clause are marked as erroneous and ignored by the optimizer.

INDEX_FTS

This hint directs the optimizer to consider one or more specified full-text indexes. If not specified, the optimizer selects the optimal available index.

Simple Syntax

fts-hint-simple ::= 'INDEX_FTS' '(' keyspace index* ')'
Syntax diagram: refer to source code listing

With the simple syntax, this hint specifies a single keyspace expression; and zero, one, or more indexes. You can use this hint multiple times within the hint comment to specify hints for more than one keyspace.

Arguments

keyspace

The keyspace or alias to which this hint applies.

index

A full-text index that the optimizer should consider for the given keyspace. This argument is optional; if omitted, the optimizer considers all full-text indexes available in the given keyspace.

JSON Syntax

fts-hint-json ::= '"index_fts"' ':' ( index-array | index-object )
Syntax diagram: refer to source code listing

With the JSON syntax, this hint takes the form of an index_fts property. You may only use this property once within the hint comment. The value of this property may be an Index Array or an Index Object.

Index Array

index-array ::= '[' index-object ( ',' index-object )* ']'
Syntax diagram: refer to source code listing

Use this array to specify indexes for multiple keyspaces. Each element must be an Index Object.

Index Object

index-object ::= '{' keyspace-property ',' indexes-property '}'
Syntax diagram: refer to source code listing

Use this object to specify indexes for a single keyspace. It must contain a Keyspace Property and an Indexes Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'
Syntax diagram: refer to source code listing

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Indexes Property

indexes-property ::= '"indexes"' ':' ( 'null'
                                     | '"' index '"'
                                     | '[' '"' index '"' ( ',' '"' index '"' )* ']' )
Syntax diagram: refer to source code listing

The value of this property may be:

null

The optimizer considers all full-text indexes available in the given keyspace.

index string

A full-text index that the optimizer should consider for the given keyspace.

index array

An array of full-text indexes that the optimizer should consider for the given keyspace.

Examples

INDEX_FTS hint

This example specifies that the optimizer should prefer any suitable FTS index, without specifying an index by name. To qualify for this query, there must be an FTS index on state and type, using the keyword analyzer. (Or alternatively, an FTS index on state, with a custom type mapping on "hotel".)

Query
n1ql
SELECT /*+ INDEX_FTS (hotel) */ META().id FROM `travel-sample`.inventory.hotel WHERE state = "Corse" OR state = "California";

All FTS indexes are considered. If a qualified FTS index is available, it is selected for the query. If none of the available FTS indexes are qualified, the available GSI indexes are considered instead.

Legacy Equivalent

This hint is equivalent to the legacy USE INDEX (USING FTS) clause. For more details, refer to USE INDEX Clause.

Note that you cannot use a hint comment and the USE clause to specify optimizer hints on the same keyspace. If you do this, the hint comment and the USE clause are marked as erroneous and ignored by the optimizer.

USE_NL

This hint directs the optimizer to consider a nested-loop join for the specified keyspace. This hint must be specified on the keyspace on the right-hand side of the join. If not specified, the optimizer selects the optimal join method.

Simple Syntax

nl-hint-simple ::= 'USE_NL' '(' ( keyspace )+ ')'
Syntax diagram: refer to source code listing

With the simple syntax, this hint specifies one or more keyspaces. You may also use this hint multiple times within the hint comment.

Arguments

keyspace

The keyspace or alias to which this hint applies.

JSON Syntax

nl-hint-json ::= '"use_nl"' ':' ( keyspace-array | keyspace-object )
Syntax diagram: refer to source code listing

With the JSON syntax, this hint takes the form of a use_nl property. You may only use this property once within the hint comment. The value of this property may be a Keyspace Array or a Keyspace Object.

Keyspace Array

keyspace-array ::= '[' keyspace-object ( ',' keyspace-object )* ']'
Syntax diagram: refer to source code listing

Use this array to apply the hint to multiple keyspaces. Each element must be a Keyspace Object.

Keyspace Object

keyspace-object ::= '{' keyspace-property '}'
Syntax diagram: refer to source code listing

Use this object to apply the hint to a single keyspace. It must contain a Keyspace Property.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'
Syntax diagram: refer to source code listing

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

USE_NL Hint
Query
SELECT /*+ USE_NL (a) */ (1)
       a.airportname AS airport, r.id AS route
FROM `travel-sample`.inventory.route AS r,
     `travel-sample`.inventory.airport AS a (2)
WHERE a.faa = r.sourceairport
  AND r.sourceairport = "SFO"
LIMIT 4;
1 The keyspace is given an explicit alias in the query. You must therefore refer to the keyspace using the explicit alias.
2 In this case, the explicit alias is a.

If you examine the plan text for this query, you can see that the query uses the suggested join method.

Explain plan
{
  "#operator": "NestedLoopJoin",
Optimized join method selection

For comparison, the following query is equivalent to the one in the USE_NL Hint example, but without the join hint.

Query
SELECT a.airportname AS airport, r.id AS route
FROM `travel-sample`.inventory.route AS r,
     `travel-sample`.inventory.airport AS a
WHERE a.faa = r.sourceairport
  AND r.sourceairport = "SFO"
LIMIT 4;

If you examine the plan for this query, you can see that the optimizer has selected a different join method.

Explain plan
{
  "#operator": "HashJoin",

Legacy Equivalent

This hint is equivalent to the legacy USE NL clause. For more details, refer to USE NL Clause.

Note that you cannot specify optimizer hints and the USE clause on the same keyspace in the same query. If you do this, the optimizer hints and USE clause are both marked as erroneous and ignored by the optimizer.

USE_HASH

This hint directs the optimizer to consider a hash join for the specified keyspace. This hint must be specified on the keyspace on the right-hand side of the join. If not specified, the optimizer selects the optimal join method.

A hash join has two sides: a build side and a probe side. The build side of the join is used to create an in-memory hash table. The probe side uses that table to find matches and perform the join. Typically, this means you want the build side to be used on the smaller of the two sets.

This hint enables you specify whether the right side of the join should be the build side or the probe side. If you specify that the right side of the join is the build side, then the left side will be the probe side, and vice versa.

For Couchbase Server Community Edition (CE), only nested-loop join is considered by the optimizer, and any specified USE_HASH hint will be silently ignored.

Simple Syntax

hash-hint-simple ::= 'USE_HASH' '(' ( keyspace ( '/' ( 'BUILD' | 'PROBE' ) )? )+ ')'
Syntax diagram: refer to source code listing

With the simple syntax, this hint specifies one or more keyspaces. For each keyspace, you may also add a slash, followed by an option. You may also use this hint multiple times within the hint comment.

Arguments

keyspace

The keyspace or alias to which this hint applies.

Options

/BUILD

The specified keyspace is to be used as the build side of the join.

/PROBE

The specified keyspace is to be used as the probe side of the join.

If you omit the option (including the slash), the optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join, based on the estimated cardinality of both sides.

JSON Syntax

hash-hint-json ::= '"use_hash"' ':' ( hash-array | hash-object )
Syntax diagram: refer to source code listing

With the JSON syntax, this hint takes the form of a use_hash property. You may only use this property once within the hint comment. The value of this property may be a Hash Array or a Hash Object.

Hash Array

hash-array ::= '[' hash-object ( ',' hash-object )* ']'
Syntax diagram: refer to source code listing

Use this array to apply the hint to multiple keyspaces. Each element must be a Hash Object.

Hash Object

hash-object ::= '{' keyspace-property ( "," option-property )? '}'
Syntax diagram: refer to source code listing

Use this object to apply the hint to a single keyspace. It must contain a Keyspace Property and an optional Option Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'
Syntax diagram: refer to source code listing

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Option Property

option-property ::= '"option"' ':' ( '"build"' | '"probe"' | 'null' )
Syntax diagram: refer to source code listing

The value of this property may be:

"build"

The specified keyspace is to be used as the build side of the join.

"probe"

The specified keyspace is to be used as the probe side of the join.

null

The optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join, based on the estimated cardinality of both sides.

Similarly, if you omit this property entirely, the optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

USE_HASH with PROBE

The keyspace aline is to be joined (with rte) using hash join, and aline is used as the probe side of the hash join.

Query — simple syntax
SELECT /*+ USE_HASH (aline/PROBE) */
       COUNT(1) AS Total_Count
FROM `travel-sample`.inventory.route rte
INNER JOIN `travel-sample`.inventory.airline aline
ON rte.airlineid = META(aline).id;

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, with the aline keyspace on the probe side.

Explain plan
{
  "#operator": "HashJoin",
  "build_aliases": [
    "rte"
  ],
  "build_exprs": [
    "(`rte`.`airlineid`)"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
USE_HASH with BUILD

This is effectively the same query as the USE_HASH with PROBE example, except the two keyspaces are switched, and here the BUILD option is used, indicating the hash join should use rte as the build side.

Query
SELECT /*+ { "use_hash": { "keyspace": "rte", "option": "build" } } */
       COUNT(1) AS Total_Count
FROM `travel-sample`.inventory.airline aline
INNER JOIN `travel-sample`.inventory.route rte
ON (rte.airlineid = META(aline).id);

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, with the rte keyspace on the build side.

Explain plan
{
  "#operator": "HashJoin",
  "build_aliases": [
    "rte"
  ],
  "build_exprs": [
    "(`rte`.`airlineid`)"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
USE_HASH with optimizer

This is the same query as the USE_HASH with PROBE example, but the hint does not specify whether the aline keyspace should be on the probe side or the build side of the join.

Query — JSON syntax
SELECT /*+ USE_HASH (aline) */
       COUNT(1) AS Total_Count
FROM `travel-sample`.inventory.route rte
INNER JOIN `travel-sample`.inventory.airline aline
ON rte.airlineid = META(aline).id;

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, but the optimizer has selected to put the aline keyspace on the build side of the join.

Explain plan
{
  "#operator": "HashJoin",
  "build_aliases": [
    "aline"
  ],
  "build_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "(`rte`.`airlineid`)"
  ],
Optimized join method selection

For comparison, the following query is equivalent to the one in the USE_HASH with PROBE example, but without the join hint.

Query
SELECT COUNT(1) AS Total_Count
FROM `travel-sample`.inventory.route rte
INNER JOIN `travel-sample`.inventory.airline aline
ON rte.airlineid = META(aline).id;

If you examine the plan for this query, you can see that the optimizer has selected to use the hash join method as before, and to put the aline keyspace on the build side of the join.

Explain plan
{
  "#operator": "HashJoin",
  "build_aliases": [
    "aline"
  ],
  "build_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "(`rte`.`airlineid`)"
  ],

Legacy Equivalent

This hint is equivalent to the legacy USE HASH clause. For more details, refer to USE HASH Clause.

Note that you cannot specify both optimizer hints and the USE clause on the same keyspace. If you do this, the optimizer hints and the USE clause are both marked as erroneous and ignored by the optimizer.