When the Pivotal Query Optimizer (PQO) is enabled and is processing a query that contains a predicate with a constant array, the optimizer_array_expansion_threshold parameter limits the optimization process based on the number of constants in the array. If the array in the query predicate contains more than the number elements specified by parameter, PQO disables the transformation of the predicate into its disjunctive normal form during query optimization.

The default value is 25.

For example, when PQO is executing a query that contains an IN clause with more than 25 elements, PQO does not transform the predicate into its disjunctive normal form during query optimization to reduce optimization time consume less memory. The difference in query processing can be seen in the filter condition for the IN clause of the query EXPLAIN plan.

Changing the value of this parameter changes the trade-off between a shorter optimization time and lower memory consumption, and the potential benefits from constraint derivation during query optimization, for example conflict detection and partition elimination.

The parameter can be set for a database system, an individual database, or a session or query.

Value Range Default Set Classifications
Integer > 0 25 master