Re: Enhanced containment selectivity function

This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Matteo Beccati wrote:
> 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);
> 

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073