我在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') $然而,如果我用这样的文字替换函数中的输入变量: 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功能要慢得多
发布评论