Allow extensions to generate lossy index conditions.

For a long time, indxpath.c has had the ability to extract derived (lossy)
index conditions from certain operators such as LIKE.  For just as long,
it's been obvious that we really ought to make that capability available
to extensions.  This commit finally accomplishes that, by adding another
API for planner support functions that lets them create derived index
conditions for their functions.  As proof of concept, the hardwired
"special index operator" code formerly present in indxpath.c is pushed
out to planner support functions attached to LIKE and other relevant
operators.

A weak spot in this design is that an extension needs to know OIDs for
the operators, datatypes, and opfamilies involved in the transformation
it wants to make.  The core-code prototypes use hard-wired OID references
but extensions don't have that option for their own operators etc.  It's
usually possible to look up the required info, but that may be slow and
inconvenient.  However, improving that situation is a separate task.

I want to do some additional refactorization around selfuncs.c, but
that also seems like a separate task.

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

6 files changed, 150 insertions, 46 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 7a65fde762b..3f4e1997626 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201902111
+#define CATALOG_VERSION_NO	201902112
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 50b742c06e6..24f99f7fc45 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -189,17 +189,21 @@
   prosrc => 'i4tochar' },
 
 { oid => '79',
-  proname => 'nameregexeq', prorettype => 'bool', proargtypes => 'name text',
-  prosrc => 'nameregexeq' },
+  proname => 'nameregexeq', prosupport => 'textregexeq_support',
+  prorettype => 'bool', proargtypes => 'name text', prosrc => 'nameregexeq' },
 { oid => '1252',
   proname => 'nameregexne', prorettype => 'bool', proargtypes => 'name text',
   prosrc => 'nameregexne' },
 { oid => '1254',
-  proname => 'textregexeq', prorettype => 'bool', proargtypes => 'text text',
-  prosrc => 'textregexeq' },
+  proname => 'textregexeq', prosupport => 'textregexeq_support',
+  prorettype => 'bool', proargtypes => 'text text', prosrc => 'textregexeq' },
 { oid => '1256',
   proname => 'textregexne', prorettype => 'bool', proargtypes => 'text text',
   prosrc => 'textregexne' },
+{ oid => '1364', descr => 'planner support for textregexeq',
+  proname => 'textregexeq_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'textregexeq_support' },
+
 { oid => '1257', descr => 'length',
   proname => 'textlen', prorettype => 'int4', proargtypes => 'text',
   prosrc => 'textlen' },
@@ -1637,8 +1641,11 @@
   proname => 'position', prorettype => 'int4', proargtypes => 'text text',
   prosrc => 'textpos' },
 { oid => '850',
-  proname => 'textlike', prorettype => 'bool', proargtypes => 'text text',
-  prosrc => 'textlike' },
+  proname => 'textlike', prosupport => 'textlike_support', prorettype => 'bool',
+  proargtypes => 'text text', prosrc => 'textlike' },
+{ oid => '1023', descr => 'planner support for textlike',
+  proname => 'textlike_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'textlike_support' },
 { oid => '851',
   proname => 'textnlike', prorettype => 'bool', proargtypes => 'text text',
   prosrc => 'textnlike' },
@@ -1663,8 +1670,8 @@
   proargtypes => 'int4 int8', prosrc => 'int48ge' },
 
 { oid => '858',
-  proname => 'namelike', prorettype => 'bool', proargtypes => 'name text',
-  prosrc => 'namelike' },
+  proname => 'namelike', prosupport => 'textlike_support', prorettype => 'bool',
+  proargtypes => 'name text', prosrc => 'namelike' },
 { oid => '859',
   proname => 'namenlike', prorettype => 'bool', proargtypes => 'name text',
   prosrc => 'namenlike' },
@@ -2354,14 +2361,17 @@
   prosrc => 'int8smaller' },
 
 { oid => '1238',
-  proname => 'texticregexeq', prorettype => 'bool', proargtypes => 'text text',
-  prosrc => 'texticregexeq' },
+  proname => 'texticregexeq', prosupport => 'texticregexeq_support',
+  prorettype => 'bool', proargtypes => 'text text', prosrc => 'texticregexeq' },
+{ oid => '1024', descr => 'planner support for texticregexeq',
+  proname => 'texticregexeq_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'texticregexeq_support' },
 { oid => '1239',
   proname => 'texticregexne', prorettype => 'bool', proargtypes => 'text text',
   prosrc => 'texticregexne' },
 { oid => '1240',
-  proname => 'nameicregexeq', prorettype => 'bool', proargtypes => 'name text',
-  prosrc => 'nameicregexeq' },
+  proname => 'nameicregexeq', prosupport => 'texticregexeq_support',
+  prorettype => 'bool', proargtypes => 'name text', prosrc => 'nameicregexeq' },
 { oid => '1241',
   proname => 'nameicregexne', prorettype => 'bool', proargtypes => 'name text',
   prosrc => 'nameicregexne' },
