Atsushi Ogawa <[EMAIL PROTECTED]> writes:
> 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.

I've been thinking more about how this might be improved.  In a test
involving the same trivial query repeated many times ("select * from tab
where foo = 'constant'") I see oprofile results like this:

samples  %        symbol name
95234     5.5458  AllocSetAlloc
89622     5.2190  hash_search
86038     5.0103  OpernameGetCandidates
76747     4.4692  SearchCatCache
64042     3.7294  base_yyparse
39473     2.2987  LWLockAcquire
32195     1.8748  base_yylex
25024     1.4572  nocachegetattr
21913     1.2761  MemoryContextAllocZeroAligned
21268     1.2385  _bt_compare
19665     1.1452  fmgr_info_cxt_security
18430     1.0732  LWLockRelease
17312     1.0081  expression_tree_walker
7787      0.4535  SearchCatCacheList

The problem with the patch you proposed is that when there *isn't* an
exact match, it costs N extra SearchCatCache probes, where N is the
length of the search_path.  We need a solution that limits the damage
in that case.

The solution I'm considering is to add an additional namespace.c routine
defined like
        Oid OpnameGetOprid(List *opname, Oid oprleft, Oid oprright)
that returns the first exact match in the search path, or 0 if none.
(parse_oper.c would try this before OpernameGetCandidates.)  The secret
is that it should use a SearchCatCacheList lookup instead of repeated
SearchCatCache probles.  If the list lookup is successful, it will
normally produce a very short result (typically only one member) and so
finding the first visible member if any is cheap.

With this approach the penalty for failure is just one extra
SearchCatCacheList lookup (which has cost comparable to SearchCatCache,
I believe, once the cache is set up) instead of N SearchCatCache
lookups.  In the example above this should add at most about half a
percent of total system runtime.  The potential of making a large dent
in OpernameGetCandidates' five percent makes that look like a good trade.

If this works out we might want to think about converting some of the
other namespace.c routines to use similar tactics.  I'm not sure I've
ever seen them high in a profile though.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to