Statements with User-defined Functions Cannot Be Pushed Down

Symptom

SQL statements cannot be pushed down.

Possible Causes

The latest version supports the pushdown of most common functions. Statements cannot be pushed down mainly because the attributes of user-defined functions are incorrectly defined.

If statements are not pushed down, the advantages of distributed computing are not leveraged. In this case, massive data is processed only by one node during the statement execution, resulting in poor performance.

Cause Analysis

1. Run explain verbose to print the execution plan of a statement.

   

   The __REMOTE keyword in the preceding execution plan indicates that the current statement cannot be pushed down.

2. The reason why a statement cannot be pushed down is printed in pg_log. The CN logs of the preceding statement are similar to the following.

   

Handling Procedure

Check whether the provolatile attribute of the user-defined function is correctly defined. If the definition is incorrect, modify the corresponding attribute so that the statement can be pushed down.

For details, see the following description.

• All attributes related to the function can be queried in the pg_proc system catalog. The two attributes that determine whether the function can be pushed down are provolatile and proshippable.
  • If the provolatile of a function is i, the function can be pushed down regardless of the value of proshippable.
  • If the provolatile of a function is s or v, the function can be pushed only if the value of proshippable is t.
• provolatile is to describe the volatile attribute of a function. The value can be i, s, or v. i indicates IMMUTABLE, s indicates STABLE, and v indicates VOLATILE.

  Examples are as follows:

  • If a function must have the same output for the same input, this function is IMMUTABLE, such as most string processing functions. These functions can always be pushed down.
  • If the returned result of a function is the same during the calling of an SQL statement, the function is STABLE. For example, the final displayed result of time-related processing functions may vary with specific GUC parameters, such as the parameter that determines the time display format. These functions can be pushed down only when their attributes are SHIPPABLE.
  • If the returned result of a function varies with each call, the function is VOLATILE. For example, the results of invoking the nextval and random functions are unpredictable. These functions can be pushed down only when their attributes are SHIPPABLE.
• proshippable indicates whether a function can be pushed down to DNs. The default value is false, and the value can be true, false, or NULL.