@@ -3130,14 +3140,14 @@
   prosrc => 'bittypmodout' },
 
 { oid => '1569', descr => 'matches LIKE expression',
-  proname => 'like', prorettype => 'bool', proargtypes => 'text text',
-  prosrc => 'textlike' },
+  proname => 'like', prosupport => 'textlike_support', prorettype => 'bool',
+  proargtypes => 'text text', prosrc => 'textlike' },
 { oid => '1570', descr => 'does not match LIKE expression',
   proname => 'notlike', prorettype => 'bool', proargtypes => 'text text',
   prosrc => 'textnlike' },
 { oid => '1571', descr => 'matches LIKE expression',
-  proname => 'like', prorettype => 'bool', proargtypes => 'name text',
-  prosrc => 'namelike' },
+  proname => 'like', prosupport => 'textlike_support', prorettype => 'bool',
+  proargtypes => 'name text', prosrc => 'namelike' },
 { oid => '1572', descr => 'does not match LIKE expression',
   proname => 'notlike', prorettype => 'bool', proargtypes => 'name text',
   prosrc => 'namenlike' },
@@ -3301,21 +3311,24 @@
   proargtypes => 'float8 interval', prosrc => 'mul_d_interval' },
 
 { oid => '1631',
-  proname => 'bpcharlike', prorettype => 'bool', proargtypes => 'bpchar text',
-  prosrc => 'textlike' },
+  proname => 'bpcharlike', prosupport => 'textlike_support',
+  prorettype => 'bool', proargtypes => 'bpchar text', prosrc => 'textlike' },
 { oid => '1632',
   proname => 'bpcharnlike', prorettype => 'bool', proargtypes => 'bpchar text',
   prosrc => 'textnlike' },
 
 { oid => '1633',
-  proname => 'texticlike', prorettype => 'bool', proargtypes => 'text text',
-  prosrc => 'texticlike' },
+  proname => 'texticlike', prosupport => 'texticlike_support',
+  prorettype => 'bool', proargtypes => 'text text', prosrc => 'texticlike' },
+{ oid => '1025', descr => 'planner support for texticlike',
+  proname => 'texticlike_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'texticlike_support' },
 { oid => '1634',
   proname => 'texticnlike', prorettype => 'bool', proargtypes => 'text text',
   prosrc => 'texticnlike' },
 { oid => '1635',
-  proname => 'nameiclike', prorettype => 'bool', proargtypes => 'name text',
-  prosrc => 'nameiclike' },
+  proname => 'nameiclike', prosupport => 'texticlike_support',
+  prorettype => 'bool', proargtypes => 'name text', prosrc => 'nameiclike' },
 { oid => '1636',
   proname => 'nameicnlike', prorettype => 'bool', proargtypes => 'name text',
   prosrc => 'nameicnlike' },
@@ -3324,20 +3337,21 @@
   prosrc => 'like_escape' },
 
 { oid => '1656',
-  proname => 'bpcharicregexeq', prorettype => 'bool',
-  proargtypes => 'bpchar text', prosrc => 'texticregexeq' },
+  proname => 'bpcharicregexeq', prosupport => 'texticregexeq_support',
+  prorettype => 'bool', proargtypes => 'bpchar text',
+  prosrc => 'texticregexeq' },
 { oid => '1657',
   proname => 'bpcharicregexne', prorettype => 'bool',
   proargtypes => 'bpchar text', prosrc => 'texticregexne' },
 { oid => '1658',
-  proname => 'bpcharregexeq', prorettype => 'bool',
-  proargtypes => 'bpchar text', prosrc => 'textregexeq' },
+  proname => 'bpcharregexeq', prosupport => 'textregexeq_support',
+  prorettype => 'bool', proargtypes => 'bpchar text', prosrc => 'textregexeq' },
 { oid => '1659',
   proname => 'bpcharregexne', prorettype => 'bool',
   proargtypes => 'bpchar text', prosrc => 'textregexne' },
 { oid => '1660',
-  proname => 'bpchariclike', prorettype => 'bool', proargtypes => 'bpchar text',
-  prosrc => 'texticlike' },
+  proname => 'bpchariclike', prosupport => 'texticlike_support',
+  prorettype => 'bool', proargtypes => 'bpchar text', prosrc => 'texticlike' },
 { oid => '1661',
   proname => 'bpcharicnlike', prorettype => 'bool',
   proargtypes => 'bpchar text', prosrc => 'texticnlike' },
