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. The primary obstacle to function pushdown is the incorrect specification of attributes in user-defined functions.

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_procPG_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 indicates the volatility attribute of a function. The value is i/s/v. i indicates IMMUTABLE, s indicates STABLE, and v indicates VOLATILE.

  Example:

  • For instance, an IMMUTABLE function consistently yields identical results for the same inputs. A common example would be most string processing functions, which are predictable and therefore can be optimized by pushing down their execution.
  • 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.