Improve performance and memory handling in case of many UDFs (except Lua)

Detail name	Value
Changelog Number	5010
Type	Improvement
Status	Open

Description

When using many (say, 50) UDFs in the same query there is a very high memory consumption and bad performance. This is especially true for SCALAR RETURNS UDFs which may be used in high number in a single SELECT-statement. The limit for the number of UDFs until problems occurs depends a lot on the script language used. The only script language without problems in that area is Lua.

The reason for the problem are separate processes startet for the the calculation of the UDFs. The number of the processes depends on the number of UDFs used in the query.

We plan to improve this behaviour in a way that using many UDFs leads to acceptable execution times and memory consumption.

Workaround

• Since the script language Lua is not affected by the problem, try to migrate the script to Lua if possible.
• Otherwise it is possible to reduce the problem by reducing the number of UDFs computed in a single SELECT that will be materialized. So it is possible to compute some UDFs in a SELECT with ORDER BY FALSE (causes materialization), then compute some more on the result of that select and so on. However, this approach might take even longer than waiting for the original select to finish due to several materialization stages. Sketch of rewrite needed in this case:

SELECT
  computed_cols,
  udf26(col),
  ...
  udf50(col),
  further_needed_columns
FROM
(
  SELECT
    udf1(col),
    ...
    udf25(col),
    further_needed_columns
  FROM
    <original select>
  ORDER BY FALSE
)