Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Rob Sargent <robjsargent@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: help debugging an issue with selectivity
Date: Tue, 17 Mar 2026 18:38:11 -0600
Message-Id: <AA983D51-CF44-4CD9-9812-AEB850CBEAF1@gmail.com>
References: <8e4f0d76-9e77-4819-96f7-cbc64a9d2b5a@gmail.com>
Cc: pgsql-general@lists.postgresql.org
In-Reply-To: <8e4f0d76-9e77-4819-96f7-cbc64a9d2b5a@gmail.com>
To: Greg Hennessy <greg.hennessy@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/AA983D51-CF44-4CD9-9812-AEB850CBEAF1%40gmail.com>
Precedence: bulk

/=E2=80=94 intentional top post=E2=80=94/
Have you been a very bad boy and usurped an existing email thread?

> On Mar 17, 2026, at 11:29=E2=80=AFAM, Greg Hennessy <greg.hennessy@gmail.c=
om> wrote:
>=20
> =EF=BB=BFI am not sure if this belongs in pgsql-general or pgsql-hackers, I=
 am trying first in psgl-general.
>=20
> I am trying to understand setting a selectivity function that gets applied=
 to an operator (to hopefully
> provide better information for the planner/optmizer). This is for the q3c e=
xtension, source code found at
> https://github.com/segasai/q3c.
>=20
> There are functions for selectivity, and for an operator.
>=20
> -- A dummy type used in the selectivity operator
> create type q3c_type as (ra double precision, dec double precision,
>        ra1 double precision, dec1 double precision);
>=20
> -- A dummy operator function (always returns true)
> CREATE OR REPLACE FUNCTION q3c_seloper(double precision, q3c_type)
>         RETURNS bool
>         AS 'MODULE_PATHNAME', 'pgq3c_seloper'
>         LANGUAGE C STRICT IMMUTABLE COST 1000;
>=20
> -- A selectivity function for the q3c operator
> CREATE OR REPLACE FUNCTION q3c_sel(internal, oid, internal, int4)
>         RETURNS float8
>         AS 'MODULE_PATHNAME', 'pgq3c_sel'
>         LANGUAGE C IMMUTABLE STRICT ;
>=20
> -- A selectivity function for the q3c operator
> CREATE OR REPLACE FUNCTION q3c_seljoin(internal, oid, internal, int2, inte=
rnal)
>         RETURNS float8
>         AS 'MODULE_PATHNAME', 'pgq3c_seljoin'
>         LANGUAGE C IMMUTABLE STRICT ;
>=20
> -- distance operator with correct selectivity
> CREATE OPERATOR =3D=3D<<>>=3D=3D (
>         LEFTARG =3D double precision,
>         RIGHTARG =3D q3c_type,
>         PROCEDURE =3D q3c_seloper,
>         RESTRICT =3D q3c_sel,
>         JOIN =3D q3c_seljoin
> );
>=20
> The C portions are declared as:
>=20
> /* The actual selectivity function, it returns the ratio of the
>  * search circle to the whole sky area
>  */
> PG_FUNCTION_INFO_V1(pgq3c_sel);
> Datum pgq3c_sel(PG_FUNCTION_ARGS)
>=20
> where the actual calculation portion is (not showing the setup portion):
>=20
>         ratio =3D 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
>         /* clamp at 0, 1*/
>         CLAMP_PROBABILITY(ratio);
>         elog(WARNING, "HERE0 pgq3c_sel.... %e", ratio);
>         PG_RETURN_FLOAT8(ratio);
> }
>=20
> The join function is declared as:
> PG_FUNCTION_INFO_V1(pgq3c_seljoin);
> Datum pgq3c_seljoin(PG_FUNCTION_ARGS)
> {
>=20
> where the meat portion is:
>        ratio =3D 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
>         /* clamp at 0, 1*/
>         CLAMP_PROBABILITY(ratio);
>         elog(WARNING, "HERE0 pgq3c_seljoin.... %e", ratio);
>         PG_RETURN_FLOAT8(ratio);
> }
>=20
> The two elog statements aren't in the orig code, I've added them to help m=
e trace
> the code. As far as I can tell, the these selectivity functions are called=
 in
