From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
        Christophe Courtois <christophe.courtois@dalibo.com>,
        Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Indexes on expressions with multiple columns and operators
In-reply-to: <1916727.1758209549@sss.pgh.pa.us>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
 <1507576.1758120083@sss.pgh.pa.us>
 <62133334-b844-4d0b-b248-1a8446757e5f@dalibo.com>
 <1916727.1758209549@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 18 Sep 2025 11:32:29 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1972947.1758213616.0@sss.pgh.pa.us>
Date: Thu, 18 Sep 2025 12:40:39 -0400
Message-ID: <1972974.1758213639@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1972974.1758213639%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1972947.1758213616.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="v1-fix-misfactoring-of-function-selectivity.patch";
	charset="us-ascii"
Content-ID: <1972947.1758213616.2@sss.pgh.pa.us>
Content-Description: v1-fix-misfactoring-of-function-selectivity.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimize=
r/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 =3D 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 cl=
ause
+ * 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 =3D T_SupportRequestSelectivity;
 	req.root =3D root;
@@ -2188,9 +2180,8 @@ function_selectivity(PlannerInfo *root,
 		DatumGetPointer(OidFunctionCall1(prosupport,
 										 PointerGetDatum(&req)));
 =

-	/* If support function fails, use default */
 	if (sresult !=3D &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/self=
uncs.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 varRel=
id)
 		selec =3D 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 =3D 0.3333333;
+	}
 	else
 	{
 		/* Otherwise, the default estimate is 0.5 */

------- =_aaaaaaaaaa0--