使用输入变量时Postgres功能要慢得多 [英] Postgres function much slower when using input variables

问题描述

我在Postgres 8.3.5中有一个函数，用于从多个表中选择数据并将结果转储到一个表中：

 创建或替换函数test_function_2（startdate timestamp，enddate timestamp）
返回void作为$$ 
 begin 
从cl_final_report中删除; 
 
 INSERT INTO cl_final_report 
 SELECT 
 b.batchkey AS batchnumber，
 pv.productkey，
 p.name AS产品名称，
 avg（ r.value）AS avgchemlean，
 sum（r.auxvalue）AS总重量，
 max（o.time）AS timecompleted 
 FROM结果r 
 LEFT JOIN物理值pv ON r。 physicalvaluekey = pv.physicalvaluekey 
 LEFT JOIN产品p ON pv.productkey = p.productkey 
 LEFT JOIN对象o ON r.objectkey = o.objectkey 
 LEFT JOIN批次b ON o.batchkey = b.batchkey 
 WHERE pv.name ='CL':: text AND 
以及startdate和enddate之间的o.time 
 GROUP BY b.batchkey，pv.productkey，p.name 
结束
 $$语言plpgsql; 
  

这个函数需要113秒来完成使用PgAdmin并执行这个命令：

  select test_function_2（'05 / 02/2013'，'05 / 03/2013'）
   
 
 
  $ b>  code>创建或替换函数test_function_2（startdate timestamp，enddate timestamp）
返回void作为$$ 
 begin 
从cl_final_report中删除; 
 
 INSERT INTO cl_final_report 
 SELECT 
 b.batchkey AS batchnumber，
 pv.productkey，
 p.name AS产品名称，
 avg（ r.value）AS avgchemlean，
 sum（r.auxvalue）AS总重量，
 max（o.time）AS timecompleted 
 FROM结果r 
 LEFT JOIN物理值pv ON r。 physicalvaluekey = pv.physicalvaluekey 
 LEFT JOIN产品p ON pv.productkey = p.productkey 
 LEFT JOIN对象o ON r.objectkey = o.objectkey 
 LEFT JOIN批次b ON o.batchkey = b.batchkey 
 WHERE pv.name ='CL':: text AND 
和'05 / 02/2013'和'05 / 03/2013'之间的o.time 
 GROUP BY b .batchkey，pv.productkey，p.name 
 end 
 $$语言plpgsql; 
  
该函数在不到5秒的时间内执行。  
 
 
我是Postgres的新手，所以可能会丢失一些东西，但我似乎无法在任何地方找到答案。 
解决方案
如果您将查询设为动态，您可以在每次执行时强制执行一个新计划： 
 
 
 创建或替换函数test_function_2（
 startdate timestamp，enddate timestamp 
）返回void作为$ function $ 
 begin 
从cl_final_report删除; 
 
执行$$ 
 INSERT INTO cl_final_report 
 SELECT 
 b.batchkey AS batchnumber，
 pv.productkey，
 p.name AS产品名称，
 avg（r.value）AS avgchemlean，
 sum（r.auxvalue）AS总重量，
 max（o.time）AS完成时间
 FROM结果r 
 LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey 
 LEFT JOIN产品p ON pv.productkey = p.productkey 
 LEFT JOIN对象o ON r.objectkey = o.objectkey 
 LEFT JOIN批次b ON o.batchkey = b.batchkey 
 WHERE pv.name ='CL':: text AND 
和o.time在$ 1和$ 2之间
 GROUP BY b.batchkey，pv。 productkey，p.name 
 $$使用startdate，enddate; 
 end; 
 $ function $ language plpgsql; 
  
使用做字符串连接： 
 
 
 创建或替换函数test_function_2（
 startdate timestamp，enddate timestamp 
）返回void作为$ function $ 
 begin 
从cl_final_report中删除; 
 
执行$$ 
 INSERT INTO cl_final_report 
 SELECT 
 b.batchkey AS batchnumber，
 pv.productkey，
 p.name AS产品名称，
 avg（r.value）AS avgchemlean，
 sum（r.auxvalue）AS总重量，
 max（o.time）AS完成时间
 FROM结果r 
 LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey 
 LEFT JOIN产品p ON pv.productkey = p.productkey 
 LEFT JOIN对象o ON r.objectkey = o.objectkey 
 LEFT JOIN批次b ON o.batchkey = b.batchkey 
 WHERE pv.name ='CL':: text AND 
