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: Sun, 2 Jul 2023 18:09:12 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Here's an updated version of the patch series.
I've polished and pushed the first three patches with cleanup, tests to
improve test coverage and so on. I chose not to backpatch those - I
planned to do that to make future backpatches simpler, but the changes
ended up less disruptive than expected.
The remaining patches are just about adding SK_SEARCHARRAY to BRIN.
0001 - adds the optional preprocess procedure, calls it from brinrescan
0002 to 0005 - adds the support to the existing BRIN opclasses
The main open question I have is what exactly does it mean that the
procedure is optional. In particular, should it be supported to have a
BRIN opclass without the "preprocess" procedure but using the other
built-in support procedures?
For example, imagine you have a custom BRIN opclass in an extension (for
a custom data type or something). This does not need to implement any
procedures, it can just call the existing built-in ones. Of course, this
won't get the "preprocess" procedure automatically.
Should we support such opclasses or should we force the extension to be
updated by adding a preprocess procedure? I'd say "optional" means we
should support (otherwise it'd not really optional).
The reason why this matters is that "amsearcharray" is AM-level flag,
but the support procedure is defined by the opclass. So the consistent
function needs to handle SK_SEARCHARRAY keys both with and without
preprocessing.
That's mostly what I did for all existing BRIN opclasses (it's a bit
confusing that opclass may refer to both the "generic" minmax or the
opclass defined for a particular data type). All the opclasses now
handle three cases:
1) scalar keys (just like before, with amsearcharray=fase)
2) array keys with preprocessing (sorted array, array of hashes, ...)
3) array keys without preprocessing (for compatibility with old
opclasses missing the optional preprocess procedure)
The current code is a bit ugly, because it duplicates a bunch of code,
because the option (3) mostly does (1) in a loop. I'm confident this can
be reduced by refactoring and reusing some of the "shared" code.
The question is if my interpretation of what "optional" procedure means
is reasonable. Thoughts?
The other thing is how to test this "compatibility" code. I assume we
want to have the procedure for all built-in opclasses, so that won't
exercise it. I did test it by temporarily removing the procedure from a
couple pg_amproc.dat entries. I guess creating a custom opclass in the
regression test is the right solution.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230702.patch (8.5K, ../[email protected]/2-0001-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230702.patch)
download | inline diff:
From 2f2f011a3256724af658f87b030f1bfd017a235d Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Tue, 14 Feb 2023 20:29:33 +0100
Subject: [PATCH 1/5] 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.
The trouble is that amsearcharray=true is at the AM level, so it's not
entirely optional.
Discussion: https://postgr.es/m/f6aab55c-35eb-4118-a7ff-571c62e6cc39%40enterprisedb.com
---
doc/src/sgml/xindex.sgml | 7 ++
src/backend/access/brin/brin.c | 154 +++++++++++++++++++++++++++--
src/include/access/brin_internal.h | 1 +
3 files changed, 153 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml
index c753d8005a..183a30cfb6 100644
--- a/doc/src/sgml/xindex.sgml
+++ b/doc/src/sgml/xindex.sgml
@@ -464,6 +464,13 @@
</entry>
<entry>5</entry>
</row>
+ <row>
+ <entry>
+ Preprocess scan keys in a way specific to this operator class
+ (optional)
+ </entry>
+ <entry>6</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 3c6a956eaa..0f773ad75d 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -60,12 +60,26 @@ typedef struct BrinBuildState
/*
* Struct used as "opaque" during index scans
+ *
+ * If the operator class preprocesses scan keys, the results are stored in
+ * bo_scanKeys. It's up to the operator class to decide which keys are worth
+ * preprocessing and which are simply copied.
+ *
+ * Either no keys are preprocessed (and ten bo_scanKeys is NULL), or all keys
+ * have a matching entry in bo_scanKeys (either preprocessed or a pointer to
+ * the original key).
*/
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 +349,12 @@ 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);
+
+ /* no keys are preprocessed by default */
+ opaque->bo_numScanKeys = 0;
+ opaque->bo_scanKeys = NULL;
+ opaque->bo_scanKeysCxt = NULL;
+
scan->opaque = opaque;
return scan;
@@ -377,6 +397,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
char *ptr;
Size len;
char *tmp PG_USED_FOR_ASSERTS_ONLY;
+ ScanKey *scankeys;
opaque = (BrinOpaque *) scan->opaque;
bdesc = opaque->bo_bdesc;
@@ -454,10 +475,18 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
memset(nkeys, 0, sizeof(int) * bdesc->bd_tupdesc->natts);
memset(nnullkeys, 0, sizeof(int) * bdesc->bd_tupdesc->natts);
- /* Preprocess the scan keys - split them into per-attribute arrays. */
+ /* get the preprocessed scan keys (if present) */
+ scankeys = (opaque->bo_scanKeys) ? opaque->bo_scanKeys : &scan->keyData;
+
+ /*
+ * Preprocess the scan keys - split them into per-attribute arrays.
+ *
+ * XXX A bit misleading, as this is a different kind of preprocessing of the
+ * scan keys.
+ */
for (int keyno = 0; keyno < scan->numberOfKeys; keyno++)
{
- ScanKey key = &scan->keyData[keyno];
+ ScanKey key = scankeys[keyno];
AttrNumber keyattno = key->sk_attno;
/*
@@ -747,17 +776,124 @@ 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;
+ bool preprocess = false;
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 keys.
+ *
+ * We only do this if the opclass (or at least one of them, for multi
+ * column indexes) defines BRIN_PROCNUM_PREPROCESS procedure. If none
+ * of them does, it's pointless to create the memory context etc.
+ *
+ * FIXME Probably needs fixes to handle NULLs correctly.
+ *
+ * XXX Maybe we should not ignore IS NULL scan keys? Could it make sense
+ * to preprocess those in some way?
+ */
+
+ /*
+ * Inspect if at least one scankey has BRIN_PROCNUM_PREPROCESS.
+ *
+ * Might seem wasteful, as this may require walking the scan keys twice
+ * (now and then also to do the preprocessing). But we stop on the first
+ * match and the number of keys should be small. Seems worth it if we
+ * can skip allocating the context.
+ */
+ for (int i = 0; i < nscankeys; i++)
+ {
+ ScanKey key = &scan->keyData[i];
+ Oid procid;
+
+ /* skip IS NULL keys - there's nothing to preprocess */
+ if (key->sk_flags & SK_ISNULL)
+ continue;
+
+ /* fetch key preprocess support procedure if specified */
+ procid = index_getprocid(idxRel, key->sk_attno,
+ BRIN_PROCNUM_PREPROCESS);
+
+ /* don't look further if we found a preprocess procedure */
+ if (OidIsValid(procid))
+ {
+ preprocess = true;
+ break;
+ }
+ }
+
+ /* No index attribute has preprocess procedure that we could use. */
+ if (!preprocess)
+ return;
+
+ /*
+ * Do the actual scan key preprocessing. If this is the first time
+ * through, we need to create the memory context. Otherwise we need
+ * to reset it, which throws away previously preprocessed keys.
+ */
+ 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;
+ }
+
+ /* preprocess the scan key and store the result */
+ 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 97ddc925b2..d6a51f2bc4 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.41.0
[text/x-patch] 0002-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230702.patch (75.2K, ../[email protected]/3-0002-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230702.patch)
download | inline diff:
From f35148b5ca9158ad361e0a9fa02fe0cd03db279d Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 10 Feb 2023 16:07:57 +0100
Subject: [PATCH 2/5] 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 element
of the array as if it was a separate scalar keys combined by OR. This
walked the BRIN index many times, making it quite expensive, especially
for indexes with many ranges (large tables and/or low pages_per_range).
The array is sorted in BRIN_PROCNUM_PREPROCESS procedure, called from
brinrescan. The procedure is per opclass and optional, so there may be
opclasses without it. To handle that, the consistent function handles
SK_SEARCHARRAY without preprocessing by walking the array (the array
needs to be deconstructed for each page range, though).
TODO / open issues
- The other BRIN opclasses don't handle SK_SEARCHARRAY yet, but the
amsearcharray=true affects them. The tests don't fail most likely
because there are no queries with BRIN indexes and arrays. Will be
separate patches, but then probably need to be squashed for commit.
---
src/backend/access/brin/brin.c | 3 +-
src/backend/access/brin/brin_minmax.c | 529 +++++++++++++--
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, 1706 insertions(+), 40 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 0f773ad75d..bc215d4552 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"
@@ -115,7 +116,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 8229493c84..d246bd0d5a 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,195 @@ brin_minmax_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+/*
+ * preprocessing of scan keys for the minmax opclass
+ *
+ * For now we care only about array keys - instead of simple linear search
+ * in the array (for each range), we sort the arrays during preprocessing
+ * and then use that for binary sort in consistent function.
+ */
+
+/* qsort comparator used to sort array elements */
+static int
+minmax_compare_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);
+}
+
+/*
+ * Deconstructed and sorted scan key array (we might build ArrayType, but then
+ * we'd have to deconstruct it over and over for each page range). So we just
+ * do it once during preprocessing.
+ */
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+/*
+ * minmax_lower_boundary
+ * Given a value, determine the minimum index so that (array[index] >= value)
+ *
+ * We use this to check if a minmax range [minvalue, maxvalue] intersects with
+ * the array in the scan key (which is expected to be sorted). We calculate
+ * the lower boundary for [minvalue] and then check if it actually falls under
+ * the maxvalue too. If yes, we found an element consistent with the page range.
+ *
+ * If all array elements match (i.e. all elements >= value), returns 0. If no
+ * elements match (i.e. all elements < 0), returns nvalues.
+ */
+static int
+minmax_lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (minmax_compare_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (minmax_compare_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = minmax_compare_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(minmax_compare_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(minmax_compare_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+/*
+ * brin_minmax_preprocess
+ * preprocess scan keys for the minmax opclass
+ *
+ * For now we just care about SK_SEARCHARRAY keys, which we sort and keep the
+ * deconstructed array. All other scan keys are ignored (returned as is).
+ *
+ * XXX Do we need to remember if the array contained NULL values?
+ */
+Datum
+brin_minmax_preprocess(PG_FUNCTION_ARGS)
+{
+ 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 (just return the original scan key)
+ *
+ * XXX Maybe we should preprocess scalar keys too, and treat them as arrays
+ * with a single element. It'd make the consistent function simpler by not
+ * having to do 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;
+
+ /* FIXME What if num_nonnulls is 0? Can it even happen / get here? */
+
+ /*
+ * sort the array
+ *
+ * XXX Should we walk the sorted array again and eliminate duplicate values?
+ * Seems unnecessary - we're not going to repalloc/release the memory anyway
+ * and it's unlikely to speed up the binsearch (unless there's a lot of
+ * duplicate values, which does not seem plausible/common).
+ */
+ 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),
+ minmax_compare_values, &ssup);
+
+ /* Construct the new scan key, with sorted array as ScanKeyArray. */
+ 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 +356,300 @@ 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 can do a binary search in the sorted array.
+ *
+ * FIXME This should also handle the "array but not preprocessed" case
+ * too, for opclasses not defining the optional preprocess procedure.
+ * Otherwise we'd have issues with such opclasses because amsearcharray
+ * is defined at the AM level.
+ */
+ if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/
+ {
+ 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.
+ */
+ {
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ Datum element,
+ minvalue,
+ maxvalue;
+ int lower;
+
+ /* for readability */
+ minvalue = column->bv_values[0];
+ maxvalue = column->bv_values[1];
+
+ /*
+ * Before doing the binary search on sorted array, do a quick search
+ * if the range can match at all. If the whole array is outside the
+ * page range, we're done. This is much cheaper than the binsearch,
+ * and we assume most ranges do not match.
+ */
+
+ /* Is the first (smallest) array element after the BRIN range? */
+ element = array->elements[0];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, element, maxvalue);
+
+ /* first element > range maxvalue */
+ if (!DatumGetBool(matches))
+ break;
+
+ /* Is the last (largest) array element before the BRIN range? */
+ element = array->elements[array->nelements-1];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, element, minvalue);
+
+ /* last element < range minvalue */
+ if (!DatumGetBool(matches))
+ break;
+
+ /*
+ * It seems there might be some array elements consistent with
+ * the page range. We find the first element above the range
+ * minvalue and check if it's smaller than maxvalue. If yes,
+ * we have a match. If not, other elements can't be consistent
+ * either (will exceed maxval too, thanks to sort).
+ */
+ 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 = minmax_lower_boundary(array->elements, array->nelements,
+ minvalue, &ssup);
+
+ /*
+ * If the lower boundary is nelements, then no array elements
+ * can possibly match this page range.
+ *
+ * XXX This is probably impossible due to the earlier check.
+ * This would mean all elements are < minvalue, but we did
+ * check for that.
+ */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * We have an element that might be consistent with the page
+ * range, so let's check the maxvalue too (if it exceeds it,
+ * no following elements will too).
+ */
+ element = 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.
+ *
+ * XXX This minvalue check is likely unnecessary, thanks to the
+ * lower boundary guaranteeing this to be true.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid,
+ minvalue, element);
+ if (!DatumGetBool(matches))
+ break;
+
+ /* maxvalue >= element */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid,
+ maxvalue, element);
+ 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;
+ }
+ }
+ else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */
+ {
+ 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);
+
+ /* we'll skip NULL elements */
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ 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],
+ elem_values[i]);
+ 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],
+ elem_values[i]);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ elem_values[i]);
+ break;
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ elem_values[i]);
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 0;
+ break;
+ }
+
+ /* found a consistent value, we're done */
+ if (DatumGetBool(matches))
+ break;
+ }
+
+ /*
+ * free the arrays
+ *
+ * XXX is this necessary?
+ */
+ pfree(elem_values);
+ pfree(elem_nulls);
+ }
+ else /* scalar scan key */
{
- 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;
- /* 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 c8edfb3759..0889e24bc0 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 5b950129de..166681c31e 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 6996073989..8f17532094 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 7ab6113c61..f3e1fbd2ae 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 f0b7de5bf7..24361b90eb 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 929a087a25..7199c9108b 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.41.0
[text/x-patch] 0003-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230702.patch (84.3K, ../[email protected]/4-0003-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230702.patch)
download | inline diff:
From 49606760ca8d119f2794626620a67dd568795015 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 17 Feb 2023 02:45:14 +0100
Subject: [PATCH 3/5] 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 | 770 ++++++++++++++--
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, 1994 insertions(+), 64 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f8b2a3f9bc..a8c65bfc20 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];
@@ -2568,6 +2576,197 @@ brin_minmax_multi_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(modified);
}
+/*
+ * preprocessing of scan keys for the minmax multi opclass
+ *
+ * For now we care only about array keys - instead of simple linear search
+ * in the array (for each range), we sort the arrays during preprocessing
+ * and then use that for binary sort in consistent function.
+ *
+ * FIXME A lot of this is exactly the same as for plain minmax, maybe we
+ * can share that somehow.
+ */
+
+/* qsort comparator used to sort array elements */
+static int
+minmax_multi_compare_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);
+}
+
+/*
+ * Deconstructed and sorted scan key array (we might build ArrayType, but then
+ * we'd have to deconstruct it over and over for each page range). So we just
+ * do it once during preprocessing.
+ */
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+/*
+ * minmax_multi_lower_boundary
+ * Given a value, determine the minimum index so that (array[index] >= value)
+ *
+ * We use this to check if a minmax range [minvalue, maxvalue] intersects with
+ * the array in the scan key (which is expected to be sorted). We calculate
+ * the lower boundary for [minvalue] and then check if it actually falls under
+ * the maxvalue too. If yes, we found an element consistent with the page range.
+ *
+ * If all array elements match (i.e. all elements >= value), returns 0. If no
+ * elements match (i.e. all elements < 0), returns nvalues.
+ */
+static int
+minmax_multi_lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (minmax_multi_compare_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (minmax_multi_compare_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = minmax_multi_compare_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(minmax_multi_compare_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(minmax_multi_compare_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+/*
+ * brin_multi_minmax_preprocess
+ * preprocess scan keys for the minmax multi opclass
+ *
+ * For now we just care about SK_SEARCHARRAY keys, which we sort and keep the
+ * deconstructed array. All other scan keys are ignored (returned as is).
+ *
+ * XXX Do we need to remember if the array contained NULL values?
+ */
+Datum
+brin_minmax_multi_preprocess(PG_FUNCTION_ARGS)
+{
+ 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 (just return the original scan key)
+ *
+ * XXX Maybe we should preprocess scalar keys too, and treat them as arrays
+ * with a single element. It'd make the consistent function simpler by not
+ * having to do 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;
+
+ /* FIXME What if num_nonnulls is 0? Can it even happen / get here? */
+
+ /*
+ * sort the array
+ *
+ * XXX Should we walk the sorted array again and eliminate duplicate values?
+ * Seems unnecessary - we're not going to repalloc/release the memory anyway
+ * and it's unlikely to speed up the binsearch (unless there's a lot of
+ * duplicate values, which does not seem plausible/common).
+ */
+ 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),
+ minmax_multi_compare_values, &ssup);
+
+ /* Construct the new scan key, with sorted array as ScanKeyArray. */
+ 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
@@ -2590,13 +2789,21 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
Ranges *ranges;
int keyno;
int rangeno;
- int i;
attno = column->bv_attno;
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++)
{
@@ -2617,67 +2824,349 @@ brin_minmax_multi_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 can do a binary search in the sorted array.
+ *
+ * FIXME This should also handle the "array but not preprocessed" case
+ * too, for opclasses not defining the optional preprocess procedure.
+ * Otherwise we'd have issues with such opclasses because amsearcharray
+ * is defined at the AM level.
+ */
+ if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* first value from the array */
- matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, value));
- break;
+ ScanKeyArray *array = (ScanKeyArray *) value;
- case BTEqualStrategyNumber:
- {
- Datum compar;
- FmgrInfo *cmpFn;
+ /* can happen if the IN list contained just NULLs */
+ if (array->nelements == 0)
+ PG_RETURN_BOOL(false);
- /* by default this range does not match */
- matches = false;
+ 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 = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval,
+ array->elements[array->nelements-1]));
+ break;
+
+ case BTEqualStrategyNumber:
/*
- * 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.
+ * In the equality case (WHERE col = someval), we want to
+ * return the current page range if the minimum value in
+ * the range <= key, and the maximum value >= 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.
*/
- cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- BTGreaterStrategyNumber);
- compar = FunctionCall2Coll(cmpFn, colloid, minval, value);
+ {
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ Datum element;
+ int lower;
+
+ /*
+ * Before doing the binary search on sorted array, do
+ * a quick search if the range can match at all. If
+ * the whole array is outside the page range, we're
+ * done. This is much cheaper than the binsearch, and
+ * we assume most ranges do not match.
+ */
+
+ /*
+ * Is the first (smallest) array element after the BRIN
+ * page range?
+ */
+ element = array->elements[0];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid,
+ element, maxval));
+
+ /* first element > range maxvalue */
+ if (!matches)
+ break;
+
+ /*
+ * Is the last (largest) array element before the BRIN
+ * page range?
+ */
+ element = array->elements[array->nelements-1];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid,
+ element, minval));
+
+ /* last element < range minvalue */
+ if (!matches)
+ break;
+
+ /*
+ * It seems there might be some array elements
+ * consistent with the page range. We find the first
+ * element above the range minvalue and check if it's
+ * smaller than maxvalue. If yes, we have a match. If
+ * not, other elements can't be consistent either
+ * (will exceed maxval too, thanks to sort).
+ */
+ 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 = minmax_multi_lower_boundary(array->elements, array->nelements,
+ minval, &ssup);
+
+ /*
+ * If the lower boundary is nelements, then no array
+ * elements can possibly match this page range.
+ *
+ * XXX This is probably impossible due to the earlier
+ * check. This would mean all elements are < minvalue,
+ * but we did check for that.
+ */
+ if (lower == array->nelements)
+ {
+ matches = false;
+ break;
+ }
+
+ /*
+ * We have an element that might be consistent with
+ * the page range, so let's check the maxvalue too
+ * (if it exceeds it, no following elements will too).
+ */
+ element = 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.
+ *
+ * XXX This minvalue check is likely unnecessary,
+ * thanks to the lower boundary guaranteeing this to
+ * be true.
+ */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid,
+ minval, element));
+ if (!matches)
+ break;
+
+ /* maxvalue >= element */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid,
+ maxval, element));
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval,
+ array->elements[0]));
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = false;
+ break;
+ }
+ }
+ else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */
+ {
+ 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);
+
+ /* we'll skip NULL elements */
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
- /* smaller than the smallest value in this range */
- if (DatumGetBool(compar))
+ 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 = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval,
+ elem_values[i]));
break;
- cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- BTLessStrategyNumber);
- compar = FunctionCall2Coll(cmpFn, colloid, maxval, value);
+ case BTEqualStrategyNumber:
+ {
+ Datum compar;
+ FmgrInfo *cmpFn;
+
+ /* by default this range does not match */
+ matches = 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,
+ elem_values[i]);
+
+ /* 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,
+ elem_values[i]);
+
+ /* 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 = true;
+
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval,
+ elem_values[i]));
+ break;
- /* larger than the largest value in this range */
- if (DatumGetBool(compar))
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = false;
break;
+ }
- /*
- * We haven't managed to eliminate this range, so
- * consider it matching.
- */
- matches = true;
+ /* found a consistent value, we're done */
+ if (DatumGetBool(matches))
+ break;
+ }
+ /*
+ * free the arrays
+ *
+ * XXX is this necessary?
+ */
+ pfree(elem_values);
+ pfree(elem_nulls);
+ }
+ else /* scalar scan key */
+ {
+ 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 = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, value));
break;
- }
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* last value from the array */
- matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, value));
- break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = false;
- break;
+ case BTEqualStrategyNumber:
+ {
+ Datum compar;
+ FmgrInfo *cmpFn;
+
+ /* by default this range does not match */
+ matches = 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 = true;
+
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, value));
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = false;
+ break;
+ }
}
/* the range has to match all the scan keys */
@@ -2700,7 +3189,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
* here, because we're dealing with serialized / fully compacted ranges,
* so there should be only very few values.
*/
- for (i = 0; i < ranges->nvalues; i++)
+ for (int i = 0; i < ranges->nvalues; i++)
{
Datum val = ranges->values[2 * ranges->nranges + i];
@@ -2719,24 +3208,177 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+
+ if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- case BTEqualStrategyNumber:
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
-
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, value));
- break;
+ ScanKeyArray *array = (ScanKeyArray *) value;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = false;
- break;
+ /* 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 = DatumGetBool(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 = minmax_multi_lower_boundary(array->elements, array->nelements,
+ val, &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = 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 = DatumGetBool(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 = DatumGetBool(FunctionCall2Coll(finfo, colloid, val,
+ array->elements[0]));
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = false;
+ break;
+ }
+ }
+ else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */
+ {
+ 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);
+
+ /* we'll skip NULL elements */
+ for (int j = 0; j < num_elems; j++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[j])
+ continue;
+
+ 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 = DatumGetBool(FunctionCall2Coll(finfo, colloid,
+ val, elem_values[j]));
+
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = false;
+ break;
+ }
+
+ /* found a consistent value, we're done */
+ if (DatumGetBool(matches))
+ break;
+ }
+
+ /*
+ * free the arrays
+ *
+ * XXX is this necessary?
+ */
+ pfree(elem_values);
+ pfree(elem_nulls);
+ }
+ else
+ {
+ 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 = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, value));
+ break;
+
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 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 166681c31e..4f17f0d58c 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 8f17532094..04555746e6 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 9f46934c9b..d774cd91a1 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -823,3 +823,929 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525
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 d50dbdee68..0e70e43807 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -586,3 +586,304 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525
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.41.0
[text/x-patch] 0004-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230702.patch (25.1K, ../[email protected]/5-0004-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230702.patch)
download | inline diff:
From 099f4e8795bef2d9db78f2ec69a209caa7f4127a Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 15:23:25 +0100
Subject: [PATCH 4/5] Support SK_SEARCHARRAY in BRIN inclusion
---
src/backend/access/brin/brin_inclusion.c | 323 +++++++++++++++++++----
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, 470 insertions(+), 51 deletions(-)
diff --git a/src/backend/access/brin/brin_inclusion.c b/src/backend/access/brin/brin_inclusion.c
index 02f4d0ae76..4a58aaeeda 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
{
@@ -239,43 +247,119 @@ brin_inclusion_add_value(PG_FUNCTION_ARGS)
}
/*
- * BRIN inclusion consistent function
+ * preprocessing of scan keys for the inclusion opclass
*
- * 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.
+ * Deconstructed scan key array (we might build ArrayType, but then we'd
+ * have to deconstruct it over and over for each page range). So we just
+ * do it once during preprocessing.
*
- * All of the strategies are optional.
+ * We don't sort the array, because for inclusion that's likely pointless.
+ */
+typedef struct ScanKeyArray {
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+/*
+ * brin_inclusion_preprocess
+ * preprocess scan keys for the inclusion opclass
+ *
+ * For now we just care about SK_SEARCHARRAY keys, which we deconstruct
+ * and keep like that. All other scan keys are ignored (returned as is).
+ *
+ * XXX Do we need to remember if the array contained NULL values?
*/
Datum
-brin_inclusion_consistent(PG_FUNCTION_ARGS)
+brin_inclusion_preprocess(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;
- FmgrInfo *finfo;
- Datum result;
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
- /* This opclass uses the old signature with only three arguments. */
- Assert(PG_NARGS() == 3);
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
- /* Should not be dealing with all-NULL ranges. */
- Assert(!column->bv_allnulls);
+ /* number of non-null elements in the array */
+ int num_nonnulls;
- /* It has to be checked, if it contains elements that are not mergeable. */
- if (DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
- PG_RETURN_BOOL(true);
+ /*
+ * ignore scalar keys (just return the original scan key)
+ *
+ * XXX Maybe we should preprocess scalar keys too, and treat them as arrays
+ * with a single element. It'd make the consistent function simpler by not
+ * having to do branching.
+ */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
- attno = key->sk_attno;
- subtype = key->sk_subtype;
- query = key->sk_argument;
- unionval = column->bv_values[INCLUSION_UNION];
- switch (key->sk_strategy)
+ 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;
+
+ /* FIXME What if num_nonnulls is 0? Can it even happen / get here? */
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ /* Construct the new scan key, with sorted array as ScanKeyArray. */
+ 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);
+}
+
+/*
+ * Check consistency of a single scalar value with the BRIN range.
+ *
+ * Called for both scalar scankeys and for each value in SK_SEARCHARRAY.
+ */
+static bool
+brin_inclusion_consistent_value(BrinDesc *bdesc, BrinValues *column,
+ AttrNumber attno,
+ StrategyNumber strategy, Oid subtype,
+ Oid colloid, Datum unionval, Datum query)
+{
+ FmgrInfo *finfo;
+ Datum result;
+
+ switch (strategy)
{
/*
* Placement strategies
@@ -294,49 +378,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 +436,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 +458,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 +477,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 +512,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 +522,167 @@ 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 preprocessed array keys
+ * (SK_BRIN_ARRAY) we simply walk the deconstructed array. For raw
+ * SK_SEARCHARRAY keys we need to deparse the array and loop through
+ * the values.
+ */
+ if (key->sk_flags & SK_BRIN_ARRAY)
+ {
+ ScanKeyArray *array = (ScanKeyArray *) query;
+ bool matches = false;
+
+ /*
+ * Loop through all deconstructed array, check the inclusion for
+ * each element.
+ *
+ * XXX With empty array (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);
+ }
+ else if (key->sk_flags & SK_SEARCHARRAY)
+ {
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ bool matches = false;
+
+ 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);
+
+ /*
+ * Loop through all deconstructed array, check the inclusion for
+ * each element.
+ *
+ * XXX With empty array (which can happen for IN clause with only
+ * NULL values), we leave the matches flag set to false.
+ */
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ matches = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, elem_values[i]);
+
+ if (matches)
+ break;
+ }
+
+ /*
+ * free the arrays
+ *
+ * XXX is this necessary?
+ */
+ pfree(elem_values);
+ pfree(elem_nulls);
+
+ /* we could get here for empty array, e.g. with "@> '{}'::point[]" */
+ PG_RETURN_BOOL(matches);
+ }
+ else
+ {
+ bool tmp;
+
+ tmp = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, query);
+ PG_RETURN_BOOL(tmp);
}
}
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 4f17f0d58c..ed5b21e7f9 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 04555746e6..d82250875f 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 24361b90eb..bed25fb198 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 7199c9108b..86c7545ed5 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.41.0
[text/x-patch] 0005-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230702.patch (44.0K, ../[email protected]/6-0005-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230702.patch)
download | inline diff:
From 9d6f6848d8783bc0cdc958253563d8898af9c439 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 20:50:03 +0100
Subject: [PATCH 5/5] Support SK_SEARCHARRAY in BRIN bloom
---
src/backend/access/brin/brin_bloom.c | 256 ++++++++++++++-
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, 814 insertions(+), 17 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index f47eb81012..6e58bf2c1d 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.
*/
@@ -404,19 +413,13 @@ bloom_add_value(BloomFilter *filter, uint32 value, bool *updated)
/*
- * bloom_contains_value
- * Check if the bloom filter contains a particular value.
+ * bloom_contains_hashes
+ * Check if the bloom filter contains a particular pair of hash values.
*/
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++)
@@ -435,6 +438,23 @@ bloom_contains_value(BloomFilter *filter, uint32 value)
return true;
}
+/*
+ * bloom_contains_value
+ * Check if the bloom filter contains a particular value.
+ */
+static bool
+bloom_contains_value(BloomFilter *filter, uint32 value)
+{
+ 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;
+
+ return bloom_contains_hashes(filter, h1, h2);
+}
+
typedef struct BloomOpaque
{
/*
@@ -590,6 +610,124 @@ brin_bloom_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+/*
+ * preprocessing of scan keys for the minmax opclass
+ *
+ * Cache of precalculated hashes for values (either a single value or an array
+ * of them).
+ */
+typedef struct HashCache {
+ int nelements;
+ uint64 *h1;
+ uint64 *h2;
+} HashCache;
+
+/*
+ * brin_bloom_preprocess
+ * preprocess scan keys for the bloom opclass
+ *
+ * For now we just care about SK_SEARCHARRAY keys, for which we precalculate
+ * hash values so that we don't need to do that for each page range.
+ *
+ * XXX Do we need to remember if the array contained NULL values?
+ */
+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 hash 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 scalar 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++;
+ }
+
+ /* free the deconstructed array representation */
+ pfree(elem_values);
+ pfree(elem_nulls);
+ }
+
+ /* Construct the new scan key, with the array of hashes in HashCache. */
+ 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
@@ -631,18 +769,102 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
value = key->sk_argument;
+ /*
+ * The opclass may not use the optional preprocess procedure, in which
+ * case we need to calculate the hash here as before.
+ *
+ * With preprocessed keys we just use the hash cache (even for scalar
+ * keys, not just for SK_SEARCHARRAY ones).
+ */
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);
+ if (key->sk_flags & SK_BRIN_HASHES) /* preprocessed keys */
+ {
+ HashCache *cache = (HashCache *) value;
+
+ /* 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;
+ }
+ }
+ }
+ else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */
+ {
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ bool match = false;
+
+ /* deconstruct the array */
+ arrayval = DatumGetArrayTypeP(value);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ /*
+ * 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);
+
+ /* we'll skip NULL elements */
+ for (int i = 0; i < num_elems; i++)
+ {
+ /* skip NULL elements */
+ if (elem_nulls[i])
+ continue;
+
+ hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, colloid, elem_values[i]));
+ match = bloom_contains_value(filter, hashValue);
+
+ /* did we find a match in the array? */
+ if (match)
+ break;
+ }
+
+ matches &= match;
+
+ }
+ else /* scalar value without preprocessing */
+ {
+ /*
+ * 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);
+ }
break;
default:
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index ed5b21e7f9..d951fcd1a0 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 d82250875f..ae13d07678 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 32c56a996a..b83b2bae16 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 5d499208e3..d187e89a60 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.41.0
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