I had noticed that performance wasn't great when using the @> or <@
operators when examining if an element is contained in a range.
Based on the discussion in [1] I would like to suggest the following
changes:
This patch attempts to improve the row estimation, as well as opening
the possibility of using a btree index scan when using the containment
operators.
This is done via a new support function handling the following 2 requests:
* SupportRequestIndexCondition
find_index_quals will build an operator clause, given at least one
finite RangeBound.
* SupportRequestSimplify
find_simplified_clause will rewrite the containment operator into a
clause using inequality operators from the btree family (if available
for the element type).
A boolean constant is returned if the range is either empty or has no
bounds.
Performing the rewrite here lets the clausesel machinery provide the
same estimates as for normal scalar inequalities.
In both cases build_bound_expr is used to build the operator clauses
from RangeBounds.
Thanks to Laurenz Albe for giving the patch a look before submission.
[1]
https://www.postgresql.org/message-id/222c75fd-43b8-db3e-74a6-bb4fe22f7...@kimmet.dk
Regards,
Kim Johan Anderssondiff --git a/src/backend/utils/adt/rangetypes.c
b/src/backend/utils/adt/rangetypes.c
index d65e5625c7..f20ca76d87 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,13 +31,19 @@
#include "postgres.h"
#include "access/tupmacs.h"
+#include "access/stratnum.h"
#include "common/hashfn.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/supportnodes.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pg_list.h"
#include "nodes/miscnodes.h"
#include "port/pg_bitutils.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
#include "utils/rangetypes.h"
@@ -69,7 +75,13 @@ static Size datum_compute_size(Size data_length, Datum val,
bool typbyval,
char typalign, int16
typlen, char typstorage);
static Pointer datum_write(Pointer ptr, Datum datum, bool typbyval,
char typalign, int16 typlen,
char typstorage);
-
+static Expr *build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound,
bool isInclusive,
+ Datum val, Expr
*otherExpr);
+static Node *find_index_quals(Const *rangeConst, Expr *otherExpr,
+ Oid opfamily);
+static Node *find_simplified_clause(Const *rangeConst, Expr *otherExpr);
+static Node *match_support_request(Node *rawreq);
/*
*----------------------------------------------------------
@@ -558,7 +570,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
-
/* range, range -> bool functions */
/* equality (internal version) */
@@ -2173,6 +2184,29 @@ make_empty_range(TypeCacheEntry *typcache)
return make_range(typcache, &lower, &upper, true, NULL);
}
+/*
+ * Planner support function for elem_contained_by_range operator
+ */
+Datum
+elem_contained_by_range_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
+
+/*
+ * Planner support function for range_contains_elem operator
+ */
+Datum
+range_contains_elem_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
/*
*----------------------------------------------------------
@@ -2714,3 +2748,277 @@ datum_write(Pointer ptr, Datum datum, bool typbyval,
char typalign,
return ptr;
}
+
+static Expr *
+build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache, bool isLowerBound,
bool isInclusive, Datum val, Expr *otherExpr)
+{
+ Oid elemType = typeCache->type_id;
+ int16 elemTypeLen = typeCache->typlen;
+ bool elemByValue = typeCache->typbyval;
+ Oid elemCollation = typeCache->typcollation;
+ int16 strategy;
+ Oid oproid;
+ Expr *constExpr;
+
+ if (isLowerBound)
+ strategy = isInclusive ? BTGreaterEqualStrategyNumber :
BTGreaterStrategyNumber;
+ else
+ strategy = isInclusive ? BTLessEqualStrategyNumber :
BTLessStrategyNumber;
+
+ oproid = get_opfamily_member(opfamily, elemType, elemType, strategy);
+
+ if (!OidIsValid(oproid))
+ return NULL;
+
+ constExpr = (Expr *) makeConst(elemType,
+ -1,
+
elemCollation,
+ elemTypeLen,
+ val,
+ false,
+ elemByValue);
+
+ return make_opclause(oproid,
+ BOOLOID,
+ false,
+ otherExpr,
+ constExpr,
+ InvalidOid,
+ InvalidOid);
+}
+
+/*
+ * find_index_quals
+ * Try to generate indexquals for an element contained in a range.
+ * We need at least one RangeBound to do anything useful here.
+ *
+ * Supports both the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM cases.
+ */
+static Node *
+find_index_quals(Const *rangeConst, Expr *otherExpr, Oid opfamily)
+{
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache =
lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+
+ range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+
+ /*
+ * The planner will call us for an empty range.find_simplified_clause
+ * should prevent this.
+ */
+ if (empty)
+ return NULL;
+
+ if (!(lower.infinite && upper.infinite))
+ {
+ /* At least one bound is available, we have something to work
with. */
+ List *result = NULL;
+ TypeCacheEntry *elemTypcache =
lookup_type_cache(rangetypcache->rngelemtype->type_id,
TYPECACHE_BTREE_OPFAMILY);
+
+ /* There might not be an operator family available for this
element */
+ if (!OidIsValid(elemTypcache->btree_opf))
+ return NULL;
+
+ if (!lower.infinite)
+ {
+ Expr *lowerExpr = build_bound_expr(opfamily,
+
elemTypcache,
+
true,
+
lower.inclusive,
+
lower.val,
+
otherExpr);
+
+ if (lowerExpr)
+ result = lappend(result, lowerExpr);
+ }
+
+ if (!upper.infinite)
+ {
+ Expr *upperExpr = build_bound_expr(opfamily,
+
elemTypcache,
+
false,
+
upper.inclusive,
+
upper.val,
+
otherExpr);
+
+ if (upperExpr)
+ result = lappend(result, upperExpr);
+ }
+
+ return (Node *) result;
+ }
+
+ return NULL;
+}
+
+/*
+ * find_simplified_clause
+ *
+ *
+ * Supports both the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM cases.
+ */
+static Node *
+find_simplified_clause(Const *rangeConst, Expr *otherExpr)
+{
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache =
lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+
+ range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+
+ if (empty)
+ {
+ /* If the range is empty, then there can be no matches. */
+ return makeBoolConst(false, false);
+ }
+ else if (lower.infinite && upper.infinite)
+ {
+ /* The range has no bounds, so matches everything. */
+ return makeBoolConst(true, false);
+ }
+ else
+ {
+ /* At least one bound is available, we have something to work
with. */
+ TypeCacheEntry *elemTypcache =
lookup_type_cache(rangetypcache->rngelemtype->type_id,
TYPECACHE_BTREE_OPFAMILY);
+ Expr *lowerExpr = NULL;
+ Expr *upperExpr = NULL;
+
+ /* There might not be an operator family available for this
element */
+ if (!OidIsValid(elemTypcache->btree_opf))
+ return NULL;
+
+ if (!lower.infinite)
+ {
+ lowerExpr = build_bound_expr(elemTypcache->btree_opf,
+
elemTypcache,
+
true,
+
lower.inclusive,
+
lower.val,
+
otherExpr);
+ }
+
+ if (!upper.infinite)
+ {
+ upperExpr = build_bound_expr(elemTypcache->btree_opf,
+
elemTypcache,
+
false,
+
upper.inclusive,
+
upper.val,
+
otherExpr);
+ }
+
+ if (lowerExpr != NULL && upperExpr != NULL)
+ return (Node *) makeBoolExpr(AND_EXPR,
list_make2(lowerExpr, upperExpr), -1);
+ else if (lowerExpr != NULL)
+ return (Node *) lowerExpr;
+ else if (upperExpr != NULL)
+ return (Node *) upperExpr;
+ }
+
+ return NULL;
+}
+
+static Node *
+match_support_request(Node *rawreq)
+{
+ if (IsA(rawreq, SupportRequestIndexCondition))
+ {
+ SupportRequestIndexCondition *req =
(SupportRequestIndexCondition *) rawreq;
+ OpExpr *clause;
+ Node *leftop;
+ Node *rightop;
+ Const *rangeConst;
+ Expr *otherExpr;
+ Node *result;
+
+ if (!is_opclause(req->node))
+ return NULL;
+
+ clause = (OpExpr *) req->node;
+ leftop = get_leftop(clause);
+ rightop = get_rightop(clause);
+
+ switch (req->funcid)
+ {
+ case F_ELEM_CONTAINED_BY_RANGE:
+
+ if (!IsA(rightop, Const) || ((Const *)
rightop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) rightop;
+ otherExpr = (Expr *) leftop;
+ break;
+
+ case F_RANGE_CONTAINS_ELEM:
+
+ if (!IsA(leftop, Const) || ((Const *)
leftop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) leftop;
+ otherExpr = (Expr *) rightop;
+ break;
+
+ default:
+ return NULL;
+ }
+
+ result = find_index_quals(rangeConst,
+ otherExpr,
+
req->opfamily);
+
+ /* If matched, the index condition is exact. */
+ if (result != NULL)
+ {
+ req->lossy = false;
+ }
+
+ return result;
+ }
+ else if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *clause = req->fcall;
+ Node *leftop;
+ Node *rightop;
+ Const *rangeConst;
+ Expr *otherExpr;
+
+ Assert(list_length(clause->args) == 2);
+
+ leftop = linitial(clause->args);
+ rightop = lsecond(clause->args);
+
+ switch (clause->funcid)
+ {
+ case F_ELEM_CONTAINED_BY_RANGE:
+
+ if (!IsA(rightop, Const) || ((Const *)
rightop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) rightop;
+ otherExpr = (Expr *) leftop;
+ break;
+
+ case F_RANGE_CONTAINS_ELEM:
+
+ if (!IsA(leftop, Const) || ((Const *)
leftop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) leftop;
+ otherExpr = (Expr *) rightop;
+ break;
+
+ default:
+ return NULL;
+ }
+
+ return find_simplified_clause(rangeConst, otherExpr);
+ }
+
+ return NULL;
+}
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c
b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e18c..7c4cf0aef3 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -196,9 +196,9 @@ rangesel(PG_FUNCTION_ARGS)
else if (operator == OID_RANGE_ELEM_CONTAINED_OP)
{
/*
- * Here, the Var is the elem, not the range. For now we just
punt and
- * return the default estimate. In future we could disassemble
the
- * range constant and apply scalarineqsel ...
+ * Here, the Var is the elem, not the range.
+ * The support function in rangetypes.c should have simplified
this case,
+ * enabling the clausesel.c machinery to handle it.
*/
}
else if (((Const *) other)->consttype == vardata.vartype)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b2bc81b15f..2b59180ff9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10474,13 +10474,15 @@
proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
{ oid => '3858',
proname => 'range_contains_elem', prorettype => 'bool',
- proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem' },
+ proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem',
+ prosupport => 'range_contains_elem_support' },
{ oid => '3859',
proname => 'range_contains', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contains' },
{ oid => '3860',
proname => 'elem_contained_by_range', prorettype => 'bool',
- proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range' },
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range',
+ prosupport => 'elem_contained_by_range_support' },
{ oid => '3861',
proname => 'range_contained_by', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contained_by' },
@@ -10502,6 +10504,12 @@
{ oid => '3867',
proname => 'range_union', prorettype => 'anyrange',
proargtypes => 'anyrange anyrange', prosrc => 'range_union' },
+{ oid => '9998', descr => 'Planner support function for range_contains_elem
operator',
+ proname => 'range_contains_elem_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'range_contains_elem_support' },
+{ oid => '9999', descr => 'Planner support function for
elem_contained_by_range operator',
+ proname => 'elem_contained_by_range_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'elem_contained_by_range_support' },
{ oid => '4057',
descr => 'the smallest range which includes both of the given ranges',
proname => 'range_merge', prorettype => 'anyrange',
diff --git a/src/test/regress/expected/rangetypes.out
b/src/test/regress/expected/rangetypes.out
index ee02ff0163..cc149007d0 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,303 @@ create function table_fail(i anyelement) returns
table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
ERROR: cannot determine result data type
DETAIL: A result of type anyrange requires at least one input of type
anyrange or anymultirange.
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE integer_support_test AS
+(
+ SELECT
+ some_number
+ FROM
+ (
+ SELECT
+ generate_series AS some_number
+ FROM
+ generate_series(-1000, 1000)
+ ) q
+);
+CREATE UNIQUE INDEX ON integer_support_test( some_number );
+ANALYZE integer_support_test;
+-- No bounds, so not a bounded range:
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(null, null);
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=2001 loops=1)
+(1 row)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(null, null) @>
some_number;
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=2001 loops=1)
+(1 row)
+
+-- Empty ranges
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@ int4range(0,
0, '()');
+ QUERY PLAN
+--------------------------------
+ Result (actual rows=0 loops=1)
+ One-Time Filter: false
+(2 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(0, 0, '()') @>
some_number;
+ QUERY PLAN
+--------------------------------
+ Result (actual rows=0 loops=1)
+ One-Time Filter: false
+(2 rows)
+
+-- Only lower bound present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, null, '[]');
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=1003 loops=1)
+ Filter: (some_number >= '-2'::integer)
+ Rows Removed by Filter: 998
+(3 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, null, '[]')
@> some_number;
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=1003 loops=1)
+ Filter: (some_number >= '-2'::integer)
+ Rows Removed by Filter: 998
+(3 rows)
+
+-- Only upper bound present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(null, 2, '[]');
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=1003 loops=1)
+ Filter: (some_number < 3)
+ Rows Removed by Filter: 998
+(3 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(null, 2, '[]') @>
some_number;
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on integer_support_test (actual rows=1003 loops=1)
+ Filter: (some_number < 3)
+ Rows Removed by Filter: 998
+(3 rows)
+
+-- Both bounds present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 2, '[]');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 2, '[]');
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 2, '[]') @>
some_number;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 2, '[]') @>
some_number;
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+-- Both bounds present, upper is not inclusive.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 3, '[)');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 3, '[)');
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 3, '[)') @>
some_number;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 3, '[)') @>
some_number;
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+-- Both bounds present, lower is not inclusive.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-3, 2, '(]');
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-3, 2, '(]');
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-3, 2, '(]') @>
some_number;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Index Only Scan using integer_support_test_some_number_idx on
integer_support_test (actual rows=5 loops=1)
+ Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
+ Heap Fetches: 5
+(3 rows)
+
+SELECT some_number FROM integer_support_test WHERE int4range(-3, 2, '(]') @>
some_number;
+ some_number
+-------------
+ -2
+ -1
+ 0
+ 1
+ 2
+(5 rows)
+
+DROP TABLE integer_support_test;
+-- Try out a type that has special values (+/- infinity):
+CREATE TABLE date_support_test AS
+(
+ SELECT
+ some_date
+ FROM
+ (
+ SELECT
+ '2000-01-01'::DATE + generate_series AS
some_date
+ FROM
+ generate_series(-1000, 1000)
+ ) q
+);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+-- No bounds, so not a bounded range.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null,
null);
+ QUERY PLAN
+----------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=2003 loops=1)
+(1 row)
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in
the range
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '()');
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=1000 loops=1)
+ Filter: ((some_date > '-infinity'::date) AND (some_date <
'01-01-2000'::date))
+ Rows Removed by Filter: 1003
+(3 rows)
+
+-- Should return 1001 rows, since -infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '[)');
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=1001 loops=1)
+ Filter: ((some_date >= '-infinity'::date) AND (some_date <
'01-01-2000'::date))
+ Rows Removed by Filter: 1002
+(3 rows)
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=1002 loops=1)
+ Filter: ((some_date >= '-infinity'::date) AND (some_date <
'01-02-2000'::date))
+ Rows Removed by Filter: 1001
+(3 rows)
+
+-- Should return 1001 rows, since infinity not included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('2000-01-01'::DATE, 'infinity', '[)');
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=1001 loops=1)
+ Filter: ((some_date >= '01-01-2000'::date) AND (some_date <
'infinity'::date))
+ Rows Removed by Filter: 1002
+(3 rows)
+
+-- Should return 1002 rows, since infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('2000-01-01'::DATE, 'infinity', '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Seq Scan on date_support_test (actual rows=1002 loops=1)
+ Filter: ((some_date >= '01-01-2000'::date) AND (some_date <=
'infinity'::date))
+ Rows Removed by Filter: 1001
+(3 rows)
+
+-- Should return 1 rows, since just infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('infinity', 'infinity', '[]');
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_some_date_idx on date_support_test
(actual rows=1 loops=1)
+ Index Cond: ((some_date >= 'infinity'::date) AND (some_date <=
'infinity'::date))
+ Heap Fetches: 1
+(3 rows)
+
+-- Should return 0 rows, since this is up to, but not including infinity
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('infinity', 'infinity', '[)');
+ QUERY PLAN
+--------------------------------
+ Result (actual rows=0 loops=1)
+ One-Time Filter: false
+(2 rows)
+
+DROP TABLE date_support_test;
diff --git a/src/test/regress/sql/rangetypes.sql
b/src/test/regress/sql/rangetypes.sql
index c23be928c3..bf0f0db89c 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,123 @@ create function inoutparam_fail(inout i anyelement, out r
anyrange)
--should fail
create function table_fail(i anyelement) returns table(i anyelement, r
anyrange)
as $$ select $1, '[1,10]' $$ language sql;
+
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE integer_support_test AS
+(
+ SELECT
+ some_number
+ FROM
+ (
+ SELECT
+ generate_series AS some_number
+ FROM
+ generate_series(-1000, 1000)
+ ) q
+);
+CREATE UNIQUE INDEX ON integer_support_test( some_number );
+ANALYZE integer_support_test;
+
+-- No bounds, so not a bounded range:
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(null, null);
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(null, null) @>
some_number;
+
+-- Empty ranges
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@ int4range(0,
0, '()');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(0, 0, '()') @>
some_number;
+
+-- Only lower bound present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, null, '[]');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, null, '[]')
@> some_number;
+
+-- Only upper bound present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(null, 2, '[]');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(null, 2, '[]') @>
some_number;
+
+-- Both bounds present
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 2, '[]');
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 2, '[]');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 2, '[]') @>
some_number;
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 2, '[]') @>
some_number;
+
+-- Both bounds present, upper is not inclusive.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 3, '[)');
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-2, 3, '[)');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 3, '[)') @>
some_number;
+SELECT some_number FROM integer_support_test WHERE int4range(-2, 3, '[)') @>
some_number;
+
+-- Both bounds present, lower is not inclusive.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-3, 2, '(]');
+SELECT some_number FROM integer_support_test WHERE some_number <@
int4range(-3, 2, '(]');
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_number FROM integer_support_test WHERE int4range(-3, 2, '(]') @>
some_number;
+SELECT some_number FROM integer_support_test WHERE int4range(-3, 2, '(]') @>
some_number;
+
+DROP TABLE integer_support_test;
+
+-- Try out a type that has special values (+/- infinity):
+CREATE TABLE date_support_test AS
+(
+ SELECT
+ some_date
+ FROM
+ (
+ SELECT
+ '2000-01-01'::DATE + generate_series AS
some_date
+ FROM
+ generate_series(-1000, 1000)
+ ) q
+);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+
+-- No bounds, so not a bounded range.
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null,
null);
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in
the range
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '()');
+
+-- Should return 1001 rows, since -infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '[)');
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+-- Should return 1001 rows, since infinity not included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('2000-01-01'::DATE, 'infinity', '[)');
+
+-- Should return 1002 rows, since infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+-- Should return 1 rows, since just infinity is included here
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('infinity', 'infinity', '[]');
+
+-- Should return 0 rows, since this is up to, but not including infinity
+EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF, COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@
daterange('infinity', 'infinity', '[)');
+
+DROP TABLE date_support_test;
