Subject: [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint

From: Justin Pryzby <[email-masked]>
Date: Thu, 2 Jul 2020 18:46:48 -0500

---
 .../postgres_fdw/expected/postgres_fdw.out    | 12 ++++-----
 src/backend/optimizer/path/indxpath.c         |  5 ++++
 src/test/regress/expected/create_index.out    | 25 +++++++++++++++++++
 src/test/regress/sql/create_index.sql         | 10 ++++++++
 4 files changed, 45 insertions(+), 7 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
 -- Check case where the foreign partition is a subplan target rel
 explain (verbose, costs off)
 update utrtest set a = 1 where a = 1 or a = 2 returning *;
-                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
+                           QUERY PLAN                            
+-----------------------------------------------------------------
  Update on public.utrtest
    Output: utrtest_1.a, utrtest_1.b
    Foreign Update on public.remp utrtest_1
    Update on public.locp utrtest_2
    ->  Foreign Update on public.remp utrtest_1
-         Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+         Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
    ->  Seq Scan on public.locp utrtest_2
          Output: 1, utrtest_2.b, utrtest_2.ctid
-         Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
 
 -- The new values are concatenated with ' triggered !'
 update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
    Update on public.locp utrtest_1
    ->  Seq Scan on public.locp utrtest_1
          Output: 1, utrtest_1.b, utrtest_1.ctid
-         Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
 
 -- The new values are concatenated with ' triggered !'
 update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
 				Assert(!restriction_is_or_clause(rinfo));
 				orargs = list_make1(rinfo);
 
+				/* Avoid scanning indexes using a scan condition which is
+				 * inconsistent with the partition constraint */
+				if (predicate_refuted_by(rel->partition_qual, orargs, false))
+					continue;
+
 				indlist = build_paths_for_OR(root, rel,
 											 orargs,
 											 all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
     10
 (1 row)
 
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append
+   ->  Bitmap Heap Scan on bitmapor1 bitmapor_1
+         Recheck Cond: ((i = 1) OR (i = 2))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on bitmapor1_i_idx
+                     Index Cond: (i = 1)
+               ->  Bitmap Index Scan on bitmapor1_i_idx
+                     Index Cond: (i = 2)
+   ->  Bitmap Heap Scan on bitmapor2 bitmapor_2
+         Recheck Cond: (i = 11)
+         ->  Bitmap Index Scan on bitmapor2_i_idx
+               Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
 --
 -- Check behavior with duplicate index column contents
 --
-- 
2.17.0

--X1bOJ3K7DJ5YkBrT--