public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint 22+ messages / 3 participants [nested] [flat]
* [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint @ 2020-07-02 23:46 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ messages in thread
* Re: Oversight in reparameterize_path_by_child leading to executor crash @ 2023-08-01 13:20 Tom Lane <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Tom Lane @ 2023-08-01 13:20 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: pgsql-hackers Richard Guo <[email protected]> writes: > In this case what we need to do is to adjust the TableSampleClause to > refer to the correct child relations. We can do that with the help of > adjust_appendrel_attrs_multilevel(). One problem is that the > TableSampleClause is stored in RangeTblEntry, and it does not seem like > a good practice to alter RangeTblEntry in this place. Ugh. That's why we didn't think to adjust it, obviously. You are right that we can't just modify the RTE on the fly, since it's shared with other non-parameterized paths. > So what I'm thinking is that maybe we can add a new type of path, named > SampleScanPath, to have the TableSampleClause per path. Then we can > safely reparameterize the TableSampleClause as needed for each > SampleScanPath. That's what the attached patch does. Alternatively, could we postpone the reparameterization until createplan.c? Having to build reparameterized expressions we might not use seems expensive, and it would also contribute to the memory bloat being complained of in nearby threads. > There are some other plan types that do not have a separate path type > but may have lateral references in expressions stored in RangeTblEntry, > such as FunctionScan, TableFuncScan and ValuesScan. But it's not clear > to me if there are such problems with them too. Hmm, well, not for partition-wise join anyway. regards, tom lane ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Oversight in reparameterize_path_by_child leading to executor crash @ 2023-08-02 11:02 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 22+ messages in thread From: Richard Guo @ 2023-08-02 11:02 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-hackers On Tue, Aug 1, 2023 at 9:20 PM Tom Lane <[email protected]> wrote: > Richard Guo <[email protected]> writes: > > So what I'm thinking is that maybe we can add a new type of path, named > > SampleScanPath, to have the TableSampleClause per path. Then we can > > safely reparameterize the TableSampleClause as needed for each > > SampleScanPath. That's what the attached patch does. > > Alternatively, could we postpone the reparameterization until > createplan.c? Having to build reparameterized expressions we might > not use seems expensive, and it would also contribute to the memory > bloat being complained of in nearby threads. I did think about this option but figured out that it seems beyond the scope of just fixing SampleScan. But if we want to optimize the reparameterization mechanism besides fixing this crash, I think this option is much better. I drafted a patch as attached. Thanks Richard Attachments: [application/octet-stream] v2-0001-Postpone-reparameterization-of-paths-until-when-creating-plans.patch (14.4K, ../../CAMbWs4_vPd1yvxhiwD67o5cBsmgWc-py+B8gFFDDTrTQY5EXeg@mail.gmail.com/3-v2-0001-Postpone-reparameterization-of-paths-until-when-creating-plans.patch) download | inline diff: From 1e195c405014140090b2be49738a017eabb59de4 Mon Sep 17 00:00:00 2001 From: Richard Guo <[email protected]> Date: Wed, 2 Aug 2023 16:09:55 +0800 Subject: [PATCH v2] Postpone reparameterization of paths until when creating plans --- src/backend/optimizer/path/joinpath.c | 52 +++---------- src/backend/optimizer/plan/createplan.c | 17 +++++ src/backend/optimizer/util/pathnode.c | 77 ++++++++++++++++++-- src/include/nodes/pathnodes.h | 13 ++++ src/include/optimizer/pathnode.h | 1 + src/test/regress/expected/partition_join.out | 60 +++++++++++++++ src/test/regress/sql/partition_join.sql | 12 +++ 7 files changed, 185 insertions(+), 47 deletions(-) diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index 4b58936fa4..b5e208c761 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -30,19 +30,6 @@ /* Hook for plugins to get control in add_paths_to_joinrel() */ set_join_pathlist_hook_type set_join_pathlist_hook = NULL; -/* - * Paths parameterized by the parent can be considered to be parameterized by - * any of its child. - */ -#define PATH_PARAM_BY_PARENT(path, rel) \ - ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), \ - (rel)->top_parent_relids)) -#define PATH_PARAM_BY_REL_SELF(path, rel) \ - ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), (rel)->relids)) - -#define PATH_PARAM_BY_REL(path, rel) \ - (PATH_PARAM_BY_REL_SELF(path, rel) || PATH_PARAM_BY_PARENT(path, rel)) - static void try_partial_mergejoin_path(PlannerInfo *root, RelOptInfo *joinrel, Path *outer_path, @@ -778,23 +765,15 @@ try_nestloop_path(PlannerInfo *root, { /* * If the inner path is parameterized, it is parameterized by the - * topmost parent of the outer rel, not the outer rel itself. Fix - * that. + * topmost parent of the outer rel, not the outer rel itself. We will + * fix that in createplan.c. For now we need to check whether we can + * translate the inner path, and if not avoid creating nestloop path. */ - if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent)) + if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent) && + !path_is_reparameterizable_by_child(inner_path)) { - inner_path = reparameterize_path_by_child(root, inner_path, - outer_path->parent); - - /* - * If we could not translate the path, we can't create nest loop - * path. - */ - if (!inner_path) - { - bms_free(required_outer); - return; - } + bms_free(required_outer); + return; } add_path(joinrel, (Path *) @@ -869,20 +848,11 @@ try_partial_nestloop_path(PlannerInfo *root, return; /* - * If the inner path is parameterized, it is parameterized by the topmost - * parent of the outer rel, not the outer rel itself. Fix that. + * See the comments in try_nestloop_path. */ - if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent)) - { - inner_path = reparameterize_path_by_child(root, inner_path, - outer_path->parent); - - /* - * If we could not translate the path, we can't create nest loop path. - */ - if (!inner_path) - return; - } + if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent) && + !path_is_reparameterizable_by_child(inner_path)) + return; /* Might be good enough to be worth trying, so let's try it. */ add_partial_path(joinrel, (Path *) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index af48109058..4cb37d4187 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -29,6 +29,7 @@ #include "optimizer/cost.h" #include "optimizer/optimizer.h" #include "optimizer/paramassign.h" +#include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/placeholder.h" #include "optimizer/plancat.h" @@ -4327,6 +4328,22 @@ create_nestloop_plan(PlannerInfo *root, List *nestParams; Relids saveOuterRels = root->curOuterRels; + /* + * If the inner path is parameterized, it is parameterized by the + * topmost parent of the outer rel, not the outer rel itself. Fix + * that. + */ + if (PATH_PARAM_BY_PARENT(best_path->jpath.innerjoinpath, + best_path->jpath.outerjoinpath->parent)) + { + best_path->jpath.innerjoinpath = + reparameterize_path_by_child(root, + best_path->jpath.innerjoinpath, + best_path->jpath.outerjoinpath->parent); + + Assert(best_path->jpath.innerjoinpath != NULL); + } + /* NestLoop can project, so no need to be picky about child tlists */ outer_plan = create_plan_recurse(root, best_path->jpath.outerjoinpath, 0); diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index f123fcb41e..1977ef1409 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -2430,6 +2430,16 @@ create_nestloop_path(PlannerInfo *root, { NestPath *pathnode = makeNode(NestPath); Relids inner_req_outer = PATH_REQ_OUTER(inner_path); + Relids outerrelids; + + /* + * Paths are parameterized by top-level parents, so run parameterization + * tests on the parent relids. + */ + if (outer_path->parent->top_parent_relids) + outerrelids = outer_path->parent->top_parent_relids; + else + outerrelids = outer_path->parent->relids; /* * If the inner path is parameterized by the outer, we must drop any @@ -2439,7 +2449,7 @@ create_nestloop_path(PlannerInfo *root, * estimates for this path. We detect such clauses by checking for serial * number match to clauses already enforced in the inner path. */ - if (bms_overlap(inner_req_outer, outer_path->parent->relids)) + if (bms_overlap(inner_req_outer, outerrelids)) { Bitmapset *enforced_serials = get_param_path_clause_serials(inner_path); List *jclauses = NIL; @@ -4019,6 +4029,43 @@ reparameterize_path(PlannerInfo *root, Path *path, return NULL; } +/* + * path_is_reparameterizable_by_child + * Given a path parameterized by the parent of a child relation, check to + * see if it can be translated to be parameterized by child relation. + * + * Currently, only a few path types are supported here, though more could be + * added at need. + */ +bool +path_is_reparameterizable_by_child(Path *path) +{ + switch (nodeTag(path)) + { + case T_Path: + case T_IndexPath: + case T_BitmapHeapPath: + case T_BitmapAndPath: + case T_BitmapOrPath: + case T_ForeignPath: + case T_CustomPath: + case T_NestPath: + case T_MergePath: + case T_HashPath: + case T_AppendPath: + case T_MaterialPath: + case T_MemoizePath: + case T_GatherPath: + return true; + default: + + /* We don't know how to reparameterize this path. */ + return false; + } + + return false; +} + /* * reparameterize_path_by_child * Given a path parameterized by the parent of the given child relation, @@ -4047,11 +4094,13 @@ reparameterize_path_by_child(PlannerInfo *root, Path *path, ( (newnode) = makeNode(nodetype), \ memcpy((newnode), (node), sizeof(nodetype)) ) -#define ADJUST_CHILD_ATTRS(node) \ +#define ADJUST_CHILD_EXPRS(node, fieldtype) \ ((node) = \ - (List *) adjust_appendrel_attrs_multilevel(root, (Node *) (node), \ - child_rel, \ - child_rel->top_parent)) + (fieldtype) adjust_appendrel_attrs_multilevel(root, (Node *) (node), \ + child_rel, \ + child_rel->top_parent)) + +#define ADJUST_CHILD_ATTRS(node) ADJUST_CHILD_EXPRS(node, List *) #define REPARAMETERIZE_CHILD_PATH(path) \ do { \ @@ -4099,7 +4148,23 @@ do { \ switch (nodeTag(path)) { case T_Path: - FLAT_COPY_PATH(new_path, path, Path); + { + FLAT_COPY_PATH(new_path, path, Path); + + if (path->pathtype == T_SampleScan) + { + Index scan_relid = path->parent->relid; + RangeTblEntry *rte; + + /* it should be a base rel with a tablesample clause... */ + Assert(scan_relid > 0); + rte = planner_rt_fetch(scan_relid, root); + Assert(rte->rtekind == RTE_RELATION); + Assert(rte->tablesample != NULL); + + ADJUST_CHILD_EXPRS(rte->tablesample, TableSampleClause *); + } + } break; case T_IndexPath: diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index c17b53f7ad..c6c6c9eba8 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1637,6 +1637,19 @@ typedef struct Path #define PATH_REQ_OUTER(path) \ ((path)->param_info ? (path)->param_info->ppi_req_outer : (Relids) NULL) +/* + * Paths parameterized by the parent can be considered to be parameterized by + * any of its child. + */ +#define PATH_PARAM_BY_PARENT(path, rel) \ + ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), \ + (rel)->top_parent_relids)) +#define PATH_PARAM_BY_REL_SELF(path, rel) \ + ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), (rel)->relids)) + +#define PATH_PARAM_BY_REL(path, rel) \ + (PATH_PARAM_BY_REL_SELF(path, rel) || PATH_PARAM_BY_PARENT(path, rel)) + /*---------- * IndexPath represents an index scan over a single index. * diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 001e75b5b7..85f5562033 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -295,6 +295,7 @@ extern Path *reparameterize_path(PlannerInfo *root, Path *path, double loop_count); extern Path *reparameterize_path_by_child(PlannerInfo *root, Path *path, RelOptInfo *child_rel); +extern bool path_is_reparameterizable_by_child(Path *path); /* * prototypes for relnode.c diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6560fe2416..a11f738411 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -505,6 +505,31 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL 550 | | (12 rows) +-- lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 JOIN LATERAL + (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a; + QUERY PLAN +------------------------------------------------------------- + Append + -> Nested Loop + -> Seq Scan on prt1_p1 t1_1 + -> Sample Scan on prt1_p1 t2_1 + Sampling: system (t1_1.a) REPEATABLE (t1_1.b) + Filter: (t1_1.a = a) + -> Nested Loop + -> Seq Scan on prt1_p2 t1_2 + -> Sample Scan on prt1_p2 t2_2 + Sampling: system (t1_2.a) REPEATABLE (t1_2.b) + Filter: (t1_2.a = a) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + -> Sample Scan on prt1_p3 t2_3 + Sampling: system (t1_3.a) REPEATABLE (t1_3.b) + Filter: (t1_3.a = a) +(16 rows) + -- bug with inadequate sort key representation SET enable_partitionwise_aggregate TO true; SET enable_hashjoin TO false; @@ -1944,6 +1969,41 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL 550 | 0 | 0002 | | | | | (12 rows) +-- partitionwise join with lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 JOIN LATERAL + (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s ON + t1.a = s.a AND t1.b = s.b AND t1.c = s.c; + QUERY PLAN +---------------------------------------------------------------------------------------- + Append + -> Nested Loop + -> Seq Scan on prt1_l_p1 t1_1 + -> Sample Scan on prt1_l_p1 t2_1 + Sampling: system (t1_1.a) REPEATABLE (t1_1.b) + Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p1 t1_2 + -> Sample Scan on prt1_l_p2_p1 t2_2 + Sampling: system (t1_2.a) REPEATABLE (t1_2.b) + Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p2 t1_3 + -> Sample Scan on prt1_l_p2_p2 t2_3 + Sampling: system (t1_3.a) REPEATABLE (t1_3.b) + Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p1 t1_4 + -> Sample Scan on prt1_l_p3_p1 t2_4 + Sampling: system (t1_4.a) REPEATABLE (t1_4.b) + Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p2 t1_5 + -> Sample Scan on prt1_l_p3_p2 t2_5 + Sampling: system (t1_5.a) REPEATABLE (t1_5.b) + Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text)) +(26 rows) + -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 48daf3aee3..e2daab03fb 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -100,6 +100,12 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a; +-- lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 JOIN LATERAL + (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a; + -- bug with inadequate sort key representation SET enable_partitionwise_aggregate TO true; SET enable_hashjoin TO false; @@ -387,6 +393,12 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a; +-- partitionwise join with lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 JOIN LATERAL + (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s ON + t1.a = s.a AND t1.b = s.b AND t1.c = s.c; + -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; -- 2.31.0 ^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2023-08-02 11:02 UTC | newest] Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2020-07-02 23:46 [PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint Justin Pryzby <[email protected]> 2023-08-01 13:20 Re: Oversight in reparameterize_path_by_child leading to executor crash Tom Lane <[email protected]> 2023-08-02 11:02 ` Re: Oversight in reparameterize_path_by_child leading to executor crash Richard Guo <[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