From 9416a89cfe268f29c69f1ef8b0b7d72af9d18da1 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Thu, 2 Jul 2020 18:46:48 -0500 Subject: [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint --- .../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