Speeding up queries with scalar functions using search optimization¶

Enabling search optimization for queries that use scalar functions¶

Queries aren’t improved unless you enable search optimization for the columns that are specified in equality predicates that use scalar function calls. To improve the performance of queries with scalar functions on a table, use the ALTER TABLE … ADD SEARCH OPTIMIZATION command to do the following:

• Enable search optimization for specific columns.

• Enable search optimization for all columns of the table.

In general, enabling search optimization only for specific columns is the best practice. Use the ON EQUALITY clause to specify the columns. This example enables search optimization for a specific column:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(mycol);

To specify EQUALITY for all columns of the supported data types (except for semi-structured and GEOGRAPHY):

ALTER TABLE mytable ADD SEARCH OPTIMIZATION;

For more information, see Enabling and disabling search optimization.

Supported data types¶

The search optimization service can improve the performance of queries that use columns of the following data types in equality predicates that use scalar function calls:

• Data types for fixed-point numbers, including the following:

  • All INTEGER data types, which have a scale of 0.

  • Fixed-point non-integers, which have a scale other than 0 (such as NUMBER(10,2)).

  • Casts of fixed-point numbers (for example, NUMBER(30, 2)::NUMBER(30, 5)).

• String & binary data types (for example, VARCHAR and BINARY).

• Date & time data types (for example, DATE, TIME, and TIMESTAMP).

Queries that involve other types of values (for example, VARIANT, FLOAT, GEOGRAPHY, or GEOMETRY) don’t benefit.

Examples of supported queries with scalar functions¶

The following queries use scalar functions and are supported by the search optimization service.

SELECT *
  FROM test_so_scalar_function_system
  WHERE mycol = SHA2('Snowflake');

CREATE OR REPLACE FUNCTION test_scalar_udf(x INTEGER)
RETURNS INTEGER
AS
$$
  SELECT x + POW(2, 3)::INTEGER + 2
$$
;

SELECT *
  FROM test_so_scalar_function_udf
  WHERE mycol = test_scalar_udf(15750);