PostgreSQL 18 preview - 将命中同一个索引的多个OR条件转换为 = ANY (.....) , 避免使用更低效的BitmapOr

铃木园子 2025-01-16

193

PostgreSQL 18 preview - 将命中同一个索引的多个`OR`条件转换为 `= ANY (.....)` , 避免使用更低效的BitmapOr

作者

digoal

日期

2024-11-25

背景

PostgreSQL 18 preview - 优化器改进, 将命中同一个索引的多个OR条件转换为 = ANY (.....), 避免使用更低效的BitmapOr. 例如:

+EXPLAIN (COSTS OFF)  
+SELECT * FROM tenk1  
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);  
+                                  QUERY PLAN                                    
+------------------------------------------------------------------------------  
+ Index Scan using tenk1_thous_tenthous on tenk1  
+   Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))  
+(2 rows)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d4378c0005e61b1bb78e88097ea6efcdddbe2d6e

Transform OR-clauses to SAOP's during index matching  
author	Alexander Korotkov <akorotkov@postgresql.org>	  
Sat, 23 Nov 2024 23:40:20 +0000 (01:40 +0200)  
committer	Alexander Korotkov <akorotkov@postgresql.org>	  
Sat, 23 Nov 2024 23:40:20 +0000 (01:40 +0200)  
commit	d4378c0005e61b1bb78e88097ea6efcdddbe2d6e  
tree	7b1d55b537b5a809c668a2413c119d359063ad60	tree  
parent	869ee4f10eca2acda3d2210198a46d5029a569fc	commit | diff  
Transform OR-clauses to SAOP's during index matching  
  
This commit makes match_clause_to_indexcol() match  
"(indexkey op C1) OR (indexkey op C2) ... (indexkey op CN)" expression  
to the index while transforming it into "indexkey op ANY(ARRAY[C1, C2, ...])"  
(ScalarArrayOpExpr node).  
  
This transformation allows handling long OR-clauses with single IndexScan  
avoiding diving them into a slower BitmapOr.  
  
We currently restrict Ci to be either Const or Param to apply this  
transformation only when it's clearly beneficial.  However, in the future,  
we might switch to a liberal understanding of constants, as it is in other  
cases.  
  
Discussion: https://postgr.es/m/567ED6CA.2040504%40sigaev.ru  
Author: Alena Rybakina, Andrey Lepikhov, Alexander Korotkov  
Reviewed-by: Peter Geoghegan, Ranier Vilela, Alexander Korotkov, Robert Haas  
Reviewed-by: Jian He, Tom Lane, Nikolay Shaplov

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ae4569161a27823793ca24825bbabce2a91a0bc9

Teach bitmap path generation about transforming OR-clauses to SAOP's  
author	Alexander Korotkov <akorotkov@postgresql.org>	  
Sat, 23 Nov 2024 23:41:45 +0000 (01:41 +0200)  
committer	Alexander Korotkov <akorotkov@postgresql.org>	  
Sat, 23 Nov 2024 23:41:45 +0000 (01:41 +0200)  
commit	ae4569161a27823793ca24825bbabce2a91a0bc9  
tree	59d2131807aed4b471ad48e0d7a0e55bfa213ff9	tree  
parent	d4378c0005e61b1bb78e88097ea6efcdddbe2d6e	commit | diff  
Teach bitmap path generation about transforming OR-clauses to SAOP's  
  
When optimizer generates bitmap paths, it considers breaking OR-clause  
arguments one-by-one.  But now, a group of similar OR-clauses can be  
transformed into SAOP during index matching.  So, bitmap paths should  
keep up.  
  
This commit teaches bitmap paths generation machinery to group similar  
OR-clauses into dedicated RestrictInfos.  Those RestrictInfos are considered  
both to match index as a whole (as SAOP), or to match as a set of individual  
OR-clause argument one-by-one (the old way).  
  
Therefore, bitmap path generation will takes advantage of OR-clauses to SAOP's  
transformation.  The old way of handling them is also considered.  So, there  
shouldn't be planning regression.  
  
Discussion: https://postgr.es/m/CAPpHfdu5iQOjF93vGbjidsQkhHvY2NSm29duENYH_cbhC6x%2BMg%40mail.gmail.com  
Author: Alexander Korotkov, Andrey Lepikhov  
Reviewed-by: Alena Rybakina, Andrei Lepikhov, Jian he, Robert Haas  
Reviewed-by: Peter Geoghegan