Here is a gprof result of the pgbench.

  %   cumulative   self             self    total
 time   seconds   seconds    calls  s/call  s/call  name
  4.77      4.04     4.04    14000    0.00    0.00  OpernameGetCandidates
  4.70      8.02     3.98  2007008    0.00    0.00
HeapTupleSatisfiesSnapshot
  4.50     11.83     3.81    14001    0.00    0.00  base_yyparse
  3.78     15.03     3.20  2838312    0.00    0.00  AllocSetAlloc
  3.73     18.18     3.15    40001    0.00    0.00  heapgetpage

The OpernameGetCandidates called from oper. The function of oper is
search for a binary operator. It does the following processing:

(1)Create candidates of operator that matches operator name and
operator kind by OpernameGetCandidates.
(2)Find an operator that matches exactly ltypeId and rtypeId from
the candidates of operator by binary_oper_exact.
(3)If not found, find an operator from the candidates of operator by
oper_select_candidate. The oper_select_candidate accepts coerce type
and resolve the conflict.

I think that we can search the system catalog cache instead of
retrieval from the candidates of operator in the binary_oper_exact,
and reverse the order of executing (1) and (2) for performance
improvement. Because for a general operator such as '=', the number
of the candidates of operator exceeds 50. And in common cases the
typeIds matches exactly.

I tested following commands:

pgbench -i
pgbench -t 4000 (5 times)

results (tps):
             1       2       3       4       5     average
-----------------------------------------------------------
 original: 214.33  184.60  192.52  158.62  170.04  184.02
 patched : 223.86  198.72  207.48  165.70  179.67  195.09

regards,

---
Atsushi Ogawa

*** ./src/backend/catalog/namespace.c.orig      Tue Apr 25 23:16:57 2006
--- ./src/backend/catalog/namespace.c   Wed Apr 26 00:14:33 2006
***************
*** 681,686 ****
--- 681,744 ----
        return visible;
  }
  
+ /*
+  * OpernameGet
+  *            Given a possibly-qualified operator name, left and right type 
IDs,
+  *            find an operator in the current search path.
+  */
+ Oid
+ OpernameGet(List *names, Oid ltypeId, Oid rtypeId)
+ {
+       char       *schemaname;
+       char       *opername;
+       Oid                     namespaceId;
+       HeapTuple       tup = NULL;
+ 
+       /* deconstruct the name list */
+       DeconstructQualifiedName(names, &schemaname, &opername);
+ 
+       if (schemaname)
+       {
+               /* use exact schema given */
+               namespaceId = LookupExplicitNamespace(schemaname);
+ 
+               tup = SearchSysCache(OPERNAMENSP,
+                                                        
CStringGetDatum(opername),
+                                                        
ObjectIdGetDatum(ltypeId),
+                                                        
ObjectIdGetDatum(rtypeId),
+                                                        
ObjectIdGetDatum(namespaceId));
+       }
+       else
+       {
+               /* we need namespace search */
+               ListCell   *nsp;
+ 
+               recomputeNamespacePath();
+ 
+               foreach(nsp, namespaceSearchPath)
+               {
+                       namespaceId = lfirst_oid(nsp);
+ 
+                       tup = SearchSysCache(OPERNAMENSP,
+                                                                
CStringGetDatum(opername),
+                                                                
ObjectIdGetDatum(ltypeId),
+                                                                
ObjectIdGetDatum(rtypeId),
+                                                                
ObjectIdGetDatum(namespaceId));
+                       if (HeapTupleIsValid(tup))
+                               break;
+               }
+       }
+ 
+       if (HeapTupleIsValid(tup))
+       {
+               Oid operOid = HeapTupleGetOid(tup);
+               ReleaseSysCache(tup);
+               return operOid;
+       }
+ 
+       return InvalidOid;
+ }
+ 
  
  /*
   * OpernameGetCandidates
*** ./src/backend/parser/parse_oper.c.orig      Tue Apr 25 22:12:52 2006
--- ./src/backend/parser/parse_oper.c   Wed Apr 26 00:19:04 2006
***************
*** 29,36 ****
  #include "utils/typcache.h"
  
  
! static Oid binary_oper_exact(Oid arg1, Oid arg2,
!                                 FuncCandidateList candidates);
  static FuncDetailCode oper_select_candidate(int nargs,
                                          Oid *input_typeids,
                                          FuncCandidateList candidates,
--- 29,35 ----
  #include "utils/typcache.h"
  
  
! static Oid binary_oper_exact(List *opname, Oid arg1, Oid arg2);
  static FuncDetailCode oper_select_candidate(int nargs,
                                          Oid *input_typeids,
                                          FuncCandidateList candidates,
***************
*** 387,397 ****
   * be reduced to its base type to find an "exact" match.
   */
  static Oid
