EXPLAIN FUNCTION

Capella Operational

reference

For a specified user-defined function, you use EXPLAIN FUNCTION to expose the execution plan for the SQL++ subqueries or embedded statements that it contains.

Purpose

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

For an inline function, EXPLAIN FUNCTION returns the query plans for all of the subqueries present in the function body.

For more information about inline functions, see User-Defined Functions.
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.

For more information about user-defined functions with JavaScript, see guides:javascript-udfs.adoc.

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. Refer to Global Functions and Scoped Functions for more information.

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

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 named lib1.

You then add a JavaScript function named function1 to that library as follows:

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.

You 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;