Speedup ScalarArrayOpExpr evaluation

Enterprise / PostgreSQL - David Rowley [postgresql.org] - 8 April 2021 11:51 UTC

ScalarArrayOpExprs with "useOr=true" and a set of Consts on the righthand side have traditionally been evaluated by using a linear search over the array. When these arrays contain large numbers of elements then this linear search could become a significant part of execution time.

Here we add a new method of evaluating ScalarArrayOpExpr expressions to allow them to be evaluated by first building a hash table containing each element, then on subsequent evaluations, we just probe that hash table to determine if there is a match.

The planner is in charge of determining when this optimization is possible and it enables it by setting hashfuncid in the ScalarArrayOpExpr. The executor will only perform the hash table evaluation when the hashfuncid is set.

This means that not all cases are optimized. For example CHECK constraints containing an IN clause won't go through the planner, so won't get the hashfuncid set. We could maybe do something about that at some later date. The reason we're not doing it now is from fear that we may slow down cases where the expression is evaluated only once. Those cases can be common, for example, a single row INSERT to a table with a CHECK constraint containing an IN clause.

In the planner, we enable this when there are suitable hash functions for the ScalarArrayOpExpr's operator and only when there is at least MIN_ARRAY_SIZE_FOR_HASHED_SAOP elements in the array. The threshold is currently set to 9.

Author: James Coleman, David Rowley

50e17ad281 Speedup ScalarArrayOpExpr evaluation
src/backend/executor/execExpr.c | 99 +++++++++--
src/backend/executor/execExprInterp.c | 262 ++++++++++++++++++++++++++++++
src/backend/jit/llvm/llvmjit_expr.c | 6 +
src/backend/jit/llvm/llvmjit_types.c | 1 +
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 6 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/path/costsize.c | 43 ++++-
src/backend/optimizer/plan/planner.c | 10 ++
src/backend/optimizer/plan/setrefs.c | 10 +-
src/backend/optimizer/prep/prepqual.c | 1 +
src/backend/optimizer/util/clauses.c | 64 ++++++++
src/backend/parser/parse_oper.c | 1 +
src/backend/partitioning/partbounds.c | 1 +
src/include/catalog/catversion.h | 2 +-
src/include/executor/execExpr.h | 19 +++
src/include/nodes/primnodes.h | 9 +-
src/include/optimizer/optimizer.h | 2 +
src/test/regress/expected/expressions.out | 118 ++++++++++++++
src/test/regress/sql/expressions.sql | 85 ++++++++++
21 files changed, 712 insertions(+), 30 deletions(-)

Upstream: git.postgresql.org


  • Share