From 72bd38cc99a15da6f97373fae98027c908c398ea Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Mon, 8 Apr 2024 01:27:28 +0300 Subject: Transform OR clauses to ANY expression Replace (expr op C1) OR (expr op C2) ... with expr op ANY(ARRAY[C1, C2, ...]) on the preliminary stage of optimization when we are still working with the expression tree. Here Cn is a n-th constant expression, 'expr' is non-constant expression, 'op' is an operator which returns boolean result and has a commuter (for the case of reverse order of constant and non-constant parts of the expression, like 'Cn op expr'). Sometimes it can lead to not optimal plan. This is why there is a or_to_any_transform_limit GUC. It specifies a threshold value of length of arguments in an OR expression that triggers the OR-to-ANY transformation. Generally, more groupable OR arguments mean that transformation will be more likely to win than to lose. Discussion: https://postgr.es/m/567ED6CA.2040504%40sigaev.ru Author: Alena Rybakina Author: Andrey Lepikhov Reviewed-by: Peter Geoghegan Reviewed-by: Ranier Vilela Reviewed-by: Alexander Korotkov Reviewed-by: Robert Haas Reviewed-by: Jian He --- src/backend/utils/misc/guc_tables.c | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'src/backend/utils/misc/guc_tables.c') diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 7d4e4387cf5..f9bb2b0f9e5 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -3657,6 +3657,18 @@ struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, + { + {"or_to_any_transform_limit", PGC_USERSET, QUERY_TUNING_OTHER, + gettext_noop("Set the minimum length of the list of OR clauses to attempt the OR-to-ANY transformation."), + gettext_noop("Once the limit is reached, the planner will try to replace expression like " + "'x=c1 OR x=c2 ..' to the expression 'x = ANY(ARRAY[c1,c2,..])'"), + GUC_EXPLAIN + }, + &or_to_any_transform_limit, + 5, -1, INT_MAX, + NULL, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL -- cgit v1.2.3