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 --- doc/src/sgml/config.sgml | 57 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index d8e1282e128..ac945ca4d19 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6304,6 +6304,63 @@ SELECT * FROM parent WHERE key = 2400; + + or_to_any_transform_limit (boolean) + + or_to_any_transform_limit configuration parameter + + + + + Sets the minimum length of arguments in an OR + expression exceeding which planner will try to lookup and group + multiple similar OR expressions to + ANY () + expressions. The grouping technique of this transformation is based + on the equivalence of variable sides. One side of such an expression + must be a constant clause, and the other must contain a variable + clause. The default value is 5. The value of + -1 completely disables the transformation. + + + The advantage of this OR-to-ANY transformation is + faster query planning and execution. In certain cases, this + transformation also leads to more effective plans containing + a single index scan instead of multiple bitmap scans. However, it + may also cause a planning regression when distinct + OR arguments are better to match to distinct indexes. + This may happen when they have different matching partial indexes or + have different distributions of other columns used in the query. + Generally, more groupable OR arguments mean that + transformation will be more likely to win than to lose. + + + For example, this query has its set of five OR + expressions transformed to ANY with the default + value of or_to_any_transform_limit. But not with + the increased value. + +# EXPLAIN SELECT * FROM tbl WHERE key = 1 OR key = 2 OR key = 3 OR key = 4 OR key = 5; + QUERY PLAN +----------------------------------------------------- + Seq Scan on tbl (cost=0.00..51.44 rows=64 width=4) + Filter: (key = ANY ('{1,2,3,4,5}'::integer[])) +(2 rows) + +# SET or_to_any_transform_limit = 6; +SET + +# EXPLAIN SELECT * FROM tbl WHERE key = 1 OR key = 2 OR key = 3 OR key = 4 OR key = 5; + QUERY PLAN +--------------------------------------------------------------------------- + Seq Scan on tbl (cost=0.00..67.38 rows=63 width=4) + Filter: ((key = 1) OR (key = 2) OR (key = 3) OR (key = 4) OR (key = 5)) +(2 rows) + + + + + plan_cache_mode (enum) -- cgit v1.2.3