EXPLAIN FUNCTION

reference

Use EXPLAIN FUNCTION to expose the execution plan for the SQL++ subqueries or embedded statements that a specified user-defined function contains.

Purpose

You can request the execution plan for an inline or external user-defined function.

Inline functions are defined using SQL++ expressions. For an inline function, EXPLAIN FUNCTION returns the query plans for all of the subqueries present in the function body.
External functions are defined using JavaScript. For an external function, EXPLAIN FUNCTION returns the query plans for all embedded SQL++ queries inside the referenced JavaScript body, or the line number on which a N1QL() call appears. Line numbers are calculated from the beginning of the JavaScript function definition.

The following constraints apply:

If a user-defined function itself contains other, nested user-defined function executions, EXPLAIN FUNCTION generates the query plan for the specified function only, and not for its nested SQL++ queries.
If an external function defines an alias for a N1QL() call, EXPLAIN FUNCTION cannot return its line number.

Prerequisites

To execute EXPLAIN FUNCTION on …	You must have …
Global inline functions	Execute Global Functions role.
Scoped inline functions	Execute Scope Functions role, with permissions on the specified bucket and scope.
Global external functions	Execute Global External Functions role.
Scoped external functions	Execute Scope External Functions role, with permissions on the specified bucket and scope.

To execute EXPLAIN FUNCTION on …

You must have …

Global inline functions

Execute Global Functions role.

Scoped inline functions

Execute Scope Functions role, with permissions on the specified bucket and scope.

Global external functions

Execute Global External Functions role.

Scoped external functions

Execute Scope External Functions role, with permissions on the specified bucket and scope.

You must also have the necessary privileges required for the SQL++ statements inside the function.

For more information about user roles, see Authorization.

Syntax

explain-function ::= 'EXPLAIN' 'FUNCTION' function

function

Function Name

function ::= ( namespace ':' ( bucket '.' scope '.' )? )? identifier

The name of the function. This is usually an unqualified identifier, such as func1 or `func-1`. In this case, the path to the function is determined by the current query context.

To get the plan for a global function in a particular namespace, the function name must be a qualified identifier with a namespace, such as default:func1. Similarly, to get the plan for a scoped function in a particular scope, the function name must be a qualified identifier with the full path to a scope, such as default:`travel-sample`.inventory.func1. For more information, see Global Functions and Scoped Functions.

The name of a user-defined function is case-sensitive, unlike that of a built-in function. You must get the plan for the user-defined function using the same case that was used when it was created.

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. Inline Function Example

In this example, you create an inline function named func1 and then request the plan for its subquery.

CREATE FUNCTION func1() {
  (SELECT * FROM default:`travel-sample`.inventory.route)};

EXPLAIN FUNCTION func1;

Results

[
  {
    "function": "default:travel-sample.inventory.func1",
    "plans": [
      {
        "cardinality": 24024,
        "cost": 33346.763562464446,
        "plan": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "PrimaryScan3",
              "bucket": "travel-sample",
              "index": "def_inventory_route_primary",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "route",
              "namespace": "default",
              "optimizer_estimates": {
                "cardinality": 24024,
                "cost": 4108.612246388904,
                "fr_cost": 12.170521655277593,
                "size": 11
              },
              "scope": "inventory",
              "using": "gsi"
            },
            {
              "#operator": "Fetch",
              "bucket": "travel-sample",
              "keyspace": "route",
              "namespace": "default",
              "optimizer_estimates": {
                "cardinality": 24024,
                "cost": 32773.70177195316,
                "fr_cost": 25.36320769946525,
                "size": 569
              },
              "scope": "inventory"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "InitialProject",
                    "discard_original": true,
                    "optimizer_estimates": {
                      "cardinality": 24024,
                      "cost": 33346.763562464446,
                      "fr_cost": 25.387061420349003,
                      "size": 569
                    },
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        "statement": "select self.* from `default`:`travel-sample`.`inventory`.`route`"
      }
    ]
  }
]

Example 2. External Function Example

This example assumes that you have defined a JavaScript library in the current query context named lib1.

Add a JavaScript function named function1 to that library:

function function1() {
    SELECT * FROM default:`travel-sample`; (1)
    N1QL("SELECT 100");                    (2)
}

1	An embedded SQL++ statement.
2	A N1QL() call that executes a SQL++ statement.

Then create the corresponding SQL++ user-defined function for that JavaScript function, named jsfunction1, and request the plan information for the statements within the function definition:

CREATE FUNCTION jsfunction1() 
  LANGUAGE JAVASCRIPT 
  AS "function1" AT "lib1";

EXPLAIN FUNCTION jsfunction1;