Re: Enhanced containment selectivity function

Hi,

>>Moving it in contrib/ltree would be more difficult to me because it
>>depends on other functions declared in selfuncs.c
>>(get_restriction_variable, etc).
>
> I'd be willing to consider exporting those functions from selfuncs.c.

In the meanwhile here is the latest patch which uses both mcv and
histogram values.


BTW, when restoring my test database I've found out that there were many
errors on ALTER INDEX "something" OWNER TO ... :

ERROR:  "something" is not a table, view, or sequence

This using 8.1devel pg_restore and a 8.0.3 compressed dump. I could be
wrong, but I didn't get those errors a few days ago (some cvs updates ago).


Best regards
--
Matteo Beccati
http://phpadsnew.com/
http://phppgads.com/
Index: contrib/ltree/ltree.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/ltree/ltree.sql.in,v
retrieving revision 1.9
diff -c -r1.9 ltree.sql.in
*** contrib/ltree/ltree.sql.in  30 Mar 2004 15:45:32 -0000      1.9
--- contrib/ltree/ltree.sql.in  6 Aug 2005 13:10:35 -0000
***************
*** 230,236 ****
        RIGHTARG = ltree,
        PROCEDURE = ltree_isparent,
          COMMUTATOR = '<@',
!         RESTRICT = contsel,
        JOIN = contjoinsel
  );

--- 230,236 ----
        RIGHTARG = ltree,
        PROCEDURE = ltree_isparent,
          COMMUTATOR = '<@',
!         RESTRICT = parentsel,
        JOIN = contjoinsel
  );

***************
*** 248,254 ****
        RIGHTARG = ltree,
        PROCEDURE = ltree_risparent,
          COMMUTATOR = '@>',
!         RESTRICT = contsel,
        JOIN = contjoinsel
  );

--- 248,254 ----
        RIGHTARG = ltree,
        PROCEDURE = ltree_risparent,
          COMMUTATOR = '@>',
!         RESTRICT = parentsel,
        JOIN = contjoinsel
  );

Index: src/backend/utils/adt/selfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.187
diff -c -r1.187 selfuncs.c
*** src/backend/utils/adt/selfuncs.c    21 Jul 2005 04:41:43 -0000      1.187
--- src/backend/utils/adt/selfuncs.c    6 Aug 2005 13:10:46 -0000
***************
*** 1306,1311 ****
--- 1306,1488 ----
        return (Selectivity) selec;
  }

+ #define DEFAULT_PARENT_SEL 0.001
+
+ /*
+  *            parentsel               - Selectivity of parent relationship for ltree data types.
+  */
+ Datum
+ parentsel(PG_FUNCTION_ARGS)
+ {
+       PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
+       Oid                     operator = PG_GETARG_OID(1);
+       List       *args = (List *) PG_GETARG_POINTER(2);
+       int                     varRelid = PG_GETARG_INT32(3);
+       VariableStatData vardata;
+       Node       *other;
+       bool            varonleft;
+       Datum      *values;
+       int                     nvalues;
+       float4     *numbers;
+       int                     nnumbers;
+       double          selec = 0.0;
+
+       /*
+        * If expression is not variable <@ something or something <@ variable,
+        * then punt and return a default estimate.
+        */
+       if (!get_restriction_variable(root, args, varRelid,
+                                                                 &vardata, &other, &varonleft))
+               PG_RETURN_FLOAT8(DEFAULT_PARENT_SEL);
+
+       /*
+        * If the something is a NULL constant, assume operator is strict and
+        * return zero, ie, operator will never return TRUE.
+        */
+       if (IsA(other, Const) &&
+               ((Const *) other)->constisnull)
+       {
+               ReleaseVariableStats(vardata);
+               PG_RETURN_FLOAT8(0.0);
+       }
+
+       if (HeapTupleIsValid(vardata.statsTuple))
+       {
+               Form_pg_statistic stats;
+               double          mcvsum = 0.0;
+               double          mcvsel = 0.0;
+               double          hissel = 0.0;
+
+               stats = (Form_pg_statistic) GETSTRUCT(vardata.statsTuple);
+
+               if (IsA(other, Const))
+               {
+                       /* Variable is being compared to a known non-null constant */
+                       Datum           constval = ((Const *) other)->constvalue;
+                       bool            match = false;
+                       int                     i;
+
+                       /*
+                        * Is the constant "<@" to any of the column's most common
+                        * values?
+                        */
+                       if (get_attstatsslot(vardata.statsTuple,
+                                                                vardata.atttype, vardata.atttypmod,
+                                                                STATISTIC_KIND_MCV, InvalidOid,
+                                                                &values, &nvalues,
+                                                                &numbers, &nnumbers))
+                       {
+                               FmgrInfo        contproc;
+
+                               fmgr_info(get_opcode(operator), &contproc);
+
+                               for (i = 0; i < nvalues; i++)
+                               {
+                                       /* be careful to apply operator right way 'round */
+                                       if (varonleft)
+                                               match = DatumGetBool(FunctionCall2(&contproc,
+
values[i],
+
constval));
+                                       else
+                                               match = DatumGetBool(FunctionCall2(&contproc,
+
constval,
+
values[i]));
+
+                                       /* calculate total selectivity of all most-common-values */
+                                       mcvsum += numbers[i];
+
+                                       /* calculate selectivity of matching most-common-values */
+                                       if (match)
+                                               mcvsel += numbers[i];
+                               }
+                       }
+                       else
+                       {
+                               /* no most-common-values info available */
+                               values = NULL;
+                               numbers = NULL;
+                               i = nvalues = nnumbers = 0;
+                       }
+
+                       free_attstatsslot(vardata.atttype, values, nvalues,
+                                                         NULL, 0);
+
+
+                       /*
+                        * Is the constant "<@" to any of the column's histogram
+                        * values?
+                        */
+                       if (get_attstatsslot(vardata.statsTuple,
+                                                                vardata.atttype, vardata.atttypmod,
+                                                                STATISTIC_KIND_HISTOGRAM, InvalidOid,
+                                                                &values, &nvalues,
+                                                                NULL, NULL))
+                       {
+                               FmgrInfo        contproc;
+
+                               fmgr_info(get_opcode(operator), &contproc);
+
+                               for (i = 0; i < nvalues; i++)
+                               {
+                                       /* be careful to apply operator right way 'round */
+                                       if (varonleft)
+                                               match = DatumGetBool(FunctionCall2(&contproc,
+
values[i],
+
constval));
+                                       else
+                                               match = DatumGetBool(FunctionCall2(&contproc,
+
constval,
+
values[i]));
+                                       /* count matching histogram values */
+                                       if (match)
+                                               hissel++;
+                               }
+
+                               if (hissel > 0.0)
+                               {
+                                       /*
+                                        * some matching values found inside histogram, divide matching entries number
+                                        * by total histogram entries to get the histogram related selectivity
+                                        */
+                                       hissel /= nvalues;
+                               }
+                       }
+                       else
+                       {
+                               /* no histogram info available */
+                               values = NULL;
+                               i = nvalues = 0;
+                       }
+
+                       free_attstatsslot(vardata.atttype, values, nvalues,
+                                                         NULL, 0);
+
+
+                       /*
+                        * calculate selectivity based on MCV and histogram result
+                        * histogram selectivity needs to be scaled down if there are any most-common-values
+                        */
+                       selec = mcvsel + hissel * (1.0 - mcvsum);
+
+                       /* don't return 0.0 selectivity unless all table values are inside mcv */
+                       if (selec == 0.0 && mcvsum != 1.0)
+                               selec = DEFAULT_PARENT_SEL;
+               }
+               else
+                       selec = DEFAULT_PARENT_SEL;
+       }
+       else
+               selec = DEFAULT_PARENT_SEL;
+
+
+       ReleaseVariableStats(vardata);
+
+       /* result should be in range, but make sure... */
+       CLAMP_PROBABILITY(selec);
+
+       PG_RETURN_FLOAT8((float8) selec);
+ }
+
  /*
   *            eqjoinsel               - Join selectivity of "="
   */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.380
diff -c -r1.380 pg_proc.h
*** src/include/catalog/pg_proc.h       2 Aug 2005 16:11:57 -0000       1.380
--- src/include/catalog/pg_proc.h       6 Aug 2005 13:10:59 -0000
***************
*** 3750,3755 ****
--- 3750,3758 ----
  DATA(insert OID = 2592 (  gist_circle_compress        PGNSP PGUID 12 f f t f i 1 2281 "2281" _null_ _null_ _null_
gist_circle_compress- _null_ )); 
  DESCR("GiST support");

+ DATA(insert OID = 2600 (  parentsel              PGNSP PGUID 12 f f t f s 4 701 "2281 26 2281 23" _null_ _null_
_null_       parentsel - _null_ )); 
+ DESCR("enhanced restriction selectivity for ltree isparent comparison operators");
+

  /*
   * Symbolic values for provolatile column: these indicate whether the result
Index: src/include/utils/selfuncs.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/selfuncs.h,v
retrieving revision 1.23
diff -c -r1.23 selfuncs.h
*** src/include/utils/selfuncs.h        5 Jun 2005 22:32:58 -0000       1.23
--- src/include/utils/selfuncs.h        6 Aug 2005 13:11:00 -0000
***************
*** 95,100 ****
--- 95,102 ----
  extern Datum nlikesel(PG_FUNCTION_ARGS);
  extern Datum icnlikesel(PG_FUNCTION_ARGS);

+ extern Datum parentsel(PG_FUNCTION_ARGS);
+
  extern Datum eqjoinsel(PG_FUNCTION_ARGS);
  extern Datum neqjoinsel(PG_FUNCTION_ARGS);
  extern Datum scalarltjoinsel(PG_FUNCTION_ARGS);