! binary_oper_exact(Oid arg1, Oid arg2,
!                                 FuncCandidateList candidates)
  {
        FuncCandidateList cand;
        bool            was_unknown = false;
  
        /* Unspecified type for one of the arguments? then use the other */
        if ((arg1 == UNKNOWNOID) && (arg2 != InvalidOid))
--- 386,396 ----
   * be reduced to its base type to find an "exact" match.
   */
  static Oid
! binary_oper_exact(List *opname, Oid arg1, Oid arg2)
  {
        FuncCandidateList cand;
        bool            was_unknown = false;
+       Oid                     operOid;
  
        /* Unspecified type for one of the arguments? then use the other */
        if ((arg1 == UNKNOWNOID) && (arg2 != InvalidOid))
***************
*** 405,415 ****
                was_unknown = true;
        }
  
!       for (cand = candidates; cand != NULL; cand = cand->next)
!       {
!               if (arg1 == cand->args[0] && arg2 == cand->args[1])
!                       return cand->oid;
!       }
  
        if (was_unknown)
        {
--- 404,412 ----
                was_unknown = true;
        }
  
!       operOid = OpernameGet(opname, arg1, arg2);
!       if (OidIsValid(operOid))
!               return operOid;
  
        if (was_unknown)
        {
***************
*** 418,428 ****
  
                if (basetype != arg1)
                {
!                       for (cand = candidates; cand != NULL; cand = cand->next)
!                       {
!                               if (basetype == cand->args[0] && basetype == 
cand->args[1])
!                                       return cand->oid;
!                       }
                }
        }
  
--- 415,423 ----
  
                if (basetype != arg1)
                {
!                       operOid = OpernameGet(opname, basetype, basetype);
!                       if (OidIsValid(operOid))
!                               return operOid;
                }
        }
  
***************
*** 509,530 ****
        FuncDetailCode fdresult = FUNCDETAIL_NOTFOUND;
        HeapTuple       tup = NULL;
  
!       /* Get binary operators of given name */
!       clist = OpernameGetCandidates(opname, 'b');
  
!       /* No operators found? Then fail... */
!       if (clist != NULL)
        {
                /*
!                * Check for an "exact" match.
                 */
-               operOid = binary_oper_exact(ltypeId, rtypeId, clist);
-               if (!OidIsValid(operOid))
-               {
-                       /*
-                        * Otherwise, search for the most suitable candidate.
-                        */
  
                        /*
                         * Unspecified type for one of the arguments? then use 
the other
                         * (XXX this is probably dead code?)
--- 504,526 ----
        FuncDetailCode fdresult = FUNCDETAIL_NOTFOUND;
        HeapTuple       tup = NULL;
  
!       /*
!        * Check for an "exact" match.
!        */
!       operOid = binary_oper_exact(opname, ltypeId, rtypeId);
  
!       if (!OidIsValid(operOid))
        {
                /*
!                * Otherwise, search for the most suitable candidate.
                 */
  
+               /* Get binary operators of given name */
+               clist = OpernameGetCandidates(opname, 'b');
+ 
+               /* No operators found? Then fail... */
+               if (clist != NULL)
+               {
                        /*
                         * Unspecified type for one of the arguments? then use 
the other
                         * (XXX this is probably dead code?)
***************
*** 537,548 ****
                        inputOids[1] = rtypeId;
                        fdresult = oper_select_candidate(2, inputOids, clist, 
&operOid);
                }
-               if (OidIsValid(operOid))
-                       tup = SearchSysCache(OPEROID,
-                                                                
ObjectIdGetDatum(operOid),
-                                                                0, 0, 0);
        }
  
        if (!HeapTupleIsValid(tup) && !noError)
                op_error(pstate, opname, 'b', ltypeId, rtypeId, fdresult, 
location);
  
--- 533,545 ----
                        inputOids[1] = rtypeId;
                        fdresult = oper_select_candidate(2, inputOids, clist, 
&operOid);
                }
        }
  
+       if (OidIsValid(operOid))
+               tup = SearchSysCache(OPEROID,
+                                                        
ObjectIdGetDatum(operOid),
+                                                        0, 0, 0);
+ 
        if (!HeapTupleIsValid(tup) && !noError)
                op_error(pstate, opname, 'b', ltypeId, rtypeId, fdresult, 
location);
  
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to