From 49606760ca8d119f2794626620a67dd568795015 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 17 Feb 2023 02:45:14 +0100 Subject: [PATCH 3/5] Support SK_SEARCHARRAY in BRIN minmax-multi Similar approach to minmax, but the issues with deconstructing the array over and over are even more serious. --- src/backend/access/brin/brin_minmax_multi.c | 770 ++++++++++++++-- src/include/catalog/pg_amproc.dat | 57 ++ src/include/catalog/pg_proc.dat | 4 + src/test/regress/expected/brin_multi.out | 926 ++++++++++++++++++++ src/test/regress/sql/brin_multi.sql | 301 +++++++ 5 files changed, 1994 insertions(+), 64 deletions(-) diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c index f8b2a3f9bc..a8c65bfc20 100644 --- a/src/backend/access/brin/brin_minmax_multi.c +++ b/src/backend/access/brin/brin_minmax_multi.c @@ -109,6 +109,14 @@ #define MINMAX_BUFFER_MAX 8192 #define MINMAX_BUFFER_LOAD_FACTOR 0.5 +/* + * We use some private sk_flags bits in preprocessed scan keys. We're allowed + * to use bits 16-31 (see skey.h). The uppermost bits are copied from the + * index's indoption[] array entry for the index attribute. + */ +#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */ + + typedef struct MinmaxMultiOpaque { FmgrInfo extra_procinfos[MINMAX_MAX_PROCNUMS]; @@ -2568,6 +2576,197 @@ brin_minmax_multi_add_value(PG_FUNCTION_ARGS) PG_RETURN_BOOL(modified); } +/* + * preprocessing of scan keys for the minmax multi opclass + * + * For now we care only about array keys - instead of simple linear search + * in the array (for each range), we sort the arrays during preprocessing + * and then use that for binary sort in consistent function. + * + * FIXME A lot of this is exactly the same as for plain minmax, maybe we + * can share that somehow. + */ + +/* qsort comparator used to sort array elements */ +static int +minmax_multi_compare_values(const void *a, const void *b, void *arg) +{ + Datum da = * (Datum *) a; + Datum db = * (Datum *) b; + SortSupport ssup = (SortSupport) arg; + + return ApplySortComparator(da, false, db, false, ssup); +} + +/* + * Deconstructed and sorted scan key array (we might build ArrayType, but then + * we'd have to deconstruct it over and over for each page range). So we just + * do it once during preprocessing. + */ +typedef struct ScanKeyArray { + Oid typeid; + int nelements; + Datum *elements; +} ScanKeyArray; + +/* + * minmax_multi_lower_boundary + * Given a value, determine the minimum index so that (array[index] >= value) + * + * We use this to check if a minmax range [minvalue, maxvalue] intersects with + * the array in the scan key (which is expected to be sorted). We calculate + * the lower boundary for [minvalue] and then check if it actually falls under + * the maxvalue too. If yes, we found an element consistent with the page range. + * + * If all array elements match (i.e. all elements >= value), returns 0. If no + * elements match (i.e. all elements < 0), returns nvalues. + */ +static int +minmax_multi_lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup) +{ + int start = 0, + end = (nvalues - 1); + + /* everything exceeds minval and might match */ + if (minmax_multi_compare_values(&minvalue, &values[start], ssup) <= 0) + return 0; + + /* nothing could match */ + if (minmax_multi_compare_values(&minvalue, &values[end], ssup) > 0) + return nvalues; + + while ((end - start) > 0) + { + int midpoint; + int r; + + midpoint = start + (end - start) / 2; + + r = minmax_multi_compare_values(&minvalue, &values[midpoint], ssup); + + if (r > 0) + start = Max(midpoint, start + 1); + else + end = midpoint; + } + + /* the value should meet the (v >=minvalue) requirement */ + Assert(minmax_multi_compare_values(&values[start], &minvalue, ssup) >= 0); + + /* we know start can't be 0, so it's legal to subtract 1 */ + Assert(minmax_multi_compare_values(&values[start-1], &minvalue, ssup) < 0); + + return start; +} + +/* + * brin_multi_minmax_preprocess + * preprocess scan keys for the minmax multi opclass + * + * For now we just care about SK_SEARCHARRAY keys, which we sort and keep the + * deconstructed array. All other scan keys are ignored (returned as is). + * + * XXX Do we need to remember if the array contained NULL values? + */ +Datum +brin_minmax_multi_preprocess(PG_FUNCTION_ARGS) +{ + ScanKey key = (ScanKey) PG_GETARG_POINTER(1); + ScanKey newkey; + ScanKeyArray *scanarray; + + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + TypeCacheEntry *type; + SortSupportData ssup; + + /* number of non-null elements in the array */ + int num_nonnulls; + + /* + * ignore scalar keys (just return the original scan key) + * + * XXX Maybe we should preprocess scalar keys too, and treat them as arrays + * with a single element. It'd make the consistent function simpler by not + * having to do branching. + */ + if (!(key->sk_flags & SK_SEARCHARRAY)) + PG_RETURN_POINTER(key); + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* eliminate NULL elements */ + num_nonnulls = 0; + for (int i = 0; i < num_elems; i++) + { + /* skip NULL elements */ + if (elem_nulls[i]) + continue; + + /* if needed, move the non-NULL ones */ + if (num_nonnulls != i) + elem_values[num_nonnulls] = elem_values[i]; + + num_nonnulls++; + } + + num_elems = num_nonnulls; + + /* FIXME What if num_nonnulls is 0? Can it even happen / get here? */ + + /* + * sort the array + * + * XXX Should we walk the sorted array again and eliminate duplicate values? + * Seems unnecessary - we're not going to repalloc/release the memory anyway + * and it's unlikely to speed up the binsearch (unless there's a lot of + * duplicate values, which does not seem plausible/common). + */ + type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + qsort_interruptible(elem_values, num_elems, sizeof(Datum), + minmax_multi_compare_values, &ssup); + + /* Construct the new scan key, with sorted array as ScanKeyArray. */ + scanarray = palloc0(sizeof(ScanKeyArray)); + scanarray->typeid = ARR_ELEMTYPE(arrayval); + scanarray->nelements = num_elems; + scanarray->elements = elem_values; + + newkey = palloc0(sizeof(ScanKeyData)); + + ScanKeyEntryInitializeWithInfo(newkey, + (key->sk_flags | SK_BRIN_SORTED), + key->sk_attno, + key->sk_strategy, + key->sk_subtype, + key->sk_collation, + &key->sk_func, + PointerGetDatum(scanarray)); + + PG_RETURN_POINTER(newkey); +} + /* * Given an index tuple corresponding to a certain page range and a scan key, * return whether the scan key is consistent with the index tuple's min/max @@ -2590,13 +2789,21 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS) Ranges *ranges; int keyno; int rangeno; - int i; attno = column->bv_attno; serialized = (SerializedRanges *) PG_DETOAST_DATUM(column->bv_values[0]); ranges = brin_range_deserialize(serialized->maxvalues, serialized); + /* + * XXX Would it make sense to have a quick initial check on the whole + * summary? We know most page ranges are not expected to match, and we + * know the ranges/values are sorted so we could check global min/max + * (essentially what regular minmax is doing) and bail if no match is + * possible. That should be cheap and might save a lot on inspecting + * the individual ranges/values. + */ + /* inspect the ranges, and for each one evaluate the scan keys */ for (rangeno = 0; rangeno < ranges->nranges; rangeno++) { @@ -2617,67 +2824,349 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS) attno = key->sk_attno; subtype = key->sk_subtype; value = key->sk_argument; - switch (key->sk_strategy) + + /* + * For regular (scalar) scan keys, we simply compare the value to the + * range min/max values, and we're done. For preprocessed SK_SEARCHARRAY + * keys we can do a binary search in the sorted array. + * + * FIXME This should also handle the "array but not preprocessed" case + * too, for opclasses not defining the optional preprocess procedure. + * Otherwise we'd have issues with such opclasses because amsearcharray + * is defined at the AM level. + */ + if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/ { - case BTLessStrategyNumber: - case BTLessEqualStrategyNumber: - finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - /* first value from the array */ - matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, value)); - break; + ScanKeyArray *array = (ScanKeyArray *) value; - case BTEqualStrategyNumber: - { - Datum compar; - FmgrInfo *cmpFn; + /* can happen if the IN list contained just NULLs */ + if (array->nelements == 0) + PG_RETURN_BOOL(false); - /* by default this range does not match */ - matches = false; + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* first value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, + array->elements[array->nelements-1])); + break; + + case BTEqualStrategyNumber: /* - * Otherwise, need to compare the new value with - * boundaries of all the ranges. First check if it's - * less than the absolute minimum, which is the first - * value in the array. + * In the equality case (WHERE col = someval), we want to + * return the current page range if the minimum value in + * the range <= key, and the maximum value >= key. + * + * We do this in two phases. We check the array min/max + * values to see if there even can be a matching value, + * and if yes we do a binary search to find the first + * value that exceeds range minval. And then we check if + * it actually matches the range. + * + * XXX The first phase is probably unnecessary, because + * lower_bound() does pretty much exactly that too. */ - cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, - BTGreaterStrategyNumber); - compar = FunctionCall2Coll(cmpFn, colloid, minval, value); + { + TypeCacheEntry *type; + SortSupportData ssup; + + Datum element; + int lower; + + /* + * Before doing the binary search on sorted array, do + * a quick search if the range can match at all. If + * the whole array is outside the page range, we're + * done. This is much cheaper than the binsearch, and + * we assume most ranges do not match. + */ + + /* + * Is the first (smallest) array element after the BRIN + * page range? + */ + element = array->elements[0]; + + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, + element, maxval)); + + /* first element > range maxvalue */ + if (!matches) + break; + + /* + * Is the last (largest) array element before the BRIN + * page range? + */ + element = array->elements[array->nelements-1]; + + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, + element, minval)); + + /* last element < range minvalue */ + if (!matches) + break; + + /* + * It seems there might be some array elements + * consistent with the page range. We find the first + * element above the range minvalue and check if it's + * smaller than maxvalue. If yes, we have a match. If + * not, other elements can't be consistent either + * (will exceed maxval too, thanks to sort). + */ + type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + lower = minmax_multi_lower_boundary(array->elements, array->nelements, + minval, &ssup); + + /* + * If the lower boundary is nelements, then no array + * elements can possibly match this page range. + * + * XXX This is probably impossible due to the earlier + * check. This would mean all elements are < minvalue, + * but we did check for that. + */ + if (lower == array->nelements) + { + matches = false; + break; + } + + /* + * We have an element that might be consistent with + * the page range, so let's check the maxvalue too + * (if it exceeds it, no following elements will too). + */ + element = array->elements[lower]; + + /* + * In the equality case (WHERE col = someval), we + * want to return the current page range if the + * minimum value in the range <= scan key, and the + * maximum value >= scan key. + * + * XXX This minvalue check is likely unnecessary, + * thanks to the lower boundary guaranteeing this to + * be true. + */ + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, + minval, element)); + if (!matches) + break; + + /* maxvalue >= element */ + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, + maxval, element)); + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* last value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, + array->elements[0])); + break; + + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; + break; + } + } + else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */ + { + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* we'll skip NULL elements */ + for (int i = 0; i < num_elems; i++) + { + /* skip NULL elements */ + if (elem_nulls[i]) + continue; - /* smaller than the smallest value in this range */ - if (DatumGetBool(compar)) + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* first value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, + elem_values[i])); break; - cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, - BTLessStrategyNumber); - compar = FunctionCall2Coll(cmpFn, colloid, maxval, value); + case BTEqualStrategyNumber: + { + Datum compar; + FmgrInfo *cmpFn; + + /* by default this range does not match */ + matches = false; + + /* + * Otherwise, need to compare the new value with + * boundaries of all the ranges. First check if it's + * less than the absolute minimum, which is the first + * value in the array. + */ + cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterStrategyNumber); + compar = FunctionCall2Coll(cmpFn, colloid, minval, + elem_values[i]); + + /* smaller than the smallest value in this range */ + if (DatumGetBool(compar)) + break; + + cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTLessStrategyNumber); + compar = FunctionCall2Coll(cmpFn, colloid, maxval, + elem_values[i]); + + /* larger than the largest value in this range */ + if (DatumGetBool(compar)) + break; + + /* + * We haven't managed to eliminate this range, so + * consider it matching. + */ + matches = true; + + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* last value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, + elem_values[i])); + break; - /* larger than the largest value in this range */ - if (DatumGetBool(compar)) + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; break; + } - /* - * We haven't managed to eliminate this range, so - * consider it matching. - */ - matches = true; + /* found a consistent value, we're done */ + if (DatumGetBool(matches)) + break; + } + /* + * free the arrays + * + * XXX is this necessary? + */ + pfree(elem_values); + pfree(elem_nulls); + } + else /* scalar scan key */ + { + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* first value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, minval, value)); break; - } - case BTGreaterEqualStrategyNumber: - case BTGreaterStrategyNumber: - finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - /* last value from the array */ - matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, value)); - break; - default: - /* shouldn't happen */ - elog(ERROR, "invalid strategy number %d", key->sk_strategy); - matches = false; - break; + case BTEqualStrategyNumber: + { + Datum compar; + FmgrInfo *cmpFn; + + /* by default this range does not match */ + matches = false; + + /* + * Otherwise, need to compare the new value with + * boundaries of all the ranges. First check if it's + * less than the absolute minimum, which is the first + * value in the array. + */ + cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterStrategyNumber); + compar = FunctionCall2Coll(cmpFn, colloid, minval, value); + + /* smaller than the smallest value in this range */ + if (DatumGetBool(compar)) + break; + + cmpFn = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTLessStrategyNumber); + compar = FunctionCall2Coll(cmpFn, colloid, maxval, value); + + /* larger than the largest value in this range */ + if (DatumGetBool(compar)) + break; + + /* + * We haven't managed to eliminate this range, so + * consider it matching. + */ + matches = true; + + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* last value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, maxval, value)); + break; + + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; + break; + } } /* the range has to match all the scan keys */ @@ -2700,7 +3189,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS) * here, because we're dealing with serialized / fully compacted ranges, * so there should be only very few values. */ - for (i = 0; i < ranges->nvalues; i++) + for (int i = 0; i < ranges->nvalues; i++) { Datum val = ranges->values[2 * ranges->nranges + i]; @@ -2719,24 +3208,177 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS) attno = key->sk_attno; subtype = key->sk_subtype; value = key->sk_argument; - switch (key->sk_strategy) + + if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/ { - case BTLessStrategyNumber: - case BTLessEqualStrategyNumber: - case BTEqualStrategyNumber: - case BTGreaterEqualStrategyNumber: - case BTGreaterStrategyNumber: - - finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, value)); - break; + ScanKeyArray *array = (ScanKeyArray *) value; - default: - /* shouldn't happen */ - elog(ERROR, "invalid strategy number %d", key->sk_strategy); - matches = false; - break; + /* can happen if the IN list contained just NULLs */ + if (array->nelements == 0) + PG_RETURN_BOOL(false); + + /* + * XXX We should be able to be smarter for the scalar values, as + * we keep them sorted too. So we should be able to quickly check + * if any of the values can match the sorted key values. + */ + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* first value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, + array->elements[array->nelements-1])); + break; + + case BTEqualStrategyNumber: + + /* + * See brin_minmax.c for description of what this is doing. + */ + { + SortSupportData ssup; + int lower; + TypeCacheEntry *type; + + /* + * OK, there might be some values matching the range. We have + * to search them one by one, or perhaps try binsearch. + */ + type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + lower = minmax_multi_lower_boundary(array->elements, array->nelements, + val, &ssup); + + /* no elements can possibly match */ + if (lower == array->nelements) + { + matches = false; + break; + } + + /* + * OK, check the first element must match the upper boundary too + * (if it does not, no following elements can). + * + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + BTEqualStrategyNumber); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, + array->elements[lower])); + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + /* last value from the array */ + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, + array->elements[0])); + break; + + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; + break; + } + } + else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */ + { + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* we'll skip NULL elements */ + for (int j = 0; j < num_elems; j++) + { + /* skip NULL elements */ + if (elem_nulls[j]) + continue; + + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + case BTEqualStrategyNumber: + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, + val, elem_values[j])); + + break; + + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; + break; + } + + /* found a consistent value, we're done */ + if (DatumGetBool(matches)) + break; + } + + /* + * free the arrays + * + * XXX is this necessary? + */ + pfree(elem_values); + pfree(elem_nulls); + } + else + { + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + case BTEqualStrategyNumber: + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + + finfo = minmax_multi_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = DatumGetBool(FunctionCall2Coll(finfo, colloid, val, value)); + break; + + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = false; + break; + } } /* the range has to match all the scan keys */ diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 166681c31e..4f17f0d58c 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -946,6 +946,9 @@ { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '11', amproc => 'brin_minmax_multi_distance_int2' }, @@ -965,6 +968,9 @@ { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '11', amproc => 'brin_minmax_multi_distance_int4' }, @@ -984,6 +990,9 @@ { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8', amprocrighttype => 'int8', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int8', amprocrighttype => 'int8', amprocnum => '11', amproc => 'brin_minmax_multi_distance_int8' }, @@ -1095,6 +1104,9 @@ { amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '11', amproc => 'brin_minmax_multi_distance_int4' }, @@ -1161,6 +1173,9 @@ { amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/tid_minmax_multi_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '11', amproc => 'brin_minmax_multi_distance_tid' }, @@ -1214,6 +1229,9 @@ { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4', amprocrighttype => 'float4', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4', amprocrighttype => 'float4', amprocnum => '11', amproc => 'brin_minmax_multi_distance_float4' }, @@ -1233,6 +1251,9 @@ { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '11', amproc => 'brin_minmax_multi_distance_float8' }, @@ -1305,6 +1326,9 @@ { amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr', amprocrighttype => 'macaddr', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr', amprocrighttype => 'macaddr', amprocnum => '11', amproc => 'brin_minmax_multi_distance_macaddr' }, @@ -1361,6 +1385,9 @@ { amprocfamily => 'brin/macaddr8_minmax_multi_ops', amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/macaddr8_minmax_multi_ops', + amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/macaddr8_minmax_multi_ops', amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '11', amproc => 'brin_minmax_multi_distance_macaddr8' }, @@ -1415,6 +1442,9 @@ { amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '11', amproc => 'brin_minmax_multi_distance_inet' }, @@ -1521,6 +1551,9 @@ { amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '11', amproc => 'brin_minmax_multi_distance_time' }, @@ -1604,6 +1637,9 @@ { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'timestamp', amprocrighttype => 'timestamp', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/datetime_minmax_multi_ops', + amproclefttype => 'timestamp', amprocrighttype => 'timestamp', + amprocnum => '6', amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'timestamp', amprocrighttype => 'timestamp', amprocnum => '11', amproc => 'brin_minmax_multi_distance_timestamp' }, @@ -1623,6 +1659,9 @@ { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/datetime_minmax_multi_ops', + amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', + amprocnum => '6', amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '11', amproc => 'brin_minmax_multi_distance_timestamp' }, @@ -1642,6 +1681,9 @@ { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/datetime_minmax_multi_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '11', amproc => 'brin_minmax_multi_distance_date' }, @@ -1733,6 +1775,9 @@ { amprocfamily => 'brin/interval_minmax_multi_ops', amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/interval_minmax_multi_ops', + amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/interval_minmax_multi_ops', amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '11', amproc => 'brin_minmax_multi_distance_interval' }, @@ -1789,6 +1834,9 @@ { amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz', amprocrighttype => 'timetz', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz', amprocrighttype => 'timetz', amprocnum => '11', amproc => 'brin_minmax_multi_distance_timetz' }, @@ -1875,6 +1923,9 @@ { amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric', amprocrighttype => 'numeric', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric', amprocrighttype => 'numeric', amprocnum => '11', amproc => 'brin_minmax_multi_distance_numeric' }, @@ -1929,6 +1980,9 @@ { amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '11', amproc => 'brin_minmax_multi_distance_uuid' }, @@ -2005,6 +2059,9 @@ { amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn', amprocrighttype => 'pg_lsn', amprocnum => '5', amproc => 'brin_minmax_multi_options' }, +{ amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '6', + amproc => 'brin_minmax_multi_preprocess' }, { amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn', amprocrighttype => 'pg_lsn', amprocnum => '11', amproc => 'brin_minmax_multi_distance_pg_lsn' }, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 8f17532094..04555746e6 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8551,6 +8551,10 @@ proname => 'brin_minmax_multi_options', proisstrict => 'f', prorettype => 'void', proargtypes => 'internal', prosrc => 'brin_minmax_multi_options' }, +{ oid => '9326', descr => 'BRIN multi minmax support', + proname => 'brin_minmax_multi_preprocess', proisstrict => 'f', + prorettype => 'internal', proargtypes => 'internal internal', + prosrc => 'brin_minmax_multi_preprocess' }, { oid => '4621', descr => 'BRIN multi minmax int2 distance', proname => 'brin_minmax_multi_distance_int2', prorettype => 'float8', diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index 9f46934c9b..d774cd91a1 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -823,3 +823,929 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525 DROP TABLE brin_test_multi_2; RESET enable_seqscan; +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_multi_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); +CREATE INDEX brin_in_test_multi_1_idx_1 ON brin_in_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_multi_1_idx_2 ON brin_in_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +-- int: equality +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = 113) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = 113) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{113,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{113,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{113,177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{113,177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{-113,-177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{-113,-177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{313,377}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{313,377}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{113,177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25); + count +------- + 24 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25); + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25); + count +------- + 24 +(1 row) + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a < ANY ('{113}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a < ANY ('{113}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]); + count +------- + 515 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a < ANY ('{113,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a < ANY ('{113,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]); + count +------- + 515 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a < ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a < ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a <= ANY ('{113,177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a <= ANY ('{113,177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]); + count +------- + 843 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a < ANY ('{113,177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a < ANY ('{113,177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]); + count +------- + 835 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a <= ANY ('{113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a <= ANY ('{113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]); + count +------- + 843 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]); + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a < ANY ('{NULL,113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a < ANY ('{NULL,113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]); + count +------- + 835 +(1 row) + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a > ANY ('{113}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a > ANY ('{113}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]); + count +------- + 477 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a > ANY ('{113,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a > ANY ('{113,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]); + count +------- + 477 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a > ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a > ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a >= ANY ('{113,177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a >= ANY ('{113,177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]); + count +------- + 485 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a > ANY ('{113,177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a > ANY ('{113,177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]); + count +------- + 477 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a > ANY ('{113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a > ANY ('{113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]); + count +------- + 917 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]); + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (a >= ANY ('{NULL,113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_1 + Index Cond: (a >= ANY ('{NULL,113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]); + count +------- + 925 +(1 row) + +-- bigint: equality +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = 82) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = 82) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{82,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{82,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{82,41}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{82,41}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{-82,-141}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{-82,-141}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{382,441}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{382,441}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{82,41,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15); + count +------- + 25 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15); + count +------- + 25 +(1 row) + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b < ANY ('{82}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b < ANY ('{82}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]); + count +------- + 674 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b < ANY ('{82,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b < ANY ('{82,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]); + count +------- + 674 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b < ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b < ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b <= ANY ('{82,41}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b <= ANY ('{82,41}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]); + count +------- + 684 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b < ANY ('{82,41,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b < ANY ('{82,41,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]); + count +------- + 674 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b <= ANY ('{82,41,15}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b <= ANY ('{82,41,15}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]); + count +------- + 684 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b < ANY ('{NULL,82,41,15}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b < ANY ('{NULL,82,41,15}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]); + count +------- + 674 +(1 row) + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b > ANY ('{82}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b > ANY ('{82}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]); + count +------- + 316 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b > ANY ('{82,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b > ANY ('{82,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]); + count +------- + 316 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b > ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b > ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b > ANY ('{82,41}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b > ANY ('{82,41}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]); + count +------- + 726 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b >= ANY ('{82,41,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b >= ANY ('{82,41,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]); + count +------- + 736 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b >= ANY ('{82,41,15}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b >= ANY ('{82,41,15}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]); + count +------- + 961 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_1 + Recheck Cond: (b > ANY ('{NULL,82,41,15}'::integer[])) + -> Bitmap Index Scan on brin_in_test_multi_1_idx_2 + Index Cond: (b > ANY ('{NULL,82,41,15}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]); + count +------- + 956 +(1 row) + +DROP TABLE brin_in_test_multi_1; +RESET enable_seqscan; +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_multi_2 (a UUID) WITH (fillfactor=10); +INSERT INTO brin_in_test_multi_2 +SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); +CREATE INDEX brin_in_test_multi_2_idx ON brin_in_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189'); + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189'); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL); + QUERY PLAN +--------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,NULL}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,NULL}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{NULL,NULL}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{NULL,NULL}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0'); + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0'); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL); + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,NULL}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,NULL}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + count +------- + 39 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_multi_2 + Recheck Cond: (a = ANY ('{NULL,33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[])) + -> Bitmap Index Scan on brin_in_test_multi_2_idx + Index Cond: (a = ANY ('{NULL,33e75ff0-9dd6-01bb-e69f-351039152189,f457c545-a9de-d88f-18ec-ee47145a72c0,c51ce410-c124-a10e-0db5-e4b97fc2af39}'::uuid[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + count +------- + 39 +(1 row) + +DROP TABLE brin_in_test_multi_2; +RESET enable_seqscan; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index d50dbdee68..0e70e43807 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -586,3 +586,304 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a = 'aab32389-22bc-c25a-6f60-6eb525 DROP TABLE brin_test_multi_2; RESET enable_seqscan; + + +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_multi_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); + +CREATE INDEX brin_in_test_multi_1_idx_1 ON brin_in_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_multi_1_idx_2 ON brin_in_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +-- int: equality +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (-113, -177); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (313, 377); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (113, 177, 25); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a IN (NULL, 113, 177, 25); + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[113, 177, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a <= ANY(ARRAY[113, 177, 25]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a < ANY(ARRAY[NULL, 113, 177, 25]); + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[113, 177]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a > ANY(ARRAY[113, 177, 25]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE a >= ANY(ARRAY[NULL, 113, 177, 25]); + +-- bigint: equality +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (-82, -141); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (382, 441); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (82, 41, 15); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b IN (NULL, 82, 41, 15); + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[82, 41, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b <= ANY(ARRAY[82, 41, 15]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b < ANY(ARRAY[NULL, 82, 41, 15]); + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[82, 41]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b >= ANY(ARRAY[82, 41, 15]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]); + +SELECT COUNT(*) FROM brin_in_test_multi_1 WHERE b > ANY(ARRAY[NULL, 82, 41, 15]); + + +DROP TABLE brin_in_test_multi_1; +RESET enable_seqscan; + + +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_multi_2 (a UUID) WITH (fillfactor=10); +INSERT INTO brin_in_test_multi_2 +SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); + +CREATE INDEX brin_in_test_multi_2_idx ON brin_in_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189'); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0'); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN ('33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_multi_2 WHERE a IN (NULL, '33e75ff0-9dd6-01bb-e69f-351039152189', 'f457c545-a9de-d88f-18ec-ee47145a72c0', 'c51ce410-c124-a10e-0db5-e4b97fc2af39'); + +DROP TABLE brin_in_test_multi_2; +RESET enable_seqscan; -- 2.41.0