Optimizer Hints

  • reference
  • Couchbase Server 7.1
    +
    Optimizer hints enable you to supply directives to the optimizer.

    You can use optimizer hints to request that the optimizer should consider specific indexes, join methods, join ordering, and so on, when creating the plan for a query. This may be useful in situations where the optimizer is not able to come up with the preferred plan, due to lack of optimizer statistics, high level of skew in data, data correlations, and so on.

    Generally speaking, you should rely on the optimizer to generate the query plan.

    Syntax

    hint-comment ::= block-hint-comment | line-hint-comment
    Syntax diagram: refer to source code listing
    block-hint-comment ::= '/*+' hints '*/'
    Syntax diagram: refer to source code listing
    line-hint-comment ::= '--+' hints
    Syntax diagram: refer to source code listing

    You can supply hints to the operator within a specially-formatted hint comment. The hint comment may be a block comment or a line comment. There must be a plus sign + immediately after the start of the comment; this is the distinguishing delimiter of the hint comment.

    Note that a line comment includes all text up to the end of the line. Therefore, if the hint comment is a line comment, the next part of the query must start on the following line.

    Example 1. Block hint comment
    Query
    SELECT /*+ INDEX(airport def_inventory_airport_city) */ airportname
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";
    Example 2. Line hint comment
    Equivalent to Example 1
    SELECT --+ INDEX(airport def_inventory_airport_city)
           airportname
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";

    Placement

    Currently, a hint comment is only supported in the SELECT statement. The hint comment must be located immediately after the SELECT keyword. For details, refer to SELECT Clause.

    There can only be one hint comment in a query block. If there is more than one hint comment, a syntax error is generated. However, the hint comment may contain one or more hints.

    Example 3. Multiple hint comments
    Incorrect query
    SELECT /*+ USE_HASH(r) */
           /*+ INDEX(a def_inventory_airport_city) */
           a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city = "San Francisco";

    This query generates a syntax error, as it contains multiple hint comments.

    Format

    hints ::= simple-hint-sequence | json-hint-object
    Syntax diagram: refer to source code listing
    simple-hint-sequence ::= simple-hint+
    Syntax diagram: refer to source code listing
    json-hint-object ::= '{' json-hint (',' json-hint )* '}'
    Syntax diagram: refer to source code listing

    Internally, the hint comment may take one of two equivalent formats:

    • The simple syntax is a plain text format, similar to the type of hint comment found in many relational databases.

    • Alternatively, you may supply optimizer hints using JSON syntax. In this case, the hint comment contains a single top-level hint object.

    You cannot mix the simple syntax and the JSON syntax within the same hint comment; each hint comment must use one syntax or the other exclusively.

    To use multiple hints with the simple syntax, simply specify the hints one after another within the hint comment. To use multiple hints with the JSON syntax, specify each hint as a property within the top-level hint object.

    Example 4. Simple hint
    Query
    SELECT /*+ INDEX(airport def_inventory_airport_city) */
           airportname, faa
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";
    Example 5. JSON hint
    Equivalent to Example 4
    SELECT /*+ {"index": {"keyspace": "airport",
                          "indexes": "def_inventory_airport_city"}} */
           airportname, faa
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";
    Example 6. Multiple simple hints
    Query
    SELECT /*+ INDEX(a def_inventory_airport_city) USE_HASH(r) */
           a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city = "San Francisco";
    Example 7. Multiple JSON hints
    Equivalent to Example 6
    SELECT /*+ {"index": {"keyspace": "a",
                          "indexes": "def_inventory_airport_city"},
                "use_hash": {"keyspace": "r"}} */
           a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city = "San Francisco";

    Legacy Equivalents

    Many optimizer hints have an equivalent legacy syntax using the USE clause. Details of these are given on the pages for individual optimizer hints, and in the pages describing the USE clause. For details, refer to USE INDEX Clause and ANSI JOIN Hints.

    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.

    Example 8. Legacy hint
    Legacy equivalent to Example 4
    SELECT airportname, faa
    FROM `travel-sample`.inventory.airport
    USE INDEX (def_inventory_airport_city)
    WHERE city = "San Francisco";

    Explain Plans

    When optimizer hints are specified for a query, the explain plan reports the status of each hint: that is, whether the hint was followed or not followed by the optimizer in choosing the query plan. Invalid hints are also reported. Specific error messages are given for any hint that is not followed, or invalid.

    Example 9. Simple hint explain plan

    When the optimizer follows a simple hint, the hint is shown in the explain plan.

    Explain plan for Example 4
    EXPLAIN SELECT /*+ INDEX(airport def_inventory_airport_city) */
                   airportname, faa
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "INDEX(airport def_inventory_airport_city)"
          ]
        },
    // ...
      }
    ]
    Example 10. JSON hint explain plan

    When the optimizer follows a JSON hint, the hint is shown in the explain plan in JSON format.

    Explain plan for Example 5
    EXPLAIN SELECT /*+ {"index": {"keyspace": "airport",
                                  "indexes": "def_inventory_airport_city"}} */
                   airportname, faa
    FROM `travel-sample`.inventory.airport
    WHERE city = "San Francisco";
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "hint": "{\"index\":{\"indexes\":[\"def_inventory_airport_city\"],\"keyspace\":\"airport\"}}"
          ]
        },
    // ...
      }
    ]
    Example 11. Multiple hint explain plan

    When the optimizer follows multiple hints, all the followed hints are shown in the explain plan.

    Explain plan for Example 6
    EXPLAIN SELECT /*+ USE_HASH(r) INDEX(a def_inventory_airport_city) */
                   a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city = "San Francisco";
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "USE_HASH(r)",
            "INDEX(a def_inventory_airport_city)"
          ]
        },
    // ...
      }
    ]
    Example 12. Legacy hint explain plan

    When the optimizer follows a hint specified using the legacy USE clause, the hint is likewise shown in the explain plan.

    Explain plan for Example 8
    EXPLAIN SELECT airportname, faa
    FROM `travel-sample`.inventory.airport
    USE INDEX (def_inventory_airport_city)
    WHERE city = "San Francisco";
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "INDEX(airport def_inventory_airport_city)"
          ]
        },
    // ...
      }
    ]
    Example 13. Unused hint explain plan

    When the optimizer cannot follow a hint, any hints that cannot be followed are shown in the explain plan.

    Explain plan
    EXPLAIN SELECT /*+ USE_HASH(r) INDEX(a def_inventory_airport_city) */
                   a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city IS MISSING;
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "USE_HASH(r)"
          ],
          "hints_not_followed": [
            "INDEX(a def_inventory_airport_city): INDEX hint cannot be followed"
          ]
        },
    // ...
      }
    ]
    Example 14. Invalid hint explain plan

    When you specify an invalid hint, any invalid hints are shown in the explain plan.

    Explain plan
    EXPLAIN SELECT /*+ USE_HASH(r) INDEX_SS(a def_inventory_airport_city) */
                   a.airportname, r.airline
    FROM `travel-sample`.inventory.airport a
    JOIN `travel-sample`.inventory.route r
    ON a.faa = r.sourceairport
    WHERE a.city = "San Francisco";
    Result
    [
      {
        "optimizer_hints": {
          "hints_followed": [
            "USE_HASH(r)"
          ],
          "invalid_hints": [
            "INDEX_SS(a def_inventory_airport_city): Invalid hint name"
          ]
        },
    // ...
      }
    ]

    Further Details

    Refer to the following pages for details of individual optimizer hints: