Re: help debugging an issue with selectivity

/— intentional top post—/
Have you been a very bad boy and usurped an existing email thread?

> On Mar 17, 2026, at 11:29 AM, Greg Hennessy <greg(dot)hennessy(at)gmail(dot)com> wrote:
>
> I am not sure if this belongs in pgsql-general or pgsql-hackers, I am trying first in psgl-general.
>
> 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 extension, source code found at
> https://github.com/segasai/q3c.
>
> There are functions for selectivity, and for an operator.
>
> -- 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);
>
> -- 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;
>
> -- 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 ;
>
> -- A selectivity function for the q3c operator
> CREATE OR REPLACE FUNCTION q3c_seljoin(internal, oid, internal, int2, internal)
> RETURNS float8
> AS 'MODULE_PATHNAME', 'pgq3c_seljoin'
> LANGUAGE C IMMUTABLE STRICT ;
>
> -- distance operator with correct selectivity
> CREATE OPERATOR ==<<>>== (
> LEFTARG = double precision,
> RIGHTARG = q3c_type,
> PROCEDURE = q3c_seloper,
> RESTRICT = q3c_sel,
> JOIN = q3c_seljoin
> );
>
> The C portions are declared as:
>
> /* 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)
>
> where the actual calculation portion is (not showing the setup portion):
>
> ratio = 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);
> }
>
> The join function is declared as:
> PG_FUNCTION_INFO_V1(pgq3c_seljoin);
> Datum pgq3c_seljoin(PG_FUNCTION_ARGS)
> {
>
> where the meat portion is:
> ratio = 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);
> }
>
> The two elog statements aren't in the orig code, I've added them to help me 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_ext.
> If I add similar elog statements, at about line 836, the code says:
>
> if (treat_as_join_clause(root, clause, rinfo, varRelid, sjinfo))
> {
> /* Estimate selectivity for a join clause. */
> if (opno > 6000)
> elog(WARNING, "clause_selectivity: join_selectivity opno %d",opno);
> s1 = 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 = restriction_selectivity(root, opno,
> opclause->args,
> opclause->inputcollid,
> varRelid);
> if (opno > 6000){
> elog(WARNING, "restriction_selectivity: s1 %lf", s1);
> }
> }
>
>
> 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
>
> where it seems to me the q3c code is returning a non zero value, but in the guts of
> postgres what is found is a zero value. If I want to verify I have the correct opr,
> which is 16818, I can verify via:
> q3c_test=# select oid,oprname,oprnamespace,oprowner,oprkind,oprleft,oprright,oprresult,oprcode from pg_operator where oid = 16818;
> oid | oprname | oprnamespace | oprowner | oprkind | oprleft | oprright | oprresult | oprcode
> -------+----------+--------------+----------+---------+---------+----------+-----------+-------------
> 16818 | ==<<>>== | 2200 | 16391 | b | 701 | 16814 | 16 | q3c_seloper
>
> which yeilds what I expect.
>
> The join_selectivity is essentially a call in src/backend/optimizer/util/plancat.c of:
> result = DatumGetFloat8(OidFunctionCall5Coll(oprjoin,
> inputcollid,
> PointerGetDatum(root),
> ObjectIdGetDatum(operatorid),
> PointerGetDatum(args),
> Int16GetDatum(jointype),
> PointerGetDatum(sjinfo)));
>
> if (result < 0.0 || result > 1.0)
> elog(ERROR, "invalid join selectivity: %f", result);
>
> while restriction_selectivity is a call to:
> result = DatumGetFloat8(OidFunctionCall4Coll(oprrest,
> inputcollid,
> PointerGetDatum(root),
> ObjectIdGetDatum(operatorid),
> PointerGetDatum(args),
> Int32GetDatum(varRelid)));
>
> 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 function return zero where
> I think they shouldn't.
>
> 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 release 43 in case which
> exact OS I'm using is relavent.
>
> Greg
>
>
>