public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 2/2] Avoid bitmap index scan inconsistent with partition constraint
7+ 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; 7+ 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] 7+ messages in thread
* gitmaster server problem?
@ 2024-08-17 16:41 Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2024-08-17 16:41 UTC (permalink / raw)
To: pgsql-hackers
About 20 minutes ago I starting getting gitmaster pull errors:
$ git pull ssh://[email protected]/postgresql.git
ssh: connect to host gitmaster.postgresql.org port 22: Connection timed
out
fatal: Could not read from remote repository.
Please make sure you have the correct access rights
and the repository exists.
Public git seems fine:
$ git pull https://git.postgresql.org/git/postgresql.git
From https://git.postgresql.org/git/postgresql
* branch HEAD -> FETCH_HEAD
Already up to date.
Can anyone confirm or report on the cause?
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: gitmaster server problem?
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
@ 2024-08-17 16:49 ` Tom Lane <[email protected]>
2024-08-17 17:22 ` Re: gitmaster server problem? Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-08-17 16:49 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]
Bruce Momjian <[email protected]> writes:
> About 20 minutes ago I starting getting gitmaster pull errors:
> ...
> Can anyone confirm or report on the cause?
Yeah, for me it's hanging up at the connection establishment
stage ("ssh -fMN gitmaster.postgresql.org"). Traceroute gets
as far as some rackspace.net machines, but gemulon itself
isn't answering.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: gitmaster server problem?
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
@ 2024-08-17 17:22 ` Tom Lane <[email protected]>
2024-08-17 18:24 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-08-17 17:22 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]
I wrote:
> Bruce Momjian <[email protected]> writes:
>> About 20 minutes ago I starting getting gitmaster pull errors:
>> Can anyone confirm or report on the cause?
> Yeah, for me it's hanging up at the connection establishment
> stage ("ssh -fMN gitmaster.postgresql.org"). Traceroute gets
> as far as some rackspace.net machines, but gemulon itself
> isn't answering.
ssh and git pull are working again now for me.
Oddly, "ping gemulon.postgresql.org" is still showing 98%+
packet drop rate. Not sure if that's normal.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: gitmaster server problem?
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 17:22 ` Re: gitmaster server problem? Tom Lane <[email protected]>
@ 2024-08-17 18:24 ` Bruce Momjian <[email protected]>
2024-08-17 18:53 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2024-08-17 18:24 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Sat, Aug 17, 2024 at 01:22:18PM -0400, Tom Lane wrote:
> I wrote:
> > Bruce Momjian <[email protected]> writes:
> >> About 20 minutes ago I starting getting gitmaster pull errors:
> >> Can anyone confirm or report on the cause?
>
> > Yeah, for me it's hanging up at the connection establishment
> > stage ("ssh -fMN gitmaster.postgresql.org"). Traceroute gets
> > as far as some rackspace.net machines, but gemulon itself
> > isn't answering.
>
> ssh and git pull are working again now for me.
ssh and git pull are still failing for me to gitmaster.postgresql.org.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: gitmaster server problem?
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 17:22 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 18:24 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
@ 2024-08-17 18:53 ` Bruce Momjian <[email protected]>
2024-08-17 19:04 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2024-08-17 18:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]; Joe Conway <[email protected]>
On Sat, Aug 17, 2024 at 02:24:54PM -0400, Bruce Momjian wrote:
> On Sat, Aug 17, 2024 at 01:22:18PM -0400, Tom Lane wrote:
> > I wrote:
> > > Bruce Momjian <[email protected]> writes:
> > >> About 20 minutes ago I starting getting gitmaster pull errors:
> > >> Can anyone confirm or report on the cause?
> >
> > > Yeah, for me it's hanging up at the connection establishment
> > > stage ("ssh -fMN gitmaster.postgresql.org"). Traceroute gets
> > > as far as some rackspace.net machines, but gemulon itself
> > > isn't answering.
> >
> > ssh and git pull are working again now for me.
>
> ssh and git pull are still failing for me to gitmaster.postgresql.org.
Joe Conway, CC'ed, is aware of the issue and is now working on it.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: gitmaster server problem?
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 17:22 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 18:24 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 18:53 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
@ 2024-08-17 19:04 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2024-08-17 19:04 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]; Joe Conway <[email protected]>
On Sat, Aug 17, 2024 at 02:53:44PM -0400, Bruce Momjian wrote:
> On Sat, Aug 17, 2024 at 02:24:54PM -0400, Bruce Momjian wrote:
> > On Sat, Aug 17, 2024 at 01:22:18PM -0400, Tom Lane wrote:
> > > I wrote:
> > > > Bruce Momjian <[email protected]> writes:
> > > >> About 20 minutes ago I starting getting gitmaster pull errors:
> > > >> Can anyone confirm or report on the cause?
> > >
> > > > Yeah, for me it's hanging up at the connection establishment
> > > > stage ("ssh -fMN gitmaster.postgresql.org"). Traceroute gets
> > > > as far as some rackspace.net machines, but gemulon itself
> > > > isn't answering.
> > >
> > > ssh and git pull are working again now for me.
> >
> > ssh and git pull are still failing for me to gitmaster.postgresql.org.
>
> Joe Conway, CC'ed, is aware of the issue and is now working on it.
Joe reports that everything should be working normally now.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-08-17 19:04 UTC | newest]
Thread overview: 7+ 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]>
2024-08-17 16:41 gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 16:49 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 17:22 ` Re: gitmaster server problem? Tom Lane <[email protected]>
2024-08-17 18:24 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 18:53 ` Re: gitmaster server problem? Bruce Momjian <[email protected]>
2024-08-17 19:04 ` Re: gitmaster server problem? Bruce Momjian <[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