From c93ca9a8377780833b480ad30ebffdf3249598d2 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 10 Feb 2023 16:07:57 +0100 Subject: [PATCH 6/9] Support SK_SEARCHARRAY in BRIN minmax Set "amsearcharray=true" for BRIN, and extend the minmax opclass to handle both scalar values and arrays. This allows handling conditions ... WHERE a IN (1, 2, 43, 2132, 134) ... WHERE a = ANY(ARRAY[34, 45, -1, 234]) ... WHERE a <= ANY(ARRAY[4938, 282, 2934]) more efficiently - until now we simply built the bitmap for each scalar value, so we walked the BRIN index many times. Which may be quite expensive for indexes with many ranges (large tables and/or low pages_per_range). There's a couple problems / open questions / TODO items: - The other opclasses don't handle SK_SEARCHARRAY yet. - The array is always searched linearly, so this may be costly for large arrays (with many elements). - The array is deconstructed again for each range. We should reuse this, somehow. --- src/backend/access/brin/brin.c | 3 +- src/backend/access/brin/brin_minmax.c | 375 +++++++++-- src/backend/access/brin/brin_validate.c | 4 + src/include/catalog/pg_amproc.dat | 64 ++ src/include/catalog/pg_proc.dat | 3 + src/test/regress/expected/amutils.out | 2 +- src/test/regress/expected/brin.out | 858 ++++++++++++++++++++++++ src/test/regress/sql/brin.sql | 283 ++++++++ 8 files changed, 1552 insertions(+), 40 deletions(-) diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index f7fa9f3f06b..6b6cb2524f0 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -38,6 +38,7 @@ #include "utils/datum.h" #include "utils/guc.h" #include "utils/index_selfuncs.h" +#include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -107,7 +108,7 @@ brinhandler(PG_FUNCTION_ARGS) amroutine->amcanunique = false; amroutine->amcanmulticol = true; amroutine->amoptionalkey = true; - amroutine->amsearcharray = false; + amroutine->amsearcharray = true; amroutine->amsearchnulls = true; amroutine->amstorage = true; amroutine->amclusterable = false; diff --git a/src/backend/access/brin/brin_minmax.c b/src/backend/access/brin/brin_minmax.c index 8229493c84a..ba814f91c27 100644 --- a/src/backend/access/brin/brin_minmax.c +++ b/src/backend/access/brin/brin_minmax.c @@ -16,11 +16,21 @@ #include "access/stratnum.h" #include "catalog/pg_amop.h" #include "catalog/pg_type.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/datum.h" #include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" +#include "utils/sortsupport.h" + +/* + * We use some private sk_flags bits in preprocessed scan keys. We're allowed + * to use bits 16-31 (see skey.h). The uppermost bits are copied from the + * index's indoption[] array entry for the index attribute. + */ +#define SK_BRIN_SORTED 0x00010000 /* deconstructed and sorted array */ + typedef struct MinmaxOpaque { @@ -126,6 +136,158 @@ brin_minmax_add_value(PG_FUNCTION_ARGS) PG_RETURN_BOOL(updated); } + +static int +compare_array_values(const void *a, const void *b, void *arg) +{ + Datum da = * (Datum *) a; + Datum db = * (Datum *) b; + SortSupport ssup = (SortSupport) arg; + + return ApplySortComparator(da, false, db, false, ssup); +} + +/* + * lower_boundary + * Determine lowest index so that (values[index] >= minvalue). + * + * The array of values is expected to be sorted, so this is the first value + * that may fall into the [minvalue, maxvalue] range, as it exceeds minval. + * It's not guaranteed, though, as it might exceed maxvalue too. + */ +static int +lower_boundary(Datum *values, int nvalues, Datum minvalue, SortSupport ssup) +{ + int start = 0, + end = (nvalues - 1); + + /* everything exceeds minval and might match */ + if (compare_array_values(&minvalue, &values[start], ssup) <= 0) + return 0; + + /* nothing could match */ + if (compare_array_values(&minvalue, &values[end], ssup) > 0) + return nvalues; + + while ((end - start) > 0) + { + int midpoint; + int r; + + midpoint = start + (end - start) / 2; + + r = compare_array_values(&minvalue, &values[midpoint], ssup); + + if (r > 0) + start = Max(midpoint, start + 1); + else + end = midpoint; + } + + /* the value should meet the (v >=minvalue) requirement */ + Assert(compare_array_values(&values[start], &minvalue, ssup) >= 0); + + /* we know start can't be 0, so it's legal to subtract 1 */ + Assert(compare_array_values(&values[start-1], &minvalue, ssup) < 0); + + return start; +} + +typedef struct ScanKeyArray { + Oid typeid; + int nelements; + Datum *elements; +} ScanKeyArray; + +Datum +brin_minmax_preprocess(PG_FUNCTION_ARGS) +{ + // BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0); + ScanKey key = (ScanKey) PG_GETARG_POINTER(1); + ScanKey newkey; + ScanKeyArray *scanarray; + + ArrayType *arrayval; + int16 elmlen; + bool elmbyval; + char elmalign; + int num_elems; + Datum *elem_values; + bool *elem_nulls; + TypeCacheEntry *type; + SortSupportData ssup; + + /* number of non-null elements in the array */ + int num_nonnulls; + + /* + * ignore scalar keys + * + * XXX Maybe we should preprocess scalar keys too. It'd make the consistent + * function simpler by removing the branching. + */ + if (!(key->sk_flags & SK_SEARCHARRAY)) + PG_RETURN_POINTER(key); + + arrayval = DatumGetArrayTypeP(key->sk_argument); + + get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), + &elmlen, &elmbyval, &elmalign); + + deconstruct_array(arrayval, + ARR_ELEMTYPE(arrayval), + elmlen, elmbyval, elmalign, + &elem_values, &elem_nulls, &num_elems); + + /* eliminate NULL elements */ + num_nonnulls = 0; + for (int i = 0; i < num_elems; i++) + { + /* skip NULL elements */ + if (elem_nulls[i]) + continue; + + /* if needed, move the non-NULL ones */ + if (num_nonnulls != i) + elem_values[num_nonnulls] = elem_values[i]; + + num_nonnulls++; + } + + num_elems = num_nonnulls; + + type = lookup_type_cache(ARR_ELEMTYPE(arrayval), TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + qsort_interruptible(elem_values, num_elems, sizeof(Datum), + compare_array_values, &ssup); + + scanarray = palloc0(sizeof(ScanKeyArray)); + scanarray->typeid = ARR_ELEMTYPE(arrayval); + scanarray->nelements = num_elems; + scanarray->elements = elem_values; + + newkey = palloc0(sizeof(ScanKeyData)); + + ScanKeyEntryInitializeWithInfo(newkey, + (key->sk_flags | SK_BRIN_SORTED), + key->sk_attno, + key->sk_strategy, + key->sk_subtype, + key->sk_collation, + &key->sk_func, + PointerGetDatum(scanarray)); + + PG_RETURN_POINTER(newkey); +} + + /* * Given an index tuple corresponding to a certain page range and a scan key, * return whether the scan key is consistent with the index tuple's min/max @@ -157,46 +319,183 @@ brin_minmax_consistent(PG_FUNCTION_ARGS) attno = key->sk_attno; subtype = key->sk_subtype; value = key->sk_argument; - switch (key->sk_strategy) + + /* + * For regular (scalar) scan keys, we simply compare the value to the + * range min/max values, and we're done. For preprocessed SK_SEARCHARRAY + * keys we need to loop through the deparsed values. + */ + if (likely(!(key->sk_flags & SK_BRIN_SORTED))) { - case BTLessStrategyNumber: - case BTLessEqualStrategyNumber: - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], - value); - break; - case BTEqualStrategyNumber: - - /* - * In the equality case (WHERE col = someval), we want to return - * the current page range if the minimum value in the range <= - * scan key, and the maximum value >= scan key. - */ - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - BTLessEqualStrategyNumber); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], - value); - if (!DatumGetBool(matches)) + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + value); + break; + case BTEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + value); + if (!DatumGetBool(matches)) + break; + /* max() >= scankey */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + value); + break; + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + value); + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; + break; + } + } + else + { + ScanKeyArray *array = (ScanKeyArray *) value; + + /* can happen if the IN list contained just NULLs */ + if (array->nelements == 0) + PG_RETURN_BOOL(false); + + switch (key->sk_strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + /* + * Check the last (largest) value in the array - at least this + * value has to exceed the range minval. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + array->elements[array->nelements-1]); + break; + case BTEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + * + * We do this in two phases. We check the array min/max values to see + * if there even can be a matching value, and if yes we do a binary + * search to find the first value that exceeds range minval. And then + * we check if it actually matches the range. + * + * XXX The first phase is probably unnecessary, because lower_bound() + * does pretty much exactly that too. + */ + { + Datum val; + SortSupportData ssup; + int lower; + TypeCacheEntry *type; + + /* Is the first (smallest) value after the BRIN range? */ + val = array->elements[0]; + + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[1]); + + /* minval > max(range values) */ + if (!DatumGetBool(matches)) + break; + + /* Is the last (largest) value before the BRIN range? */ + val = array->elements[array->nelements-1]; + + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, val, column->bv_values[0]); + + /* maxval < min(range values) */ + if (!DatumGetBool(matches)) + break; + + /* + * OK, there might be some values matching the range. We have + * to search them one by one, or perhaps try binsearch. + */ + type = lookup_type_cache(array->typeid, TYPECACHE_LT_OPR); + + memset(&ssup, 0, sizeof(SortSupportData)); + + ssup.ssup_collation = key->sk_collation; + ssup.ssup_cxt = CurrentMemoryContext; + + PrepareSortSupportFromOrderingOp(type->lt_opr, &ssup); + + lower = lower_boundary(array->elements, array->nelements, column->bv_values[0], &ssup); + + /* no elements can possibly match */ + if (lower == array->nelements) + { + matches = BoolGetDatum(false); + break; + } + + /* + * OK, the first element must match the upper boundary too + * (if it does not, no following elements can). + */ + val = array->elements[lower]; + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTLessEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[0], + val); + if (!DatumGetBool(matches)) + break; + /* max() >= scankey */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + BTGreaterEqualStrategyNumber); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + val); + break; + } + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + /* + * Check the first (smallest) value in the array - at least this + * value has to be smaller than the range maxval. + */ + finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, + key->sk_strategy); + matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], + array->elements[0]); + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; break; - /* max() >= scankey */ - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - BTGreaterEqualStrategyNumber); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], - value); - break; - case BTGreaterEqualStrategyNumber: - case BTGreaterStrategyNumber: - finfo = minmax_get_strategy_procinfo(bdesc, attno, subtype, - key->sk_strategy); - matches = FunctionCall2Coll(finfo, colloid, column->bv_values[1], - value); - break; - default: - /* shouldn't happen */ - elog(ERROR, "invalid strategy number %d", key->sk_strategy); - matches = 0; - break; + } } PG_RETURN_DATUM(matches); diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c index c8edfb37591..0889e24bc01 100644 --- a/src/backend/access/brin/brin_validate.c +++ b/src/backend/access/brin/brin_validate.c @@ -108,6 +108,10 @@ brinvalidate(Oid opclassoid) case BRIN_PROCNUM_OPTIONS: ok = check_amoptsproc_signature(procform->amproc); break; + case BRIN_PROCNUM_PREPROCESS: + ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + 2, 2, INTERNALOID, INTERNALOID); + break; default: /* Complain if it's not a valid optional proc number */ if (procform->amprocnum < BRIN_FIRST_OPTIONAL_PROCNUM || diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 5b950129de0..166681c31ef 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -804,6 +804,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea', amprocrighttype => 'bytea', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom bytea { amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', @@ -835,6 +837,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom "char" { amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', @@ -864,6 +868,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom name { amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', @@ -893,6 +899,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8', amprocrighttype => 'int8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '1', @@ -905,6 +913,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '1', @@ -917,6 +927,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', amprocrighttype => 'int4', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi integer: int2, int4, int8 { amprocfamily => 'brin/integer_minmax_multi_ops', amproclefttype => 'int2', @@ -1034,6 +1046,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom text { amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', @@ -1062,6 +1076,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi oid { amprocfamily => 'brin/oid_minmax_multi_ops', amproclefttype => 'oid', @@ -1110,6 +1126,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # bloom tid { amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', @@ -1160,6 +1178,9 @@ { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4', amprocrighttype => 'float4', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '1', @@ -1173,6 +1194,9 @@ { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', amprocrighttype => 'float8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi float { amprocfamily => 'brin/float_minmax_multi_ops', amproclefttype => 'float4', @@ -1261,6 +1285,9 @@ { amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr', amprocrighttype => 'macaddr', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi macaddr { amprocfamily => 'brin/macaddr_minmax_multi_ops', amproclefttype => 'macaddr', @@ -1314,6 +1341,9 @@ { amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi macaddr8 { amprocfamily => 'brin/macaddr8_minmax_multi_ops', @@ -1366,6 +1396,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi inet { amprocfamily => 'brin/network_minmax_multi_ops', amproclefttype => 'inet', @@ -1436,6 +1468,9 @@ { amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar', amprocrighttype => 'bpchar', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bpchar_minmax_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # bloom character { amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', @@ -1467,6 +1502,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi time without time zone { amprocfamily => 'brin/time_minmax_multi_ops', amproclefttype => 'time', @@ -1517,6 +1554,9 @@ { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp', amprocrighttype => 'timestamp', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '1', @@ -1530,6 +1570,9 @@ { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', amprocrighttype => 'timestamptz', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '1', @@ -1542,6 +1585,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', amprocrighttype => 'date', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi datetime (date, timestamp, timestamptz) { amprocfamily => 'brin/datetime_minmax_multi_ops', @@ -1668,6 +1713,9 @@ { amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi interval { amprocfamily => 'brin/interval_minmax_multi_ops', @@ -1721,6 +1769,9 @@ { amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz', amprocrighttype => 'timetz', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi time with time zone { amprocfamily => 'brin/timetz_minmax_multi_ops', amproclefttype => 'timetz', @@ -1771,6 +1822,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit', amprocrighttype => 'bit', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit', + amprocrighttype => 'bit', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax bit varying { amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', @@ -1785,6 +1838,9 @@ { amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', amprocrighttype => 'varbit', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/varbit_minmax_ops', amproclefttype => 'varbit', + amprocrighttype => 'varbit', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax numeric { amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', @@ -1799,6 +1855,9 @@ { amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', amprocrighttype => 'numeric', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/numeric_minmax_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi numeric { amprocfamily => 'brin/numeric_minmax_multi_ops', amproclefttype => 'numeric', @@ -1851,6 +1910,8 @@ amproc => 'brin_minmax_consistent' }, { amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '6', amproc => 'brin_minmax_preprocess' }, # minmax multi uuid { amprocfamily => 'brin/uuid_minmax_multi_ops', amproclefttype => 'uuid', @@ -1924,6 +1985,9 @@ { amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn', amprocrighttype => 'pg_lsn', amprocnum => '4', amproc => 'brin_minmax_union' }, +{ amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '6', + amproc => 'brin_minmax_preprocess' }, # minmax multi pg_lsn { amprocfamily => 'brin/pg_lsn_minmax_multi_ops', amproclefttype => 'pg_lsn', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6996073989a..8f17532094b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8527,6 +8527,9 @@ { oid => '3386', descr => 'BRIN minmax support', proname => 'brin_minmax_union', prorettype => 'bool', proargtypes => 'internal internal internal', prosrc => 'brin_minmax_union' }, +{ oid => '9327', descr => 'BRIN minmax support', + proname => 'brin_minmax_preprocess', prorettype => 'internal', + proargtypes => 'internal internal', prosrc => 'brin_minmax_preprocess' }, # BRIN minmax multi { oid => '4616', descr => 'BRIN multi minmax support', diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 7ab6113c619..f3e1fbd2ae3 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -102,7 +102,7 @@ select prop, orderable | t | f | f | f | f | f | f distance_orderable | f | f | t | f | t | f | f returnable | t | f | f | t | t | f | f - search_array | t | f | f | f | f | f | f + search_array | t | f | f | f | f | f | t search_nulls | t | f | t | t | t | f | t bogus | | | | | | | (10 rows) diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out index f0b7de5bf76..24361b90eb2 100644 --- a/src/test/regress/expected/brin.out +++ b/src/test/regress/expected/brin.out @@ -572,3 +572,861 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange); CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n); INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric)); DROP TABLE brintest_unlogged; +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); +CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +-- int: equalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = 113) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = 113) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + count +------- + 8 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + count +------- + 16 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + count +------- + 24 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a = ANY ('{NULL,113,177,25}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + count +------- + 24 +(1 row) + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{30}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{30}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + count +------- + 103 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{20,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{20,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + count +------- + 51 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{35,29}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{35,29}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + count +------- + 127 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a <= ANY ('{45,60,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a <= ANY ('{45,60,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + count +------- + 255 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a < ANY ('{41,37,55}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a < ANY ('{41,37,55}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + count +------- + 227 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a <= ANY ('{NULL,60,43,94}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a <= ANY ('{NULL,60,43,94}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + count +------- + 427 +(1 row) + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{200}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{200}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + count +------- + 45 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{177,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{177,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + count +------- + 157 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{NULL,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{NULL,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{153,140}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{153,140}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + count +------- + 345 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a >= ANY ('{173,191,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a >= ANY ('{173,191,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + count +------- + 185 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a > ANY ('{120,184,164}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a > ANY ('{120,184,164}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + count +------- + 445 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (a >= ANY ('{NULL,130,181,169}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_1 + Index Cond: (a >= ANY ('{NULL,130,181,169}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + count +------- + 397 +(1 row) + +-- bigint: eqalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = 82) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = 82) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + count +------- + 10 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + count +------- + 20 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + count +------- + 25 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b = ANY ('{NULL,82,41,15}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + count +------- + 25 +(1 row) + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{31}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{31}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + count +------- + 164 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{55,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{55,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + count +------- + 404 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b <= ANY ('{73,51}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b <= ANY ('{73,51}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + count +------- + 594 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{69,87,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{69,87,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + count +------- + 724 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b <= ANY ('{82,91,35}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b <= ANY ('{82,91,35}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + count +------- + 774 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b < ANY ('{NULL,63,21,85}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b < ANY ('{NULL,63,21,85}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + count +------- + 704 +(1 row) + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{94}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{94}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + count +------- + 196 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{80,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{80,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + count +------- + 336 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{NULL,NULL}'::bigint[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{NULL,NULL}'::bigint[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{199,107}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{199,107}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + count +------- + 78 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b >= ANY ('{182,101,NULL}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b >= ANY ('{182,101,NULL}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + count +------- + 137 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + QUERY PLAN +------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{300,106,251}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{300,106,251}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + count +------- + 86 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_1 + Recheck Cond: (b > ANY ('{NULL,182,101,155}'::integer[])) + -> Bitmap Index Scan on brin_in_test_1_idx_2 + Index Cond: (b > ANY ('{NULL,182,101,155}'::integer[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + count +------- + 127 +(1 row) + +DROP TABLE brin_in_test_1; +RESET enable_seqscan; +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10); +INSERT INTO brin_in_test_2 +SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); +CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1); +SET enable_seqscan=off; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + QUERY PLAN +-------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = '33e75ff09dd601bbe69f351039152189'::text) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = '33e75ff09dd601bbe69f351039152189'::text) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + QUERY PLAN +----------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + count +------- + 13 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + QUERY PLAN +------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{NULL,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{NULL,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,NULL}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + count +------- + 26 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + count +------- + 39 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_in_test_2 + Recheck Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) + -> Bitmap Index Scan on brin_in_test_2_idx + Index Cond: (a = ANY ('{NULL,33e75ff09dd601bbe69f351039152189,f457c545a9ded88f18ecee47145a72c0,c51ce410c124a10e0db5e4b97fc2af39}'::text[])) +(5 rows) + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + count +------- + 39 +(1 row) + +DROP TABLE brin_in_test_2; +RESET enable_seqscan; diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql index 929a087a25d..7199c9108b0 100644 --- a/src/test/regress/sql/brin.sql +++ b/src/test/regress/sql/brin.sql @@ -515,3 +515,286 @@ CREATE UNLOGGED TABLE brintest_unlogged (n numrange); CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n); INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric)); DROP TABLE brintest_unlogged; + +-- do some tests on IN clauses for simple data types +CREATE TABLE brin_in_test_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_in_test_1 +SELECT i/5 + mod(991 * i + 617, 20), + i/10 + mod(853 * i + 491, 30) + FROM generate_series(1,1000) s(i); + +CREATE INDEX brin_in_test_1_idx_1 ON brin_in_test_1 USING brin (a int4_minmax_ops) WITH (pages_per_range=1); +CREATE INDEX brin_in_test_1_idx_2 ON brin_in_test_1 USING brin (b int8_minmax_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +-- int: equalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (113, 177, 25); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a IN (NULL, 113, 177, 25); + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[30]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[20, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[35, 29]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[45, 60, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a < ANY (ARRAY[41, 37, 55]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a <= ANY (ARRAY[NULL, 60, 43, 94]); + + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[200]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[177, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[NULL, NULL]::int[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[153, 140]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[173, 191, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a > ANY (ARRAY[120, 184, 164]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE a >= ANY (ARRAY[NULL, 130, 181, 169]); + + +-- bigint: eqalities +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (82, 41, 15); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b IN (NULL, 82, 41, 15); + + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[31]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[55, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[73, 51]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[69, 87, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b <= ANY (ARRAY[82, 91, 35]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b < ANY (ARRAY[NULL, 63, 21, 85]); + + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[94]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[80, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, NULL]::bigint[]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[199, 107]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b >= ANY (ARRAY[182, 101, NULL]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[300, 106, 251]); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + +SELECT COUNT(*) FROM brin_in_test_1 WHERE b > ANY (ARRAY[NULL, 182, 101, 155]); + + +DROP TABLE brin_in_test_1; +RESET enable_seqscan; + +-- do some tests on IN clauses for varlena data types +CREATE TABLE brin_in_test_2 (a TEXT) WITH (fillfactor=10); +INSERT INTO brin_in_test_2 +SELECT v FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); + +CREATE INDEX brin_in_test_2_idx ON brin_in_test_2 USING brin (a text_minmax_ops) WITH (pages_per_range=1); + +SET enable_seqscan=off; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', NULL); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN ('33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +SELECT COUNT(*) FROM brin_in_test_2 WHERE a IN (NULL, '33e75ff09dd601bbe69f351039152189', 'f457c545a9ded88f18ecee47145a72c0', 'c51ce410c124a10e0db5e4b97fc2af39'); + +DROP TABLE brin_in_test_2; +RESET enable_seqscan; -- 2.40.1