Help Center> Data Warehouse Service> Troubleshooting> Database Performance and Resources> Statements with User-defined Functions Cannot Be Pushed Down
View PDF

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

  1. 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.
    • provolatile describes that a function is IMMUTABLE, STABLE, or 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.