Add new selectivity estimation functions for pattern-matching operators

(LIKE and regexp matches).  These are not yet referenced in pg_operator,
so by default the system will continue to use eqsel/neqsel.
Also, tweak convert_to_scalar() logic so that common prefixes of strings
are stripped off, allowing better accuracy when all strings in a table
share a common prefix.

2 files changed, 85 insertions, 11 deletions

diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 34d4494b23..6995587c0c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.132 2000/04/12 17:16:29 momjian Exp $
+ * $Id: pg_proc.h,v 1.133 2000/04/16 04:41:03 tgl Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2436,6 +2436,32 @@ DESCR("convert text to timestamp");
 DATA(insert OID = 1780 ( to_date			PGUID 11 f t f 2 f	1082 "25 25" 100 0 0 100  to_date - ));
 DESCR("convert text to date");
 
+/* Selectivity estimators for LIKE and related operators */
+DATA(insert OID = 1818 ( regexeqsel			PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  regexeqsel - ));
+DESCR("restriction selectivity of regex match");
+DATA(insert OID = 1819 ( likesel			PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  likesel - ));
+DESCR("restriction selectivity of LIKE");
+DATA(insert OID = 1820 ( icregexeqsel		PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  icregexeqsel - ));
+DESCR("restriction selectivity of case-insensitive regex match");
+DATA(insert OID = 1821 ( regexnesel			PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  regexnesel - ));
+DESCR("restriction selectivity of regex non-match");
+DATA(insert OID = 1822 ( nlikesel			PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  nlikesel - ));
+DESCR("restriction selectivity of NOT LIKE");
+DATA(insert OID = 1823 ( icregexnesel		PGUID 11 f t f 5 f 701 "26 26 21 0 23" 100 0 0 100  icregexnesel - ));
+DESCR("restriction selectivity of case-insensitive regex non-match");
+DATA(insert OID = 1824 ( regexeqjoinsel		PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	regexeqjoinsel - ));
+DESCR("join selectivity of regex match");
+DATA(insert OID = 1825 ( likejoinsel		PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	likejoinsel - ));
+DESCR("join selectivity of LIKE");
+DATA(insert OID = 1826 ( icregexeqjoinsel	PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	icregexeqjoinsel - ));
+DESCR("join selectivity of case-insensitive regex match");
+DATA(insert OID = 1827 ( regexnejoinsel		PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	regexnejoinsel - ));
+DESCR("join selectivity of regex non-match");
+DATA(insert OID = 1828 ( nlikejoinsel		PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	nlikejoinsel - ));
+DESCR("join selectivity of NOT LIKE");
+DATA(insert OID = 1829 ( icregexnejoinsel	PGUID 11 f t f 5 f 701 "26 26 21 26 21" 100 0 0 100	icregexnejoinsel - ));
+DESCR("join selectivity of case-insensitive regex non-match");
+
 
 /*
  * prototypes for functions pg_proc.c
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 80d91aba75..16bff65054 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: builtins.h,v 1.110 2000/04/12 17:16:54 momjian Exp $
+ * $Id: builtins.h,v 1.111 2000/04/16 04:41:03 tgl Exp $
  *
  * NOTES
  *	  This should normally only be included by fmgr.h.
@@ -371,15 +371,47 @@ extern char *deparse_expression(Node *expr, List *rangetables,
 				   bool forceprefix);
 
 /* selfuncs.c */
-extern float64 eqsel(Oid opid, Oid relid, AttrNumber attno, Datum value, int32 flag);
-extern float64 neqsel(Oid opid, Oid relid, AttrNumber attno, Datum value, int32 flag);
-extern float64 scalarltsel(Oid opid, Oid relid, AttrNumber attno, Datum value, int32 flag);
-extern float64 scalargtsel(Oid opid, Oid relid, AttrNumber attno, Datum value, int32 flag);
-extern float64 eqjoinsel(Oid opid, Oid relid1, AttrNumber attno1, Oid relid2, AttrNumber attno2);
-extern float64 neqjoinsel(Oid opid, Oid relid1, AttrNumber attno1, Oid relid2, AttrNumber attno2);
-extern float64 scalarltjoinsel(Oid opid, Oid relid1, AttrNumber attno1, Oid relid2, AttrNumber attno2);
-extern float64 scalargtjoinsel(Oid opid, Oid relid1, AttrNumber attno1, Oid relid2, AttrNumber attno2);
-extern bool convert_to_scalar(Datum value, Oid typid, double *scaleval);
+extern float64 eqsel(Oid opid, Oid relid, AttrNumber attno,
+					 Datum value, int32 flag);
+extern float64 neqsel(Oid opid, Oid relid, AttrNumber attno,
+					  Datum value, int32 flag);
+extern float64 scalarltsel(Oid opid, Oid relid, AttrNumber attno,
+						   Datum value, int32 flag);
+extern float64 scalargtsel(Oid opid, Oid relid, AttrNumber attno,
+						   Datum value, int32 flag);
+extern float64 regexeqsel(Oid opid, Oid relid, AttrNumber attno,
+						  Datum value, int32 flag);
+extern float64 likesel(Oid opid, Oid relid, AttrNumber attno,
+					   Datum value, int32 flag);
+extern float64 icregexeqsel(Oid opid, Oid relid, AttrNumber attno,
+							Datum value, int32 flag);
+extern float64 regexnesel(Oid opid, Oid relid, AttrNumber attno,
+						  Datum value, int32 flag);
+extern float64 nlikesel(Oid opid, Oid relid, AttrNumber attno,
+						Datum value, int32 flag);
+extern float64 icregexnesel(Oid opid, Oid relid, AttrNumber attno,
+							Datum value, int32 flag);
+
+extern float64 eqjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+						 Oid relid2, AttrNumber attno2);
+extern float64 neqjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+						  Oid relid2, AttrNumber attno2);
+extern float64 scalarltjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+							   Oid relid2, AttrNumber attno2);
+extern float64 scalargtjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+							   Oid relid2, AttrNumber attno2);
+extern float64 regexeqjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+							  Oid relid2, AttrNumber attno2);
+extern float64 likejoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+						   Oid relid2, AttrNumber attno2);
+extern float64 icregexeqjoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+								Oid relid2, AttrNumber attno2);
+extern float64 regexnejoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+							  Oid relid2, AttrNumber attno2);
+extern float64 nlikejoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+							Oid relid2, AttrNumber attno2);
+extern float64 icregexnejoinsel(Oid opid, Oid relid1, AttrNumber attno1,
+								Oid relid2, AttrNumber attno2);
 
 extern void btcostestimate(Query *root, RelOptInfo *rel,
 			   IndexOptInfo *index, List *indexQuals,
@@ -402,6 +434,22 @@ extern void gistcostestimate(Query *root, RelOptInfo *rel,
 				 Cost *indexTotalCost,
 				 Selectivity *indexSelectivity);
 
+typedef enum
+{
+	Pattern_Type_Like, Pattern_Type_Regex, Pattern_Type_Regex_IC
+} Pattern_Type;
+
+typedef enum
+{
+	Pattern_Prefix_None, Pattern_Prefix_Partial, Pattern_Prefix_Exact
+} Pattern_Prefix_Status;
+
+extern Pattern_Prefix_Status pattern_fixed_prefix(char *patt,
+												  Pattern_Type ptype,
+												  char **prefix,
+												  char **rest);
+extern char *make_greater_string(const char *str, Oid datatype);
+
 /* tid.c */
 extern ItemPointer tidin(const char *str);
 extern char *tidout(ItemPointer itemPtr);