Re: Indexes on expressions with multiple columns and operators - Mailing list pgsql-performance

I wrote:
> Sigh ... so the answer is this used to work (since commit 39df0f150)
> and then I carelessly broke it in commit a391ff3c3.  If you try this
> test case in versions 9.5..11 you get a spot-on rowcount estimate.
> Serves me right for not having a test case I guess, but I'm astonished
> that nobody complained sooner.

The attached fixes things so it works like it did pre-a391ff3c3.

I spent some time trying to devise a test case, and was reminded
of why I didn't have one before: it's hard to make a case that
will be robust enough to not show diffs in the buildfarm.
I'll keep thinking about that though.

            regards, tom lane

diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 5d51f97f219..d0f516b7645 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -874,6 +874,10 @@ clause_selectivity_ext(PlannerInfo *root,
                                   varRelid,
                                   jointype,
                                   sjinfo);
+
+        /* If no support, fall back on boolvarsel */
+        if (s1 < 0)
+            s1 = boolvarsel(root, clause, varRelid);
     }
     else if (IsA(clause, ScalarArrayOpExpr))
     {
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index f8641204a67..da5d901ec3c 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -2143,9 +2143,8 @@ join_selectivity(PlannerInfo *root,
 /*
  * function_selectivity
  *
- * Returns the selectivity of a specified boolean function clause.
- * This code executes registered procedures stored in the
- * pg_proc relation, by calling the function manager.
+ * Attempt to estimate the selectivity of a specified boolean function clause
+ * by asking its support function.  If the function lacks support, return -1.
  *
  * See clause_selectivity() for the meaning of the additional parameters.
  */
@@ -2163,15 +2162,8 @@ function_selectivity(PlannerInfo *root,
     SupportRequestSelectivity req;
     SupportRequestSelectivity *sresult;

-    /*
-     * If no support function is provided, use our historical default
-     * estimate, 0.3333333.  This seems a pretty unprincipled choice, but
-     * Postgres has been using that estimate for function calls since 1992.
-     * The hoariness of this behavior suggests that we should not be in too
-     * much hurry to use another value.
-     */
     if (!prosupport)
-        return (Selectivity) 0.3333333;
+        return (Selectivity) -1;    /* no support function */

     req.type = T_SupportRequestSelectivity;
     req.root = root;
@@ -2188,9 +2180,8 @@ function_selectivity(PlannerInfo *root,
         DatumGetPointer(OidFunctionCall1(prosupport,
                                          PointerGetDatum(&req)));

-    /* If support function fails, use default */
     if (sresult != &req)
-        return (Selectivity) 0.3333333;
+        return (Selectivity) -1;    /* function did not honor request */

     if (req.selectivity < 0.0 || req.selectivity > 1.0)
         elog(ERROR, "invalid function selectivity: %f", req.selectivity);
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 1c480cfaaf7..e5e066a5537 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -1528,6 +1528,17 @@ boolvarsel(PlannerInfo *root, Node *arg, int varRelid)
         selec = var_eq_const(&vardata, BooleanEqualOperator, InvalidOid,
                              BoolGetDatum(true), false, true, false);
     }
+    else if (is_funcclause(arg))
+    {
+        /*
+         * If we have no stats and it's a function call, estimate 0.3333333.
+         * This seems a pretty unprincipled choice, but Postgres has been
+         * using that estimate for function calls since 1992.  The hoariness
+         * of this behavior suggests that we should not be in too much hurry
+         * to use another value.
+         */
+        selec = 0.3333333;
+    }
     else
     {
         /* Otherwise, the default estimate is 0.5 */