Subject: Re: [PATCH] Add support function for containment operators

From: Laurenz Albe <[email-masked]>
Date: Fri, 07 Jul 2023 13:20:41 +0200

I wrote:
> You implement both "SupportRequestIndexCondition" and "SupportRequestSimplify",
> but when I experimented, the former was never called.  That does not
> surprise me, since any expression of the shape "expr <@ range constant"
> can be simplified.  Is the "SupportRequestIndexCondition" branch dead code?
> If not, do you have an example that triggers it?

I had an idea about this:
So far, you only consider constant ranges.  But if we have a STABLE range
expression, you could use an index scan for "expr <@ range", for example
Index Cond (expr >= lower(range) AND expr < upper(range)).

Yours,
Laurenz Albe

---

From: Kim Johan Andersson <[email-masked]>
Date: Sat, 8 Jul 2023 08:11:10 +0200

>> If not, do you have an example that triggers it?

I would think it is dead code, I came to the same conclusion. So we can 
drop SupportRequestIndexCondition, since the simplification happens to 
take care of everything.

> Index Cond (expr >= lower(range) AND expr < upper(range)).

I will try to look into this. Originally that was what I was hoping for, 
but didn't see way of going about it.

Thanks for your comments, I will also look at the locale-related 
breakage you spotted.

	Regards,
		Kimjand

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 01 Aug 2023 04:07:24 +0200

> Thanks for your comments, I will also look at the locale-related 

I have marked the patch as "returned with feedback".

I encourage you to submit an improved version in a future commitfest!

Yours,
Laurenz Albe

---

From: jian he <[email-masked]>
Date: Fri, 13 Oct 2023 14:26:01 +0800

> > > Index Cond (expr >= lower(range) AND expr < upper(range)).

The above part, not sure how to implement it, not sure it is doable.

Refactor:
drop SupportRequestIndexCondition and related code, since mentioned in
upthread, it's dead code.
refactor the regression test. (since data types with infinity cover
more cases than int4range, so I deleted some tests).

now there are 3 helper functions (build_bound_expr,
find_simplified_clause, match_support_request), 2 entry functions
(elem_contained_by_range_support, range_contains_elem_support)

Collation problem seems solved. Putting the following test on the
src/test/regress/sql/rangetypes.sql will not work. Maybe because of
the order of the regression test, in SQL-ASCII encoding, I cannot
create collation="cs-CZ-x-icu".

drop type if EXISTS textrange1, textrange2;
drop table if EXISTS collate_test1, collate_test2;
CREATE TYPE textrange1 AS RANGE (SUBTYPE = text, collation="C");
create type textrange2 as range(subtype=text, collation="cs-CZ-x-icu");
CREATE TABLE collate_test1 (b text COLLATE "en-x-icu" NOT NULL);
INSERT INTO collate_test1(b) VALUES ('a'), ('c'), ('d'), ('ch');
CREATE TABLE collate_test2 (b text NOT NULL);
INSERT INTO collate_test2(b) VALUES ('a'), ('c'), ('d'), ('ch');

--should include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange2('a', 'd');
--should include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange2('a', 'd');
-----------------
From bcae7f8a0640b48b04f243660539e8670de43d41 Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Fri, 13 Oct 2023 12:43:41 +0800
Subject: [PATCH v2 1/1] Optimize qual cases like Expr <@ RangeConst and
 RangeConst @> Expr

Previously these two quals will be processed as is.
With this patch, adding prosupport
function to range_contains_elem, elem_contained_by_range.

So cases like Expr <@ rangeCOnst, rangeConst @> expr
will be rewritten to "expr opr range_lower_bound and expr opr
range_upper_bound".

Expr <@ rangeConst will be logically equivalent to rangeConst @> Expr,
if rangeConst is the same. added some tests to validate the generated
plan.
---
 src/backend/utils/adt/rangetypes.c          | 199 +++++++++++++++++++-
 src/backend/utils/adt/rangetypes_selfuncs.c |   6 +-
 src/include/catalog/pg_proc.dat             |  12 +-
 src/test/regress/expected/rangetypes.out    | 194 +++++++++++++++++++
 src/test/regress/sql/rangetypes.sql         | 101 ++++++++++
 5 files changed, 505 insertions(+), 7 deletions(-)

diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625..647bd5e4 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,11 @@ 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, Oid rng_collation);
+static Node *find_simplified_clause(Const *rangeConst, Expr *otherExpr);
+static Node *match_support_request(Node *rawreq);
 
 /*
  *----------------------------------------------------------
@@ -558,7 +568,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 +2182,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 +2746,166 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
 
 	return ptr;
 }
+/*
+ * build (Expr Operator Range_bound) expression. something like: expr >= lower(range)
+ * if operator both sides have collation, operator should use (right) range's collation
+ *
+*/
+static Expr *
+build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+					bool isLowerBound, bool isInclusive,
+					Datum val, Expr *otherExpr, Oid rng_collation)
+{
+	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,
+						 rng_collation
+						 );
+}
+
+/*
+ * 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;
+	Oid			rng_collation;
+
+	range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+
+	rng_collation = rangetypcache->rng_collation;
+
+	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,
+										 rng_collation
+										 );
+		}
+
+		if (!upper.infinite)
+		{
+			upperExpr = build_bound_expr(elemTypcache->btree_opf,
+										 elemTypcache,
+										 false,
+										 upper.inclusive,
+										 upper.val,
+										 otherExpr,
+										 rng_collation
+										 );
+		}
+
+		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, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr   *fexpr = req->fcall;
+		Node	   *leftop;
+		Node	   *rightop;
+		Const	   *rangeConst;
+		Expr	   *otherExpr;
+
+		Assert(list_length(fexpr->args) == 2);
+
+		leftop = linitial(fexpr->args);
+		rightop = lsecond(fexpr->args);
+
+		switch (fexpr->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;
+}
\ No newline at end of file
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e1..7c4cf0ae 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 f0b7b9cb..3777ce4a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10468,13 +10468,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' },
@@ -10496,6 +10498,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 ee02ff01..02f3f29e 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,197 @@ 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 date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+				generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+  declare ln1 text default '';
+          ln2 text default '';
+BEGIN
+  execute $1 into ln1;
+  execute $2 into ln2;
+    if ln1 = ln2 and ln1 != '' and ln2 != '' then
+      RAISE NOTICE 'these two query''plan is the same';
+    end if;
+END;
+$$
+LANGUAGE plpgsql;
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+                 QUERY PLAN                  
+---------------------------------------------
+ Seq Scan on date_support_test
+   Filter: (some_date >= '01-01-2000'::date)
+(2 rows)
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on date_support_test
+   Filter: (some_date < '01-02-2000'::date)
+(2 rows)
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+          QUERY PLAN           
+-------------------------------
+ Seq Scan on date_support_test
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+                                       QUERY PLAN                                       
+----------------------------------------------------------------------------------------
+ Aggregate
+   ->  Seq Scan on date_support_test
+         Filter: ((some_date > '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ count 
+-------
+  1000
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Aggregate
+   ->  Seq Scan on date_support_test
+         Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ count 
+-------
+  1001
+(1 row)
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+          SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+          $$,
+      $$
+      EXPLAIN (COSTS OFF)
+      SELECT count(some_date) FROM date_support_test
+      WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+      $$);
+NOTICE:  these two query'plan is the same
+ check2plan 
+------------
+ 
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Aggregate
+   ->  Seq Scan on date_support_test
+         Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-02-2000'::date))
+(3 rows)
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ count 
+-------
+  1002
+(1 row)
+
+select check2plan($a$
+              EXPLAIN (COSTS OFF)
+              SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+              $a$,
+      $b$
+      EXPLAIN (COSTS OFF)
+      SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+      $b$);
+NOTICE:  these two query'plan is the same
+ check2plan 
+------------
+ 
+(1 row)
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+ count 
+-------
+  1001
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Aggregate
+   ->  Seq Scan on date_support_test
+         Filter: ((some_date >= '01-01-2000'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ count 
+-------
+  1002
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+                                        QUERY PLAN                                         
+-------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Index Only Scan using date_support_test_some_date_idx on date_support_test
+         Index Cond: ((some_date >= 'infinity'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ count 
+-------
+     1
+(1 row)
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ count 
+-------
+     0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+           QUERY PLAN           
+--------------------------------
+ Aggregate
+   ->  Result
+         One-Time Filter: false
+(3 rows)
+
+DROP TABLE date_support_test;
+drop function check2plan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928..7d6d1799 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,104 @@ 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 date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+				generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+  declare ln1 text default '';
+          ln2 text default '';
+BEGIN
+  execute $1 into ln1;
+  execute $2 into ln2;
+    if ln1 = ln2 and ln1 != '' and ln2 != '' then
+      RAISE NOTICE 'these two query''plan is the same';
+    end if;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+          SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+          $$,
+      $$
+      EXPLAIN (COSTS OFF)
+      SELECT count(some_date) FROM date_support_test
+      WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+      $$);
+
+EXPLAIN (COSTS OFF)
+SELECT count(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
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+select check2plan($a$
+              EXPLAIN (COSTS OFF)
+              SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+              $a$,
+      $b$
+      EXPLAIN (COSTS OFF)
+      SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+      $b$);
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+-- Should return 1 rows, since just infinity is included here
+SELECT count(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
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+DROP TABLE date_support_test;
+drop function check2plan;
\ No newline at end of file
-- 
2.34.1

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 19 Oct 2023 18:01:40 +0200

On Fri, 2023-10-13 at 14:26 +0800, jian he wrote:
> Collation problem seems solved.

I didn't review your patch in detail, there is still a problem
with my example:

  CREATE TYPE textrange AS RANGE (
     SUBTYPE = text,
     SUBTYPE_OPCLASS = text_pattern_ops
  );

  CREATE TABLE tx (t text COLLATE "cs-CZ-x-icu");

  INSERT INTO tx VALUES ('a'), ('c'), ('d'), ('ch');

  SELECT * FROM tx WHERE t <@ textrange('a', 'd');

   t  
  ════
   a
   c
   ch
  (3 rows)

That was correct.

  EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');

                       QUERY PLAN                     
  ════════════════════════════════════════════════════
   Seq Scan on tx  (cost=0.00..30.40 rows=7 width=32)
     Filter: ((t >= 'a'::text) AND (t < 'd'::text))
  (2 rows)

But that was weird.  The operators seem wrong.  Look at that
query:

  SELECT * FROM tx WHERE t >= 'a' AND t < 'd';

   t 
  ═══
   a
   c
  (2 rows)

But the execution plan is identical...

I am not sure what is the problem here, but in my opinion the
operators shown in the execution plan should be like this:

  SELECT * FROM tx WHERE t ~>=~ 'a' AND t ~<~ 'd';

   t  
  ════
   a
   c
   ch
  (3 rows)

Yours,
Laurenz Albe

---

From: jian he <[email-masked]>
Date: Fri, 20 Oct 2023 16:24:24 +0800

>   EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');

Thanks for pointing this out!

The problem is that TypeCacheEntry->rngelemtype typcaheentry don't
have the range's SUBTYPE_OPCLASS info.
So in find_simplified_clause, we need to get the range's
SUBTYPE_OPCLASS from the pg_catalog table.
Also in pg_range, column rngsubopc  is not null. so this should be fine.
From 810208a42e99109bcaf56cddae6968efbcf969c5 Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Fri, 20 Oct 2023 16:20:38 +0800
Subject: [PATCH v3 1/1]  Optimize quals (Expr <@ RangeConst) and (RangeConst
 @> Expr)

Previously these two quals will be processed as is.
This patch will rewritten the expression to expose more info to optimizer by
adding prosupport function to range_contains_elem, elem_contained_by_range.

Expr <@ rangeConst will be rewritten to "expr opr range_lower_bound and expr opr
range_upper_bound". (range bound inclusiveness will be handled properly).

Added some tests to validate the generated plan.
---
 src/backend/utils/adt/rangetypes.c          | 229 +++++++++++++++++++-
 src/backend/utils/adt/rangetypes_selfuncs.c |   6 +-
 src/include/catalog/pg_proc.dat             |  12 +-
 src/test/regress/expected/rangetypes.out    | 194 +++++++++++++++++
 src/test/regress/sql/rangetypes.sql         | 101 +++++++++
 5 files changed, 535 insertions(+), 7 deletions(-)

diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625..d100e55e 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,16 +31,24 @@
 #include "postgres.h"
 
+#include "catalog/pg_range.h"
+#include "utils/syscache.h"
 #include "utils/timestamp.h"
 #include "varatt.h"
 
@@ -69,7 +77,11 @@ static Size datum_compute_size(Size data_length, Datum val, bool typbyval,
 
 /*
  *----------------------------------------------------------
@@ -558,7 +570,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
 	PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
 }
 
-
 
 /* equality (internal version) */
@@ -2173,6 +2184,29 @@ make_empty_range(TypeCacheEntry *typcache)
 	return make_range(typcache, &lower, &upper, true, NULL);
 }
 
+/*
 
 /*
  *----------------------------------------------------------
@@ -2714,3 +2748,194 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
 
 	return ptr;
 }
+/*
+	Form_pg_range pg_range;
+	HeapTuple	tup;
+	Oid			opclassOid;
+	TypeCacheEntry *elemTypcache;
+	Oid			opfamily =	InvalidOid;
+		/* this part is get the range's SUBTYPE_OPCLASS from pg_range catalog.
+		 * Refer load_rangetype_info function last line.
+		 * TypeCacheEntry->rngelemtype typcaheenetry either don't have opclass entry or with default opclass.
+		 * Range's subtype opclass only in catalog table.
+		tup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(RangeTypeGetOid(range)));
+		/* should not fail, since we already checked typtype ... */
+		if (!HeapTupleIsValid(tup))
+			elog(ERROR, "cache lookup failed for range type %u", RangeTypeGetOid(range));
+		pg_range = (Form_pg_range) GETSTRUCT(tup);
+		opclassOid = pg_range->rngsubopc;
+		ReleaseSysCache(tup);
+		/* get opclass properties and look up the comparison function */
+		opfamily = get_opclass_family(opclassOid);
+		if (!OidIsValid(opfamily))
+		/* flags set to 0 should be fine. Since already get the opfamily */
+		elemTypcache = lookup_type_cache(rangetypcache->rngelemtype->type_id, 0);
+			lowerExpr = build_bound_expr(opfamily,
+			upperExpr = build_bound_expr(opfamily,
index c92d0631..6475bec6 100644
@@ -10477,13 +10477,15 @@
@@ -10505,6 +10507,12 @@