EXPLAIN

  • Capella Operational
  • reference
    +
    The EXPLAIN statement when used before any SQL++ statement, provides information about the execution plan for the statement.

    Prerequisites

    To execute the EXPLAIN statement, you must have the privileges required for the SQL++ statement that is being explained. For more details about user roles, see Authorization.

    RBAC Examples

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

    To execute the following statement, you must have the Query Insert privilege on the landmark keyspace and the Query Select privilege on the `beer-sample` keyspace.

    EXPLAIN INSERT INTO landmark (KEY foo, VALUE bar)
            SELECT META(doc).id AS foo, doc AS bar
            FROM `beer-sample` AS doc WHERE type = "brewery";

    To execute the following statement, you must have the Query Insert, Query Update, and Query Select privileges on the testbucket keyspace.

    EXPLAIN UPSERT INTO testbucket VALUES ("key1", { "a" : "b" }) RETURNING meta().cas;

    Syntax

    explain ::= 'EXPLAIN' statement
    Syntax diagram: refer to source code listing

    The statement consists of the EXPLAIN keyword, followed by the query whose execution plan you want to see.

    Example

    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.

    EXPLAIN SELECT title, activity, hours
    FROM landmark
    ORDER BY title;
    Results
    [
      {
        "plan": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "PrimaryScan3",
                  "bucket": "travel-sample",
                  "index": "def_inventory_landmark_primary",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "landmark",
                  "namespace": "default",
                  "scope": "inventory",
                  "using": "gsi"
                },
                {
                  "#operator": "Fetch",
                  "bucket": "travel-sample",
                  "keyspace": "landmark",
                  "namespace": "default",
                  "scope": "inventory"
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "InitialProject",
                        "result_terms": [
                          {
                            "expr": "(`landmark`.`title`)"
                          },
                          {
                            "expr": "(`landmark`.`activity`)"
                          },
                          {
                            "expr": "(`landmark`.`hours`)"
                          }
                        ]
                      }
                    ]
                  }
                }
              ]
            },
            {
              "#operator": "Order",
              "sort_terms": [
                {
                  "expr": "(`landmark`.`title`)"
                }
              ]
            }
          ]
        },
        "text": "SELECT title, activity, hours FROM landmark ORDER BY title;"
      }
    ]