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
* libpq: Process buffered SSL read bytes to support records >8kB on async API @ 2024-09-08 20:07 Lars Kanis <[email protected]> 0 siblings, 0 replies; 21+ messages in thread From: Lars Kanis @ 2024-09-08 20:07 UTC (permalink / raw) To: [email protected] Dear hackers, I'm the maintainer of ruby-pg the ruby interface to the PostgreSQL database. This binding uses the asynchronous API of libpq by default to facilitate the ruby IO wait and scheduling mechanisms. This works well with the vanilla postgresql server, but it leads to starvation with other types of servers using the postgresql wire protocol 3. This is because the current functioning of the libpq async interface depends on a maximum size of SSL records of 8kB. The following servers were reported to starve with ruby-pg: * AWS RDS Aurora Serverless [1] * YugabyteDb [2] * CockroachDB [3] They block infinitely on certain message sizes sent from the backend to the libpq frontend. It is best described in [4]. A repro docker composition is provided by YugabyteDB at [2]. To fix this issue the attached patch calls pqReadData() repeatedly in PQconsumeInput() until there is no buffered SSL data left to be read. Another solution could be to process buffered SSL read bytes in PQisBusy() instead of PQconsumeInput() . The synchronous libpq API isn't affected, since it supports arbitrary SSL record sizes already. That's why I think that the asynchronous API should also support bigger SSL record sizes. Regards, Lars [1] https://github.com/ged/ruby-pg/issues/325 [2] https://github.com/ged/ruby-pg/issues/588 [3] https://github.com/ged/ruby-pg/issues/583 [4] https://github.com/ged/ruby-pg/issues/325#issuecomment-737561270 Attachments: [text/x-patch] 0001-libpq-Process-buffered-SSL-read-bytes-to-support-rec.patch (1.7K, ../../[email protected]/2-0001-libpq-Process-buffered-SSL-read-bytes-to-support-rec.patch) download | inline diff: From ab793829a4ce473f1cc2bbc0e2a6f6753553255d Mon Sep 17 00:00:00 2001 From: Lars Kanis <[email protected]> Date: Sun, 8 Sep 2024 13:59:05 +0200 Subject: [PATCH] libpq: Process buffered SSL read bytes to support records >8kB on async API The async API of libpq doesn't support SSL record sizes >8kB so far. This size isn't exceeded by vanilla PostgreSQL, but by other products using the postgres wire protocol 3. PQconsumeInput() reads all data readable from the socket, so that the read condition is cleared. But it doesn't process all the data that is pending on the SSL layer. Also a subsequent call to PQisBusy() doesn't process it, so that the client is triggered to wait for more readable data on the socket. But this never arrives, so that the connection blocks infinitely. To fix this issue call pqReadData() repeatedly until there is no buffered SSL data left to be read. The synchronous libpq API isn't affected, since it supports arbitrary SSL record sizes already. --- src/interfaces/libpq/fe-exec.c | 13 +++++++++++++ 1 file changed, 13 insertions(+) diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index 0d224a852..637894ee1 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -2006,6 +2006,19 @@ PQconsumeInput(PGconn *conn) if (pqReadData(conn) < 0) return 0; + #ifdef USE_SSL + /* + * Ensure all buffered read bytes in the SSL library are processed, + * which might be not the case, if the SSL record size exceeds 8k. + * Otherwise parseInput can't process the data. + */ + while (conn->ssl_in_use && pgtls_read_pending(conn)) + { + if (pqReadData(conn) < 0) + return 0; + } + #endif + /* Parsing of the data waits till later. */ return 1; } -- 2.43.0 ^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2024-09-08 20:07 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]> 2024-09-08 20:07 libpq: Process buffered SSL read bytes to support records >8kB on async API Lars Kanis <[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