和'$$ ||之间的o.time startdate || $$'和'$$ || enddate || $$'
 GROUP BY b.batchkey，pv.productkey，p.name 
 $$; 
 end; 
 $ function $ language plpgsql; 
  
 
I have a function in Postgres 8.3.5 that selects data from multiple tables and dumps the result in a  single table:
create or replace function test_function_2(startdate timestamp, enddate timestamp)
returns void as $$
begin
     delete from cl_final_report;

     INSERT INTO cl_final_report
     SELECT 
         b.batchkey AS batchnumber, 
         pv.productkey, 
         p.name AS productname, 
         avg(r.value) AS avgchemlean, 
         sum(r.auxvalue) AS totalweight, 
         max(o.time) AS timecompleted
     FROM result r
     LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey
     LEFT JOIN product p ON pv.productkey = p.productkey
     LEFT JOIN object o ON r.objectkey = o.objectkey
     LEFT JOIN batch b ON o.batchkey = b.batchkey
     WHERE pv.name = 'CL'::text AND
         and o.time between startdate and enddate
     GROUP BY b.batchkey, pv.productkey, p.name
end
$$ language plpgsql;
This function takes 113 seconds to complete using PgAdmin and executing this command:
select test_function_2('05/02/2013', '05/03/2013')
However, if I replace the input variables in the function with literals like this:
create or replace function test_function_2(startdate timestamp, enddate timestamp)
returns void as $$
begin
     delete from cl_final_report;

     INSERT INTO cl_final_report
     SELECT 
         b.batchkey AS batchnumber, 
         pv.productkey, 
         p.name AS productname, 
         avg(r.value) AS avgchemlean, 
         sum(r.auxvalue) AS totalweight, 
         max(o.time) AS timecompleted
     FROM result r
     LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey
     LEFT JOIN product p ON pv.productkey = p.productkey
     LEFT JOIN object o ON r.objectkey = o.objectkey
     LEFT JOIN batch b ON o.batchkey = b.batchkey
     WHERE pv.name = 'CL'::text AND
         and o.time between '05/02/2013' and '05/03/2013'
     GROUP BY b.batchkey, pv.productkey, p.name
end
$$ language plpgsql;
The function executes in less than 5 seconds.  


I'm new to Postgres so there's probably something I'm missing, but I can't seem to find an answer anywhere.  
解决方案
You can force a new plan on each execution if you make the query dynamic:
create or replace function test_function_2(
    startdate timestamp, enddate timestamp
) returns void as $function$
begin
    delete from cl_final_report;

    execute $$
        INSERT INTO cl_final_report
        SELECT 
            b.batchkey AS batchnumber, 
            pv.productkey, 
            p.name AS productname, 
            avg(r.value) AS avgchemlean, 
            sum(r.auxvalue) AS totalweight, 
            max(o.time) AS timecompleted
        FROM result r
        LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey
        LEFT JOIN product p ON pv.productkey = p.productkey
        LEFT JOIN object o ON r.objectkey = o.objectkey
        LEFT JOIN batch b ON o.batchkey = b.batchkey
        WHERE pv.name = 'CL'::text AND
            and o.time between $1 and $2
        GROUP BY b.batchkey, pv.productkey, p.name
    $$ using startdate, enddate;
end;
$function$ language plpgsql;
For it to work in 8.3 without using do string concatenation:
create or replace function test_function_2(
    startdate timestamp, enddate timestamp
) returns void as $function$
begin
    delete from cl_final_report;

    execute $$
        INSERT INTO cl_final_report
        SELECT 
            b.batchkey AS batchnumber, 
            pv.productkey, 
            p.name AS productname, 
            avg(r.value) AS avgchemlean, 
            sum(r.auxvalue) AS totalweight, 
            max(o.time) AS timecompleted
        FROM result r
        LEFT JOIN physicalvalue pv ON r.physicalvaluekey = pv.physicalvaluekey
        LEFT JOIN product p ON pv.productkey = p.productkey
        LEFT JOIN object o ON r.objectkey = o.objectkey
        LEFT JOIN batch b ON o.batchkey = b.batchkey
        WHERE pv.name = 'CL'::text AND
            and o.time between '$$ || startdate || $$' and '$$ || enddate || $$'
        GROUP BY b.batchkey, pv.productkey, p.name
    $$;
end;
$function$ language plpgsql;


                        
这篇关于使用输入变量时Postgres功能要慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！