public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
21+ messages / 2 participants
[nested] [flat]
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
@ 2020-07-02 23:46 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2020-07-02 23:46 UTC (permalink / raw)
---
.../postgres_fdw/expected/postgres_fdw.out | 12 ++++-----
src/backend/optimizer/path/indxpath.c | 5 ++++
src/test/regress/expected/create_index.out | 25 +++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 ++++++++
4 files changed, 45 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dbbae1820e..e8c2af1c04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7823,18 +7823,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7855,8 +7854,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index bcb1bc6097..0532b3ddd0 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1305,6 +1305,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is
+ * inconsistent with the partition constraint */
+ if (predicate_refuted_by(rel->partition_qual, orargs, false))
+ continue;
+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index e3e6634d7e..1a976ad211 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1
+ Recheck Cond: ((i = 1) OR (i = 2))
+ -> BitmapOr
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 1)
+ -> Bitmap Index Scan on bitmapor1_i_idx
+ Index Cond: (i = 2)
+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2
+ Recheck Cond: (i = 11)
+ -> Bitmap Index Scan on bitmapor2_i_idx
+ Index Cond: (i = 11)
+(12 rows)
+
+DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f3667bacdc..dd1de8ee1d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint
+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);
+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);
+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);
+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;
+VACUUM ANALYZE bitmapor;
+CREATE INDEX ON bitmapor(i);
+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);
+DROP TABLE bitmapor;
+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
--X1bOJ3K7DJ5YkBrT--
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: wake up logical workers after ALTER SUBSCRIPTION
@ 2023-01-03 18:10 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Nathan Bossart @ 2023-01-03 18:10 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Tom Lane <[email protected]>; Melih Mutlu <[email protected]>; Thomas Munro <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
On Tue, Jan 03, 2023 at 11:03:32AM +0530, Amit Kapila wrote:
> On Thu, Dec 15, 2022 at 4:47 AM Nathan Bossart <[email protected]> wrote:
>> On Wed, Dec 14, 2022 at 02:02:58PM -0500, Tom Lane wrote:
>> > Maybe we could have workers that are exiting for that reason set a
>> > flag saying "please restart me without delay"?
>>
>> That helps a bit, but there are still delays when starting workers for new
>> subscriptions. I think we'd need to create a new array in shared memory
>> for subscription OIDs that need their workers started immediately.
>
> That would be tricky because the list of subscription OIDs can be
> longer than the workers. Can't we set a boolean variable
> (check_immediate or something like that) in LogicalRepCtxStruct and
> use that to traverse the subscriptions? So, when any worker will
> restart because of a parameter change, we can set the variable and
> send a signal to the launcher. The launcher can then check this
> variable to decide whether to start the missing workers for enabled
> subscriptions.
My approach was to add a variable to LogicalRepWorker that indicated
whether a worker needed to be restarted immediately. While this is a
little weird because the workers array is treated as slots, it worked
nicely for ALTER SUBSCRIPTION. However, this doesn't help at all for
CREATE SUBSCRIPTION.
IIUC you are suggesting just one variable that would bypass
wal_retrieve_retry_interval for all subscriptions, not just those newly
altered or created. This definitely seems like it would prevent delays,
but it would also cause wal_retrieve_retry_interval to be incorrectly
bypassed for the other workers in some cases. Is this acceptable?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2023-01-03 18:10 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]>
2023-01-03 18:10 Re: wake up logical workers after ALTER SUBSCRIPTION Nathan Bossart <[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