Re: Improvement of search for a binary operator

Atsushi Ogawa <a_ogawa(at)hi-ho(dot)ne(dot)jp> 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