SQL performance: large list of values as subquery expression
Background
ELLA is performing queries with the IN
operator at multiple places. (expression IN (value [, ...])
)
According to the docs (v14) this is inefficient for long lists:
The right-hand side is a parenthesized list of scalar expressions. The result is “true” if the left-hand expression's result is equal to any of the right-hand expressions. This is a shorthand notation for
expression = value1 OR expression = value2 OR
Implementation
provide list as subquery expression instead:
IN(SELECT(UNNEST(array)))
When loading an analysis in ella, this gives about 10% speed improvement.