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