@@ -3878,17 +3892,21 @@
   proname => 'network_cmp', proleakproof => 't', prorettype => 'int4',
   proargtypes => 'inet inet', prosrc => 'network_cmp' },
 { oid => '927',
-  proname => 'network_sub', prorettype => 'bool', proargtypes => 'inet inet',
-  prosrc => 'network_sub' },
+  proname => 'network_sub', prosupport => 'network_subset_support',
+  prorettype => 'bool', proargtypes => 'inet inet', prosrc => 'network_sub' },
 { oid => '928',
-  proname => 'network_subeq', prorettype => 'bool', proargtypes => 'inet inet',
-  prosrc => 'network_subeq' },
+  proname => 'network_subeq', prosupport => 'network_subset_support',
+  prorettype => 'bool', proargtypes => 'inet inet', prosrc => 'network_subeq' },
 { oid => '929',
-  proname => 'network_sup', prorettype => 'bool', proargtypes => 'inet inet',
-  prosrc => 'network_sup' },
+  proname => 'network_sup', prosupport => 'network_subset_support',
+  prorettype => 'bool', proargtypes => 'inet inet', prosrc => 'network_sup' },
 { oid => '930',
-  proname => 'network_supeq', prorettype => 'bool', proargtypes => 'inet inet',
-  prosrc => 'network_supeq' },
+  proname => 'network_supeq', prosupport => 'network_subset_support',
+  prorettype => 'bool', proargtypes => 'inet inet', prosrc => 'network_supeq' },
+{ oid => '1173', descr => 'planner support for network_sub/superset',
+  proname => 'network_subset_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'network_subset_support' },
+
 { oid => '3551',
   proname => 'network_overlap', prorettype => 'bool',
   proargtypes => 'inet inet', prosrc => 'network_overlap' },
@@ -5482,14 +5500,14 @@
   prosrc => 'select $1::pg_catalog.text || $2' },
 
 { oid => '2005',
-  proname => 'bytealike', prorettype => 'bool', proargtypes => 'bytea bytea',
-  prosrc => 'bytealike' },
+  proname => 'bytealike', prosupport => 'textlike_support',
+  prorettype => 'bool', proargtypes => 'bytea bytea', prosrc => 'bytealike' },
 { oid => '2006',
   proname => 'byteanlike', prorettype => 'bool', proargtypes => 'bytea bytea',
   prosrc => 'byteanlike' },
 { oid => '2007', descr => 'matches LIKE expression',
-  proname => 'like', prorettype => 'bool', proargtypes => 'bytea bytea',
-  prosrc => 'bytealike' },
+  proname => 'like', prosupport => 'textlike_support', prorettype => 'bool',
+  proargtypes => 'bytea bytea', prosrc => 'bytealike' },
 { oid => '2008', descr => 'does not match LIKE expression',
   proname => 'notlike', prorettype => 'bool', proargtypes => 'bytea bytea',
   prosrc => 'byteanlike' },
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 453079a9e26..f9389257c60 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -510,7 +510,8 @@ typedef enum NodeTag
 	T_SupportRequestSimplify,	/* in nodes/supportnodes.h */
 	T_SupportRequestSelectivity,	/* in nodes/supportnodes.h */
 	T_SupportRequestCost,		/* in nodes/supportnodes.h */
-	T_SupportRequestRows		/* in nodes/supportnodes.h */
+	T_SupportRequestRows,		/* in nodes/supportnodes.h */
+	T_SupportRequestIndexCondition	/* in nodes/supportnodes.h */
 } NodeTag;
 
 /*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c23c4304f37..616ec3b3dbf 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -764,7 +764,12 @@ typedef struct RelOptInfo
  *		(by plancat.c), indrestrictinfo and predOK are set later, in
  *		check_index_predicates().
  */
-typedef struct IndexOptInfo
+#ifndef HAVE_INDEXOPTINFO_TYPEDEF
+typedef struct IndexOptInfo IndexOptInfo;
+#define HAVE_INDEXOPTINFO_TYPEDEF 1
+#endif
+
+struct IndexOptInfo
 {
 	NodeTag		type;
 
@@ -818,7 +823,7 @@ typedef struct IndexOptInfo
 	bool		amcanparallel;	/* does AM support parallel scan? */
 	/* Rather than include amapi.h here, we declare amcostestimate like this */
 	void		(*amcostestimate) ();	/* AM's cost estimator */
-} IndexOptInfo;
+};
 
 /*
  * ForeignKeyOptInfo
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 1a3a36ba99c..460d75bd2dd 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -35,7 +35,8 @@
 
 #include "nodes/primnodes.h"
 
-struct PlannerInfo;				/* avoid including relation.h here */
+struct PlannerInfo;				/* avoid including pathnodes.h here */
+struct IndexOptInfo;
 struct SpecialJoinInfo;
 
 
@@ -167,4 +168,75 @@ typedef struct SupportRequestRows
 	double		rows;			/* number of rows expected to be returned */
 } SupportRequestRows;
 