> src/backend/optimizer/path/clausesel.c in the routine clause_selectivity_e=
xt.
> If I add similar elog statements, at about line 836, the code says:
>=20
>               if (treat_as_join_clause(root, clause, rinfo, varRelid, sjin=
fo))
>         {
>             /* Estimate selectivity for a join clause. */
>             if (opno > 6000)
>                 elog(WARNING, "clause_selectivity: join_selectivity opno %=
d",opno);
>             s1 =3D join_selectivity(root, opno,
>                                   opclause->args,
>                                   opclause->inputcollid,
>                                   jointype,
>                                   sjinfo);
>             if (opno > 6000){
>                 elog(WARNING, "join_selectivity: s1 %f", s1);
>             }
>         }
>         else
>         {
>             /* Estimate selectivity for a restriction clause. */
>             if (opno > 6000)
>                 elog(WARNING, "clause_selectivity: restriction_selectivity=
 opno %d", opno);
>             s1 =3D restriction_selectivity(root, opno,
>                                          opclause->args,
>                                          opclause->inputcollid,
>                                          varRelid);
>             if (opno > 6000){
>                 elog(WARNING, "restriction_selectivity: s1 %lf", s1);
>             }
>         }
>=20
>=20
> When I actually execute this, I get output to the terminal of the form:
> WARNING:  join_selectivity: operator id 16818 jointype 0 0
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  datum result 4438812783922730423 0.000000
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  join_selectivity: 0.000000 16818 jointype 0
> WARNING:  join_selectivity: s1 0.000000
> WARNING:  clause_selectivity: s1 0.000000
>=20
> where it seems to me the q3c code is returning a non zero value, but in th=
e guts of
> postgres what is found is a zero value. If I want to verify I have the cor=
rect opr,
> which is 16818, I can verify via:
> q3c_test=3D# select oid,oprname,oprnamespace,oprowner,oprkind,oprleft,oprr=
ight,oprresult,oprcode from pg_operator where oid =3D 16818;
>   oid  | oprname  | oprnamespace | oprowner | oprkind | oprleft | oprright=
 | oprresult |   oprcode
> -------+----------+--------------+----------+---------+---------+---------=
-+-----------+-------------
>  16818 | =3D=3D<<>>=3D=3D |         2200 |    16391 | b       |    701 |  =
  16814 |        16 | q3c_seloper
>=20
> which yeilds what I expect.
>=20
> The join_selectivity is essentially a call in src/backend/optimizer/util/p=
lancat.c of:
>     result =3D DatumGetFloat8(OidFunctionCall5Coll(oprjoin,
>             inputcollid,
>             PointerGetDatum(root),
>             ObjectIdGetDatum(operatorid),
>             PointerGetDatum(args),
>             Int16GetDatum(jointype),
>             PointerGetDatum(sjinfo)));
>=20
>     if (result < 0.0 || result > 1.0)
>         elog(ERROR, "invalid join selectivity: %f", result);
>=20
> while restriction_selectivity is a call to:
>     result =3D DatumGetFloat8(OidFunctionCall4Coll(oprrest,
>                     inputcollid,
>                     PointerGetDatum(root),
>                     ObjectIdGetDatum(operatorid),
>                     PointerGetDatum(args),
>                     Int32GetDatum(varRelid)));
>=20
> This is the point where I run out of steam. The basic issue I have is that=
 q3c code is attempting
> to return a small, but non-zero value for the selectivity in two functions=
, but the guts of
> postgresql has both the join_selectivity  and restriction_selectivity func=
tion return zero where
> I think they shouldn't.
>=20
> Any advice in how to make progress on this is welcome.  I'm using 19devel (=
I can probably do a
> git merge to move to a more up to  date version), and I'm running Fedora r=
elease 43 in case which
> exact OS I'm using is relavent.
>=20
> Greg
>=20
>=20
>=20