public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
23+ messages / 3 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ 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; 23+ 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] 23+ messages in thread
* pg_combinebackup --clone doesn't work
@ 2024-06-20 05:55 Peter Eisentraut <[email protected]>
2024-06-20 09:31 ` Re: pg_combinebackup --clone doesn't work Tomas Vondra <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Peter Eisentraut @ 2024-06-20 05:55 UTC (permalink / raw)
To: pgsql-hackers
The pg_combinebackup --clone option currently doesn't work at all. Even
on systems where it should it be supported, you'll always get a "file
cloning not supported on this platform" error.
The reason is this checking code in pg_combinebackup.c:
#if (defined(HAVE_COPYFILE) && defined(COPYFILE_CLONE_FORCE)) || \
(defined(__linux__) && defined(FICLONE))
if (opt.dry_run)
pg_log_debug("would use cloning to copy files");
else
pg_log_debug("will use cloning to copy files");
#else
pg_fatal("file cloning not supported on this platform");
#endif
The problem is that this file does not include the appropriate OS header
files that would define COPYFILE_CLONE_FORCE or FICLONE, respectively.
The same problem also exists in copy_file.c. (That one has the right
header file for macOS but still not for Linux.)
This should be pretty easy to fix up, and we should think about some
ways to refactor this to avoid repeating all these OS-specific things a
few times. (The code was copied from pg_upgrade originally.)
But in the short term, how about some test coverage? You can exercise
the different pg_combinebackup copy modes like this:
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm
b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 83f385a4870..7e8dd024c82 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -848,7 +848,7 @@ sub init_from_backup
}
local %ENV = $self->_get_env();
- my @combineargs = ('pg_combinebackup', '-d');
+ my @combineargs = ('pg_combinebackup', '-d', '--clone');
if (exists $params{tablespace_map})
{
while (my ($olddir, $newdir) = each %{
$params{tablespace_map} })
We could do something like what we have for pg_upgrade, where we can use
the environment variable PG_TEST_PG_UPGRADE_MODE to test the different
copy modes. We could turn this into a global option. (This might also
be useful for future work to use file cloning elsewhere, like in CREATE
DATABASE?)
Also, I think it would be useful for consistency if pg_combinebackup had
a --copy option to select the default mode, like pg_upgrade does.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: pg_combinebackup --clone doesn't work
2024-06-20 05:55 pg_combinebackup --clone doesn't work Peter Eisentraut <[email protected]>
@ 2024-06-20 09:31 ` Tomas Vondra <[email protected]>
2024-06-20 22:07 ` Re: pg_combinebackup --clone doesn't work Tomas Vondra <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Tomas Vondra @ 2024-06-20 09:31 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; pgsql-hackers
On 6/20/24 07:55, Peter Eisentraut wrote:
> The pg_combinebackup --clone option currently doesn't work at all. Even
> on systems where it should it be supported, you'll always get a "file
> cloning not supported on this platform" error.
>
> The reason is this checking code in pg_combinebackup.c:
>
> #if (defined(HAVE_COPYFILE) && defined(COPYFILE_CLONE_FORCE)) || \
> (defined(__linux__) && defined(FICLONE))
>
> if (opt.dry_run)
> pg_log_debug("would use cloning to copy files");
> else
> pg_log_debug("will use cloning to copy files");
>
> #else
> pg_fatal("file cloning not supported on this platform");
> #endif
>
> The problem is that this file does not include the appropriate OS header
> files that would define COPYFILE_CLONE_FORCE or FICLONE, respectively.
>
> The same problem also exists in copy_file.c. (That one has the right
> header file for macOS but still not for Linux.)
>
Seems like my bug, I guess :-( Chances are the original patches had the
include, but it got lost during refactoring or something. Anyway, will
fix shortly.
> This should be pretty easy to fix up, and we should think about some
> ways to refactor this to avoid repeating all these OS-specific things a
> few times. (The code was copied from pg_upgrade originally.)
>
Yeah. The ifdef forest got rather hard to navigate.
> But in the short term, how about some test coverage? You can exercise
> the different pg_combinebackup copy modes like this:
>
> diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm
> b/src/test/perl/PostgreSQL/Test/Cluster.pm
> index 83f385a4870..7e8dd024c82 100644
> --- a/src/test/perl/PostgreSQL/Test/Cluster.pm
> +++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
> @@ -848,7 +848,7 @@ sub init_from_backup
> }
>
> local %ENV = $self->_get_env();
> - my @combineargs = ('pg_combinebackup', '-d');
> + my @combineargs = ('pg_combinebackup', '-d', '--clone');
> if (exists $params{tablespace_map})
> {
> while (my ($olddir, $newdir) = each %{
> $params{tablespace_map} })
>
For ad hoc testing? Sure, but that won't work on platforms without the
clone support, right?
> We could do something like what we have for pg_upgrade, where we can use
> the environment variable PG_TEST_PG_UPGRADE_MODE to test the different
> copy modes. We could turn this into a global option. (This might also
> be useful for future work to use file cloning elsewhere, like in CREATE
> DATABASE?)
>
Yeah, this sounds like a good way to do this. Is there a good reason to
have multiple different variables, one for each tool, or should we have
a single PG_TEST_COPY_MODE affecting all the places?
> Also, I think it would be useful for consistency if pg_combinebackup had
> a --copy option to select the default mode, like pg_upgrade does.
>
I vaguely recall this might have been discussed in the thread about
adding cloning to pg_combinebackup, but I don't recall the details why
we didn't adopt the pg_uprade way. But we can revisit that, IMO.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: pg_combinebackup --clone doesn't work
2024-06-20 05:55 pg_combinebackup --clone doesn't work Peter Eisentraut <[email protected]>
2024-06-20 09:31 ` Re: pg_combinebackup --clone doesn't work Tomas Vondra <[email protected]>
@ 2024-06-20 22:07 ` Tomas Vondra <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: Tomas Vondra @ 2024-06-20 22:07 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; pgsql-hackers
Here's a fix adding the missing headers to pg_combinebackup, and fixing
some compile-time issues in the ifdef-ed block.
I've done some basic manual testing today - I plan to test this a bit
more tomorrow, and I'll also look at integrating this into the existing
tests.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-fix-clone-headers.patch (2.0K, ../../[email protected]/2-0001-fix-clone-headers.patch)
download | inline diff:
From ae712aa6316c8f7035edee9fb49e1bfe1ea30b94 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Thu, 20 Jun 2024 23:50:22 +0200
Subject: [PATCH] fix clone headers
---
src/bin/pg_combinebackup/copy_file.c | 12 +++++++++++-
src/bin/pg_combinebackup/pg_combinebackup.c | 8 ++++++++
2 files changed, 19 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_combinebackup/copy_file.c b/src/bin/pg_combinebackup/copy_file.c
index 08c3b875420..8b50e937346 100644
--- a/src/bin/pg_combinebackup/copy_file.c
+++ b/src/bin/pg_combinebackup/copy_file.c
@@ -13,6 +13,10 @@
#ifdef HAVE_COPYFILE_H
#include <copyfile.h>
#endif
+#ifdef __linux__
+#include <sys/ioctl.h>
+#include <linux/fs.h>
+#endif
#include <fcntl.h>
#include <limits.h>
#include <sys/stat.h>
@@ -214,6 +218,9 @@ copy_file_clone(const char *src, const char *dest,
pg_fatal("error while cloning file \"%s\" to \"%s\": %m", src, dest);
#elif defined(__linux__) && defined(FICLONE)
{
+ int src_fd;
+ int dest_fd;
+
if ((src_fd = open(src, O_RDONLY | PG_BINARY, 0)) < 0)
pg_fatal("could not open file \"%s\": %m", src);
@@ -228,8 +235,11 @@ copy_file_clone(const char *src, const char *dest,
unlink(dest);
pg_fatal("error while cloning file \"%s\" to \"%s\": %s",
- src, dest);
+ src, dest, strerror(save_errno));
}
+
+ close(src_fd);
+ close(dest_fd);
}
#else
pg_fatal("file cloning not supported on this platform");
diff --git a/src/bin/pg_combinebackup/pg_combinebackup.c b/src/bin/pg_combinebackup/pg_combinebackup.c
index 01d7fb98e79..f67ccf76ea2 100644
--- a/src/bin/pg_combinebackup/pg_combinebackup.c
+++ b/src/bin/pg_combinebackup/pg_combinebackup.c
@@ -16,6 +16,14 @@
#include <fcntl.h>
#include <limits.h>
+#ifdef HAVE_COPYFILE_H
+#include <copyfile.h>
+#endif
+#ifdef __linux__
+#include <sys/ioctl.h>
+#include <linux/fs.h>
+#endif
+
#include "backup_label.h"
#include "common/blkreftable.h"
#include "common/checksum_helper.h"
--
2.45.2
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2024-06-20 22:07 UTC | newest]
Thread overview: 23+ 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]>
2024-06-20 05:55 pg_combinebackup --clone doesn't work Peter Eisentraut <[email protected]>
2024-06-20 09:31 ` Re: pg_combinebackup --clone doesn't work Tomas Vondra <[email protected]>
2024-06-20 22:07 ` Re: pg_combinebackup --clone doesn't work Tomas Vondra <[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