+/*
+ * The IndexCondition request allows the support function to generate
+ * a directly-indexable condition based on a target function call that is
+ * not itself indexable.  The target function call must appear at the top
+ * level of WHERE or JOIN/ON, so this applies only to functions returning
+ * boolean.
+ *
+ * The "node" argument is the parse node that is invoking the target function;
+ * currently this will always be a FuncExpr or OpExpr.  The call is made
+ * only if at least one function argument matches an index column's variable
+ * or expression.  "indexarg" identifies the matching argument (it's the
+ * argument's zero-based index in the node's args list).
+ *
+ * If the transformation is possible, return a List of directly-indexable
+ * condition expressions, else return NULL.  (A List is used because it's
+ * sometimes useful to generate more than one indexable condition, such as
+ * when a LIKE with constant prefix gives rise to both >= and < conditions.)
+ *
+ * "Directly indexable" means that the condition must be directly executable
+ * by the index machinery.  Typically this means that it is a binary OpExpr
+ * with the index column value on the left, a pseudo-constant on the right,
+ * and an operator that is in the index column's operator family.  Other
+ * possibilities include RowCompareExpr, ScalarArrayOpExpr, and NullTest,
+ * depending on the index type; but those seem less likely to be useful for
+ * derived index conditions.  "Pseudo-constant" means that the right-hand
+ * expression must not contain any volatile functions, nor any Vars of the
+ * table the index is for; use is_pseudo_constant_for_index() to check this.
+ * (Note: if the passed "node" is an OpExpr, the core planner already verified
+ * that the non-indexkey operand is pseudo-constant; but when the "node"
+ * is a FuncExpr, it does not check, since it doesn't know which of the
+ * function's arguments you might need to use in an index comparison value.)
+ *
+ * In many cases, an index condition can be generated but it is weaker than
+ * the function condition itself; for example, a LIKE with a constant prefix
+ * can produce an index range check based on the prefix, but we still need
+ * to execute the LIKE operator to verify the rest of the pattern.  We say
+ * that such an index condition is "lossy".  When returning an index condition,
+ * you should set the "lossy" request field to true if the condition is lossy,
+ * or false if it is an exact equivalent of the function's result.  The core
+ * code will initialize that field to true, which is the common case.
+ *
+ * It is important to verify that the index operator family is the correct
+ * one for the condition you want to generate.  Core support functions tend
+ * to use the known OID of a built-in opfamily for this, but extensions need
+ * to work harder, since their OIDs aren't fixed.  A possibly workable
+ * answer for an index on an extension datatype is to verify the index AM's
+ * OID instead, and then assume that there's only one relevant opclass for
+ * your datatype so the opfamily must be the right one.  Generating OpExpr
+ * nodes may also require knowing extension datatype OIDs (often you can
+ * find these out by applying exprType() to a function argument) and
+ * operator OIDs (which you can look up using get_opfamily_member).
+ */
+typedef struct SupportRequestIndexCondition
+{
+	NodeTag		type;
+
+	/* Input fields: */
+	struct PlannerInfo *root;	/* Planner's infrastructure */
+	Oid			funcid;			/* function we are inquiring about */
+	Node	   *node;			/* parse node invoking function */
+	int			indexarg;		/* index of function arg matching indexcol */
+	struct IndexOptInfo *index; /* planner's info about target index */
+	int			indexcol;		/* index of target index column (0-based) */
+	Oid			opfamily;		/* index column's operator family */
+	Oid			indexcollation; /* index column's collation */
+
+	/* Output fields: */
+	bool		lossy;			/* set to false if index condition is an exact
+								 * equivalent of the function call */
+} SupportRequestIndexCondition;
+
 #endif							/* SUPPORTNODES_H */
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index ffaf5b9450b..ffd812a4ed8 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -35,7 +35,11 @@ typedef struct PlannerInfo PlannerInfo;
 #define HAVE_PLANNERINFO_TYPEDEF 1
 #endif
 
-/* Likewise for SpecialJoinInfo. */
+/* Likewise for IndexOptInfo and SpecialJoinInfo. */
+#ifndef HAVE_INDEXOPTINFO_TYPEDEF
+typedef struct IndexOptInfo IndexOptInfo;
+#define HAVE_INDEXOPTINFO_TYPEDEF 1
+#endif
 #ifndef HAVE_SPECIALJOININFO_TYPEDEF
 typedef struct SpecialJoinInfo SpecialJoinInfo;
 #define HAVE_SPECIALJOININFO_TYPEDEF 1
@@ -74,6 +78,10 @@ extern PGDLLIMPORT int effective_cache_size;
 
 extern double clamp_row_est(double nrows);
 
+/* in path/indxpath.c: */
+
+extern bool is_pseudo_constant_for_index(Node *expr, IndexOptInfo *index);
+
 /* in plan/planner.c: */
 
 /* possible values for force_parallel_mode */