From f35148b5ca9158ad361e0a9fa02fe0cd03db279d Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 10 Feb 2023 16:07:57 +0100 Subject: [PATCH 2/5] Support SK_SEARCHARRAY in BRIN minmax Set "amsearcharray=true" for BRIN, and extend the minmax opclass to handle both scalar values and arrays. This allows handling conditions ... WHERE a IN (1, 2, 43, 2132, 134) ... WHERE a = ANY(ARRAY[34, 45, -1, 234]) ... WHERE a <= ANY(ARRAY[4938, 282, 2934]) more efficiently. Until now we simply built the bitmap for each element of the array as if it was a separate scalar keys combined by OR. This walked the BRIN index many times, making it quite expensive, especially for indexes with many ranges (large tables and/or low pages_per_range). The array is sorted in BRIN_PROCNUM_PREPROCESS procedure, called from brinrescan. The procedure is per opclass and optional, so there may be opclasses without it. To handle that, the consistent function handles SK_SEARCHARRAY without preprocessing by walking the array (the array needs to be deconstructed for each page range, though). TODO / open issues - The other BRIN opclasses don't handle SK_SEARCHARRAY yet, but the amsearcharray=true affects them. The tests don't fail most likely because there are no queries with BRIN indexes and arrays. Will be separate patches, but then probably need to be squashed for commit. --- src/backend/access/brin/brin.c | 3 +- src/backend/access/brin/brin_minmax.c | 529 +++++++++++++-- src/backend/access/brin/brin_validate.c | 4 + src/include/catalog/pg_amproc.dat | 64 ++ src/include/catalog/pg_proc.dat | 3 + src/test/regress/expected/amutils.out | 2 +- src/test/regress/expected/brin.out | 858 ++++++++++++++++++++++++ src/test/regress/sql/brin.sql | 283 ++++++++ 8 files changed, 1706 insertions(+), 40 deletions(-) diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index 0f773ad75d..bc215d4552 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -38,6 +38,7 @@ #include "utils/datum.h" #include "utils/guc.h" #include "utils/index_selfuncs.h" +#include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -115,7 +116,7 @@ brinhandler(PG_FUNCTION_ARGS) amroutine->amcanunique = false; amroutine->amcanmulticol = true; amroutine->amoptionalkey = true; - amroutine->amsearcharray = false; + amroutine->amsearcharray = true; amroutine->amsearchnulls = true; amroutine->amstorage = true; amroutine->amclusterable = false; diff --git a/src/backend/access/brin/brin_minmax.c b/src/backend/access/brin/brin_minmax.c index 8229493c84..d246bd0d5a 100644 --- a/src/backend/access/brin/brin_minmax.c +++ b/src/backend/access/brin/brin_minmax.c @@ -16,11 +16,21 @@ #include "access/stratnum.h" #include "catalog/pg_amop.h" #include "catalog/pg_type.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/datum.h" #include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" +#include "utils/sortsupport.h" + +/* + * We use some private sk_flags bits in preprocessed scan keys. We're allowed + * to use bits 16-31 (see skey.h). The uppermost bits are copied from the + * index's indoption[] array entry for the index attribute. + */ +#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */ + typedef struct MinmaxOpaque { @@ -126,6 +136,195 @@ brin_minmax_add_value(PG_FUNCTION_ARGS) PG_RETURN_BOOL(updated); } +/* + * preprocessing of scan keys for the minmax opclass + * + * For now we care only about array keys - instead of simple linear search + * in the array (for each range), we sort the arrays during preprocessing + * and then use that for binary sort in consistent function. + */ + +/* qsort comparator used to sort array elements */ +static int +minmax_compare_values(const void *a, const void *b, void *arg) +{ + Datum da = * (Datum *) a; + Datum db = * (Datum *) b; + SortSupport ssup = (SortSupport) arg; + + return ApplySortComparator(da, false, db, false, ssup); +} + +/* + * Deconstructed and sorted scan key array (we might build ArrayType, but then + * we'd have to deconstruct it over and over for each page range). So we just + * do it once during preprocessing. + */ +typedef struct ScanKeyArray { + Oid typeid; + int nelements; + Datum *elements; +} ScanKeyArray; + +/* + * minmax_lower_boundary + * Given a value, determine the minimum index so that (array[index] >= value) + * + * We use this to check if a minmax range [minvalue, maxvalue] intersects with + * the array in the scan key (which is expected to be sorted). We calculate + * the lower boundary for [minvalue] and then check if it actually falls under + * the maxvalue too. If yes, we found an element consistent with the page range. + * + * If all array elements match (i.e. all elements >= value), returns 0. If no + * elements match (i.e. all elements < 0), returns nvalues. + */ +static int +minmax_lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup) +{ + int start = 0, + end = (nvalues - 1); + + /* everything exceeds minval and might match */ + if (minmax_compare_values(&minvalue, &values[start], ssup) <= 0) + return 0; + + /* nothing could match */ + if (minmax_compare_values(&minvalue, &values[end], ssup) > 0) + return nvalues; + + while ((end - start) > 0) + { + int midpoint; + int r; + + midpoint = start + (end - start) / 2; + + r = minmax_compare_values(&minvalue, &values[midpoint], ssup); + + if (r > 0) + start = Max(midpoint, start + 1); + else + end = midpoint; + } + + /* the value should meet the (v >=minvalue) requirement */ + Assert(minmax_compare_values(&values[start], &minvalue, ssup) >= 0); + + /* we know start can't be 0, so it's legal to subtract 1 */ + Assert(minmax_compare_values(&values[start-1], &minvalue, ssup) < 0); + + return start; +} + +/* + * brin_minmax_preprocess + * preprocess scan keys for the minmax opclass + * + * For now we just care about SK_SEARCHARRAY keys, which we sort and keep the + * deconstructed array. All other scan keys are ignored (returned as is). + * + * XXX Do we need to remember if the array contained NULL values? + */ +Datum +brin_minmax_preprocess(PG_FUNCTION_ARGS) +{ + ScanKey key = (ScanKey) PG_GETARG_POINTER(1); + ScanKey newkey; + ScanKeyArray *scanarray; + + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + TypeCacheEntry *type; + SortSupportData ssup; + + /* number of non-null elements in the array */ + int num_nonnulls; + + /* + * ignore scalar keys (just return the original scan key) + * + * XXX Maybe we should preprocess scalar keys too, and treat them as arrays + * with a single element. It'd make the consistent function simpler by not + * having to do branching. + */ + if (!(key->sk_flags & SK_SEARCHARRAY)) + PG_RETURN_POINTER(key); + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* eliminate NULL elements */ + num_nonnulls = 0; + for (int i = 0; i < num_elems; i++) + { + /* skip NULL elements */ + if (elem_nulls[i]) + continue; + + /* if needed, move the non-NULL ones */ + if (num_nonnulls != i) + elem_values[num_nonnulls] = elem_values[i]; + + num_nonnulls++; + } + + num_elems = num_nonnulls; + + /* FIXME What if num_nonnulls is 0? Can it even happen / get here? */ + + /* + * sort the array + * + * XXX Should we walk the sorted array again and eliminate duplicate values? + * Seems unnecessary - we're not going to repalloc/release the memory anyway + * and it's unlikely to speed up the binsearch (unless there's a lot of + * duplicate values, which does not seem plausible/common). + */ + type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + qsort_interruptible(elem_values, num_elems, sizeof(Datum), + minmax_compare_values, &ssup); + + /* Construct the new scan key, with sorted array as ScanKeyArray. */ + scanarray = palloc0(sizeof(ScanKeyArray)); + scanarray->typeid = ARR_ELEMTYPE(arrayval); + scanarray->nelements = num_elems; + scanarray->elements = elem_values; + + newkey = palloc0(sizeof(ScanKeyData)); + + ScanKeyEntryInitializeWithInfo(newkey, + (key->sk_flags | SK_BRIN_SORTED), + key->sk_attno, + key->sk_strategy, + key->sk_subtype, + key->sk_collation, + &key->sk_func, + PointerGetDatum(scanarray)); + + PG_RETURN_POINTER(newkey); +} + + /* * Given an index tuple corresponding to a certain page range and a scan key, * return whether the scan key is consistent with the index tuple's min/max @@ -157,46 +356,300 @@ brin_minmax_consistent(PG_FUNCTION_ARGS) attno = key->sk_attno; subtype = key->sk_subtype; value = key->sk_argument; - switch (key->sk_strategy) + + /* + * For regular (scalar) scan keys, we simply compare the value to the + * range min/max values, and we're done. For preprocessed SK_SEARCHARRAY + * keys we can do a binary search in the sorted array. + * + * FIXME This should also handle the "array but not preprocessed" case + * too, for opclasses not defining the optional preprocess procedure. + * Otherwise we'd have issues with such opclasses because amsearcharray + * is defined at the AM level. + */ + if (key->sk_flags & SK_BRIN_SORTED) /* preprocessed array*/ + { + ScanKeyArray *array = (ScanKeyArray *) value; + + /* can happen if the IN list contained just NULLs */ + if (array->nelements == 0) + PG_RETURN_BOOL(false); + + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + /* + * Check the last (largest) value in the array - at least this + * value has to exceed the range minval. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + array->elements[array->nelements-1]); + break; + case BTEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + * + * We do this in two phases. We check the array min/max values to see + * if there even can be a matching value, and if yes we do a binary + * search to find the first value that exceeds range minval. And then + * we check if it actually matches the range. + * + * XXX The first phase is probably unnecessary, because lower_bound() + * does pretty much exactly that too. + */ + { + TypeCacheEntry *type; + SortSupportData ssup; + + Datum element, + minvalue, + maxvalue; + int lower; + + /* for readability */ + minvalue = column->bv_values[0]; + maxvalue = column->bv_values[1]; + + /* + * Before doing the binary search on sorted array, do a quick search + * if the range can match at all. If the whole array is outside the + * page range, we're done. This is much cheaper than the binsearch, + * and we assume most ranges do not match. + */ + + /* Is the first (smallest) array element after the BRIN range? */ + element = array->elements[0]; + + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, element, maxvalue); + + /* first element > range maxvalue */ + if (!DatumGetBool(matches)) + break; + + /* Is the last (largest) array element before the BRIN range? */ + element = array->elements[array->nelements-1]; + + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, element, minvalue); + + /* last element < range minvalue */ + if (!DatumGetBool(matches)) + break; + + /* + * It seems there might be some array elements consistent with + * the page range. We find the first element above the range + * minvalue and check if it's smaller than maxvalue. If yes, + * we have a match. If not, other elements can't be consistent + * either (will exceed maxval too, thanks to sort). + */ + type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + lower = minmax_lower_boundary(array->elements, array->nelements, + minvalue, &ssup); + + /* + * If the lower boundary is nelements, then no array elements + * can possibly match this page range. + * + * XXX This is probably impossible due to the earlier check. + * This would mean all elements are < minvalue, but we did + * check for that. + */ + if (lower == array->nelements) + { + matches = BoolGetDatum(false); + break; + } + + /* + * We have an element that might be consistent with the page + * range, so let's check the maxvalue too (if it exceeds it, + * no following elements will too). + */ + element = array->elements[lower]; + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + * + * XXX This minvalue check is likely unnecessary, thanks to the + * lower boundary guaranteeing this to be true. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, + minvalue, element); + if (!DatumGetBool(matches)) + break; + + /* maxvalue >= element */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, + maxvalue, element); + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + /* + * Check the first (smallest) value in the array - at least this + * value has to be smaller than the range maxval. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + array->elements[0]); + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; + break; + } + } + else if (key->sk_flags & SK_SEARCHARRAY) /* array without preprocessing */ + { + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* we'll skip NULL elements */ + for (int i = 0; i < num_elems; i++) + { + /* skip NULL elements */ + if (elem_nulls[i]) + continue; + + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + elem_values[i]); + break; + case BTEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + elem_values[i]); + if (!DatumGetBool(matches)) + break; + /* max() >= scankey */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + elem_values[i]); + break; + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + elem_values[i]); + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; + break; + } + + /* found a consistent value, we're done */ + if (DatumGetBool(matches)) + break; + } + + /* + * free the arrays + * + * XXX is this necessary? + */ + pfree(elem_values); + pfree(elem_nulls); + } + else /* scalar scan key */ { - case BTLessStrategyNumber: - case BTLessEqualStrategyNumber: - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], - value); - break; - case BTEqualStrategyNumber: - - /* - * In the equality case (WHERE col = someval), we want to return - * the current page range if the minimum value in the range <= - * scan key, and the maximum value >= scan key. - */ - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - BTLessEqualStrategyNumber); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], - value); - if (!DatumGetBool(matches)) + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + value); + break; + case BTEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + value); + if (!DatumGetBool(matches)) + break; + /* max() >= scankey */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + value); + break; + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + value); + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; break; - /* max() >= scankey */ - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - BTGreaterEqualStrategyNumber); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], - value); - break; - case BTGreaterEqualStrategyNumber: - case BTGreaterStrategyNumber: - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], - value); - break; - default: - /* shouldn't happen */ - elog(ERROR, "invalid strategy number %d", key->sk_strategy); - matches = 0; - break; + } } PG_RETURN_DATUM(matches); diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c index c8edfb3759..0889e24bc0 100644 --- a/src/backend/access/brin/brin_validate.c +++ b/src/backend/access/brin/brin_validate.c @@ -108,6 +108,10 @@ brinvalidate(Oid opclassoid) case BRIN_PROCNUM_OPTIONS: ok = check_amoptsproc_signature(procform->amproc); break; + case BRIN_PROCNUM_PREPROCESS: + ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + 2, 2, INTERNALOID, INTERNALOID); + break; default: /* Complain if it's not a valid optional proc number */ if (procform->amprocnum < BRIN_FIRST_OPTIONAL_PROCNUM || diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 5b950129de..166681c31e 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -804,6 +804,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea', amprocrighttype => 'bytea', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom bytea { amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', @@ -835,6 +837,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom "char" { amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', @@ -864,6 +868,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom name { amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', @@ -893,6 +899,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8', amprocrighttype => 'int8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '1', @@ -905,6 +913,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '1', @@ -917,6 +927,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi integer: int2, int4, int8 { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2', @@ -1034,6 +1046,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom text { amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', @@ -1062,6 +1076,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi oid { amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid', @@ -1110,6 +1126,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom tid { amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', @@ -1160,6 +1178,9 @@ { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4', amprocrighttype => 'float4', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '1', @@ -1173,6 +1194,9 @@ { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi float { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4', @@ -1261,6 +1285,9 @@ { amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr', amprocrighttype => 'macaddr', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi macaddr { amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr', @@ -1314,6 +1341,9 @@ { amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi macaddr8 { amprocfamily => 'brin/macaddr8_minmax_multi_ops', @@ -1366,6 +1396,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi inet { amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet', @@ -1436,6 +1468,9 @@ { amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar', amprocrighttype => 'bpchar', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # bloom character { amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', @@ -1467,6 +1502,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi time without time zone { amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time', @@ -1517,6 +1554,9 @@ { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp', amprocrighttype => 'timestamp', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '1', @@ -1530,6 +1570,9 @@ { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '1', @@ -1542,6 +1585,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi datetime (date, timestamp, timestamptz) { amprocfamily => 'brin/datetime_minmax_multi_ops', @@ -1668,6 +1713,9 @@ { amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi interval { amprocfamily => 'brin/interval_minmax_multi_ops', @@ -1721,6 +1769,9 @@ { amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz', amprocrighttype => 'timetz', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi time with time zone { amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz', @@ -1771,6 +1822,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit', amprocrighttype => 'bit', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit', + amprocrighttype => 'bit', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax bit varying { amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', @@ -1785,6 +1838,9 @@ { amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', amprocrighttype => 'varbit', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', + amprocrighttype => 'varbit', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax numeric { amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', @@ -1799,6 +1855,9 @@ { amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', amprocrighttype => 'numeric', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi numeric { amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric', @@ -1851,6 +1910,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi uuid { amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid', @@ -1924,6 +1985,9 @@ { amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn', amprocrighttype => 'pg_lsn', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi pg_lsn { amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6996073989..8f17532094 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8527,6 +8527,9 @@ { oid => '3386', descr => 'BRIN minmax support', proname => 'brin_minmax_union', prorettype => 'bool', proargtypes => 'internal internal internal', prosrc => 'brin_minmax_union' }, +{ oid => '9327', descr => 'BRIN minmax support', + proname => 'brin_minmax_preprocess', prorettype => 'internal', + proargtypes => 'internal internal', prosrc => 'brin_minmax_preprocess' }, # BRIN minmax multi { oid => '4616', descr => 'BRIN multi minmax support', diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 7ab6113c61..f3e1fbd2ae 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -102,7 +102,7 @@ select prop, orderable | t | f | f | f | f | f | f distance_orderable | f | f | t | f | t | f | f returnable | t | f | f | t | t | f | f - search_array | t | f | f | f | f | f | f + search_array | t | f | f | f | f | f | t search_nulls | t | f | t | t | t | f | t bogus | | | | | | | (10 rows) diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out index f0b7de5bf7..24361b90eb 100644 --- a/src/test/regress/expected/brin.out +++ b/src/test/regress/expected/brin.out @@ -572,3 +572,861 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange); CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n); INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric)); DROP TABLE brintest_unlogged; +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); +CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +-- int: equalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = 113) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = 113) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + count +------- + 24 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + count +------- + 24 +(1 row) + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{30}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{30}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + count +------- + 103 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{20,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{20,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + count +------- + 51 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{35,29}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{35,29}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + count +------- + 127 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a <= ANY ('{45,60,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a <= ANY ('{45,60,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + count +------- + 255 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{41,37,55}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{41,37,55}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + count +------- + 227 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a <= ANY ('{NULL,60,43,94}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a <= ANY ('{NULL,60,43,94}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + count +------- + 427 +(1 row) + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{200}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{200}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + count +------- + 45 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + count +------- + 157 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{153,140}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{153,140}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + count +------- + 345 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a >= ANY ('{173,191,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a >= ANY ('{173,191,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + count +------- + 185 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{120,184,164}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{120,184,164}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + count +------- + 445 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a >= ANY ('{NULL,130,181,169}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a >= ANY ('{NULL,130,181,169}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + count +------- + 397 +(1 row) + +-- bigint: eqalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = 82) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = 82) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + count +------- + 25 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + count +------- + 25 +(1 row) + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{31}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{31}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + count +------- + 164 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{55,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{55,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + count +------- + 404 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b <= ANY ('{73,51}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b <= ANY ('{73,51}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + count +------- + 594 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{69,87,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{69,87,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + count +------- + 724 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b <= ANY ('{82,91,35}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b <= ANY ('{82,91,35}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + count +------- + 774 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{NULL,63,21,85}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{NULL,63,21,85}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + count +------- + 704 +(1 row) + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{94}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{94}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + count +------- + 196 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{80,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{80,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + count +------- + 336 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{199,107}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{199,107}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + count +------- + 78 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b >= ANY ('{182,101,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b >= ANY ('{182,101,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + count +------- + 137 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{300,106,251}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{300,106,251}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + count +------- + 86 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{NULL,182,101,155}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{NULL,182,101,155}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + count +------- + 127 +(1 row) + +DROP TABLE brin_in_test_1; +RESET enable_seqscan; +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10); +INSERT INTO brin_in_test_2 +SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); +CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + QUERY PLAN +-------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = '33e75ff09dd601bbe69f351039152189'::text) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = '33e75ff09dd601bbe69f351039152189'::text) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + QUERY PLAN +----------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{NULL,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{NULL,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + count +------- + 39 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + count +------- + 39 +(1 row) + +DROP TABLE brin_in_test_2; +RESET enable_seqscan; diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql index 929a087a25..7199c9108b 100644 --- a/src/test/regress/sql/brin.sql +++ b/src/test/regress/sql/brin.sql @@ -515,3 +515,286 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange); CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n); INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric)); DROP TABLE brintest_unlogged; + +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); + +CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +-- int: equalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + + +-- bigint: eqalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + + +DROP TABLE brin_in_test_1; +RESET enable_seqscan; + +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10); +INSERT INTO brin_in_test_2 +SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); + +CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +DROP TABLE brin_in_test_2; +RESET enable_seqscan; -- 2.41.0