Say I have 2 functions using third one. Say function check_permission(user_id) is used by get_company(user_id) and get_location(user_id).
How would execution plan cache work? I mean would it be making separate execution plans for check_permission and get_company functions or would it be one plan for get_company? There is a chance that execution plan will be more efficient if it is built for get_company and get_location individually even if they are both using check_permission function.
"It depends".
It could do either or both.
If check_permission is a LANGUAGE sql function that satisfies the requirements for inlining, and get_company and/or get_location are also LANGUAGE sql or use it as part of non-trivial queries, it might get inlined and planned twice, once for each caller, as part of the calling query.
Otherwise, it will generally get planned once, when first called by either caller.
By the way, consider using views instead of functions when practical. They're more efficient and give the planner more options. But there's less benefit if they need to be SECURITY_BARRIER views to guard against information leaks.
I wonder if you might be making a real mistake by focusing on planning time without (as far as you have shown) looking into how much planning time is actually impacting your performance. And you don't seem to be considering the significant overheads of doing work via the fmgr and plpgsql procedure handlers etc, vs raw queries, and weighing that against planning costs. I strongly advise you not to pursue this further until you've made sure you're optimally using prepared statements across the app, at least. Then measure relative costs, for your workload.
