0

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.

Yevgeniy Afanasyev
  • 37,872
  • 26
  • 173
  • 191

1 Answers1

0

"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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • In my knowledge views don't get their execution plan cached. – Yevgeniy Afanasyev Oct 11 '17 at 01:28
  • Views don't, but queries do, including queries that incorporate views. And overall you'll tend to get better results that way than by going through the fmgr interface for function calls, unless your function has an *insanely* complex query plan that's also very cheap to actually execute and cannot ever significantly benefit from inlining. – Craig Ringer Oct 11 '17 at 01:47
  • Queries get their execution plan cached? Thanks, good to know. It gives me more questions though. Please have a look at my other qustions [here](https://stackoverflow.com/questions/46678578/postrgresql-performance-ad-hoc-sql-vs-a-functions) – Yevgeniy Afanasyev Oct 11 '17 at 02:08
  • 1
    I have herd that in plpgsql functions would cache query plans. Ad-hoc SQL doesn't, unless you explicitly make use of prepared queries. [here](http://www.postgresql-archive.org/Monitoring-query-plan-cache-td5831561.html) – Yevgeniy Afanasyev Oct 11 '17 at 02:18
  • 2
    Yes, that's right. But presumably you're already using prepared statements if you're even thinking about stuff like this. – Craig Ringer Oct 11 '17 at 02:44
  • Actually, I'm not focused on planning time. I'm more concern about pitfalls in using cache for execution plans. [This](https://stackoverflow.com/questions/46698386/execution-plan-cache-for-pl-pgsql-functions-in-postgresql) is how it started. Please, have a look. – Yevgeniy Afanasyev Oct 11 '17 at 22:16
  • It would've been really handy to link to that at the start. But thanks. – Craig Ringer Oct 12 '17 at 01:56
  • Thanks, I made a comment with this link to the question. – Yevgeniy Afanasyev Oct 12 '17 at 05:14