Re: Improvement of search for a binary operator

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

AFAICT, this will make things a bit faster if there is an exact match,
and quite a bit slower if there is not (especially if the search path
is long). I've known for awhile that OpernameGetCandidates is a
bottleneck, but I don't want a solution that optimizes some cases at the
price of making others worse. pgbench is not a good model of the real
world for such tradeoffs.

Whatever solution we find, it should be applied to the unary operator
paths as well. It's not appropriate to introduce gratuitous differences
between the binary and unary operator paths. (Again, pgbench is a poor
model of the real world ... I don't think it even uses any unary
operators.)

regards, tom lane