PostgreSQL函数执行计划缓存原理 [英] PostgreSQL function execution plan cache principle

问题描述

说我有3种使用第三种功能。假设get_company（user_id）和get_location（user_id）使用check_permission（user_id）函数。

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

执行计划缓存如何工作？我的意思是会为check_permission和get_company函数制定单独的执行计划，还是为get_company制定一个计划？如果为执行get_company和get_location分别构建执行计划，即使它们都使用check_permission函数，执行计划也会更有效率。

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 could do either or both.

如果 check_permission 是 LANGUAGE sql 函数，可以满足内联和 get_company 和/或 get_location 也是 LANGUAGE sql 或将其用作非平凡查询的一部分，它可能会内联

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.

在可行的情况下，请考虑使用视图而不是函数。它们效率更高，为计划者提供了更多选择。但是，如果需要 SECURITY_BARRIER 视图来防止信息泄漏，那么好处就更少了。

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.

我想知道是否可以通过专注于计划时间而犯了一个真正的错误，而没有（就您所显示的那样）调查了多少计划时间实际上在影响您的绩效。而且，您似乎并没有考虑通过fmgr和plpgsql过程处理程序等进行工作的大量开销，而不是原始查询，并根据计划成本进行权衡。我强烈建议您至少在确保已在整个应用程序中最佳使用准备好的语句之前，不要进一步这样做。然后针对您的工作量测量相对费用。

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.

这篇关于PostgreSQL函数执行计划缓存原理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！