EXPLAIN FUNCTION

Capella Operational

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 this statement, your client must have necessary privileges depending on your cluster access credential type and whether the function is global or scoped.

Credential Type Function Type Privilege

Credential Type	Function Type	Privilege
Basic	Global or scoped	`Write`
Advanced	Global	`Global Function Execute`
Advanced	Scoped	`Query Execute`

Basic

Global or scoped

Write

Advanced

Global

Global Function Execute

Advanced

Scoped

Query Execute

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

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;