本文介绍了使用输入变量时Postgres功能要慢得多的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在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功能要慢得多
发布评论