public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: BRIN indexes vs. SK_SEARCHARRAY (and preprocessing scan keys)
Date: Thu, 16 Feb 2023 02:35:47 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Hi,
Attached is a patch series adopting the idea of scan key preprocessing
in brinrescan(), and producing scan keys. It turns out to work pretty
nicely, and it allows different opclasses doing different things:
- minmax / minmax-multi: sort the array values (leave scalars alone)
- inclusion: no preprocessing
- bloom: precalculate hash values
The _consistent functions are modified to leverage the preprocessed
keys. I wonder if it should check the existence of the (optional)
procedure, and fallback to the non-optimized search if not defined.
That would allow opclasses (e.g. from extensions) to keep using the
built-in consistent function without tweaking the definition to also
have the preprocess function. But that seems like a rather minor issue,
especially because the number of external opclasses is tiny and updating
the definition to also reference the preprocess function is trivial. I
don't think it's worth the extra code complexity.
0001 and 0002 are minor code cleanup in the opclasses introduced in PG
13. There's a couple places assigning boolean values to Datum variables,
and misleading comments.
0003 is a minor refactoring making the Bloom filter size calculation
easier to reuse.
0004 introduces the optional "preprocess" opclass procedure, and calls
it for keys from brinrescan().
0005-0008 add the preprocess procedure to the various BRIN types, and
adjust the consistent procedures accordingly.
Attached is a python script I used to measure this. It builds a table
with 10M rows, with sequential but slightly randomized (value may move
within the 1% of table), and minmax/bloom indexes. The table has ~500MB,
the indexes are using pages_per_range=1 (tiny, but simulates large table
with regular page ranges).
And then the script queries the table with different number of random
values in the "IN (...)" clause, and measures query duration (in ms).
The results look like this:
int text
index values master patched master patched int text
------------------------------------------------------------------
minmax 1 7 7 27 25 100% 92%
10 66 15 277 70 23% 25%
20 132 16 558 85 12% 15%
50 331 21 1398 102 7% 7%
100 663 29 2787 118 4% 4%
500 3312 81 13964 198 2% 1%
------------------------------------------------------------------
bloom 1 30 27 23 18 92% 77%
10 302 208 231 35 69% 15%
20 585 381 463 54 65% 12%
50 1299 761 1159 111 59% 10%
100 2194 1099 2312 204 50% 9%
500 6850 1228 11559 918 18% 8%
------------------------------------------------------------------
With minmax, consider for example queries with 20 values, which used to
take ~130ms, but with the patch this drops to 16ms (~23%). And the
improvement is even more significant for larger number of values. For
text data the results are pretty comparable.
With bloom indexes, the improvements are proportional to how expensive
the hash function is (for the data type). For int the hash is fairly
cheap, so the improvement is rather moderate (but visible). For text,
the improvements are way more significant - for 10 values the duration
is reduced by a whopping 85%.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-BRIN-bloom-cleanup-20230215.patch (1.9K, ../[email protected]/2-0001-BRIN-bloom-cleanup-20230215.patch)
download | inline diff:
From 9c2726e9a227dad2ed11d246bea321c37e38c5c1 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 15 Feb 2023 12:39:23 +0100
Subject: [PATCH 1/9] BRIN bloom cleanup
Minor cleanup of the BRIN bloom code - use the proper data type for the
boolean variable, and correct comment copied from minmax.
---
src/backend/access/brin/brin_bloom.c | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e4953a9d37..6c716924ff 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -574,7 +574,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
Oid colloid = PG_GET_COLLATION();
AttrNumber attno;
Datum value;
- Datum matches;
+ bool matches;
FmgrInfo *finfo;
uint32 hashValue;
BloomFilter *filter;
@@ -584,6 +584,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
Assert(filter);
+ /* assume all scan keys match */
matches = true;
for (keyno = 0; keyno < nkeys; keyno++)
@@ -601,9 +602,8 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
case BloomEqualStrategyNumber:
/*
- * In the equality case (WHERE col = someval), we want to
- * return the current page range if the minimum value in the
- * range <= scan key, and the maximum value >= scan key.
+ * We want to return the current page range if the bloom filter
+ * seems to contain the value.
*/
finfo = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH);
@@ -614,7 +614,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = false;
break;
}
@@ -622,7 +622,7 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
break;
}
- PG_RETURN_DATUM(matches);
+ PG_RETURN_BOOL(matches);
}
/*
--
2.39.1
[text/x-patch] 0002-BRIN-minmax-multi-cleanup-20230215.patch (2.2K, ../[email protected]/3-0002-BRIN-minmax-multi-cleanup-20230215.patch)
download | inline diff:
From b57514ed44566295bb8388db200b7d1638dda002 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 15 Feb 2023 12:53:47 +0100
Subject: [PATCH 2/9] BRIN minmax-multi cleanup
When assigning to a Datum variable, use BoolGetDatum() consistently.
Simnplify the code by using PG_RETURN_BOOL().
---
src/backend/access/brin/brin_minmax_multi.c | 14 +++++++-------
1 file changed, 7 insertions(+), 7 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 0ace6035be..859e0022fb 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2627,7 +2627,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
FmgrInfo *cmpFn;
/* by default this range does not match */
- matches = false;
+ matches = BoolGetDatum(false);
/*
* Otherwise, need to compare the new value with
@@ -2655,7 +2655,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
* We haven't managed to eliminate this range, so
* consider it matching.
*/
- matches = true;
+ matches = BoolGetDatum(true);
break;
}
@@ -2670,7 +2670,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = BoolGetDatum(false);
break;
}
@@ -2686,7 +2686,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
* have we found a range matching all scan keys? if yes, we're done
*/
if (matching)
- PG_RETURN_DATUM(BoolGetDatum(true));
+ PG_RETURN_BOOL(true);
}
/*
@@ -2729,7 +2729,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
default:
/* shouldn't happen */
elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
+ matches = BoolGetDatum(false);
break;
}
@@ -2743,10 +2743,10 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
/* have we found a range matching all scan keys? if yes, we're done */
if (matching)
- PG_RETURN_DATUM(BoolGetDatum(true));
+ PG_RETURN_BOOL(true);
}
- PG_RETURN_DATUM(BoolGetDatum(false));
+ PG_RETURN_BOOL(false);
}
/*
--
2.39.1
[text/x-patch] 0003-Introduce-bloom_filter_size-20230215.patch (3.5K, ../[email protected]/4-0003-Introduce-bloom_filter_size-20230215.patch)
download | inline diff:
From 5c48f948486a5b4da5e888ab57451818ebb36eef Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Tue, 14 Feb 2023 20:28:08 +0100
Subject: [PATCH 3/9] Introduce bloom_filter_size
Wrap calculation of Bloom filter parameters (from ndistinct and false
positive rate) into a function. We'll need to do this calculation in
other places, and this makes it more consistent.
---
src/backend/access/brin/brin_bloom.c | 63 +++++++++++++++++++++-------
1 file changed, 47 insertions(+), 16 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index 6c716924ff..4ff80aeb0c 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -259,6 +259,48 @@ typedef struct BloomFilter
char data[FLEXIBLE_ARRAY_MEMBER];
} BloomFilter;
+/*
+ * bloom_filter_size
+ * Calculate Bloom filter parameters (nbits, nbytes, nhashes).
+ *
+ * Given expected number of distinct values and desired false positive rate,
+ * calculates the optimal parameters of the Bloom filter.
+ *
+ * The resulting parameters are returned through nbytesp (number of bytes),
+ * nbitsp (number of bits) and nhashesp (number of hash functions). If a
+ * pointer is NULL, the parameter is not returned.
+ */
+static void
+bloom_filter_size(int ndistinct, double false_positive_rate,
+ int *nbytesp, int *nbitsp, int *nhashesp)
+{
+ double k;
+ int nbits,
+ nbytes;
+
+ /* sizing bloom filter: -(n * ln(p)) / (ln(2))^2 */
+ nbits = ceil(-(ndistinct * log(false_positive_rate)) / pow(log(2.0), 2));
+
+ /* round m to whole bytes */
+ nbytes = ((nbits + 7) / 8);
+ nbits = nbytes * 8;
+
+ /*
+ * round(log(2.0) * m / ndistinct), but assume round() may not be
+ * available on Windows
+ */
+ k = log(2.0) * nbits / ndistinct;
+ k = (k - floor(k) >= 0.5) ? ceil(k) : floor(k);
+
+ if (nbytesp)
+ *nbytesp = nbytes;
+
+ if (nbitsp)
+ *nbitsp = nbits;
+
+ if (nhashesp)
+ *nhashesp = (int) k;
+}
/*
* bloom_init
@@ -275,19 +317,15 @@ bloom_init(int ndistinct, double false_positive_rate)
int nbits; /* size of filter / number of bits */
int nbytes; /* size of filter / number of bytes */
-
- double k; /* number of hash functions */
+ int nhashes; /* number of hash functions */
Assert(ndistinct > 0);
Assert((false_positive_rate >= BLOOM_MIN_FALSE_POSITIVE_RATE) &&
(false_positive_rate < BLOOM_MAX_FALSE_POSITIVE_RATE));
- /* sizing bloom filter: -(n * ln(p)) / (ln(2))^2 */
- nbits = ceil(-(ndistinct * log(false_positive_rate)) / pow(log(2.0), 2));
-
- /* round m to whole bytes */
- nbytes = ((nbits + 7) / 8);
- nbits = nbytes * 8;
+ /* calculate bloom filter size / parameters */
+ bloom_filter_size(ndistinct, false_positive_rate,
+ &nbytes, &nbits, &nhashes);
/*
* Reject filters that are obviously too large to store on a page.
@@ -310,13 +348,6 @@ bloom_init(int ndistinct, double false_positive_rate)
elog(ERROR, "the bloom filter is too large (%d > %zu)", nbytes,
BloomMaxFilterSize);
- /*
- * round(log(2.0) * m / ndistinct), but assume round() may not be
- * available on Windows
- */
- k = log(2.0) * nbits / ndistinct;
- k = (k - floor(k) >= 0.5) ? ceil(k) : floor(k);
-
/*
* We allocate the whole filter. Most of it is going to be 0 bits, so the
* varlena is easy to compress.
@@ -326,7 +357,7 @@ bloom_init(int ndistinct, double false_positive_rate)
filter = (BloomFilter *) palloc0(len);
filter->flags = 0;
- filter->nhashes = (int) k;
+ filter->nhashes = nhashes;
filter->nbits = nbits;
SET_VARSIZE(filter, len);
--
2.39.1
[text/x-patch] 0004-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230215.patch (4.8K, ../[email protected]/5-0004-Introduce-BRIN_PROCNUM_PREPROCESS-procedure-20230215.patch)
download | inline diff:
From 48593363cdca9358efb3784cb8e3b33952764842 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Tue, 14 Feb 2023 20:29:33 +0100
Subject: [PATCH 4/9] Introduce BRIN_PROCNUM_PREPROCESS procedure
Allow BRIN opclasses to define an optional procedure to preprocess scan
keys, and call it from brinrescan(). This allows the opclass to modify
the keys in various ways - sort arrays, calculate hashes, ...
Note: The procedure is optional, so existing opclasses don't need to add
it. But if it uses the existing BRIN_PROCNUM_CONSISTENT function, it'll
get broken. If we want to make this backwards-compatible, we might check
if BRIN_PROCNUM_PREPROCESS exist from BRIN_PROCNUM_CONSISTENT, and
adjust behavior based on that.
---
src/backend/access/brin/brin.c | 72 ++++++++++++++++++++++++++----
src/include/access/brin_internal.h | 1 +
2 files changed, 65 insertions(+), 8 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 85ae795949..ef3d64daf6 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -66,6 +66,12 @@ typedef struct BrinOpaque
BlockNumber bo_pagesPerRange;
BrinRevmap *bo_rmAccess;
BrinDesc *bo_bdesc;
+
+ /* preprocessed scan keys */
+ int bo_numScanKeys; /* number of (preprocessed) scan keys */
+ ScanKey *bo_scanKeys; /* modified copy of scan->keyData */
+ MemoryContext bo_scanKeysCxt; /* scan-lifespan context for key data */
+
} BrinOpaque;
#define BRIN_ALL_BLOCKRANGES InvalidBlockNumber
@@ -334,6 +340,11 @@ brinbeginscan(Relation r, int nkeys, int norderbys)
opaque->bo_rmAccess = brinRevmapInitialize(r, &opaque->bo_pagesPerRange,
scan->xs_snapshot);
opaque->bo_bdesc = brin_build_desc(r);
+
+ opaque->bo_numScanKeys = 0;
+ opaque->bo_scanKeys = NULL;
+ opaque->bo_scanKeysCxt = NULL;
+
scan->opaque = opaque;
return scan;
@@ -456,7 +467,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
/* Preprocess the scan keys - split them into per-attribute arrays. */
for (int keyno = 0; keyno < scan->numberOfKeys; keyno++)
{
- ScanKey key = &scan->keyData[keyno];
+ ScanKey key = opaque->bo_scanKeys[keyno];
AttrNumber keyattno = key->sk_attno;
/*
@@ -735,17 +746,62 @@ void
brinrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
ScanKey orderbys, int norderbys)
{
- /*
- * Other index AMs preprocess the scan keys at this point, or sometime
- * early during the scan; this lets them optimize by removing redundant
- * keys, or doing early returns when they are impossible to satisfy; see
- * _bt_preprocess_keys for an example. Something like that could be added
- * here someday, too.
- */
+ BrinOpaque *bo = (BrinOpaque *) scan->opaque;
+ Relation idxRel = scan->indexRelation;
+ MemoryContext oldcxt;
if (scankey && scan->numberOfKeys > 0)
memmove(scan->keyData, scankey,
scan->numberOfKeys * sizeof(ScanKeyData));
+
+ /*
+ * Use the BRIN_PROCNUM_PREPROCESS procedure (if defined) to preprocess
+ * the scan keys. The procedure may do anything, as long as the result
+ * looks like a ScanKey. If there's no procedure, we keep the original
+ * scan key.
+ *
+ * FIXME Probably need fixes to handle NULLs correctly.
+ */
+ if (bo->bo_scanKeysCxt == NULL)
+ bo->bo_scanKeysCxt = AllocSetContextCreate(CurrentMemoryContext,
+ "BRIN scan keys context",
+ ALLOCSET_SMALL_SIZES);
+ else
+ MemoryContextReset(bo->bo_scanKeysCxt);
+
+ oldcxt = MemoryContextSwitchTo(bo->bo_scanKeysCxt);
+
+ bo->bo_scanKeys = palloc0(sizeof(ScanKey) * nscankeys);
+
+ for (int i = 0; i < nscankeys; i++)
+ {
+ FmgrInfo *finfo;
+ ScanKey key = &scan->keyData[i];
+ Oid procid;
+ Datum ret;
+
+ /* fetch key preprocess support procedure if specified */
+ procid = index_getprocid(idxRel, key->sk_attno,
+ BRIN_PROCNUM_PREPROCESS);
+
+ /* not specified, just point to the original key */
+ if (!OidIsValid(procid))
+ {
+ bo->bo_scanKeys[i] = key;
+ continue;
+ }
+
+ finfo = index_getprocinfo(idxRel, key->sk_attno,
+ BRIN_PROCNUM_PREPROCESS);
+
+ ret = FunctionCall2(finfo,
+ PointerGetDatum(bo->bo_bdesc),
+ PointerGetDatum(key));
+
+ bo->bo_scanKeys[i] = (ScanKey) DatumGetPointer(ret);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
}
/*
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index 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.39.1
[text/x-patch] 0005-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230215.patch (27.7K, ../[email protected]/6-0005-Support-SK_SEARCHARRAY-in-BRIN-minmax-20230215.patch)
download | inline diff:
From 455932eb8e4d05e9f05350ea1a137e93bf786ae9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 10 Feb 2023 16:07:57 +0100
Subject: [PATCH 5/9] Support SK_SEARCHARRAY in BRIN minmax
Set "amsearcharray=true" for BRIN, and extend the minmax opclass to
handle both scalar values and arrays. This allows handling conditions
... WHERE a IN (1, 2, 43, 2132, 134)
... WHERE a = ANY(ARRAY[34, 45, -1, 234])
... WHERE a <= ANY(ARRAY[4938, 282, 2934])
more efficiently - until now we simply built the bitmap for each
scalar value, so we walked the BRIN index many times. Which may be quite
expensive for indexes with many ranges (large tables and/or low
pages_per_range).
There's a couple problems / open questions / TODO items:
- The other opclasses don't handle SK_SEARCHARRAY yet.
- The array is always searched linearly, so this may be costly for large
arrays (with many elements).
- The array is deconstructed again for each range. We should reuse this,
somehow.
---
src/backend/access/brin/brin.c | 3 +-
src/backend/access/brin/brin_minmax.c | 346 +++++++++++++++++++++---
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 +-
6 files changed, 382 insertions(+), 40 deletions(-)
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index ef3d64daf6..a232275c14 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -38,6 +38,7 @@
#include "utils/datum.h"
#include "utils/guc.h"
#include "utils/index_selfuncs.h"
+#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -107,7 +108,7 @@ brinhandler(PG_FUNCTION_ARGS)
amroutine->amcanunique = false;
amroutine->amcanmulticol = true;
amroutine->amoptionalkey = true;
- amroutine->amsearcharray = false;
+ amroutine->amsearcharray = true;
amroutine->amsearchnulls = true;
amroutine->amstorage = true;
amroutine->amclusterable = false;
diff --git a/src/backend/access/brin/brin_minmax.c b/src/backend/access/brin/brin_minmax.c
index 2431591be6..359bb39f96 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,133 @@ brin_minmax_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+
+static int
+compare_array_values(const void *a, const void *b, void *arg)
+{
+ Datum da = * (Datum *) a;
+ Datum db = * (Datum *) b;
+ SortSupport ssup = (SortSupport) arg;
+
+ return ApplySortComparator(da, false, db, false, ssup);
+}
+
+/*
+ * lower_boundary
+ * Determine lowest index so that (values[index] >= minvalue).
+ *
+ * The array of values is expected to be sorted, so this is the first value
+ * that may fall into the [minvalue, maxvalue] range, as it exceeds minval.
+ * It's not guaranteed, though, as it might exceed maxvalue too.
+ */
+static int
+lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (compare_array_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (compare_array_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = compare_array_values(&minvalue, &values[midpoint], ssup);
+
+ if (r > 0)
+ start = Max(midpoint, start + 1);
+ else
+ end = midpoint;
+ }
+
+ /* the value should meet the (v >=minvalue) requirement */
+ Assert(compare_array_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(compare_array_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_minmax_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ /* ignore scalar keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ qsort_interruptible(elem_values, num_elems, sizeof(Datum),
+ compare_array_values, &ssup);
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->typeid = ARR_ELEMTYPE(arrayval);
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_SORTED),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's min/max
@@ -157,46 +294,179 @@ brin_minmax_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+
+ /*
+ * For regular (scalar) scan keys, we simply compare the value to the
+ * range min/max values, and we're done. For preprocessed SK_SEARCHARRAY
+ * keys we need to loop through the deparsed values.
+ */
+ if (likely(!(key->sk_flags & SK_BRIN_SORTED)))
+ {
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ value);
+ break;
+ case BTEqualStrategyNumber:
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ value);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ value);
+ break;
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ value);
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 0;
+ break;
+ }
+ }
+ else
{
- 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))
+ ScanKeyArray *array = (ScanKeyArray *) value;
+
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ /*
+ * Check the last (largest) value in the array - at least this
+ * value has to exceed the range minval.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ array->elements[array->nelements-1]);
+ break;
+ case BTEqualStrategyNumber:
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ *
+ * We do this in two phases. We check the array min/max values to see
+ * if there even can be a matching value, and if yes we do a binary
+ * search to find the first value that exceeds range minval. And then
+ * we check if it actually matches the range.
+ *
+ * XXX The first phase is probably unnecessary, because lower_bound()
+ * does pretty much exactly that too.
+ */
+ {
+ Datum val;
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ /* Is the first (smallest) value after the BRIN range? */
+ val = array->elements[0];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[1]);
+
+ /* minval > max(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /* Is the last (largest) value before the BRIN range? */
+ val = array->elements[array->nelements-1];
+
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[0]);
+
+ /* maxval < min(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /*
+ * OK, there might be some values matching the range. We have
+ * to search them one by one, or perhaps try binsearch.
+ */
+ type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(array->elements, array->nelements, column->bv_values[0], &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * OK, the first element must match the upper boundary too
+ * (if it does not, no following elements can).
+ */
+ val = array->elements[lower];
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0],
+ val);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ val);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ /*
+ * Check the first (smallest) value in the array - at least this
+ * value has to be smaller than the range maxval.
+ */
+ finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
+ array->elements[0]);
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = 0;
break;
- /* max() >= scankey */
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- BTGreaterEqualStrategyNumber);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
- value);
- break;
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
- finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1],
- value);
- break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = 0;
- break;
+ }
}
PG_RETURN_DATUM(matches);
diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c
index 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 66b73c3900..6638552bd0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8496,6 +8496,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)
--
2.39.1
[text/x-patch] 0006-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230215.patch (26.9K, ../[email protected]/7-0006-Support-SK_SEARCHARRAY-in-BRIN-minmax-multi-20230215.patch)
download | inline diff:
From 7bbafab1085dea175ef7e54ac8513c633fe77461 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 19:43:38 +0100
Subject: [PATCH 6/9] Support SK_SEARCHARRAY in BRIN minmax-multi
Similar approach to minmax, but the issues with deconstructing the array
over and over are even more serious.
---
src/backend/access/brin/brin_minmax_multi.c | 439 +++++++++++++++++---
src/include/catalog/pg_amproc.dat | 57 +++
src/include/catalog/pg_proc.dat | 4 +
3 files changed, 434 insertions(+), 66 deletions(-)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 859e0022fb..b70116d33b 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -109,6 +109,14 @@
#define MINMAX_BUFFER_MAX 8192
#define MINMAX_BUFFER_LOAD_FACTOR 0.5
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */
+
+
typedef struct MinmaxMultiOpaque
{
FmgrInfo extra_procinfos[MINMAX_MAX_PROCNUMS];
@@ -2562,6 +2570,132 @@ brin_minmax_multi_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(modified);
}
+
+static int
+compare_array_values(const void *a, const void *b, void *arg)
+{
+ Datum da = * (Datum *) a;
+ Datum db = * (Datum *) b;
+ SortSupport ssup = (SortSupport) arg;
+
+ return ApplySortComparator(da, false, db, false, ssup);
+}
+
+/*
+ * lower_boundary
+ * Determine lowest index so that (values[index] >= minvalue).
+ *
+ * The array of values is expected to be sorted, so this is the first value
+ * that may fall into the [minvalue, maxvalue] range, as it exceeds minval.
+ * It's not guaranteed, though, as it might exceed maxvalue too.
+ */
+static int
+lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup)
+{
+ int start = 0,
+ end = (nvalues - 1);
+
+ /* everything exceeds minval and might match */
+ if (compare_array_values(&minvalue, &values[start], ssup) <= 0)
+ return 0;
+
+ /* nothing could match */
+ if (compare_array_values(&minvalue, &values[end], ssup) > 0)
+ return nvalues;
+
+ while ((end - start) > 0)
+ {
+ int midpoint;
+ int r;
+
+ midpoint = start + (end - start) / 2;
+
+ r = compare_array_values(&minvalue, &values[midpoint], ssup);
+
+ if (r > 0)
+ start = Max(midpoint, start + 1);
+ else
+ end = midpoint;
+ }
+
+ /* the value should meet the (v >=minvalue) requirement */
+ Assert(compare_array_values(&values[start], &minvalue, ssup) >= 0);
+
+ /* we know start can't be 0, so it's legal to subtract 1 */
+ Assert(compare_array_values(&values[start-1], &minvalue, ssup) < 0);
+
+ return start;
+}
+
+typedef struct ScanKeyArray {
+ Oid typeid;
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_minmax_multi_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+ TypeCacheEntry *type;
+ SortSupportData ssup;
+
+ /* ignore scalar keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ qsort_interruptible(elem_values, num_elems, sizeof(Datum),
+ compare_array_values, &ssup);
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->typeid = ARR_ELEMTYPE(arrayval);
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_SORTED),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's min/max
@@ -2591,6 +2725,15 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
serialized = (SerializedRanges *) PG_DETOAST_DATUM(column->bv_values[0]);
ranges = brin_range_deserialize(serialized->maxvalues, serialized);
+ /*
+ * XXX Would it make sense to have a quick initial check on the whole
+ * summary? We know most page ranges are not expected to match, and we
+ * know the ranges/values are sorted so we could check global min/max
+ * (essentially what regular minmax is doing) and bail if no match is
+ * possible. That should be cheap and might save a lot on inspecting
+ * the individual ranges/values.
+ */
+
/* inspect the ranges, and for each one evaluate the scan keys */
for (rangeno = 0; rangeno < ranges->nranges; rangeno++)
{
@@ -2611,67 +2754,179 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
- {
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* first value from the array */
- matches = FunctionCall2Coll(finfo, colloid, minval, value);
- break;
- case BTEqualStrategyNumber:
- {
- Datum compar;
- FmgrInfo *cmpFn;
+ if (likely(!(key->sk_flags & SK_BRIN_SORTED)))
+ {
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* first value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, minval, value);
+ break;
- /* by default this range does not match */
- matches = BoolGetDatum(false);
+ case BTEqualStrategyNumber:
+ {
+ Datum compar;
+ FmgrInfo *cmpFn;
+
+ /* by default this range does not match */
+ matches = BoolGetDatum(false);
+
+ /*
+ * Otherwise, need to compare the new value with
+ * boundaries of all the ranges. First check if it's
+ * less than the absolute minimum, which is the first
+ * value in the array.
+ */
+ cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterStrategyNumber);
+ compar = FunctionCall2Coll(cmpFn, colloid, minval, value);
+
+ /* smaller than the smallest value in this range */
+ if (DatumGetBool(compar))
+ break;
+
+ cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessStrategyNumber);
+ compar = FunctionCall2Coll(cmpFn, colloid, maxval, value);
+
+ /* larger than the largest value in this range */
+ if (DatumGetBool(compar))
+ break;
+
+ /*
+ * We haven't managed to eliminate this range, so
+ * consider it matching.
+ */
+ matches = BoolGetDatum(true);
- /*
- * 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;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, maxval, value);
+ break;
- cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- BTLessStrategyNumber);
- compar = FunctionCall2Coll(cmpFn, colloid, maxval, value);
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) value;
- /* larger than the largest value in this range */
- if (DatumGetBool(compar))
- break;
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* first value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, minval,
+ array->elements[array->nelements-1]);
+ break;
+
+ case BTEqualStrategyNumber:
/*
- * We haven't managed to eliminate this range, so
- * consider it matching.
+ * See brin_minmax.c for description of what this is doing.
*/
- matches = BoolGetDatum(true);
-
+ {
+ Datum val;
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ /* Is the first (smallest) value after the BRIN range? */
+ val = array->elements[0];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, maxval);
+
+ /* minval > max(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /* Is the last (largest) value before the BRIN range? */
+ val = array->elements[array->nelements-1];
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, val, minval);
+
+ /* maxval < min(range values) */
+ if (!DatumGetBool(matches))
+ break;
+
+ /*
+ * OK, there might be some values matching the range. We have
+ * to search them one by one, or perhaps try binsearch.
+ */
+ type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+
+ ssup.ssup_collation = key->sk_collation;
+ ssup.ssup_cxt = CurrentMemoryContext;
+
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(array->elements, array->nelements, minval, &ssup);
+
+ /* no elements can possibly match */
+ if (lower == array->nelements)
+ {
+ matches = BoolGetDatum(false);
+ break;
+ }
+
+ /*
+ * OK, the first element must match the upper boundary too
+ * (if it does not, no following elements can).
+ */
+ val = array->elements[lower];
+
+ /*
+ * In the equality case (WHERE col = someval), we want to return
+ * the current page range if the minimum value in the range <=
+ * scan key, and the maximum value >= scan key.
+ */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTLessEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, minval, val);
+ if (!DatumGetBool(matches))
+ break;
+ /* max() >= scankey */
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ BTGreaterEqualStrategyNumber);
+ matches = FunctionCall2Coll(finfo, colloid, maxval, val);
+ break;
+ }
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ /* last value from the array */
+ matches = FunctionCall2Coll(finfo, colloid, maxval,
+ array->elements[0]);
break;
- }
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- /* last value from the array */
- matches = FunctionCall2Coll(finfo, colloid, maxval, value);
- break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = BoolGetDatum(false);
- break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
}
/* the range has to match all the scan keys */
@@ -2713,24 +2968,76 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS)
attno = key->sk_attno;
subtype = key->sk_subtype;
value = key->sk_argument;
- switch (key->sk_strategy)
+ if (likely(!(key->sk_flags & SK_SEARCHARRAY)))
{
- case BTLessStrategyNumber:
- case BTLessEqualStrategyNumber:
- case BTEqualStrategyNumber:
- case BTGreaterEqualStrategyNumber:
- case BTGreaterStrategyNumber:
-
- finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
- key->sk_strategy);
- matches = FunctionCall2Coll(finfo, colloid, val, value);
- break;
+ switch (key->sk_strategy)
+ {
+ case BTLessStrategyNumber:
+ case BTLessEqualStrategyNumber:
+ case BTEqualStrategyNumber:
+ case BTGreaterEqualStrategyNumber:
+ case BTGreaterStrategyNumber:
+
+ finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype,
+ key->sk_strategy);
+ matches = FunctionCall2Coll(finfo, colloid, val, value);
+ break;
- default:
- /* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- matches = BoolGetDatum(false);
- break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid strategy number %d", key->sk_strategy);
+ matches = BoolGetDatum(false);
+ break;
+ }
+ }
+ else
+ {
+ /*
+ * FIXME This is really wrong, because it deserializes the
+ * array over and over for each value in the minmax-multi
+ * summary.
+ */
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+
+ SortSupportData ssup;
+ int lower;
+ TypeCacheEntry *type;
+
+ 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);
+
+ /* assume not maches */
+ matches = BoolGetDatum(false);
+
+ /*
+ * 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(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR);
+
+ memset(&ssup, 0, sizeof(SortSupportData));
+ PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup);
+
+ lower = lower_boundary(elem_values, num_elems, value, &ssup);
+
+ if ((lower < num_elems) &&
+ (compare_array_values(&elem_values[lower], &value, &ssup) == 0))
+ {
+ matches = BoolGetDatum(true);
+ }
}
/* 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 6638552bd0..4e8d666864 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8520,6 +8520,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',
--
2.39.1
[text/x-patch] 0007-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230215.patch (14.2K, ../[email protected]/8-0007-Support-SK_SEARCHARRAY-in-BRIN-inclusion-20230215.patch)
download | inline diff:
From 6b5190a3d564ea75f144bc74160e2443f21760ae Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 15:23:25 +0100
Subject: [PATCH 7/9] Support SK_SEARCHARRAY in BRIN inclusion
---
src/backend/access/brin/brin_inclusion.c | 221 +++++++++++++++++------
src/include/catalog/pg_amproc.dat | 9 +
src/include/catalog/pg_proc.dat | 4 +
3 files changed, 180 insertions(+), 54 deletions(-)
diff --git a/src/backend/access/brin/brin_inclusion.c b/src/backend/access/brin/brin_inclusion.c
index 248116c149..0f409ef527 100644
--- a/src/backend/access/brin/brin_inclusion.c
+++ b/src/backend/access/brin/brin_inclusion.c
@@ -30,6 +30,7 @@
#include "access/skey.h"
#include "catalog/pg_amop.h"
#include "catalog/pg_type.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
@@ -72,6 +73,13 @@
#define INCLUSION_UNMERGEABLE 1
#define INCLUSION_CONTAINS_EMPTY 2
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_ARRAY 0x00010000 /* deconstructed array */
+
typedef struct InclusionOpaque
{
@@ -238,44 +246,74 @@ brin_inclusion_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(true);
}
+typedef struct ScanKeyArray {
+ int nelements;
+ Datum *elements;
+} ScanKeyArray;
+
+Datum
+brin_inclusion_preprocess(PG_FUNCTION_ARGS)
+{
+ // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ ScanKey newkey;
+ ScanKeyArray *scanarray;
+
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+
+ /* ignore scalar keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ PG_RETURN_POINTER(key);
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ scanarray = palloc0(sizeof(ScanKeyArray));
+ scanarray->nelements = num_elems;
+ scanarray->elements = elem_values;
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_ARRAY),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(scanarray));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
- * BRIN inclusion consistent function
+ * Check consistency of a single scalar value with the BRIN range.
*
- * 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.
+ * Called for both scalar scankeys and for each value in SK_SEARCHARRAY.
*/
-Datum
-brin_inclusion_consistent(PG_FUNCTION_ARGS)
+static bool
+brin_inclusion_consistent_value(BrinDesc *bdesc, BrinValues *column,
+ AttrNumber attno,
+ StrategyNumber strategy, Oid subtype,
+ Oid colloid, Datum unionval, Datum query)
{
- BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
- BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
- ScanKey key = (ScanKey) PG_GETARG_POINTER(2);
- Oid colloid = PG_GET_COLLATION(),
- subtype;
- Datum unionval;
- AttrNumber attno;
- Datum query;
FmgrInfo *finfo;
Datum result;
- /* This opclass uses the old signature with only three arguments. */
- Assert(PG_NARGS() == 3);
-
- /* Should not be dealing with all-NULL ranges. */
- Assert(!column->bv_allnulls);
-
- /* It has to be checked, if it contains elements that are not mergeable. */
- if (DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
- PG_RETURN_BOOL(true);
-
- attno = key->sk_attno;
- subtype = key->sk_subtype;
- query = key->sk_argument;
- unionval = column->bv_values[INCLUSION_UNION];
- switch (key->sk_strategy)
+ switch (strategy)
{
/*
* Placement strategies
@@ -294,49 +332,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 +390,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 +412,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 +431,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 +466,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 +476,105 @@ brin_inclusion_consistent(PG_FUNCTION_ARGS)
RTContainsStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
case RTGreaterEqualStrategyNumber:
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
if (!DatumGetBool(result))
- PG_RETURN_BOOL(true);
+ return (true);
- PG_RETURN_DATUM(column->bv_values[INCLUSION_CONTAINS_EMPTY]);
+ return (column->bv_values[INCLUSION_CONTAINS_EMPTY]);
case RTGreaterStrategyNumber:
/* no need to check for empty elements */
finfo = inclusion_get_strategy_procinfo(bdesc, attno, subtype,
RTLeftStrategyNumber);
result = FunctionCall2Coll(finfo, colloid, unionval, query);
- PG_RETURN_BOOL(!DatumGetBool(result));
+ return (!DatumGetBool(result));
default:
/* shouldn't happen */
- elog(ERROR, "invalid strategy number %d", key->sk_strategy);
- PG_RETURN_BOOL(false);
+ elog(ERROR, "invalid strategy number %d", strategy);
+ return (false);
+ }
+}
+
+/*
+ * BRIN inclusion consistent function
+ *
+ * We're no longer dealing with NULL keys in the consistent function, that is
+ * now handled by the AM code. That means we should not get any all-NULL ranges
+ * either, because those can't be consistent with regular (not [IS] NULL) keys.
+ *
+ * All of the strategies are optional.
+ */
+Datum
+brin_inclusion_consistent(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(2);
+ Oid colloid = PG_GET_COLLATION(),
+ subtype;
+ Datum unionval;
+ AttrNumber attno;
+ Datum query;
+
+ /* This opclass uses the old signature with only three arguments. */
+ Assert(PG_NARGS() == 3);
+
+ /* Should not be dealing with all-NULL ranges. */
+ Assert(!column->bv_allnulls);
+
+ /* It has to be checked, if it contains elements that are not mergeable. */
+ if (DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
+ PG_RETURN_BOOL(true);
+
+ attno = key->sk_attno;
+ subtype = key->sk_subtype;
+ query = key->sk_argument;
+ unionval = column->bv_values[INCLUSION_UNION];
+
+ /*
+ * For regular (scalar) scan keys, we simply compare the value to the
+ * range min/max values, and we're done. For SK_SEARCHARRAY keys we
+ * need to deparse the array and loop through the values.
+ */
+ if (likely(!(key->sk_flags & SK_SEARCHARRAY)))
+ {
+ bool tmp;
+
+ tmp = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, query);
+ PG_RETURN_BOOL(tmp);
+ }
+ else
+ {
+ ScanKeyArray *array = (ScanKeyArray *) query;
+ bool matches = false;
+
+ /* have to loop through all elements, having them sorted does not help */
+ for (int i = 0; i < array->nelements; i++)
+ {
+ Datum query_element = array->elements[i];
+
+ matches = brin_inclusion_consistent_value(bdesc, column, attno,
+ key->sk_strategy,
+ subtype, colloid,
+ unionval, query_element);
+
+ if (matches)
+ break;
+ }
+
+ /* we could get here for empty array, e.g. with "@> '{}'::point[]" */
+ PG_RETURN_BOOL(matches);
}
}
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 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 4e8d666864..753c41d5cd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8610,6 +8610,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',
--
2.39.1
[text/x-patch] 0008-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230215.patch (20.0K, ../[email protected]/9-0008-Support-SK_SEARCHARRAY-in-BRIN-bloom-20230215.patch)
download | inline diff:
From 2cf5d136f6a80ca2cfc703c54566fe636b41ca28 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 11 Feb 2023 20:50:03 +0100
Subject: [PATCH 8/9] Support SK_SEARCHARRAY in BRIN bloom
---
src/backend/access/brin/brin_bloom.c | 157 ++++++++++++++++++++++-----
src/include/catalog/pg_amproc.dat | 60 ++++++++++
src/include/catalog/pg_proc.dat | 3 +
3 files changed, 194 insertions(+), 26 deletions(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index 4ff80aeb0c..48b9847bb2 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -125,9 +125,11 @@
#include "access/stratnum.h"
#include "catalog/pg_type.h"
#include "catalog/pg_amop.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -151,6 +153,13 @@
*/
#define PROCNUM_BASE 11
+/*
+ * We use some private sk_flags bits in preprocessed scan keys. We're allowed
+ * to use bits 16-31 (see skey.h). The uppermost bits are copied from the
+ * index's indoption[] array entry for the index attribute.
+ */
+#define SK_BRIN_HASHES 0x00010000 /* deconstructed array, calculated hashes */
+
/*
* Storage type for BRIN's reloptions.
*/
@@ -402,21 +411,14 @@ bloom_add_value(BloomFilter *filter, uint32 value, bool *updated)
return filter;
}
-
/*
* bloom_contains_value
* Check if the bloom filter contains a particular value.
*/
static bool
-bloom_contains_value(BloomFilter *filter, uint32 value)
+bloom_contains_hashes(BloomFilter *filter, uint64 h1, uint64 h2)
{
int i;
- uint64 h1,
- h2;
-
- /* calculate the two hashes */
- h1 = hash_bytes_uint32_extended(value, BLOOM_SEED_1) % filter->nbits;
- h2 = hash_bytes_uint32_extended(value, BLOOM_SEED_2) % filter->nbits;
/* compute the requested number of hashes */
for (i = 0; i < filter->nhashes; i++)
@@ -590,6 +592,99 @@ brin_bloom_add_value(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(updated);
}
+typedef struct HashCache {
+ int nelements;
+ uint64 *h1;
+ uint64 *h2;
+} HashCache;
+
+Datum
+brin_bloom_preprocess(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ ScanKey key = (ScanKey) PG_GETARG_POINTER(1);
+ BloomOptions *opts = (BloomOptions *) PG_GET_OPCLASS_OPTIONS();
+ ScanKey newkey;
+ HashCache *cache = palloc0(sizeof(HashCache));
+
+ int nbits;
+ FmgrInfo *finfo;
+ uint32 hashValue;
+
+ /* we'll need to calculate hashes, so get the proc */
+ finfo = bloom_get_procinfo(bdesc, key->sk_attno, PROCNUM_HASH);
+
+ /*
+ * We don't have a filter from any range yet, so we just re-calculate
+ * the size (number of bits) just like bloom_init.
+ */
+ bloom_filter_size(brin_bloom_get_ndistinct(bdesc, opts),
+ BloomGetFalsePositiveRate(opts),
+ NULL, &nbits, NULL);
+
+ /* precalculate the hash even for simple scan keys */
+ if (!(key->sk_flags & SK_SEARCHARRAY))
+ {
+ Datum value = key->sk_argument;
+
+ cache->nelements = 1;
+ cache->h1 = (uint64 *) palloc0(sizeof(uint64));
+ cache->h2 = (uint64 *) palloc0(sizeof(uint64));
+
+ hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, key->sk_collation, value));
+
+ cache->h1[0] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_1) % nbits;
+ cache->h2[0] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_2) % nbits;
+ }
+ else
+ {
+ ArrayType *arrayval;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int num_elems;
+ Datum *elem_values;
+ bool *elem_nulls;
+
+ arrayval = DatumGetArrayTypeP(key->sk_argument);
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arrayval),
+ &elmlen, &elmbyval, &elmalign);
+
+ deconstruct_array(arrayval,
+ ARR_ELEMTYPE(arrayval),
+ elmlen, elmbyval, elmalign,
+ &elem_values, &elem_nulls, &num_elems);
+
+ cache->nelements = 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];
+
+ hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, key->sk_collation, element));
+
+ cache->h1[i] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_1) % nbits;
+ cache->h2[i] = hash_bytes_uint32_extended(hashValue, BLOOM_SEED_2) % nbits;
+ }
+ }
+
+ newkey = palloc0(sizeof(ScanKeyData));
+
+ ScanKeyEntryInitializeWithInfo(newkey,
+ (key->sk_flags | SK_BRIN_HASHES),
+ key->sk_attno,
+ key->sk_strategy,
+ key->sk_subtype,
+ key->sk_collation,
+ &key->sk_func,
+ PointerGetDatum(cache));
+
+ PG_RETURN_POINTER(newkey);
+}
+
/*
* Given an index tuple corresponding to a certain page range and a scan key,
* return whether the scan key is consistent with the index tuple's bloom
@@ -598,16 +693,10 @@ brin_bloom_add_value(PG_FUNCTION_ARGS)
Datum
brin_bloom_consistent(PG_FUNCTION_ARGS)
{
- BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
ScanKey *keys = (ScanKey *) PG_GETARG_POINTER(2);
int nkeys = PG_GETARG_INT32(3);
- Oid colloid = PG_GET_COLLATION();
- AttrNumber attno;
- Datum value;
bool matches;
- FmgrInfo *finfo;
- uint32 hashValue;
BloomFilter *filter;
int keyno;
@@ -621,26 +710,42 @@ brin_bloom_consistent(PG_FUNCTION_ARGS)
for (keyno = 0; keyno < nkeys; keyno++)
{
ScanKey key = keys[keyno];
+ HashCache *cache = (HashCache *) key->sk_argument;
/* NULL keys are handled and filtered-out in bringetbitmap */
Assert(!(key->sk_flags & SK_ISNULL));
- attno = key->sk_attno;
- value = key->sk_argument;
+ /*
+ * Keys should be preprocessed into a hash cache (even a single
+ * value scan keys, not just SK_SEARCHARRAY ones).
+ */
+ Assert(key->sk_flags & SK_BRIN_HASHES);
switch (key->sk_strategy)
{
case BloomEqualStrategyNumber:
-
- /*
- * We want to return the current page range if the bloom filter
- * seems to contain the value.
- */
- finfo = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH);
-
- hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, colloid, value));
- matches &= bloom_contains_value(filter, hashValue);
-
+ {
+ /* 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).
+ */
+ for (int i = 0; i < cache->nelements; i++)
+ {
+ bool tmp = false;
+
+ tmp = bloom_contains_hashes(filter, cache->h1[i], cache->h2[i]);
+
+ /* if we found a matching value, we have a match */
+ if (DatumGetBool(tmp))
+ {
+ matches = BoolGetDatum(true);
+ break;
+ }
+ }
+ }
break;
default:
/* shouldn't happen */
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 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 753c41d5cd..4325229c9d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8633,6 +8633,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',
--
2.39.1
[text/x-python] brin.py (2.3K, ../[email protected]/10-brin.py)
download | inline:
import psycopg2
import sys
import time
conn = psycopg2.connect('host=localhost dbname=test')
conn.set_session(autocommit=True)
nrows = 10000000
for t in ['int', 'text']:
for o in ['minmax', 'bloom']:
for d in [int(nrows*5/100), int(nrows/100)]:
if t == 'int':
cur = conn.cursor()
cur.execute('drop table if exists t')
cur.execute('create table t (a int)')
cur.execute('insert into t select * from (select i from generate_series(1,%d) s(i)) foo order by (i + %d * random() / 100.0)' % (nrows, d))
if o == 'minmax':
cur.execute('create index on t using brin (a int4_minmax_ops) with (pages_per_range=1)')
else:
cur.execute('create index on t using brin (a int4_bloom_ops(n_distinct_per_range=200)) with (pages_per_range=1)')
cur.execute('vacuum analyze t')
cur.close()
else:
cur = conn.cursor()
cur.execute('drop table if exists t')
cur.execute('create table t (a text)')
cur.execute('insert into t select a from (select row_number() over (order by a) as i, a from (select md5(i::text) a from generate_series(1,%d) s(i) order by 1) foo) bar order by (i + %d * random() / 100.0)' % (nrows, d))
if o == 'minmax':
cur.execute('create index on t using brin (a text_minmax_ops) with (pages_per_range=1)')
else:
cur.execute('create index on t using brin (a text_bloom_ops(n_distinct_per_range=250)) with (pages_per_range=1)')
cur.execute('vacuum analyze t')
cur.close()
cnt = sum([1, 10, 20, 50, 100, 500]) * 10
cur = conn.cursor()
cur.execute('select a from t order by random() limit %d' % (cnt,))
values = [str(v[0]) for v in cur.fetchall()]
cur.close()
# 10 runs
for r in range(0,10):
# number of values in the lists
for v in [1, 10, 20, 50, 100, 500]:
vals = values[:v]
values = values[v:]
if len(vals) != v:
print ('incorrect length', len(vals))
sys.exit(1)
if t == 'text':
vals = "'" + "','".join(vals) + "'"
else:
vals = ','.join(vals)
cur = conn.cursor()
cur.execute('set enable_seqscan = off')
cur.execute('set max_parallel_workers_per_gather = 0')
query = 'explain (analyze, timing off) select * from t where a in (%s)' % (vals,)
s = time.time()
cur.execute(query)
e = time.time()
cur.close()
print(t, o, r, d, v, round(1000 * (e-s),3))
sys.stdout.flush()
view thread (5+ 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]
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