public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Heikki Linnakangas <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)
Date: Thu, 8 Jun 2023 14:03:47 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Here's a rebased version of this patch series, no other changes.
On 2/25/23 12:45, Tomas Vondra wrote:
> On 2/24/23 22:07, Heikki Linnakangas wrote:
>> I had a quick look at just the preliminary cleanup patches:
>>
>>> 0001-BRIN-bloom-cleanup-20230218.patch
>>
>> Looks good to me
>>
>>> 0002-BRIN-minmax-multi-cleanup-20230218.patch
>>
>> Looks good, although it would feel more natural to me to do it the other
>> way round, and define 'matches' as 'bool matches', and use DatumGetBool.
>>
>
> Yeah, probably. I was trying to only do the minimal change because of
> (maybe) backpatching this.
>
I haven't changed this.
Heikki, do you think these cleanup parts should be backpatched? If yes,
do you still think it should be reworked to do it the other way, or like
I did it do minimize the change?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-BRIN-bloom-cleanup-20230608.patch (1.9K, ../[email protected]/2-0001-BRIN-bloom-cleanup-20230608.patch)
download | inline diff:
From 6cb2801ce6b1273555a8ac930a27631a242a4c45 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 15 Feb 2023 12:39:23 +0100
Subject: [PATCH 1/9] BRIN bloom cleanup
Minor cleanup of the BRIN bloom code - use the proper data type for the
boolean variable, and correct comment copied from minmax.
---
src/backend/access/brin/brin_bloom.c | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e4953a9d37b..6c716924fff 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -574,7 +574,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
Oid colloid = PG_GET_COLLATION();
AttrNumber attno;
Datum value;
- Datum matches;
+ bool matches;
FmgrInfo *finfo;
uint32 hashValue;
BloomFilter *filter;
@@ -584,6 +584,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
Assert(filter);
+ /* assume all scan keys match */
matches = true;
for (keyno = 0; keyno < nkeys; keyno++)
@@ -601,9 +602,8 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
case BloomEqualStrategyNumber:
/*
- * In the equality case (WHERE col = someval), we want to
- * return the current page range if the minimum value in the
- * range <= scan key, and the maximum value >= scan key.
+ * We want to return the current page range if the bloom filter
+ * seems to contain the value.
*/
finfo = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH);
@@ -614,7 +614,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = false;
break;
}
@@ -622,7 +622,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
break;
}
- PG_RETURN_DATUM(matches);
+ PG_RETURN_BOOL(matches);
}
/*
--
2.40.1
[text/x-patch] 0002-BRIN-minmax-multi-cleanup-20230608.patch (2.2K, ../[email protected]/3-0002-BRIN-minmax-multi-cleanup-20230608.patch)
download | inline diff:
From 1ab707c1d13bd19e8748f67846ee78fab8e93a9e Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 15 Feb 2023 12:53:47 +0100
Subject: [PATCH 2/9] BRIN minmax-multi cleanup
When assigning to a Datum variable, use BoolGetDatum() consistently.
Simnplify the code by using PG_RETURN_BOOL().
---
src/backend/access/brin/brin_minmax_multi.c | 14 +++++++-------
1 file changed, 7 insertions(+), 7 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 8e4e6c2fc8a..1d665383b2c 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2627,7 +2627,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
FmgrInfo *cmpFn;
/* by default this range does not match */
- matches = false;
+ matches = BoolGetDatum(false);
/*
* Otherwise, need to compare the new value with
@@ -2655,7 +2655,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
* We haven't managed to eliminate this range, so
* consider it matching.
*/
- matches = true;
+ matches = BoolGetDatum(true);
break;
}
@@ -2670,7 +2670,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = BoolGetDatum(false);
break;
}
@@ -2686,7 +2686,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
* have we found a range matching all scan keys? if yes, we're done
*/
if (matching)
- PG_RETURN_DATUM(BoolGetDatum(true));
+ PG_RETURN_BOOL(true);
}
/*
@@ -2729,7 +2729,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = BoolGetDatum(false);
break;
}
@@ -2743,10 +2743,10 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
/* have we found a range matching all scan keys? if yes, we're done */
if (matching)
- PG_RETURN_DATUM(BoolGetDatum(true));
+ PG_RETURN_BOOL(true);
}
- PG_RETURN_DATUM(BoolGetDatum(false));
+ PG_RETURN_BOOL(false);
}
/*
--
2.40.1
[text/x-patch] 0003-Introduce-bloom_filter_size-20230608.patch (3.5K, ../[email protected]/4-0003-Introduce-bloom_filter_size-20230608.patch)
download | inline diff:
From 778bfa79f06f8b6fd6923b6e149cd64648771eb4 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Tue, 14 Feb 2023 20:28:08 +0100
Subject: [PATCH 3/9] Introduce bloom_filter_size
Wrap calculation of Bloom filter parameters (from ndistinct and false
positive rate) into a function. We'll need to do this calculation in
other places, and this makes it more consistent.
---
src/backend/access/brin/brin_bloom.c | 63 +++++++++++++++++++++-------
1 file changed, 47 insertions(+), 16 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index 6c716924fff..4ff80aeb0cc 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -259,6 +259,48 @@ typedef struct BloomFilter
char data[FLEXIBLE_ARRAY_MEMBER];
} BloomFilter;
+/*
+ * bloom_filter_size
+ * Calculate Bloom filter parameters (nbits, nbytes, nhashes).
+ *
+ * Given expected number of distinct values and desired false positive rate,
+ * calculates the optimal parameters of the Bloom filter.
+ *
+ * The resulting parameters are returned through nbytesp (number of bytes),
+ * nbitsp (number of bits) and nhashesp (number of hash functions). If a
+ * pointer is NULL, the parameter is not returned.
+ */
+static void
+bloom_filter_size(int ndistinct, double false_positive_rate,
+ int *nbytesp, int *nbitsp, int *nhashesp)
+{
+ double k;
+ int nbits,
+ nbytes;
+
+ /* sizing bloom filter: -(n * ln(p)) / (ln(2))^2 */
+ nbits = ceil(-(ndistinct * log(false_positive_rate)) / pow(log(2.0), 2));
+
+ /* round m to whole bytes */
+ nbytes = ((nbits + 7) / 8);
+ nbits = nbytes * 8;
+
+ /*
+ * round(log(2.0) * m / ndistinct), but assume round() may not be
+ * available on Windows
+ */
+ k = log(2.0) * nbits / ndistinct;
+ k = (k - floor(k) >= 0.5) ? ceil(k) : floor(k);
+
+ if (nbytesp)
+ *nbytesp = nbytes;
+
+ if (nbitsp)
+ *nbitsp = nbits;
+
+ if (nhashesp)
+ *nhashesp = (int) k;
+}
/*
* bloom_init
@@ -275,19 +317,15 @@ bloom_init(int ndistinct, double false_positive_rate)
int nbits; /* size of filter / number of bits */
int nbytes; /* size of filter / number of bytes */
-
- double k; /* number of hash functions */
+ int nhashes; /* number of hash functions */
Assert(ndistinct > 0);
Assert((false_positive_rate >= BLOOM_MIN_FALSE_POSITIVE_RATE) &&
(false_positive_rate < BLOOM_MAX_FALSE_POSITIVE_RATE));
- /* sizing bloom filter: -(n * ln(p)) / (ln(2))^2 */
- nbits = ceil(-(ndistinct * log(false_positive_rate)) / pow(log(2.0), 2));
-
- /* round m to whole bytes */
- nbytes = ((nbits + 7) / 8);
- nbits = nbytes * 8;
+ /* calculate bloom filter size / parameters */
+ bloom_filter_size(ndistinct, false_positive_rate,
+ &nbytes, &nbits, &nhashes);
/*
* Reject filters that are obviously too large to store on a page.
@@ -310,13 +348,6 @@ bloom_init(int ndistinct, double false_positive_rate)
elog(ERROR, "the bloom filter is too large (%d > %zu)", nbytes,
BloomMaxFilterSize);
- /*
- * round(log(2.0) * m / ndistinct), but assume round() may not be
- * available on Windows
- */
- k = log(2.0) * nbits / ndistinct;
- k = (k - floor(k) >= 0.5) ? ceil(k) : floor(k);
-
/*
* We allocate the whole filter. Most of it is going to be 0 bits, so the
* varlena is easy to compress.
@@ -326,7 +357,7 @@ bloom_init(int ndistinct, double false_positive_rate)
filter = (BloomFilter *) palloc0(len);
filter->flags = 0;
- filter->nhashes = (int) k;
+ filter->nhashes = nhashes;
filter->nbits = nbits;
SET_VARSIZE(filter, len);
--
2.40.1
[text/x-patch] 0004-Add-minmax-multi-inequality-tests-20230608.patch (16.8K, ../[email protected]/5-0004-Add-minmax-multi-inequality-tests-20230608.patch)
download | inline diff:
From 02df65387eea6fe617460900da62b5c5491d04f1 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 17 Feb 2023 02:25:52 +0100
Subject: [PATCH 4/9] Add minmax-multi inequality tests
Add tests exercising the other scan key strategies, to improve test
coverage.
---
src/test/regress/expected/brin_multi.out | 363 +++++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 136 +++++++++
2 files changed, 499 insertions(+)
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index 861a06ef8ca..33c4566d7a1 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -466,3 +466,366 @@ EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1;
Filter: (b = 1)
(2 rows)
+-- do some inequality tests
+CREATE TABLE brin_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_test_multi_1
+SELECT i/5 + mod(911 * i + 483, 25),
+ i/10 + mod(751 * i + 221, 41)
+ FROM generate_series(1,1000) s(i);
+CREATE INDEX brin_test_multi_1_idx_1 ON brin_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=5);
+CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=5);
+SET enable_seqscan=off;
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a < 37)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a < 37)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37;
+ count
+-------
+ 124
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a < 113)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a < 113)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113;
+ count
+-------
+ 504
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a <= 177)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a <= 177)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177;
+ count
+-------
+ 829
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a <= 25)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a <= 25)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25;
+ count
+-------
+ 69
+(1 row)
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a > 120)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a > 120)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120;
+ count
+-------
+ 456
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a >= 180)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a >= 180)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180;
+ count
+-------
+ 161
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a > 71)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a > 71)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71;
+ count
+-------
+ 701
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (a >= 63)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_1
+ Index Cond: (a >= 63)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63;
+ count
+-------
+ 746
+(1 row)
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b < 73)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b < 73)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73;
+ count
+-------
+ 529
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b <= 47)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b <= 47)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47;
+ count
+-------
+ 279
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b < 199)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b < 199)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199;
+ count
+-------
+ 1000
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b <= 150)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b <= 150)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150;
+ count
+-------
+ 1000
+(1 row)
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b > 93)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b > 93)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93;
+ count
+-------
+ 261
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b > 37)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b > 37)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37;
+ count
+-------
+ 821
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b >= 215)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b >= 215)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215;
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201;
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_1
+ Recheck Cond: (b > 201)
+ -> Bitmap Index Scan on brin_test_multi_1_idx_2
+ Index Cond: (b > 201)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201;
+ count
+-------
+ 0
+(1 row)
+
+DROP TABLE brin_test_multi_1;
+RESET enable_seqscan;
+-- do some inequality tests for varlena data types
+CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
+INSERT INTO brin_test_multi_2
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_2
+ Recheck Cond: (a < '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+ -> Bitmap Index Scan on brin_test_multi_2_idx
+ Index Cond: (a < '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';
+ count
+-------
+ 195
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_2
+ Recheck Cond: (a > '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+ -> Bitmap Index Scan on brin_test_multi_2_idx
+ Index Cond: (a > '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';
+ count
+-------
+ 792
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_2
+ Recheck Cond: (a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'::uuid)
+ -> Bitmap Index Scan on brin_test_multi_2_idx
+ Index Cond: (a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'::uuid)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';
+ count
+-------
+ 961
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_test_multi_2
+ Recheck Cond: (a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'::uuid)
+ -> Bitmap Index Scan on brin_test_multi_2_idx
+ Index Cond: (a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'::uuid)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';
+ count
+-------
+ 272
+(1 row)
+
+DROP TABLE brin_test_multi_2;
+RESET enable_seqscan;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 070455257c0..68ba94121e3 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -421,3 +421,139 @@ VACUUM ANALYZE brin_test_multi;
EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE a = 1;
-- Ensure brin index is not used when values are not correlated
EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1;
+
+
+-- do some inequality tests
+CREATE TABLE brin_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_test_multi_1
+SELECT i/5 + mod(911 * i + 483, 25),
+ i/10 + mod(751 * i + 221, 41)
+ FROM generate_series(1,1000) s(i);
+
+CREATE INDEX brin_test_multi_1_idx_1 ON brin_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=5);
+CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=5);
+
+SET enable_seqscan=off;
+
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25;
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63;
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150;
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201;
+
+SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201;
+
+DROP TABLE brin_test_multi_1;
+RESET enable_seqscan;
+
+
+-- do some inequality tests for varlena data types
+CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
+INSERT INTO brin_test_multi_2
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+
+CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189';
+
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189';
+
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0';
+
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';
+
+SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39';
+
+DROP TABLE brin_test_multi_2;
+RESET enable_seqscan;
--
2.40.1
[text/x-patch] 0005-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230608.patch (5.2K, ../[email protected]/6-0005-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230608.patch)
download | inline diff:
From c96ee725fd6a30a92800af8fb05b487549f2f386 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Tue, 14 Feb 2023 20:29:33 +0100
Subject: [PATCH 5/9] Introduce BRIN_PROCNUM_PREPROCESS procedure
Allow BRIN opclasses to define an optional procedure to preprocess scan
keys, and call it from brinrescan(). This allows the opclass to modify
the keys in various ways - sort arrays, calculate hashes, ...
Note: The procedure is optional, so existing opclasses don't need to add
it. But if it uses the existing BRIN_PROCNUM_CONSISTENT function, it'll
get broken. If we want to make this backwards-compatible, we might check
if BRIN_PROCNUM_PREPROCESS exist from BRIN_PROCNUM_CONSISTENT, and
adjust behavior based on that.
---
src/backend/access/brin/brin.c | 86 +++++++++++++++++++++++++++---
src/include/access/brin_internal.h | 1 +
2 files changed, 79 insertions(+), 8 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 3c6a956eaa3..f7fa9f3f06b 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -66,6 +66,12 @@ typedef struct BrinOpaque
BlockNumber bo_pagesPerRange;
BrinRevmap *bo_rmAccess;
BrinDesc *bo_bdesc;
+
+ /* preprocessed scan keys */
+ int bo_numScanKeys; /* number of (preprocessed) scan keys */
+ ScanKey *bo_scanKeys; /* modified copy of scan->keyData */
+ MemoryContext bo_scanKeysCxt; /* scan-lifespan context for key data */
+
} BrinOpaque;
#define BRIN_ALL_BLOCKRANGES InvalidBlockNumber
@@ -335,6 +341,11 @@ brinbeginscan(Relation r, int nkeys, int norderbys)
opaque->bo_rmAccess = brinRevmapInitialize(r, &opaque->bo_pagesPerRange,
scan->xs_snapshot);
opaque->bo_bdesc = brin_build_desc(r);
+
+ opaque->bo_numScanKeys = 0;
+ opaque->bo_scanKeys = NULL;
+ opaque->bo_scanKeysCxt = NULL;
+
scan->opaque = opaque;
return scan;
@@ -457,7 +468,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
/* Preprocess the scan keys - split them into per-attribute arrays. */
for (int keyno = 0; keyno < scan->numberOfKeys; keyno++)
{
- ScanKey key = &scan->keyData[keyno];
+ ScanKey key = opaque->bo_scanKeys[keyno];
AttrNumber keyattno = key->sk_attno;
/*
@@ -747,17 +758,76 @@ void
brinrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
ScanKey orderbys, int norderbys)
{
- /*
- * Other index AMs preprocess the scan keys at this point, or sometime
- * early during the scan; this lets them optimize by removing redundant
- * keys, or doing early returns when they are impossible to satisfy; see
- * _bt_preprocess_keys for an example. Something like that could be added
- * here someday, too.
- */
+ BrinOpaque *bo = (BrinOpaque *) scan->opaque;
+ Relation idxRel = scan->indexRelation;
+ MemoryContext oldcxt;
if (scankey && scan->numberOfKeys > 0)
memmove(scan->keyData, scankey,
scan->numberOfKeys * sizeof(ScanKeyData));
+
+ /*
+ * Use the BRIN_PROCNUM_PREPROCESS procedure (if defined) to preprocess
+ * the scan keys. The procedure may do anything, as long as the result
+ * looks like a ScanKey. If there's no procedure, we keep the original
+ * scan key.
+ *
+ * FIXME Probably need fixes to handle NULLs correctly.
+ */
+ if (bo->bo_scanKeysCxt == NULL)
+ bo->bo_scanKeysCxt = AllocSetContextCreate(CurrentMemoryContext,
+ "BRIN scan keys context",
+ ALLOCSET_SMALL_SIZES);
+ else
+ MemoryContextReset(bo->bo_scanKeysCxt);
+
+ oldcxt = MemoryContextSwitchTo(bo->bo_scanKeysCxt);
+
+ bo->bo_scanKeys = palloc0(sizeof(ScanKey) * nscankeys);
+
+ for (int i = 0; i < nscankeys; i++)
+ {
+ FmgrInfo *finfo;
+ ScanKey key = &scan->keyData[i];
+ Oid procid;
+ Datum ret;
+
+ /*
+ * If the scan argument is NULL, nothing to preprocess.
+ *
+ * XXX Maybe we should leave these checks up to the _preprocess
+ * procedures, in case there's something smart they wan to do?
+ * But SK_ISNULL is handled by bringetbitmap() so doing it here
+ * seems reasonable.
+ */
+ if (key->sk_flags & SK_ISNULL)
+ {
+ bo->bo_scanKeys[i] = key;
+ continue;
+ }
+
+ /* fetch key preprocess support procedure if specified */
+ procid = index_getprocid(idxRel, key->sk_attno,
+ BRIN_PROCNUM_PREPROCESS);
+
+ /* not specified, just point to the original key */
+ if (!OidIsValid(procid))
+ {
+ bo->bo_scanKeys[i] = key;
+ continue;
+ }
+
+ finfo = index_getprocinfo(idxRel, key->sk_attno,
+ BRIN_PROCNUM_PREPROCESS);
+
+ ret = FunctionCall2(finfo,
+ PointerGetDatum(bo->bo_bdesc),
+ PointerGetDatum(key));
+
+ bo->bo_scanKeys[i] = (ScanKey) DatumGetPointer(ret);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
}
/*
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index 97ddc925b27..d6a51f2bc49 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -73,6 +73,7 @@ typedef struct BrinDesc
#define BRIN_PROCNUM_UNION 4
#define BRIN_MANDATORY_NPROCS 4
#define BRIN_PROCNUM_OPTIONS 5 /* optional */
+#define BRIN_PROCNUM_PREPROCESS 6 /* optional */
/* procedure numbers up to 10 are reserved for BRIN future expansion */
#define BRIN_FIRST_OPTIONAL_PROCNUM 11
#define BRIN_LAST_OPTIONAL_PROCNUM 15
--
2.40.1
[text/x-patch] 0006-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230608.patch (69.5K, ../[email protected]/7-0006-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230608.patch)
download | inline diff:
From c93ca9a8377780833b480ad30ebffdf3249598d2 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 10 Feb 2023 16:07:57 +0100
Subject: [PATCH 6/9] Support SK_SEARCHARRAY in BRIN minmax
Set "amsearcharray=true" for BRIN, and extend the minmax opclass to
handle both scalar values and arrays. This allows handling conditions
... WHERE a IN (1, 2, 43, 2132, 134)
... WHERE a = ANY(ARRAY[34, 45, -1, 234])
... WHERE a <= ANY(ARRAY[4938, 282, 2934])
more efficiently - until now we simply built the bitmap for each
scalar value, so we walked the BRIN index many times. Which may be quite
expensive for indexes with many ranges (large tables and/or low
pages_per_range).
There's a couple problems / open questions / TODO items:
- The other opclasses don't handle SK_SEARCHARRAY yet.
- The array is always searched linearly, so this may be costly for large
arrays (with many elements).
- The array is deconstructed again for each range. We should reuse this,
somehow.
---
src/backend/access/brin/brin.c | 3 +-
src/backend/access/brin/brin_minmax.c | 375 +++++++++--
src/backend/access/brin/brin_validate.c | 4 +
src/include/catalog/pg_amproc.dat | 64 ++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/amutils.out | 2 +-
src/test/regress/expected/brin.out | 858 ++++++++++++++++++++++++
src/test/regress/sql/brin.sql | 283 ++++++++
8 files changed, 1552 insertions(+), 40 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index f7fa9f3f06b..6b6cb2524f0 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -38,6 +38,7 @@
#include "utils/datum.h"
#include "utils/guc.h"
#include "utils/index_selfuncs.h"
+#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -107,7 +108,7 @@ brinhandler(PG_FUNCTION_ARGS)
amroutine->amcanunique = false;
amroutine->amcanmulticol = true;
amroutine->amoptionalkey = true;
- amroutine->amsearcharray = false;
+ amroutine->amsearcharray = true;
amroutine->amsearchnulls = true;
amroutine->amstorage = true;
amroutine->amclusterable = false;
diff --git a/src/backend/access/brin/brin_minmax.c b/src/backend/access/brin/brin_minmax.c
index 8229493c84a..ba814f91c27 100644
--- a/src/backend/access/brin/brin_minmax.c
+++ b/src/backend/access/brin/brin_minmax.c
@@ -16,11 +16,21 @@
#include "access/stratnum.h"
#include "catalog/pg_amop.h"
#include "catalog/pg_type.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
+#include "utils/sortsupport.h"
+
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */
+
typedef struct MinmaxOpaque
{
@@ -126,6 +136,158 @@ brin_minmax_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+
+static int
+compare_array_values(const void *a, const void *b, void *arg)
+{
+ Datum da = * (Datum *) a;
+ Datum db = * (Datum *) b;
+ SortSupport ssup = (SortSupport) arg;
+
+ return ApplySortComparator(da, false, db, false, ssup);
+}
+
+/*
+ * lower_boundary
+ * Determine lowest index so that (values[index] >= minvalue).
+ *
+ * The array of values is expected to be sorted, so this is the first value
+ * that may fall into the [minvalue, maxvalue] range, as it exceeds minval.
+ * It's not guaranteed, though, as it might exceed maxvalue too.
+ */
+static int
+lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (compare_array_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (compare_array_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = compare_array_values(&minvalue, &values[midpoint], ssup);
+
+ if (r > 0)
+ start = Max(midpoint, start + 1);
+ else
+ end = midpoint;
+ }
+
+ /* the value should meet the (v >=minvalue) requirement */
+ Assert(compare_array_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(compare_array_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_minmax_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ /* number of non-null elements in the array */
+ int num_nonnulls;
+
+ /*
+ * ignore scalar keys
+ *
+ * XXX Maybe we should preprocess scalar keys too. It'd make the consistent
+ * function simpler by removing the branching.
+ */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ /* eliminate NULL elements */
+ num_nonnulls = 0;
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ /* if needed, move the non-NULL ones */
+ if (num_nonnulls != i)
+ elem_values[num_nonnulls] = elem_values[i];
+
+ num_nonnulls++;
+ }
+
+ num_elems = num_nonnulls;
+
+ type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ qsort_interruptible(elem_values, num_elems, sizeof(Datum),
+ compare_array_values, &ssup);
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->typeid = ARR_ELEMTYPE(arrayval);
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_SORTED),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's min/max
@@ -157,46 +319,183 @@ brin_minmax_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+
+ /*
+ * For regular (scalar) scan keys, we simply compare the value to the
+ * range min/max values, and we're done. For preprocessed SK_SEARCHARRAY
+ * keys we need to loop through the deparsed values.
+ */
+ if (likely(!(key->sk_flags & SK_BRIN_SORTED)))
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
- value);
- break;
- case BTEqualStrategyNumber:
-
- /*
- * In the equality case (WHERE col = someval), we want to return
- * the current page range if the minimum value in the range <=
- * scan key, and the maximum value >= scan key.
- */
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- BTLessEqualStrategyNumber);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
- value);
- if (!DatumGetBool(matches))
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ value);
+ break;
+ case BTEqualStrategyNumber:
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ value);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ value);
+ break;
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ value);
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 0;
+ break;
+ }
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) value;
+
+ /* can happen if the IN list contained just NULLs */
+ if (array->nelements == 0)
+ PG_RETURN_BOOL(false);
+
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ /*
+ * Check the last (largest) value in the array - at least this
+ * value has to exceed the range minval.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ array->elements[array->nelements-1]);
+ break;
+ case BTEqualStrategyNumber:
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ *
+ * We do this in two phases. We check the array min/max values to see
+ * if there even can be a matching value, and if yes we do a binary
+ * search to find the first value that exceeds range minval. And then
+ * we check if it actually matches the range.
+ *
+ * XXX The first phase is probably unnecessary, because lower_bound()
+ * does pretty much exactly that too.
+ */
+ {
+ Datum val;
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ /* Is the first (smallest) value after the BRIN range? */
+ val = array->elements[0];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[1]);
+
+ /* minval > max(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /* Is the last (largest) value before the BRIN range? */
+ val = array->elements[array->nelements-1];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[0]);
+
+ /* maxval < min(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /*
+ * OK, there might be some values matching the range. We have
+ * to search them one by one, or perhaps try binsearch.
+ */
+ type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(array->elements, array->nelements, column->bv_values[0], &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * OK, the first element must match the upper boundary too
+ * (if it does not, no following elements can).
+ */
+ val = array->elements[lower];
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ val);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ val);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ /*
+ * Check the first (smallest) value in the array - at least this
+ * value has to be smaller than the range maxval.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ array->elements[0]);
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 0;
break;
- /* max() >= scankey */
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- BTGreaterEqualStrategyNumber);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
- value);
- break;
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
- value);
- break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
- break;
+ }
}
PG_RETURN_DATUM(matches);
diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c
index c8edfb37591..0889e24bc01 100644
--- a/src/backend/access/brin/brin_validate.c
+++ b/src/backend/access/brin/brin_validate.c
@@ -108,6 +108,10 @@ brinvalidate(Oid opclassoid)
case BRIN_PROCNUM_OPTIONS:
ok = check_amoptsproc_signature(procform->amproc);
break;
+ case BRIN_PROCNUM_PREPROCESS:
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+ 2, 2, INTERNALOID, INTERNALOID);
+ break;
default:
/* Complain if it's not a valid optional proc number */
if (procform->amprocnum < BRIN_FIRST_OPTIONAL_PROCNUM ||
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 5b950129de0..166681c31ef 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -804,6 +804,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea',
amprocrighttype => 'bytea', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea',
+ amprocrighttype => 'bytea', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# bloom bytea
{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea',
@@ -835,6 +837,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char',
amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char',
+ amprocrighttype => 'char', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# bloom "char"
{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char',
@@ -864,6 +868,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name',
amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name',
+ amprocrighttype => 'name', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# bloom name
{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name',
@@ -893,6 +899,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8',
amprocrighttype => 'int8', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8',
+ amprocrighttype => 'int8', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '1',
@@ -905,6 +913,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2',
+ amprocrighttype => 'int2', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '1',
@@ -917,6 +927,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4',
+ amprocrighttype => 'int4', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi integer: int2, int4, int8
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2',
@@ -1034,6 +1046,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text',
amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text',
+ amprocrighttype => 'text', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# bloom text
{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text',
@@ -1062,6 +1076,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid',
amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid',
+ amprocrighttype => 'oid', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi oid
{ amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid',
@@ -1110,6 +1126,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid',
amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid',
+ amprocrighttype => 'tid', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# bloom tid
{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid',
@@ -1160,6 +1178,9 @@
{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4',
amprocrighttype => 'float4', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4',
+ amprocrighttype => 'float4', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '1',
@@ -1173,6 +1194,9 @@
{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8',
+ amprocrighttype => 'float8', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi float
{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4',
@@ -1261,6 +1285,9 @@
{ amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr',
amprocrighttype => 'macaddr', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr',
+ amprocrighttype => 'macaddr', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi macaddr
{ amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr',
@@ -1314,6 +1341,9 @@
{ amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8',
amprocrighttype => 'macaddr8', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8',
+ amprocrighttype => 'macaddr8', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi macaddr8
{ amprocfamily => 'brin/macaddr8_minmax_multi_ops',
@@ -1366,6 +1396,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet',
+ amprocrighttype => 'inet', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi inet
{ amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet',
@@ -1436,6 +1468,9 @@
{ amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar',
amprocrighttype => 'bpchar', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar',
+ amprocrighttype => 'bpchar', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# bloom character
{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar',
@@ -1467,6 +1502,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time',
amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time',
+ amprocrighttype => 'time', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi time without time zone
{ amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time',
@@ -1517,6 +1554,9 @@
{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp',
amprocrighttype => 'timestamp', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp',
+ amprocrighttype => 'timestamp', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz',
amprocrighttype => 'timestamptz', amprocnum => '1',
@@ -1530,6 +1570,9 @@
{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz',
amprocrighttype => 'timestamptz', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz',
+ amprocrighttype => 'timestamptz', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '1',
@@ -1542,6 +1585,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date',
+ amprocrighttype => 'date', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi datetime (date, timestamp, timestamptz)
{ amprocfamily => 'brin/datetime_minmax_multi_ops',
@@ -1668,6 +1713,9 @@
{ amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval',
amprocrighttype => 'interval', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval',
+ amprocrighttype => 'interval', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi interval
{ amprocfamily => 'brin/interval_minmax_multi_ops',
@@ -1721,6 +1769,9 @@
{ amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz',
amprocrighttype => 'timetz', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz',
+ amprocrighttype => 'timetz', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi time with time zone
{ amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz',
@@ -1771,6 +1822,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit',
amprocrighttype => 'bit', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit',
+ amprocrighttype => 'bit', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax bit varying
{ amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit',
@@ -1785,6 +1838,9 @@
{ amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit',
amprocrighttype => 'varbit', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit',
+ amprocrighttype => 'varbit', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax numeric
{ amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric',
@@ -1799,6 +1855,9 @@
{ amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric',
amprocrighttype => 'numeric', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric',
+ amprocrighttype => 'numeric', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi numeric
{ amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric',
@@ -1851,6 +1910,8 @@
amproc => 'brin_minmax_consistent' },
{ amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid',
amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid',
+ amprocrighttype => 'uuid', amprocnum => '6', amproc => 'brin_minmax_preprocess' },
# minmax multi uuid
{ amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid',
@@ -1924,6 +1985,9 @@
{ amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn',
amprocrighttype => 'pg_lsn', amprocnum => '4',
amproc => 'brin_minmax_union' },
+{ amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn',
+ amprocrighttype => 'pg_lsn', amprocnum => '6',
+ amproc => 'brin_minmax_preprocess' },
# minmax multi pg_lsn
{ amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..8f17532094b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8527,6 +8527,9 @@
{ oid => '3386', descr => 'BRIN minmax support',
proname => 'brin_minmax_union', prorettype => 'bool',
proargtypes => 'internal internal internal', prosrc => 'brin_minmax_union' },
+{ oid => '9327', descr => 'BRIN minmax support',
+ proname => 'brin_minmax_preprocess', prorettype => 'internal',
+ proargtypes => 'internal internal', prosrc => 'brin_minmax_preprocess' },
# BRIN minmax multi
{ oid => '4616', descr => 'BRIN multi minmax support',
diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
index 7ab6113c619..f3e1fbd2ae3 100644
--- a/src/test/regress/expected/amutils.out
+++ b/src/test/regress/expected/amutils.out
@@ -102,7 +102,7 @@ select prop,
orderable | t | f | f | f | f | f | f
distance_orderable | f | f | t | f | t | f | f
returnable | t | f | f | t | t | f | f
- search_array | t | f | f | f | f | f | f
+ search_array | t | f | f | f | f | f | t
search_nulls | t | f | t | t | t | f | t
bogus | | | | | | |
(10 rows)
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index f0b7de5bf76..24361b90eb2 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -572,3 +572,861 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange);
CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n);
INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric));
DROP TABLE brintest_unlogged;
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+-- int: equalities
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113);
+ QUERY PLAN
+-------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = 113)
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = 113)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{113,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{113,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{113,177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{113,177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]);
+ QUERY PLAN
+---------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a < ANY ('{30}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a < ANY ('{30}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]);
+ count
+-------
+ 103
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a < ANY ('{20,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a < ANY ('{20,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]);
+ count
+-------
+ 51
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a < ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a < ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]);
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a < ANY ('{35,29}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a < ANY ('{35,29}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]);
+ count
+-------
+ 127
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a <= ANY ('{45,60,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a <= ANY ('{45,60,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]);
+ count
+-------
+ 255
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a < ANY ('{41,37,55}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a < ANY ('{41,37,55}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]);
+ count
+-------
+ 227
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]);
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a <= ANY ('{NULL,60,43,94}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a <= ANY ('{NULL,60,43,94}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]);
+ count
+-------
+ 427
+(1 row)
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]);
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a > ANY ('{200}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a > ANY ('{200}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]);
+ count
+-------
+ 45
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a > ANY ('{177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a > ANY ('{177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]);
+ count
+-------
+ 157
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a > ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a > ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a > ANY ('{153,140}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a > ANY ('{153,140}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]);
+ count
+-------
+ 345
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a >= ANY ('{173,191,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a >= ANY ('{173,191,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]);
+ count
+-------
+ 185
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a > ANY ('{120,184,164}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a > ANY ('{120,184,164}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]);
+ count
+-------
+ 445
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]);
+ QUERY PLAN
+------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (a >= ANY ('{NULL,130,181,169}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_1
+ Index Cond: (a >= ANY ('{NULL,130,181,169}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]);
+ count
+-------
+ 397
+(1 row)
+
+-- bigint: eqalities
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82);
+ QUERY PLAN
+-------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = 82)
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = 82)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{82,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{82,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41);
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{82,41}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{82,41}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]);
+ QUERY PLAN
+---------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b < ANY ('{31}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b < ANY ('{31}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]);
+ count
+-------
+ 164
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b < ANY ('{55,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b < ANY ('{55,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]);
+ count
+-------
+ 404
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b < ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b < ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b <= ANY ('{73,51}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b <= ANY ('{73,51}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]);
+ count
+-------
+ 594
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b < ANY ('{69,87,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b < ANY ('{69,87,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]);
+ count
+-------
+ 724
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b <= ANY ('{82,91,35}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b <= ANY ('{82,91,35}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]);
+ count
+-------
+ 774
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b < ANY ('{NULL,63,21,85}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b < ANY ('{NULL,63,21,85}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]);
+ count
+-------
+ 704
+(1 row)
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]);
+ QUERY PLAN
+---------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{94}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{94}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]);
+ count
+-------
+ 196
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{80,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{80,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]);
+ count
+-------
+ 336
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{199,107}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{199,107}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]);
+ count
+-------
+ 78
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b >= ANY ('{182,101,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b >= ANY ('{182,101,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]);
+ count
+-------
+ 137
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{300,106,251}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{300,106,251}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]);
+ count
+-------
+ 86
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]);
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_1
+ Recheck Cond: (b > ANY ('{NULL,182,101,155}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_1_idx_2
+ Index Cond: (b > ANY ('{NULL,182,101,155}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]);
+ count
+-------
+ 127
+(1 row)
+
+DROP TABLE brin_in_test_1;
+RESET enable_seqscan;
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_2
+SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = '33e75ff09dd601bbe69f351039152189'::text)
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = '33e75ff09dd601bbe69f351039152189'::text)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{NULL,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_2
+ Recheck Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_2_idx
+ Index Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+DROP TABLE brin_in_test_2;
+RESET enable_seqscan;
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 929a087a25d..7199c9108b0 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -515,3 +515,286 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange);
CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n);
INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric));
DROP TABLE brintest_unlogged;
+
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+
+CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+-- int: equalities
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25);
+
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]);
+
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]);
+
+
+-- bigint: eqalities
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15);
+
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]);
+
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]);
+
+SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]);
+
+
+DROP TABLE brin_in_test_1;
+RESET enable_seqscan;
+
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_2
+SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+
+CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+DROP TABLE brin_in_test_2;
+RESET enable_seqscan;
--
2.40.1
[text/x-patch] 0007-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230608.patch (75.1K, ../[email protected]/8-0007-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230608.patch)
download | inline diff:
From 09be16c4fbece2e4945eeeac9a5f36ae7b697588 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 17 Feb 2023 02:45:14 +0100
Subject: [PATCH 7/9] Support SK_SEARCHARRAY in BRIN minmax-multi
Similar approach to minmax, but the issues with deconstructing the array
over and over are even more serious.
---
src/backend/access/brin/brin_minmax_multi.c | 502 +++++++++--
src/include/catalog/pg_amproc.dat | 57 ++
src/include/catalog/pg_proc.dat | 4 +
src/test/regress/expected/brin_multi.out | 926 ++++++++++++++++++++
src/test/regress/sql/brin_multi.sql | 301 +++++++
5 files changed, 1725 insertions(+), 65 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 1d665383b2c..18b6db8bc96 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -109,6 +109,14 @@
#define MINMAX_BUFFER_MAX 8192
#define MINMAX_BUFFER_LOAD_FACTOR 0.5
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */
+
+
typedef struct MinmaxMultiOpaque
{
FmgrInfo extra_procinfos[MINMAX_MAX_PROCNUMS];
@@ -2562,6 +2570,157 @@ brin_minmax_multi_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(modified);
}
+
+static int
+compare_array_values(const void *a, const void *b, void *arg)
+{
+ Datum da = * (Datum *) a;
+ Datum db = * (Datum *) b;
+ SortSupport ssup = (SortSupport) arg;
+
+ return ApplySortComparator(da, false, db, false, ssup);
+}
+
+/*
+ * lower_boundary
+ * Determine lowest index so that (values[index] >= minvalue).
+ *
+ * The array of values is expected to be sorted, so this is the first value
+ * that may fall into the [minvalue, maxvalue] range, as it exceeds minval.
+ * It's not guaranteed, though, as it might exceed maxvalue too.
+ */
+static int
+lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (compare_array_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (compare_array_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = compare_array_values(&minvalue, &values[midpoint], ssup);
+
+ if (r > 0)
+ start = Max(midpoint, start + 1);
+ else
+ end = midpoint;
+ }
+
+ /* the value should meet the (v >=minvalue) requirement */
+ Assert(compare_array_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(compare_array_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_minmax_multi_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ /* number of non-null elements in the array */
+ int num_nonnulls;
+
+ /*
+ * ignore scalar keys
+ *
+ * XXX Maybe we should preprocess scalar keys too. It'd make the consistent
+ * function simpler by removing the branching.
+ */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ /* eliminate NULL elements */
+ num_nonnulls = 0;
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ /* if needed, move the non-NULL ones */
+ if (num_nonnulls != i)
+ elem_values[num_nonnulls] = elem_values[i];
+
+ num_nonnulls++;
+ }
+
+ num_elems = num_nonnulls;
+
+ type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ qsort_interruptible(elem_values, num_elems, sizeof(Datum),
+ compare_array_values, &ssup);
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->typeid = ARR_ELEMTYPE(arrayval);
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_SORTED),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's min/max
@@ -2591,6 +2750,15 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
serialized = (SerializedRanges *) PG_DETOAST_DATUM(column->bv_values[0]);
ranges = brin_range_deserialize(serialized->maxvalues, serialized);
+ /*
+ * XXX Would it make sense to have a quick initial check on the whole
+ * summary? We know most page ranges are not expected to match, and we
+ * know the ranges/values are sorted so we could check global min/max
+ * (essentially what regular minmax is doing) and bail if no match is
+ * possible. That should be cheap and might save a lot on inspecting
+ * the individual ranges/values.
+ */
+
/* inspect the ranges, and for each one evaluate the scan keys */
for (rangeno = 0; rangeno < ranges->nranges; rangeno++)
{
@@ -2611,67 +2779,183 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+
+ if (likely(!(key->sk_flags & SK_BRIN_SORTED)))
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* first value from the array */
- matches = FunctionCall2Coll(finfo, colloid, minval, value);
- break;
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* first value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, minval, value);
+ break;
- case BTEqualStrategyNumber:
- {
- Datum compar;
- FmgrInfo *cmpFn;
+ case BTEqualStrategyNumber:
+ {
+ Datum compar;
+ FmgrInfo *cmpFn;
+
+ /* by default this range does not match */
+ matches = BoolGetDatum(false);
+
+ /*
+ * Otherwise, need to compare the new value with
+ * boundaries of all the ranges. First check if it's
+ * less than the absolute minimum, which is the first
+ * value in the array.
+ */
+ cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterStrategyNumber);
+ compar = FunctionCall2Coll(cmpFn, colloid, minval, value);
+
+ /* smaller than the smallest value in this range */
+ if (DatumGetBool(compar))
+ break;
+
+ cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessStrategyNumber);
+ compar = FunctionCall2Coll(cmpFn, colloid, maxval, value);
+
+ /* larger than the largest value in this range */
+ if (DatumGetBool(compar))
+ break;
+
+ /*
+ * We haven't managed to eliminate this range, so
+ * consider it matching.
+ */
+ matches = BoolGetDatum(true);
- /* by default this range does not match */
- matches = BoolGetDatum(false);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, maxval, value);
+ break;
- /*
- * Otherwise, need to compare the new value with
- * boundaries of all the ranges. First check if it's
- * less than the absolute minimum, which is the first
- * value in the array.
- */
- cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- BTGreaterStrategyNumber);
- compar = FunctionCall2Coll(cmpFn, colloid, minval, value);
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) value;
- /* smaller than the smallest value in this range */
- if (DatumGetBool(compar))
- break;
+ /* can happen if the IN list contained just NULLs */
+ if (array->nelements == 0)
+ PG_RETURN_BOOL(false);
- cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- BTLessStrategyNumber);
- compar = FunctionCall2Coll(cmpFn, colloid, maxval, value);
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* first value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, minval,
+ array->elements[array->nelements-1]);
+ break;
- /* larger than the largest value in this range */
- if (DatumGetBool(compar))
- break;
+ case BTEqualStrategyNumber:
/*
- * We haven't managed to eliminate this range, so
- * consider it matching.
+ * See brin_minmax.c for description of what this is doing.
*/
- matches = BoolGetDatum(true);
-
+ {
+ Datum val;
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ /* Is the first (smallest) value after the BRIN range? */
+ val = array->elements[0];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, maxval);
+
+ /* minval > max(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /* Is the last (largest) value before the BRIN range? */
+ val = array->elements[array->nelements-1];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, minval);
+
+ /* maxval < min(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /*
+ * OK, there might be some values matching the range. We have
+ * to search them one by one, or perhaps try binsearch.
+ */
+ type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(array->elements, array->nelements, minval, &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * OK, the first element must match the upper boundary too
+ * (if it does not, no following elements can).
+ */
+ val = array->elements[lower];
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, minval, val);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, maxval, val);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, maxval,
+ array->elements[0]);
break;
- }
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* last value from the array */
- matches = FunctionCall2Coll(finfo, colloid, maxval, value);
- break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = BoolGetDatum(false);
- break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
}
/* the range has to match all the scan keys */
@@ -2713,24 +2997,112 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+ if (likely(!(key->sk_flags & SK_SEARCHARRAY)))
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- case BTEqualStrategyNumber:
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
-
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = FunctionCall2Coll(finfo, colloid, val, value);
- break;
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ case BTEqualStrategyNumber:
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, val, value);
+ break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = BoolGetDatum(false);
- break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) value;
+
+ /* can happen if the IN list contained just NULLs */
+ if (array->nelements == 0)
+ PG_RETURN_BOOL(false);
+
+ /*
+ * XXX We should be able to be smarter for the scalar values, as
+ * we keep them sorted too. So we should be able to quickly check
+ * if any of the values can match the sorted key values.
+ */
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* first value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, val,
+ array->elements[array->nelements-1]);
+ break;
+
+ case BTEqualStrategyNumber:
+
+ /*
+ * See brin_minmax.c for description of what this is doing.
+ */
+ {
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ /*
+ * OK, there might be some values matching the range. We have
+ * to search them one by one, or perhaps try binsearch.
+ */
+ type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(array->elements, array->nelements, val, &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * OK, check the first element must match the upper boundary too
+ * (if it does not, no following elements can).
+ *
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, array->elements[lower]);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, val,
+ array->elements[0]);
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
}
/* the range has to match all the scan keys */
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 166681c31ef..4f17f0d58c1 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -946,6 +946,9 @@
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2',
+ amprocrighttype => 'int2', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_int2' },
@@ -965,6 +968,9 @@
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4',
+ amprocrighttype => 'int4', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_int4' },
@@ -984,6 +990,9 @@
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8',
amprocrighttype => 'int8', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8',
+ amprocrighttype => 'int8', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8',
amprocrighttype => 'int8', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_int8' },
@@ -1095,6 +1104,9 @@
{ amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid',
amprocrighttype => 'oid', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid',
+ amprocrighttype => 'oid', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid',
amprocrighttype => 'oid', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_int4' },
@@ -1161,6 +1173,9 @@
{ amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid',
amprocrighttype => 'tid', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid',
+ amprocrighttype => 'tid', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid',
amprocrighttype => 'tid', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_tid' },
@@ -1214,6 +1229,9 @@
{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4',
amprocrighttype => 'float4', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4',
+ amprocrighttype => 'float4', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4',
amprocrighttype => 'float4', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_float4' },
@@ -1233,6 +1251,9 @@
{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8',
+ amprocrighttype => 'float8', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_float8' },
@@ -1305,6 +1326,9 @@
{ amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr',
amprocrighttype => 'macaddr', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr',
+ amprocrighttype => 'macaddr', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr',
amprocrighttype => 'macaddr', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_macaddr' },
@@ -1361,6 +1385,9 @@
{ amprocfamily => 'brin/macaddr8_minmax_multi_ops',
amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/macaddr8_minmax_multi_ops',
+ amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/macaddr8_minmax_multi_ops',
amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8',
amprocnum => '11', amproc => 'brin_minmax_multi_distance_macaddr8' },
@@ -1415,6 +1442,9 @@
{ amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet',
+ amprocrighttype => 'inet', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_inet' },
@@ -1521,6 +1551,9 @@
{ amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time',
amprocrighttype => 'time', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time',
+ amprocrighttype => 'time', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time',
amprocrighttype => 'time', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_time' },
@@ -1604,6 +1637,9 @@
{ amprocfamily => 'brin/datetime_minmax_multi_ops',
amproclefttype => 'timestamp', amprocrighttype => 'timestamp',
amprocnum => '5', amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/datetime_minmax_multi_ops',
+ amproclefttype => 'timestamp', amprocrighttype => 'timestamp',
+ amprocnum => '6', amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/datetime_minmax_multi_ops',
amproclefttype => 'timestamp', amprocrighttype => 'timestamp',
amprocnum => '11', amproc => 'brin_minmax_multi_distance_timestamp' },
@@ -1623,6 +1659,9 @@
{ amprocfamily => 'brin/datetime_minmax_multi_ops',
amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz',
amprocnum => '5', amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/datetime_minmax_multi_ops',
+ amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz',
+ amprocnum => '6', amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/datetime_minmax_multi_ops',
amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz',
amprocnum => '11', amproc => 'brin_minmax_multi_distance_timestamp' },
@@ -1642,6 +1681,9 @@
{ amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date',
+ amprocrighttype => 'date', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_date' },
@@ -1733,6 +1775,9 @@
{ amprocfamily => 'brin/interval_minmax_multi_ops',
amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/interval_minmax_multi_ops',
+ amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/interval_minmax_multi_ops',
amproclefttype => 'interval', amprocrighttype => 'interval',
amprocnum => '11', amproc => 'brin_minmax_multi_distance_interval' },
@@ -1789,6 +1834,9 @@
{ amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz',
amprocrighttype => 'timetz', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz',
+ amprocrighttype => 'timetz', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz',
amprocrighttype => 'timetz', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_timetz' },
@@ -1875,6 +1923,9 @@
{ amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric',
amprocrighttype => 'numeric', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric',
+ amprocrighttype => 'numeric', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric',
amprocrighttype => 'numeric', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_numeric' },
@@ -1929,6 +1980,9 @@
{ amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid',
amprocrighttype => 'uuid', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid',
+ amprocrighttype => 'uuid', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid',
amprocrighttype => 'uuid', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_uuid' },
@@ -2005,6 +2059,9 @@
{ amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn',
amprocrighttype => 'pg_lsn', amprocnum => '5',
amproc => 'brin_minmax_multi_options' },
+{ amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn',
+ amprocrighttype => 'pg_lsn', amprocnum => '6',
+ amproc => 'brin_minmax_multi_preprocess' },
{ amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn',
amprocrighttype => 'pg_lsn', amprocnum => '11',
amproc => 'brin_minmax_multi_distance_pg_lsn' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8f17532094b..04555746e60 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8551,6 +8551,10 @@
proname => 'brin_minmax_multi_options', proisstrict => 'f',
prorettype => 'void', proargtypes => 'internal',
prosrc => 'brin_minmax_multi_options' },
+{ oid => '9326', descr => 'BRIN multi minmax support',
+ proname => 'brin_minmax_multi_preprocess', proisstrict => 'f',
+ prorettype => 'internal', proargtypes => 'internal internal',
+ prosrc => 'brin_minmax_multi_preprocess' },
{ oid => '4621', descr => 'BRIN multi minmax int2 distance',
proname => 'brin_minmax_multi_distance_int2', prorettype => 'float8',
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index 33c4566d7a1..cbbcc30769d 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -829,3 +829,929 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97
DROP TABLE brin_test_multi_2;
RESET enable_seqscan;
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_multi_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+CREATE INDEX brin_in_test_multi_1_idx_1 ON brin_in_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_multi_1_idx_2 ON brin_in_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+-- int: equality
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = 113)
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = 113)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{113,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{113,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{113,177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{113,177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{-113,-177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{-113,-177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{313,377}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{313,377}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a < ANY ('{113}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a < ANY ('{113}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]);
+ count
+-------
+ 515
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a < ANY ('{113,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a < ANY ('{113,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]);
+ count
+-------
+ 515
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a < ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a < ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a <= ANY ('{113,177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a <= ANY ('{113,177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]);
+ count
+-------
+ 843
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a < ANY ('{113,177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a < ANY ('{113,177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]);
+ count
+-------
+ 835
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a <= ANY ('{113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a <= ANY ('{113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]);
+ count
+-------
+ 843
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a < ANY ('{NULL,113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a < ANY ('{NULL,113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]);
+ count
+-------
+ 835
+(1 row)
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a > ANY ('{113}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a > ANY ('{113}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]);
+ count
+-------
+ 477
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a > ANY ('{113,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a > ANY ('{113,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]);
+ count
+-------
+ 477
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a > ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a > ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a >= ANY ('{113,177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a >= ANY ('{113,177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]);
+ count
+-------
+ 485
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a > ANY ('{113,177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a > ANY ('{113,177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]);
+ count
+-------
+ 477
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a > ANY ('{113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a > ANY ('{113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]);
+ count
+-------
+ 917
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]);
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (a >= ANY ('{NULL,113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_1
+ Index Cond: (a >= ANY ('{NULL,113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]);
+ count
+-------
+ 925
+(1 row)
+
+-- bigint: equality
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = 82)
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = 82)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{82,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{82,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{82,41}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{82,41}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{-82,-141}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{-82,-141}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{382,441}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{382,441}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b < ANY ('{82}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b < ANY ('{82}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]);
+ count
+-------
+ 674
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b < ANY ('{82,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b < ANY ('{82,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]);
+ count
+-------
+ 674
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b < ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b < ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b <= ANY ('{82,41}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b <= ANY ('{82,41}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]);
+ count
+-------
+ 684
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b < ANY ('{82,41,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b < ANY ('{82,41,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]);
+ count
+-------
+ 674
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b <= ANY ('{82,41,15}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b <= ANY ('{82,41,15}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]);
+ count
+-------
+ 684
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b < ANY ('{NULL,82,41,15}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b < ANY ('{NULL,82,41,15}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]);
+ count
+-------
+ 674
+(1 row)
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b > ANY ('{82}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b > ANY ('{82}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]);
+ count
+-------
+ 316
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b > ANY ('{82,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b > ANY ('{82,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]);
+ count
+-------
+ 316
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b > ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b > ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b > ANY ('{82,41}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b > ANY ('{82,41}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]);
+ count
+-------
+ 726
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b >= ANY ('{82,41,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b >= ANY ('{82,41,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]);
+ count
+-------
+ 736
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b >= ANY ('{82,41,15}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b >= ANY ('{82,41,15}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]);
+ count
+-------
+ 961
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_1
+ Recheck Cond: (b > ANY ('{NULL,82,41,15}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_multi_1_idx_2
+ Index Cond: (b > ANY ('{NULL,82,41,15}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]);
+ count
+-------
+ 956
+(1 row)
+
+DROP TABLE brin_in_test_multi_1;
+RESET enable_seqscan;
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_multi_2 (a UUID) WITH (fillfactor=10);
+INSERT INTO brin_in_test_multi_2
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+CREATE INDEX brin_in_test_multi_2_idx ON brin_in_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189');
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189');
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,NULL}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,NULL}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL);
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{NULL,NULL}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0');
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,NULL}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,NULL}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL);
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_multi_2
+ Recheck Cond: (a = ANY ('{NULL,33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[]))
+ -> Bitmap Index Scan on brin_in_test_multi_2_idx
+ Index Cond: (a = ANY ('{NULL,33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+DROP TABLE brin_in_test_multi_2;
+RESET enable_seqscan;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 68ba94121e3..043200efebb 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -557,3 +557,304 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97
DROP TABLE brin_test_multi_2;
RESET enable_seqscan;
+
+
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_multi_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+
+CREATE INDEX brin_in_test_multi_1_idx_1 ON brin_in_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_multi_1_idx_2 ON brin_in_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+-- int: equality
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25);
+
+-- int: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]);
+
+-- int: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]);
+
+-- bigint: equality
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15);
+
+-- bigint: less than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]);
+
+-- bigint: greater than
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]);
+
+SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]);
+
+
+DROP TABLE brin_in_test_multi_1;
+RESET enable_seqscan;
+
+
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_multi_2 (a UUID) WITH (fillfactor=10);
+INSERT INTO brin_in_test_multi_2
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+
+CREATE INDEX brin_in_test_multi_2_idx ON brin_in_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189');
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0');
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39');
+
+DROP TABLE brin_in_test_multi_2;
+RESET enable_seqscan;
--
2.40.1
[text/x-patch] 0008-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230608.patch (22.8K, ../[email protected]/9-0008-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230608.patch)
download | inline diff:
From a1717ccd76632592cbd1cb7be8953c6c83a44ae2 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 15:23:25 +0100
Subject: [PATCH 8/9] Support SK_SEARCHARRAY in BRIN inclusion
---
src/backend/access/brin/brin_inclusion.c | 246 ++++++++++++++++++-----
src/include/catalog/pg_amproc.dat | 9 +
src/include/catalog/pg_proc.dat | 4 +
src/test/regress/expected/brin.out | 132 ++++++++++++
src/test/regress/sql/brin.sql | 53 +++++
5 files changed, 390 insertions(+), 54 deletions(-)
diff --git a/src/backend/access/brin/brin_inclusion.c b/src/backend/access/brin/brin_inclusion.c
index 02f4d0ae76e..712a8fd3c22 100644
--- a/src/backend/access/brin/brin_inclusion.c
+++ b/src/backend/access/brin/brin_inclusion.c
@@ -30,6 +30,7 @@
#include "access/skey.h"
#include "catalog/pg_amop.h"
#include "catalog/pg_type.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
@@ -72,6 +73,13 @@
#define INCLUSION_UNMERGEABLE 1
#define INCLUSION_CONTAINS_EMPTY 2
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_ARRAY 0x00010000 /* deconstructed array */
+
typedef struct InclusionOpaque
{
@@ -238,44 +246,94 @@ brin_inclusion_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(true);
}
+typedef struct ScanKeyArray {
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_inclusion_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+
+ /* number of non-null elements in the array */
+ int num_nonnulls;
+
+ /* ignore scalar keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ /* eliminate NULL elements */
+ num_nonnulls = 0;
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ /* if needed, move the non-NULL ones */
+ if (num_nonnulls != i)
+ elem_values[num_nonnulls] = elem_values[i];
+
+ num_nonnulls++;
+ }
+
+ num_elems = num_nonnulls;
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_ARRAY),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
- * BRIN inclusion consistent function
- *
- * We're no longer dealing with NULL keys in the consistent function, that is
- * now handled by the AM code. That means we should not get any all-NULL ranges
- * either, because those can't be consistent with regular (not [IS] NULL) keys.
+ * Check consistency of a single scalar value with the BRIN range.
*
- * All of the strategies are optional.
+ * Called for both scalar scankeys and for each value in SK_SEARCHARRAY.
*/
-Datum
-brin_inclusion_consistent(PG_FUNCTION_ARGS)
+static bool
+brin_inclusion_consistent_value(BrinDesc *bdesc, BrinValues *column,
+ AttrNumber attno,
+ StrategyNumber strategy, Oid subtype,
+ Oid colloid, Datum unionval, Datum query)
{
- BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
- BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
- ScanKey key = (ScanKey) PG_GETARG_POINTER(2);
- Oid colloid = PG_GET_COLLATION(),
- subtype;
- Datum unionval;
- AttrNumber attno;
- Datum query;
FmgrInfo *finfo;
Datum result;
- /* This opclass uses the old signature with only three arguments. */
- Assert(PG_NARGS() == 3);
-
- /* Should not be dealing with all-NULL ranges. */
- Assert(!column->bv_allnulls);
-
- /* It has to be checked, if it contains elements that are not mergeable. */
- if (DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
- PG_RETURN_BOOL(true);
-
- attno = key->sk_attno;
- subtype = key->sk_subtype;
- query = key->sk_argument;
- unionval = column->bv_values[INCLUSION_UNION];
- switch (key->sk_strategy)
+ switch (strategy)
{
/*
* Placement strategies
@@ -294,49 +352,49 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTOverRightStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTOverLeftStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTRightStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTOverRightStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTRightStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTOverLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTBelowStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTOverAboveStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTOverBelowStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTAboveStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTOverAboveStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTBelowStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
case RTAboveStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTOverBelowStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
/*
* Overlap and contains strategies
@@ -352,9 +410,9 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
case RTSubStrategyNumber:
case RTSubEqualStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
+ strategy);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_DATUM(result);
+ return (DatumGetBool(result));
/*
* Contained by strategies
@@ -374,9 +432,9 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
RTOverlapStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
/*
* Adjacent strategy
@@ -393,12 +451,12 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
RTOverlapStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTAdjacentStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_DATUM(result);
+ return (DatumGetBool(result));
/*
* Basic comparison strategies
@@ -428,9 +486,9 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
RTRightStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (!DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
case RTSameStrategyNumber:
case RTEqualStrategyNumber:
@@ -438,30 +496,110 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
RTContainsStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
case RTGreaterEqualStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (!DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
case RTGreaterStrategyNumber:
/* no need to check for empty elements */
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
default:
/* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- PG_RETURN_BOOL(false);
+ elog(ERROR, "invalid strategy number %d", strategy);
+ return (false);
+ }
+}
+
+/*
+ * BRIN inclusion consistent function
+ *
+ * We're no longer dealing with NULL keys in the consistent function, that is
+ * now handled by the AM code. That means we should not get any all-NULL ranges
+ * either, because those can't be consistent with regular (not [IS] NULL) keys.
+ *
+ * All of the strategies are optional.
+ */
+Datum
+brin_inclusion_consistent(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(2);
+ Oid colloid = PG_GET_COLLATION(),
+ subtype;
+ Datum unionval;
+ AttrNumber attno;
+ Datum query;
+
+ /* This opclass uses the old signature with only three arguments. */
+ Assert(PG_NARGS() == 3);
+
+ /* Should not be dealing with all-NULL ranges. */
+ Assert(!column->bv_allnulls);
+
+ /* It has to be checked, if it contains elements that are not mergeable. */
+ if (DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
+ PG_RETURN_BOOL(true);
+
+ attno = key->sk_attno;
+ subtype = key->sk_subtype;
+ query = key->sk_argument;
+ unionval = column->bv_values[INCLUSION_UNION];
+
+ /*
+ * For regular (scalar) scan keys, we simply compare the value to the
+ * range min/max values, and we're done. For SK_SEARCHARRAY keys we
+ * need to deparse the array and loop through the values.
+ */
+ if (likely(!(key->sk_flags & SK_SEARCHARRAY)))
+ {
+ bool tmp;
+
+ tmp = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, query);
+ PG_RETURN_BOOL(tmp);
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) query;
+ bool matches = false;
+
+ /*
+ * Loop through all pre-calculated hashes, check the bloom filter.
+ *
+ * XXX With empty cache (which can happen for IN clause with only NULL
+ * values), we leave the matches flag set to false.
+ */
+ for (int i = 0; i < array->nelements; i++)
+ {
+ Datum query_element = array->elements[i];
+
+ matches = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, query_element);
+
+ if (matches)
+ break;
+ }
+
+ /* we could get here for empty array, e.g. with "@> '{}'::point[]" */
+ PG_RETURN_BOOL(matches);
}
}
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 4f17f0d58c1..ed5b21e7f9b 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -1478,6 +1478,9 @@
{ amprocfamily => 'brin/network_inclusion_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '4',
amproc => 'brin_inclusion_union' },
+{ amprocfamily => 'brin/network_inclusion_ops', amproclefttype => 'inet',
+ amprocrighttype => 'inet', amprocnum => '6',
+ amproc => 'brin_inclusion_preprocess' },
{ amprocfamily => 'brin/network_inclusion_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '11', amproc => 'inet_merge' },
{ amprocfamily => 'brin/network_inclusion_ops', amproclefttype => 'inet',
@@ -2016,6 +2019,9 @@
{ amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange',
amprocrighttype => 'anyrange', amprocnum => '4',
amproc => 'brin_inclusion_union' },
+{ amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange',
+ amprocrighttype => 'anyrange', amprocnum => '6',
+ amproc => 'brin_inclusion_preprocess' },
{ amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange',
amprocrighttype => 'anyrange', amprocnum => '11',
amproc => 'range_merge(anyrange,anyrange)' },
@@ -2097,6 +2103,9 @@
{ amprocfamily => 'brin/box_inclusion_ops', amproclefttype => 'box',
amprocrighttype => 'box', amprocnum => '4',
amproc => 'brin_inclusion_union' },
+{ amprocfamily => 'brin/box_inclusion_ops', amproclefttype => 'box',
+ amprocrighttype => 'box', amprocnum => '6',
+ amproc => 'brin_inclusion_preprocess' },
{ amprocfamily => 'brin/box_inclusion_ops', amproclefttype => 'box',
amprocrighttype => 'box', amprocnum => '11', amproc => 'bound_box' },
{ amprocfamily => 'brin/box_inclusion_ops', amproclefttype => 'box',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 04555746e60..d82250875fc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8641,6 +8641,10 @@
proname => 'brin_inclusion_union', prorettype => 'bool',
proargtypes => 'internal internal internal',
prosrc => 'brin_inclusion_union' },
+{ oid => '9324', descr => 'BRIN inclusion support',
+ proname => 'brin_inclusion_preprocess', proisstrict => 'f',
+ prorettype => 'internal', proargtypes => 'internal internal',
+ prosrc => 'brin_inclusion_preprocess' },
# BRIN bloom
{ oid => '4591', descr => 'BRIN bloom support',
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 24361b90eb2..bed25fb1980 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -1430,3 +1430,135 @@ SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f35103
DROP TABLE brin_in_test_2;
RESET enable_seqscan;
+-- do some tests on IN clauses for boxes and points
+CREATE TABLE brin_in_test_3 (a BOX) WITH (fillfactor=10);
+INSERT INTO brin_in_test_3
+SELECT format('((%s,%s), (%s,%s))', x - mod(i,17), y - mod(i,13), x + mod(i,19), y + mod(i,11))::box FROM (
+ SELECT i,
+ i/10 + mod(991 * i + 617, 20) AS x,
+ i/10 + mod(853 * i + 491, 30) AS y
+ FROM generate_series(1,1000) s(i)
+) foo;
+CREATE INDEX brin_in_test_3_idx ON brin_in_test_3 USING brin (a box_inclusion_ops) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{"(10,10)"}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{"(10,10)"}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point]);
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, NULL]);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{"(10,10)",NULL}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{"(10,10)",NULL}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, NULL]);
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL::point, NULL::point]);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{NULL,NULL}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{NULL,NULL}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL::point, NULL::point]);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point]);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{"(10,10)","(50,50)"}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{"(10,10)","(50,50)"}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point]);
+ count
+-------
+ 80
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, NULL]);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{"(10,10)","(50,50)",NULL}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{"(10,10)","(50,50)",NULL}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, NULL]);
+ count
+-------
+ 80
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{"(10,10)","(50,50)","(25,25)"}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{"(10,10)","(50,50)","(25,25)"}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+ count
+-------
+ 129
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL, '(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_3
+ Recheck Cond: (a @> ANY ('{NULL,"(10,10)","(50,50)","(25,25)"}'::point[]))
+ -> Bitmap Index Scan on brin_in_test_3_idx
+ Index Cond: (a @> ANY ('{NULL,"(10,10)","(50,50)","(25,25)"}'::point[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL, '(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+ count
+-------
+ 129
+(1 row)
+
+DROP TABLE brin_in_test_3;
+RESET enable_seqscan;
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 7199c9108b0..86c7545ed5a 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -798,3 +798,56 @@ SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f35103
DROP TABLE brin_in_test_2;
RESET enable_seqscan;
+
+
+-- do some tests on IN clauses for boxes and points
+CREATE TABLE brin_in_test_3 (a BOX) WITH (fillfactor=10);
+INSERT INTO brin_in_test_3
+SELECT format('((%s,%s), (%s,%s))', x - mod(i,17), y - mod(i,13), x + mod(i,19), y + mod(i,11))::box FROM (
+ SELECT i,
+ i/10 + mod(991 * i + 617, 20) AS x,
+ i/10 + mod(853 * i + 491, 30) AS y
+ FROM generate_series(1,1000) s(i)
+) foo;
+
+CREATE INDEX brin_in_test_3_idx ON brin_in_test_3 USING brin (a box_inclusion_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL::point, NULL::point]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL::point, NULL::point]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, NULL]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, NULL]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY['(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL, '(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+
+SELECT COUNT(*) FROM brin_in_test_3 WHERE a @> ANY (ARRAY[NULL, '(10,10)'::point, '(50,50)'::point, '(25,25)'::point]);
+
+DROP TABLE brin_in_test_3;
+RESET enable_seqscan;
--
2.40.1
[text/x-patch] 0009-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230608.patch (41.6K, ../[email protected]/10-0009-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230608.patch)
download | inline diff:
From 83f1eb4a50832c09458502a80ae99ab86831f55e Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 20:50:03 +0100
Subject: [PATCH 9/9] Support SK_SEARCHARRAY in BRIN bloom
---
src/backend/access/brin/brin_bloom.c | 166 ++++++++--
src/include/catalog/pg_amproc.dat | 60 ++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/brin_bloom.out | 377 +++++++++++++++++++++++
src/test/regress/sql/brin_bloom.sql | 135 ++++++++
5 files changed, 715 insertions(+), 26 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index 4ff80aeb0cc..5c3ba659ba2 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -125,9 +125,11 @@
#include "access/stratnum.h"
#include "catalog/pg_type.h"
#include "catalog/pg_amop.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -151,6 +153,13 @@
*/
#define PROCNUM_BASE 11
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_HASHES 0x00010000 /* deconstructed array, calculated hashes */
+
/*
* Storage type for BRIN's reloptions.
*/
@@ -402,21 +411,14 @@ bloom_add_value(BloomFilter *filter, uint32 value, bool *updated)
return filter;
}
-
/*
* bloom_contains_value
* Check if the bloom filter contains a particular value.
*/
static bool
-bloom_contains_value(BloomFilter *filter, uint32 value)
+bloom_contains_hashes(BloomFilter *filter, uint64 h1, uint64 h2)
{
int i;
- uint64 h1,
- h2;
-
- /* calculate the two hashes */
- h1 = hash_bytes_uint32_extended(value, BLOOM_SEED_1) % filter->nbits;
- h2 = hash_bytes_uint32_extended(value, BLOOM_SEED_2) % filter->nbits;
/* compute the requested number of hashes */
for (i = 0; i < filter->nhashes; i++)
@@ -590,6 +592,104 @@ brin_bloom_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+typedef struct HashCache {
+ int nelements;
+ uint64 *h1;
+ uint64 *h2;
+} HashCache;
+
+Datum
+brin_bloom_preprocess(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ BloomOptions *opts = (BloomOptions *) PG_GET_OPCLASS_OPTIONS();
+ ScanKey newkey;
+ HashCache *cache = palloc0(sizeof(HashCache));
+
+ int nbits;
+ FmgrInfo *finfo;
+ uint32 hashValue;
+
+ /* we'll need to calculate hashes, so get the proc */
+ finfo = bloom_get_procinfo(bdesc, key->sk_attno, PROCNUM_HASH);
+
+ /*
+ * We don't have a filter from any range yet, so we just re-calculate
+ * the size (number of bits) just like bloom_init.
+ */
+ bloom_filter_size(brin_bloom_get_ndistinct(bdesc, opts),
+ BloomGetFalsePositiveRate(opts),
+ NULL, &nbits, NULL);
+
+ /* precalculate the hash even for simple scan keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ {
+ Datum value = key->sk_argument;
+
+ cache->nelements = 1;
+ cache->h1 = (uint64 *) palloc0(sizeof(uint64));
+ cache->h2 = (uint64 *) palloc0(sizeof(uint64));
+
+ hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, key->sk_collation, value));
+
+ cache->h1[0] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_1) % nbits;
+ cache->h2[0] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_2) % nbits;
+ }
+ else
+ {
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ cache->h1 = (uint64 *) palloc0(sizeof(uint64) * num_elems);
+ cache->h2 = (uint64 *) palloc0(sizeof(uint64) * num_elems);
+
+ for (int i = 0; i < num_elems; i++)
+ {
+ Datum element = elem_values[i];
+
+ /* ignore NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, key->sk_collation, element));
+
+ cache->h1[cache->nelements] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_1) % nbits;
+ cache->h2[cache->nelements] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_2) % nbits;
+
+ cache->nelements++;
+ }
+ }
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_HASHES),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(cache));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's bloom
@@ -598,16 +698,10 @@ brin_bloom_add_value(PG_FUNCTION_ARGS)
Datum
brin_bloom_consistent(PG_FUNCTION_ARGS)
{
- BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
ScanKey *keys = (ScanKey *) PG_GETARG_POINTER(2);
int nkeys = PG_GETARG_INT32(3);
- Oid colloid = PG_GET_COLLATION();
- AttrNumber attno;
- Datum value;
bool matches;
- FmgrInfo *finfo;
- uint32 hashValue;
BloomFilter *filter;
int keyno;
@@ -625,22 +719,42 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
/* NULL keys are handled and filtered-out in bringetbitmap */
Assert(!(key->sk_flags & SK_ISNULL));
- attno = key->sk_attno;
- value = key->sk_argument;
+ /*
+ * Keys should be preprocessed into a hash cache (even a single
+ * value scan keys, not just SK_SEARCHARRAY ones).
+ */
+ Assert(key->sk_flags & SK_BRIN_HASHES);
switch (key->sk_strategy)
{
case BloomEqualStrategyNumber:
-
- /*
- * We want to return the current page range if the bloom filter
- * seems to contain the value.
- */
- finfo = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH);
-
- hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, colloid, value));
- matches &= bloom_contains_value(filter, hashValue);
-
+ {
+ HashCache *cache = (HashCache *) key->sk_argument;
+
+ /* assume no match */
+ matches = false;
+
+ /*
+ * We want to return the current page range if the bloom filter
+ * seems to contain any of the values (or a single value).
+ *
+ * XXX With empty cache (which can happen for IN clause with
+ * only NULL values), we leave the matches flag set to false.
+ */
+ for (int i = 0; i < cache->nelements; i++)
+ {
+ bool tmp = false;
+
+ tmp = bloom_contains_hashes(filter, cache->h1[i], cache->h2[i]);
+
+ /* if we found a matching value, we have a match */
+ if (DatumGetBool(tmp))
+ {
+ matches = BoolGetDatum(true);
+ break;
+ }
+ }
+ }
break;
default:
/* shouldn't happen */
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index ed5b21e7f9b..d951fcd1a06 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -822,6 +822,9 @@
{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea',
amprocrighttype => 'bytea', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea',
+ amprocrighttype => 'bytea', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea',
amprocrighttype => 'bytea', amprocnum => '11', amproc => 'hashvarlena' },
@@ -853,6 +856,8 @@
amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char',
amprocrighttype => 'char', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char',
+ amprocrighttype => 'char', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char',
amprocrighttype => 'char', amprocnum => '11', amproc => 'hashchar' },
@@ -884,6 +889,8 @@
amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name',
amprocrighttype => 'name', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name',
+ amprocrighttype => 'name', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name',
amprocrighttype => 'name', amprocnum => '11', amproc => 'hashname' },
@@ -1010,6 +1017,8 @@
amprocrighttype => 'int8', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8',
amprocrighttype => 'int8', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8',
+ amprocrighttype => 'int8', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8',
amprocrighttype => 'int8', amprocnum => '11', amproc => 'hashint8' },
@@ -1025,6 +1034,8 @@
amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2',
+ amprocrighttype => 'int2', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2',
amprocrighttype => 'int2', amprocnum => '11', amproc => 'hashint2' },
@@ -1040,6 +1051,8 @@
amprocrighttype => 'int4', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4',
+ amprocrighttype => 'int4', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4',
amprocrighttype => 'int4', amprocnum => '11', amproc => 'hashint4' },
@@ -1071,6 +1084,8 @@
amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text',
amprocrighttype => 'text', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text',
+ amprocrighttype => 'text', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text',
amprocrighttype => 'text', amprocnum => '11', amproc => 'hashtext' },
@@ -1124,6 +1139,8 @@
amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid',
amprocrighttype => 'oid', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid',
+ amprocrighttype => 'oid', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid',
amprocrighttype => 'oid', amprocnum => '11', amproc => 'hashoid' },
@@ -1154,6 +1171,8 @@
amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid',
amprocrighttype => 'tid', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid',
+ amprocrighttype => 'tid', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid',
amprocrighttype => 'tid', amprocnum => '11', amproc => 'hashtid' },
@@ -1273,6 +1292,9 @@
{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4',
amprocrighttype => 'float4', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4',
+ amprocrighttype => 'float4', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4',
amprocrighttype => 'float4', amprocnum => '11', amproc => 'hashfloat4' },
@@ -1290,6 +1312,9 @@
{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8',
+ amprocrighttype => 'float8', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8',
amprocrighttype => 'float8', amprocnum => '11', amproc => 'hashfloat8' },
@@ -1349,6 +1374,9 @@
{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr',
amprocrighttype => 'macaddr', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr',
+ amprocrighttype => 'macaddr', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr',
amprocrighttype => 'macaddr', amprocnum => '11', amproc => 'hashmacaddr' },
@@ -1408,6 +1436,9 @@
{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8',
amprocrighttype => 'macaddr8', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8',
+ amprocrighttype => 'macaddr8', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8',
amprocrighttype => 'macaddr8', amprocnum => '11', amproc => 'hashmacaddr8' },
@@ -1462,6 +1493,8 @@
amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet',
+ amprocrighttype => 'inet', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '11', amproc => 'hashinet' },
@@ -1520,6 +1553,9 @@
{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar',
amprocrighttype => 'bpchar', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar',
+ amprocrighttype => 'bpchar', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar',
amprocrighttype => 'bpchar', amprocnum => '11', amproc => 'hashbpchar' },
@@ -1574,6 +1610,8 @@
amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time',
amprocrighttype => 'time', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time',
+ amprocrighttype => 'time', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time',
amprocrighttype => 'time', amprocnum => '11', amproc => 'time_hash' },
@@ -1707,6 +1745,9 @@
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp',
amprocrighttype => 'timestamp', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp',
+ amprocrighttype => 'timestamp', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp',
amprocrighttype => 'timestamp', amprocnum => '11',
amproc => 'timestamp_hash' },
@@ -1726,6 +1767,9 @@
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz',
amprocrighttype => 'timestamptz', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz',
+ amprocrighttype => 'timestamptz', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz',
amprocrighttype => 'timestamptz', amprocnum => '11',
amproc => 'timestamp_hash' },
@@ -1742,6 +1786,8 @@
amprocrighttype => 'date', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date',
+ amprocrighttype => 'date', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date',
amprocrighttype => 'date', amprocnum => '11', amproc => 'hashint4' },
@@ -1801,6 +1847,9 @@
{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval',
amprocrighttype => 'interval', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval',
+ amprocrighttype => 'interval', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval',
amprocrighttype => 'interval', amprocnum => '11', amproc => 'interval_hash' },
@@ -1859,6 +1908,9 @@
{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz',
amprocrighttype => 'timetz', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz',
+ amprocrighttype => 'timetz', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz',
amprocrighttype => 'timetz', amprocnum => '11', amproc => 'timetz_hash' },
@@ -1949,6 +2001,9 @@
{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric',
amprocrighttype => 'numeric', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric',
+ amprocrighttype => 'numeric', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric',
amprocrighttype => 'numeric', amprocnum => '11', amproc => 'hash_numeric' },
@@ -2003,6 +2058,8 @@
amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_bloom_union' },
{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid',
amprocrighttype => 'uuid', amprocnum => '5', amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid',
+ amprocrighttype => 'uuid', amprocnum => '6', amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid',
amprocrighttype => 'uuid', amprocnum => '11', amproc => 'uuid_hash' },
@@ -2087,6 +2144,9 @@
{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn',
amprocrighttype => 'pg_lsn', amprocnum => '5',
amproc => 'brin_bloom_options' },
+{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn',
+ amprocrighttype => 'pg_lsn', amprocnum => '6',
+ amproc => 'brin_bloom_preprocess' },
{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn',
amprocrighttype => 'pg_lsn', amprocnum => '11', amproc => 'pg_lsn_hash' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d82250875fc..ae13d076782 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8664,6 +8664,9 @@
{ oid => '4595', descr => 'BRIN bloom support',
proname => 'brin_bloom_options', proisstrict => 'f', prorettype => 'void',
proargtypes => 'internal', prosrc => 'brin_bloom_options' },
+{ oid => '9325', descr => 'BRIN bloom support',
+ proname => 'brin_bloom_preprocess', proisstrict => 'f', prorettype => 'internal',
+ proargtypes => 'internal internal', prosrc => 'brin_bloom_preprocess' },
# userlock replacements
{ oid => '2880', descr => 'obtain exclusive advisory lock',
diff --git a/src/test/regress/expected/brin_bloom.out b/src/test/regress/expected/brin_bloom.out
index 32c56a996a2..b83b2bae162 100644
--- a/src/test/regress/expected/brin_bloom.out
+++ b/src/test/regress/expected/brin_bloom.out
@@ -426,3 +426,380 @@ EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE b = 1;
Filter: (b = 1)
(2 rows)
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_bloom_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_bloom_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+CREATE INDEX brin_in_test_bloom_1_idx_1 ON brin_in_test_bloom_1 USING brin (a int4_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_bloom_1_idx_2 ON brin_in_test_bloom_1 USING brin (b int8_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = 113)
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = 113)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{113,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{113,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, NULL);
+ count
+-------
+ 8
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{NULL,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{113,177}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{113,177}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, NULL);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{113,177,NULL}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, NULL);
+ count
+-------
+ 16
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, 25);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, 113, 177, 25);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_1
+ Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, 113, 177, 25);
+ count
+-------
+ 24
+(1 row)
+
+-- a bit weird this requires a cast to bigint, unlike multi-value IN clause
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82::bigint);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = '82'::bigint)
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = '82'::bigint)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82::bigint);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{82,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{82,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, NULL);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, NULL);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{NULL,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{82,41}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{82,41}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, NULL);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{82,41,NULL}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, NULL);
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, 15);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, 82, 41, 15);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_1
+ Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_1_idx_2
+ Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, 82, 41, 15);
+ count
+-------
+ 25
+(1 row)
+
+DROP TABLE brin_in_test_bloom_1;
+RESET enable_seqscan;
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_bloom_2 (a TEXT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_bloom_2
+SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+CREATE INDEX brin_in_test_bloom_2_idx ON brin_in_test_bloom_2 USING brin (a text_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+SET enable_seqscan=off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = '33e75ff09dd601bbe69f351039152189'::text)
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = '33e75ff09dd601bbe69f351039152189'::text)
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+ count
+-------
+ 13
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, NULL);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{NULL,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{NULL,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+ count
+-------
+ 26
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on brin_in_test_bloom_2
+ Recheck Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+ -> Bitmap Index Scan on brin_in_test_bloom_2_idx
+ Index Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[]))
+(5 rows)
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+ count
+-------
+ 39
+(1 row)
+
+DROP TABLE brin_in_test_bloom_2;
+RESET enable_seqscan;
diff --git a/src/test/regress/sql/brin_bloom.sql b/src/test/regress/sql/brin_bloom.sql
index 5d499208e38..d187e89a609 100644
--- a/src/test/regress/sql/brin_bloom.sql
+++ b/src/test/regress/sql/brin_bloom.sql
@@ -374,3 +374,138 @@ VACUUM ANALYZE brin_test_bloom;
EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE a = 1;
-- Ensure brin index is not used when values are not correlated
EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE b = 1;
+
+
+-- do some tests on IN clauses for simple data types
+CREATE TABLE brin_in_test_bloom_1 (a INT, b BIGINT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_bloom_1
+SELECT i/5 + mod(991 * i + 617, 20),
+ i/10 + mod(853 * i + 491, 30)
+ FROM generate_series(1,1000) s(i);
+
+CREATE INDEX brin_in_test_bloom_1_idx_1 ON brin_in_test_bloom_1 USING brin (a int4_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+CREATE INDEX brin_in_test_bloom_1_idx_2 ON brin_in_test_bloom_1 USING brin (b int8_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (113, 177, 25);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, 113, 177, 25);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE a IN (NULL, 113, 177, 25);
+
+-- a bit weird this requires a cast to bigint, unlike multi-value IN clause
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82::bigint);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82::bigint);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (82, 41, 15);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, 82, 41, 15);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_1 WHERE b IN (NULL, 82, 41, 15);
+
+DROP TABLE brin_in_test_bloom_1;
+RESET enable_seqscan;
+
+
+-- do some tests on IN clauses for varlena data types
+CREATE TABLE brin_in_test_bloom_2 (a TEXT) WITH (fillfactor=10);
+INSERT INTO brin_in_test_bloom_2
+SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random();
+
+CREATE INDEX brin_in_test_bloom_2_idx ON brin_in_test_bloom_2 USING brin (a text_bloom_ops(n_distinct_per_range=50)) WITH (pages_per_range=1);
+
+SET enable_seqscan=off;
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+SELECT COUNT(*) FROM brin_in_test_bloom_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39');
+
+DROP TABLE brin_in_test_bloom_2;
+RESET enable_seqscan;
--
2.40.1
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox