Cleaned-up patch attached and applied.   Catalog version updated in
separate patch.

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

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/

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: contrib/ltree/ltree.sql.in
===================================================================
RCS file: /cvsroot/pgsql/contrib/ltree/ltree.sql.in,v
retrieving revision 1.10
diff -c -c -r1.10 ltree.sql.in
*** contrib/ltree/ltree.sql.in  27 Feb 2006 16:09:48 -0000      1.10
--- contrib/ltree/ltree.sql.in  26 Apr 2006 18:25:16 -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/geo_selfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/geo_selfuncs.c,v
retrieving revision 1.27
diff -c -c -r1.27 geo_selfuncs.c
*** src/backend/utils/adt/geo_selfuncs.c        5 Mar 2006 15:58:42 -0000       
1.27
--- src/backend/utils/adt/geo_selfuncs.c        26 Apr 2006 18:25:21 -0000
***************
*** 20,26 ****
  
  #include "utils/geo_decls.h"
  
- 
  /*
   *    Selectivity functions for geometric operators.  These are bogus -- 
unless
   *    we know the actual key distribution in the index, we can't make a good
--- 20,25 ----
***************
*** 93,95 ****
--- 92,95 ----
  {
        PG_RETURN_FLOAT8(0.001);
  }
+ 
Index: src/backend/utils/adt/selfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.199
diff -c -c -r1.199 selfuncs.c
*** src/backend/utils/adt/selfuncs.c    20 Apr 2006 17:50:18 -0000      1.199
--- src/backend/utils/adt/selfuncs.c    26 Apr 2006 18:25:28 -0000
***************
*** 4852,4854 ****
--- 4852,5033 ----
  
        PG_RETURN_VOID();
  }
+ 
+ 
+ #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);
+ }
+ 
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.406
diff -c -c -r1.406 pg_proc.h
*** src/include/catalog/pg_proc.h       25 Apr 2006 00:25:20 -0000      1.406
--- src/include/catalog/pg_proc.h       26 Apr 2006 18:25:35 -0000
***************
*** 3812,3817 ****
--- 3812,3819 ----
  DESCR("GiST support");
  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 = 2599 (  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");
  
  
  /*
Index: src/include/utils/selfuncs.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/selfuncs.h,v
retrieving revision 1.28
diff -c -c -r1.28 selfuncs.h
*** src/include/utils/selfuncs.h        5 Mar 2006 15:59:07 -0000       1.28
--- src/include/utils/selfuncs.h        26 Apr 2006 18:25:35 -0000
***************
*** 134,137 ****
--- 134,139 ----
  extern Datum hashcostestimate(PG_FUNCTION_ARGS);
  extern Datum gistcostestimate(PG_FUNCTION_ARGS);
  
+ extern Datum parentsel(PG_FUNCTION_ARGS);
+ 
  #endif   /* SELFUNCS_H */
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Reply via email to