public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
21+ messages / 2 participants
[nested] [flat]
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../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--
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Justin Pryzby <[email protected]>
@ 2022-02-10 01:01 Michael Paquier <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Michael Paquier @ 2022-02-10 01:01 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: pgsql-hackers
Hi Alexander,
On Wed, Feb 09, 2022 at 03:18:12PM +0300, Alexander Pyhalov wrote:
> I've looked at patches, introducing CREATE INDEX CONCURRENTLY for
> partitioned tables - https://www.postgresql.org/message-id/flat/20210226182019.GU20769%40telsasoft.com#da169a0a518bf81216...
> .
> The thread didn't have any activity for a year.
If you plan to review a patch set, could you reply directly on its
thread without changing the subject please? This way of replying
breaks the flow of a thread, making it harder to track.
Thanks!
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../YgRj1MCm%[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2022-02-10 01:01 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2022-02-10 01:01 Re: Justin Pryzby <[email protected]> Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox