Add a planner support function for starts_with().

This fills in some gaps in planner support for starts_with() and
the equivalent ^@ operator:

* A condition such as "textcol ^@ constant" can now use a regular
btree index, not only an SP-GiST index, so long as the index's
collation is C.  (This works just like "textcol LIKE 'foo%'".)

* "starts_with(textcol, constant)" can be optimized the same as
"textcol ^@ constant".

* Fixed-prefix LIKE and regex patterns are now more like starts_with()
in another way: if you apply one to an SPGiST-indexed column, you'll
get an index condition using ^@ rather than two index conditions with
>= and <.

Per a complaint from Shay Rojansky.  Patch by me; thanks to
Nathan Bossart for review.

Discussion: https://postgr.es/m/232599.1633800229@sss.pgh.pa.us

1 files changed, 46 insertions, 19 deletions

diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 241e6f0f598..988568825e0 100644
--- a/src/backend/utils/adt/like_support.c
+++ b/src/backend/utils/adt/like_support.c
@@ -143,6 +143,14 @@ texticregexeq_support(PG_FUNCTION_ARGS)
 	PG_RETURN_POINTER(like_regex_support(rawreq, Pattern_Type_Regex_IC));
 }
 
+Datum
+text_starts_with_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+	PG_RETURN_POINTER(like_regex_support(rawreq, Pattern_Type_Prefix));
+}
+
 /* Common code for the above */
 static Node *
 like_regex_support(Node *rawreq, Pattern_Type ptype)
@@ -246,6 +254,7 @@ match_pattern_prefix(Node *leftop,
 	Oid			eqopr;
 	Oid			ltopr;
 	Oid			geopr;
+	Oid			preopr = InvalidOid;
 	bool		collation_aware;
 	Expr	   *expr;
 	FmgrInfo	ltproc;
@@ -302,12 +311,20 @@ match_pattern_prefix(Node *leftop,
 	switch (ldatatype)
 	{
 		case TEXTOID:
-			if (opfamily == TEXT_PATTERN_BTREE_FAM_OID ||
-				opfamily == TEXT_SPGIST_FAM_OID)
+			if (opfamily == TEXT_PATTERN_BTREE_FAM_OID)
+			{
+				eqopr = TextEqualOperator;
+				ltopr = TextPatternLessOperator;
+				geopr = TextPatternGreaterEqualOperator;
+				collation_aware = false;
+			}
+			else if (opfamily == TEXT_SPGIST_FAM_OID)
 			{
 				eqopr = TextEqualOperator;
 				ltopr = TextPatternLessOperator;
 				geopr = TextPatternGreaterEqualOperator;
+				/* This opfamily has direct support for prefixing */
+				preopr = TextPrefixOperator;
 				collation_aware = false;
 			}
 			else
@@ -361,20 +378,6 @@ match_pattern_prefix(Node *leftop,
 	}
 
 	/*
-	 * If necessary, verify that the index's collation behavior is compatible.
-	 * For an exact-match case, we don't have to be picky.  Otherwise, insist
-	 * that the index collation be "C".  Note that here we are looking at the
-	 * index's collation, not the expression's collation -- this test is *not*
-	 * dependent on the LIKE/regex operator's collation.
-	 */
-	if (collation_aware)
-	{
-		if (!(pstatus == Pattern_Prefix_Exact ||
-			  lc_collate_is_c(indexcollation)))
-			return NIL;
-	}
-
-	/*
 	 * If necessary, coerce the prefix constant to the right type.  The given
 	 * prefix constant is either text or bytea type, therefore the only case
 	 * where we need to do anything is when converting text to bpchar.  Those
@@ -409,8 +412,31 @@ match_pattern_prefix(Node *leftop,
 	}
 
 	/*
-	 * Otherwise, we have a nonempty required prefix of the values.
-	 *
+	 * Otherwise, we have a nonempty required prefix of the values.  Some
+	 * opclasses support prefix checks directly, otherwise we'll try to
+	 * generate a range constraint.
+	 */
+	if (OidIsValid(preopr) && op_in_opfamily(preopr, opfamily))
+	{
+		expr = make_opclause(preopr, BOOLOID, false,
+							 (Expr *) leftop, (Expr *) prefix,
+							 InvalidOid, indexcollation);
+		result = list_make1(expr);
+		return result;
+	}
+
+	/*
+	 * Since we need a range constraint, it's only going to work reliably if
+	 * the index is collation-insensitive or has "C" collation.  Note that
+	 * here we are looking at the index's collation, not the expression's
+	 * collation -- this test is *not* dependent on the LIKE/regex operator's
+	 * collation.
+	 */
+	if (collation_aware &&
+		!lc_collate_is_c(indexcollation))
+		return NIL;
+
+	/*
 	 * We can always say "x >= prefix".
 	 */
 	if (!op_in_opfamily(geopr, opfamily))
@@ -1165,7 +1191,6 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, Oid collation,
 		case Pattern_Type_Prefix:
 			/* Prefix type work is trivial.  */
 			result = Pattern_Prefix_Partial;
-			*rest_selec = 1.0;	/* all */
 			*prefix = makeConst(patt->consttype,
 								patt->consttypmod,
 								patt->constcollid,
@@ -1175,6 +1200,8 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, Oid collation,
 										  patt->constlen),
 								patt->constisnull,
 								patt->constbyval);
+			if (rest_selec != NULL)
+				*rest_selec = 1.0;	/* all */
 			break;
 		default:
 			elog(ERROR, "unrecognized ptype: %d", (int) ptype);