From ee895a655ce4341546facd6f23e3e8f2931b96bf Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 25 Jan 2021 22:28:29 -0500 Subject: Improve performance of repeated CALLs within plpgsql procedures. This patch essentially is cleaning up technical debt left behind by the original implementation of plpgsql procedures, particularly commit d92bc83c4. That patch (or more precisely, follow-on patches fixing its worst bugs) forced us to re-plan CALL and DO statements each time through, if we're in a non-atomic context. That wasn't for any fundamental reason, but just because use of a saved plan requires having a ResourceOwner to hold a reference count for the plan, and we had no suitable resowner at hand, nor would the available APIs support using one if we did. While it's not that expensive to create a "plan" for CALL/DO, the cycles do add up in repeated executions. This patch therefore makes the following API changes: * GetCachedPlan/ReleaseCachedPlan are modified to let the caller specify which resowner to use to pin the plan, rather than forcing use of CurrentResourceOwner. * spi.c gains a "SPI_execute_plan_extended" entry point that lets callers say which resowner to use to pin the plan. This borrows the idea of an options struct from the recently added SPI_prepare_extended, hopefully allowing future options to be added without more API breaks. This supersedes SPI_execute_plan_with_paramlist (which I've marked deprecated) as well as SPI_execute_plan_with_receiver (which is new in v14, so I just took it out altogether). * I also took the opportunity to remove the crude hack of letting plpgsql reach into SPI private data structures to mark SPI plans as "no_snapshot". It's better to treat that as an option of SPI_prepare_extended. Now, when running a non-atomic procedure or DO block that contains any CALL or DO commands, plpgsql creates a ResourceOwner that will be used to pin the plans of the CALL/DO commands. (In an atomic context, we just use CurrentResourceOwner, as before.) Having done this, we can just save CALL/DO plans normally, whether or not they are used across transaction boundaries. This seems to be good for something like 2X speedup of a CALL of a trivial procedure with a few simple argument expressions. By restricting the creation of an extra ResourceOwner like this, there's essentially zero penalty in cases that can't benefit. Pavel Stehule, with some further hacking by me Discussion: https://postgr.es/m/CAFj8pRCLPdDAETvR7Po7gC5y_ibkn_-bOzbeJb39WHms01194Q@mail.gmail.com --- doc/src/sgml/spi.sgml | 165 +++++++++++++++++++++++++++++++++----------------- 1 file changed, 111 insertions(+), 54 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index f5e0a35da06..d8c121f5f35 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -1722,25 +1722,23 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * - - SPI_execute_plan_with_paramlist + + SPI_execute_plan_extended - SPI_execute_plan_with_paramlist + SPI_execute_plan_extended 3 - SPI_execute_plan_with_paramlist + SPI_execute_plan_extended execute a statement prepared by SPI_prepare -int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, - ParamListInfo params, - bool read_only, - long count) +int SPI_execute_plan_extended(SPIPlanPtr plan, + const SPIExecuteOptions * options) @@ -1748,14 +1746,29 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, Description - SPI_execute_plan_with_paramlist executes a statement - prepared by SPI_prepare. - This function is equivalent to SPI_execute_plan + SPI_execute_plan_extended executes a statement + prepared by SPI_prepare or one of its siblings. + This function is equivalent to SPI_execute_plan, except that information about the parameter values to be passed to the - query is presented differently. The ParamListInfo - representation can be convenient for passing down values that are - already available in that format. It also supports use of dynamic - parameter sets via hook functions specified in ParamListInfo. + query is presented differently, and additional execution-controlling + options can be passed. + + + + Query parameter values are represented by + a ParamListInfo struct, which is convenient for passing + down values that are already available in that format. Dynamic parameter + sets can also be used, via hook functions specified + in ParamListInfo. + + + + Also, instead of always accumulating the result tuples into a + SPI_tuptable structure, tuples can be passed to a + caller-supplied DestReceiver object as they are + generated by the executor. This is particularly helpful for queries + that might generate many tuples, since the data can be processed + on-the-fly instead of being accumulated in memory. @@ -1772,11 +1785,30 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, + + const SPIExecuteOptions * options + + + struct containing optional arguments + + + + + + + Callers should always zero out the entire options + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available options fields are: + + + ParamListInfo params - data structure containing parameter types and values; NULL if none + data structure containing query parameter types and values; NULL if none @@ -1789,7 +1821,17 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, - long count + bool no_snapshots + + + true prevents SPI from managing snapshots for + execution of the query; use with extreme caution + + + + + + uint64 tcount maximum number of rows to return, @@ -1797,6 +1839,29 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, + + + DestReceiver * dest + + + DestReceiver object that will receive any tuples + emitted by the query; if NULL, result tuples are accumulated into + a SPI_tuptable structure, as + in SPI_execute_plan + + + + + + ResourceOwner owner + + + The resource owner that will hold a reference count on the plan while + it is executed. If NULL, CurrentResourceOwner is used. Ignored for + non-saved plans, as SPI does not acquire reference counts on those. + + + @@ -1808,35 +1873,40 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, + When dest is NULL, SPI_processed and SPI_tuptable are set as in - SPI_execute_plan if successful. + SPI_execute_plan. + When dest is not NULL, + SPI_processed is set to zero and + SPI_tuptable is set to NULL. If a tuple count + is required, the caller's DestReceiver object must + calculate it. - - SPI_execute_plan_with_receiver + + SPI_execute_plan_with_paramlist - SPI_execute_plan_with_receiver + SPI_execute_plan_with_paramlist 3 - SPI_execute_plan_with_receiver + SPI_execute_plan_with_paramlist execute a statement prepared by SPI_prepare -int SPI_execute_plan_with_receiver(SPIPlanPtr plan, - ParamListInfo params, - bool read_only, - long count, - DestReceiver *dest) +int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, + ParamListInfo params, + bool read_only, + long count) @@ -1844,15 +1914,19 @@ int SPI_execute_plan_with_receiver(SPIPlanPtr plan, Description - SPI_execute_plan_with_receiver executes a statement - prepared by SPI_prepare. This function is - equivalent to SPI_execute_plan_with_paramlist - except that, instead of always accumulating the result tuples into a - SPI_tuptable structure, tuples can be passed to a - caller-supplied DestReceiver object as they are - generated by the executor. This is particularly helpful for queries - that might generate many tuples, since the data can be processed - on-the-fly instead of being accumulated in memory. + SPI_execute_plan_with_paramlist executes a statement + prepared by SPI_prepare. + This function is equivalent to SPI_execute_plan + except that information about the parameter values to be passed to the + query is presented differently. The ParamListInfo + representation can be convenient for passing down values that are + already available in that format. It also supports use of dynamic + parameter sets via hook functions specified in ParamListInfo. + + + + This function is now deprecated in favor + of SPI_execute_plan_extended. @@ -1894,17 +1968,6 @@ int SPI_execute_plan_with_receiver(SPIPlanPtr plan, - - - DestReceiver * dest - - - DestReceiver object that will receive any tuples - emitted by the query; if NULL, this function is exactly equivalent to - SPI_execute_plan_with_paramlist - - - @@ -1916,15 +1979,9 @@ int SPI_execute_plan_with_receiver(SPIPlanPtr plan, - When dest is NULL, SPI_processed and SPI_tuptable are set as in - SPI_execute_plan. - When dest is not NULL, - SPI_processed is set to zero and - SPI_tuptable is set to NULL. If a tuple count - is required, the caller's DestReceiver object must - calculate it. + SPI_execute_plan if successful. -- cgit v1.2.3