PostgreSQL为什么在函数中以不同的方式对待我的查询？ [英] Why does PostgreSQL treat my query differently in a function?

问题描述

我有一个非常简单的查询，查询没有什么复杂的：

I have a very simple query that is not much more complicated than:

select *
from table_name
where id = 1234

...运行时间不到50毫秒。

...it takes less than 50 milliseconds to run.

使用该查询并将其放入函数中：

Took that query and put it into a function:

CREATE OR REPLACE FUNCTION pie(id_param integer)
RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY SELECT *
         FROM table_name
         where id = id_param;
END
$BODY$
LANGUAGE plpgsql STABLE;

此函数在执行 select * from pie（123）; 需要22秒。

This function when executed select * from pie(123); takes 22 seconds.

如果我用硬编码代替id_param的整数，该函数将在50毫秒内执行。

If I hard code an integer in place of id_param, the function executes in under 50 milliseconds.

为什么我在where语句中使用参数会导致我的函数运行缓慢？

Why does the fact that I am using a parameter in the where statement cause my function to run slow?

编辑以添加具体示例：

CREATE TYPE test_type AS (gid integer, geocode character varying(9))

CREATE OR REPLACE FUNCTION geocode_route_by_geocode(geocode_param character)
  RETURNS SETOF test_type AS
$BODY$
BEGIN
RETURN QUERY EXECUTE
    'SELECT     gs.geo_shape_id AS gid,     
        gs.geocode
    FROM geo_shapes gs
    WHERE geocode = $1
    AND geo_type = 1 
    GROUP BY geography, gid, geocode' USING geocode_param;
END;

$BODY$
  LANGUAGE plpgsql STABLE;
ALTER FUNCTION geocode_carrier_route_by_geocode(character)
  OWNER TO root;

--Runs in 20 seconds
select * from geocode_route_by_geocode('999xyz');

--Runs in 10 milliseconds
SELECT  gs.geo_shape_id AS gid,     
        gs.geocode
    FROM geo_shapes gs
    WHERE geocode = '9999xyz'
    AND geo_type = 1 
    GROUP BY geography, gid, geocode

推荐答案

PostgreSQL 9.2中的更新

有一个重大改进，我引用了此处的发行说明：

即使使用预备语句（Tom Lane），也允许计划者为特定参数
值生成自定义计划

Allow the planner to generate custom plans for specific parameter values even when using prepared statements (Tom Lane)

过去，预备语句始终具有一个用于所有参数值的通用计划
，通常比用于包含
显式常量值的未准备语句的计划低
。现在，计划者尝试为特定参数值生成自定义
计划。仅在反复证明自定义计划无济于事后，才会使用通用计划
。
的这一更改应消除以前从
使用预准备语句（包括
PL / pgSQL中的非动态语句）看到的性能损失。

In the past, a prepared statement always had a single "generic" plan that was used for all parameter values, which was frequently much inferior to the plans used for non-prepared statements containing explicit constant values. Now, the planner attempts to generate custom plans for specific parameter values. A generic plan will only be used after custom plans have repeatedly proven to provide no benefit. This change should eliminate the performance penalties formerly seen from use of prepared statements (including non-dynamic statements in PL/pgSQL).

---

PostgreSQL 9.1或更早版本的原始答案

plpgsql函数具有类似的功能效果与 PREPARE 语句相同：解析查询并缓存查询计划。

---

Original answer for PostgreSQL 9.1 or older

A plpgsql functions has a similar effect as the PREPARE statement: queries are parsed and the query plan is cached.

优点是开销大

的缺点是查询计划并未针对调用它的特定参数值进行优化。

The advantage is that some overhead is saved for every call.
The disadvantage is that the query plan is not optimized for the particular parameter values it is called with.

对于查询在数据分布均匀的表上，这通常没有问题，而且PL / pgSQL函数的执行速度比原始SQL查询或SQL函数要快一些。但是，如果您的查询可以根据 WHERE 子句中的实际值使用某些索引，或者更一般地为特定值选择更好的查询计划，则可能会得到次优查询计划。尝试使用SQL函数或将动态SQL与 执行 强制为每个呼叫重新计划查询。可能看起来像这样：

For queries on tables with even data distribution, this will generally be no problem and PL/pgSQL functions will perform somewhat faster than raw SQL queries or SQL functions. But if your query can use certain indexes depending on the actual values in the WHERE clause or, more generally, chose a better query plan for the particular values, you may end up with a sub-optimal query plan. Try an SQL function or use dynamic SQL with EXECUTE to force a the query to be re-planned for every call. Could look like this:

CREATE OR REPLACE FUNCTION pie(id_param integer)
RETURNS SETOF record AS
$BODY$
BEGIN        
    RETURN QUERY EXECUTE
        'SELECT *
         FROM   table_name
         where  id = $1'
    USING id_param;
END
$BODY$
LANGUAGE plpgsql STABLE;

---

评论后编辑：

---

Edit after comment:

如果此变体不改变执行时间，则可能还有其他因素在起作用，您可能会错过或未提及。不同的数据库？不同的参数值？您将不得不发布更多详细信息。

If this variant does not change the execution time, there must be other factors at play that you may have missed or did not mention. Different database? Different parameter values? You would have to post more details.

我添加了报价来自手册来备份我的上述声明：

I add a quote from the manual to back up my above statements:

一个具有简单常量命令字符串和一些US
参数的EXECUTE，与上面的第一个示例一样，在功能上等同于
，它只需要直接在PL / pgSQL中编写命令并允许
替换PL / pgSQL变量会自动发生。
的重要区别是EXECUTE将在每次
执行时重新计划命令，从而生成特定于当前参数
值的计划；而PL / pgSQL通常会创建一个通用计划并将其缓存
以便重复使用。在最佳计划很大程度上取决于
参数值的情况下，EXECUTE可以显着更快；而当
计划对参数值不敏感时，重新计划将是
的浪费。

An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas PL/pgSQL normally creates a generic plan and caches it for re-use. In situations where the best plan depends strongly on the parameter values, EXECUTE can be significantly faster; while when the plan is not sensitive to parameter values, re-planning will be a waste.

这篇关于PostgreSQL为什么在函数中以不同的方式对待我的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！