Обсуждение: Query timing increased from 3s to 55s when used as function instead of select
Query timing increased from 3s to 55s when used as function instead of select
От
Tyler Hildebrandt
Дата:
We're using a function that when run as a select statement outside of the function takes roughly 1.5s to complete whereas running an identical query within a function is taking around 55s to complete. We are lost as to why placing this query within a function as opposed to substituting the variables in a select statement is so drastically different. The timings posted here are from a 512MB memory virtual machine and are not of major concern on their own but we are finding the same issue in our production environment with far superior hardware. The function can be found here: http://campbell-lange.net/media/files/fn_medirota_get_staff_leave_summary.sql --- Timings for the individual components on their own is as follows: select * from fn_medirota_validate_rota_master(6); Time: 0.670 ms select to_date(EXTRACT (YEAR FROM current_date)::text, 'YYYY'); Time: 0.749 ms select * from fn_medirota_people_template_generator(2, 6, date'2009-01-01', date'2009-12-31', TRUE) AS templates; Time: 68.004 ms select * from fn_medirota_people_template_generator(2, 6, date'2010-01-01', date'2010-12-31', TRUE) AS templates; Time: 1797.323 Copying the exact same for loop select statement from the query above into the psql query buffer and running them with variable substitution yields the following: Running FOR loop SElECT with variable substitution: Time: 3150.585 ms Whereas invoking the function yields: select * from fn_medirota_get_staff_leave_summary(6); Time: 57375.477 ms We have tried using explain analyse to update the query optimiser, dropped and recreated the function and have restarted both the machine and the postgres server multiple times. Any help or advice would be greatly appreciated. Kindest regards, Tyler Hildebrandt --- EXPLAIN ANALYSE VERBOSE SELECT * FROM fn_medirota_get_staff_leave_summary(6); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- {FUNCTIONSCAN :startup_cost 0.00 :total_cost 260.00 :plan_rows 1000 :plan_width 85 :targetlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1 } :resno 1 :resname id :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2 } :resno 2 :resname t_full_name :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 3 :vartype 16 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3 } :resno 3 :resname b_enabled :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 4 :vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4 } :resno 4 :resname t_anniversary :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5 } :resno 5 :resname n_last_year_annual :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 6 } :resno 6 :resname n_last_year_other :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 7 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7 } :resno 7 :resname n_this_year_annual :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8 } :resno 8 :resname n_this_year_other :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :qual <> :lefttree <> :righttree <> :initPlan <> :extParam (b) :allParam (b) :scanrelid 1 :funcexpr {FUNCEXPR :funcid 150447 :funcresulttype 149366 :funcretset true :funcformat 0 :args ( {CONST :consttype 23 :consttypmod -1 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 6 0 0 0 0 0 0 0 ] } ) } :funccolnames ("id" "t_full_name" "b_enabled" "t_anniversary" "n_last_year_ annual" "n_last_year_other" "n_this_year_annual" "n_this_year_other") :funccoltypes <> :funccoltypmods <> } Function Scan on fn_medirota_get_staff_leave_summary (cost=0.00..260.00 rows=1000 width=85) (actual time=51877.812..51877.893 rows=94 loops=1) Total runtime: 51878.008 ms (183 rows) -- Tyler Hildebrandt Software Developer tyler@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 020 7631 1555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928
Re: Query timing increased from 3s to 55s when used as function instead of select
От
Craig Ringer
Дата:
On 21/05/2010 9:54 PM, Tyler Hildebrandt wrote: > We're using a function that when run as a select statement outside of the > function takes roughly 1.5s to complete whereas running an identical > query within a function is taking around 55s to complete. > > We are lost as to why placing this query within a function as opposed to > substituting the variables in a select statement is so drastically different. This is a frequently asked question. It's the same issue as with prepared queries, where the planner has to pick a more general plan when it doesn't know the value of a parameter. The short answer is "work around it by using EXECUTE ... USING to invoke your query dynamically". ( Oddly, this FAQ doesn't seem to be on the FAQ list at http://wiki.postgresql.org/wiki/FAQ ) -- Craig Ringer
Re: Query timing increased from 3s to 55s when used as function instead of select
От
Craig Ringer
Дата:
On 27/05/2010 11:33 PM, Craig Ringer wrote: > On 21/05/2010 9:54 PM, Tyler Hildebrandt wrote: >> We're using a function that when run as a select statement outside of the >> function takes roughly 1.5s to complete whereas running an identical >> query within a function is taking around 55s to complete. >> >> We are lost as to why placing this query within a function as opposed to >> substituting the variables in a select statement is so drastically >> different. > > This is a frequently asked question. It's the same issue as with > prepared queries, where the planner has to pick a more general plan when > it doesn't know the value of a parameter. The short answer is "work > around it by using EXECUTE ... USING to invoke your query dynamically". > > ( Oddly, this FAQ doesn't seem to be on the FAQ list at > http://wiki.postgresql.org/wiki/FAQ ) Added as: http://wiki.postgresql.org/wiki/FAQ#Why_is_my_query_much_slower_when_run_as_a_prepared_query.3F and the subsequent entry too. Comments, edits, clarification appreciated. I know it's not as well written as it could be, could use archive links, etc; it's just pass 1. -- Craig Ringer