Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitee895a6

Browse files
committed
Improve performance of repeated CALLs within plpgsql procedures.
This patch essentially is cleaning up technical debt left behindby the original implementation of plpgsql procedures, particularlycommitd92bc83. That patch (or more precisely, follow-on patchesfixing its worst bugs) forced us to re-plan CALL and DO statementseach time through, if we're in a non-atomic context. That wasn'tfor any fundamental reason, but just because use of a saved planrequires having a ResourceOwner to hold a reference count for theplan, and we had no suitable resowner at hand, nor would theavailable APIs support using one if we did. While it's not thatexpensive to create a "plan" for CALL/DO, the cycles do add upin repeated executions.This patch therefore makes the following API changes:* GetCachedPlan/ReleaseCachedPlan are modified to let the callerspecify which resowner to use to pin the plan, rather than forcinguse of CurrentResourceOwner.* spi.c gains a "SPI_execute_plan_extended" entry point that letscallers say which resowner to use to pin the plan. This borrows theidea 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 markeddeprecated) as well as SPI_execute_plan_with_receiver (which is newin v14, so I just took it out altogether).* I also took the opportunity to remove the crude hack of lettingplpgsql reach into SPI private data structures to mark SPI plans as"no_snapshot". It's better to treat that as an option ofSPI_prepare_extended.Now, when running a non-atomic procedure or DO block that containsany CALL or DO commands, plpgsql creates a ResourceOwner thatwill be used to pin the plans of the CALL/DO commands. (In anatomic 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 CALLof 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 meDiscussion:https://postgr.es/m/CAFj8pRCLPdDAETvR7Po7gC5y_ibkn_-bOzbeJb39WHms01194Q@mail.gmail.com
1 parent55ef855 commitee895a6

File tree

15 files changed

+461
-367
lines changed

15 files changed

+461
-367
lines changed

‎doc/src/sgml/spi.sgml

Lines changed: 111 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -1722,40 +1722,53 @@ int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>
17221722

17231723
<!-- *********************************************** -->
17241724

1725-
<refentry id="spi-spi-execute-plan-with-paramlist">
1726-
<indexterm><primary>SPI_execute_plan_with_paramlist</primary></indexterm>
1725+
<refentry id="spi-spi-execute-plan-extended">
1726+
<indexterm><primary>SPI_execute_plan_extended</primary></indexterm>
17271727

17281728
<refmeta>
1729-
<refentrytitle>SPI_execute_plan_with_paramlist</refentrytitle>
1729+
<refentrytitle>SPI_execute_plan_extended</refentrytitle>
17301730
<manvolnum>3</manvolnum>
17311731
</refmeta>
17321732

17331733
<refnamediv>
1734-
<refname>SPI_execute_plan_with_paramlist</refname>
1734+
<refname>SPI_execute_plan_extended</refname>
17351735
<refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
17361736
</refnamediv>
17371737

17381738
<refsynopsisdiv>
17391739
<synopsis>
1740-
int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
1741-
ParamListInfo <parameter>params</parameter>,
1742-
bool <parameter>read_only</parameter>,
1743-
long <parameter>count</parameter>)
1740+
int SPI_execute_plan_extended(SPIPlanPtr <parameter>plan</parameter>,
1741+
const SPIExecuteOptions * <parameter>options</parameter>)
17441742
</synopsis>
17451743
</refsynopsisdiv>
17461744

17471745
<refsect1>
17481746
<title>Description</title>
17491747

17501748
<para>
1751-
<function>SPI_execute_plan_with_paramlist</function> executes a statement
1752-
prepared by <function>SPI_prepare</function>.
1753-
This function is equivalent to <function>SPI_execute_plan</function>
1749+
<function>SPI_execute_plan_extended</function> executes a statement
1750+
prepared by <function>SPI_prepare</function> or one of its siblings.
1751+
This function is equivalent to <function>SPI_execute_plan</function>,
17541752
except that information about the parameter values to be passed to the
1755-
query is presented differently. The <literal>ParamListInfo</literal>
1756-
representation can be convenient for passing down values that are
1757-
already available in that format. It also supports use of dynamic
1758-
parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
1753+
query is presented differently, and additional execution-controlling
1754+
options can be passed.
1755+
</para>
1756+
1757+
<para>
1758+
Query parameter values are represented by
1759+
a <literal>ParamListInfo</literal> struct, which is convenient for passing
1760+
down values that are already available in that format. Dynamic parameter
1761+
sets can also be used, via hook functions specified
1762+
in <literal>ParamListInfo</literal>.
1763+
</para>
1764+
1765+
<para>
1766+
Also, instead of always accumulating the result tuples into a
1767+
<varname>SPI_tuptable</varname> structure, tuples can be passed to a
1768+
caller-supplied <literal>DestReceiver</literal> object as they are
1769+
generated by the executor. This is particularly helpful for queries
1770+
that might generate many tuples, since the data can be processed
1771+
on-the-fly instead of being accumulated in memory.
17591772
</para>
17601773
</refsect1>
17611774

@@ -1772,11 +1785,30 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
17721785
</listitem>
17731786
</varlistentry>
17741787

1788+
<varlistentry>
1789+
<term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term>
1790+
<listitem>
1791+
<para>
1792+
struct containing optional arguments
1793+
</para>
1794+
</listitem>
1795+
</varlistentry>
1796+
</variablelist>
1797+
1798+
<para>
1799+
Callers should always zero out the entire <parameter>options</parameter>
1800+
struct, then fill whichever fields they want to set. This ensures forward
1801+
compatibility of code, since any fields that are added to the struct in
1802+
future will be defined to behave backwards-compatibly if they are zero.
1803+
The currently available <parameter>options</parameter> fields are:
1804+
</para>
1805+
1806+
<variablelist>
17751807
<varlistentry>
17761808
<term><literal>ParamListInfo <parameter>params</parameter></literal></term>
17771809
<listitem>
17781810
<para>
1779-
data structure containing parameter types and values; NULL if none
1811+
data structure containingqueryparameter types and values; NULL if none
17801812
</para>
17811813
</listitem>
17821814
</varlistentry>
@@ -1789,14 +1821,47 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
17891821
</varlistentry>
17901822

17911823
<varlistentry>
1792-
<term><literal>long <parameter>count</parameter></literal></term>
1824+
<term><literal>bool <parameter>no_snapshots</parameter></literal></term>
1825+
<listitem>
1826+
<para>
1827+
<literal>true</literal> prevents SPI from managing snapshots for
1828+
execution of the query; use with extreme caution
1829+
</para>
1830+
</listitem>
1831+
</varlistentry>
1832+
1833+
<varlistentry>
1834+
<term><literal>uint64 <parameter>tcount</parameter></literal></term>
17931835
<listitem>
17941836
<para>
17951837
maximum number of rows to return,
17961838
or <literal>0</literal> for no limit
17971839
</para>
17981840
</listitem>
17991841
</varlistentry>
1842+
1843+
<varlistentry>
1844+
<term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
1845+
<listitem>
1846+
<para>
1847+
<literal>DestReceiver</literal> object that will receive any tuples
1848+
emitted by the query; if NULL, result tuples are accumulated into
1849+
a <varname>SPI_tuptable</varname> structure, as
1850+
in <function>SPI_execute_plan</function>
1851+
</para>
1852+
</listitem>
1853+
</varlistentry>
1854+
1855+
<varlistentry>
1856+
<term><literal>ResourceOwner <parameter>owner</parameter></literal></term>
1857+
<listitem>
1858+
<para>
1859+
The resource owner that will hold a reference count on the plan while
1860+
it is executed. If NULL, CurrentResourceOwner is used. Ignored for
1861+
non-saved plans, as SPI does not acquire reference counts on those.
1862+
</para>
1863+
</listitem>
1864+
</varlistentry>
18001865
</variablelist>
18011866
</refsect1>
18021867

@@ -1808,51 +1873,60 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
18081873
</para>
18091874

18101875
<para>
1876+
When <parameter>dest</parameter> is NULL,
18111877
<varname>SPI_processed</varname> and
18121878
<varname>SPI_tuptable</varname> are set as in
1813-
<function>SPI_execute_plan</function> if successful.
1879+
<function>SPI_execute_plan</function>.
1880+
When <parameter>dest</parameter> is not NULL,
1881+
<varname>SPI_processed</varname> is set to zero and
1882+
<varname>SPI_tuptable</varname> is set to NULL. If a tuple count
1883+
is required, the caller's <literal>DestReceiver</literal> object must
1884+
calculate it.
18141885
</para>
18151886
</refsect1>
18161887
</refentry>
18171888

18181889
<!-- *********************************************** -->
18191890

1820-
<refentry id="spi-spi-execute-plan-with-receiver">
1821-
<indexterm><primary>SPI_execute_plan_with_receiver</primary></indexterm>
1891+
<refentry id="spi-spi-execute-plan-with-paramlist">
1892+
<indexterm><primary>SPI_execute_plan_with_paramlist</primary></indexterm>
18221893

18231894
<refmeta>
1824-
<refentrytitle>SPI_execute_plan_with_receiver</refentrytitle>
1895+
<refentrytitle>SPI_execute_plan_with_paramlist</refentrytitle>
18251896
<manvolnum>3</manvolnum>
18261897
</refmeta>
18271898

18281899
<refnamediv>
1829-
<refname>SPI_execute_plan_with_receiver</refname>
1900+
<refname>SPI_execute_plan_with_paramlist</refname>
18301901
<refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
18311902
</refnamediv>
18321903

18331904
<refsynopsisdiv>
18341905
<synopsis>
1835-
int SPI_execute_plan_with_receiver(SPIPlanPtr <parameter>plan</parameter>,
1836-
ParamListInfo <parameter>params</parameter>,
1837-
bool <parameter>read_only</parameter>,
1838-
long <parameter>count</parameter>,
1839-
DestReceiver *<parameter>dest</parameter>)
1906+
int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
1907+
ParamListInfo <parameter>params</parameter>,
1908+
bool <parameter>read_only</parameter>,
1909+
long <parameter>count</parameter>)
18401910
</synopsis>
18411911
</refsynopsisdiv>
18421912

18431913
<refsect1>
18441914
<title>Description</title>
18451915

18461916
<para>
1847-
<function>SPI_execute_plan_with_receiver</function> executes a statement
1848-
prepared by <function>SPI_prepare</function>. This function is
1849-
equivalent to <function>SPI_execute_plan_with_paramlist</function>
1850-
except that, instead of always accumulating the result tuples into a
1851-
<varname>SPI_tuptable</varname> structure, tuples can be passed to a
1852-
caller-supplied <literal>DestReceiver</literal> object as they are
1853-
generated by the executor. This is particularly helpful for queries
1854-
that might generate many tuples, since the data can be processed
1855-
on-the-fly instead of being accumulated in memory.
1917+
<function>SPI_execute_plan_with_paramlist</function> executes a statement
1918+
prepared by <function>SPI_prepare</function>.
1919+
This function is equivalent to <function>SPI_execute_plan</function>
1920+
except that information about the parameter values to be passed to the
1921+
query is presented differently. The <literal>ParamListInfo</literal>
1922+
representation can be convenient for passing down values that are
1923+
already available in that format. It also supports use of dynamic
1924+
parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
1925+
</para>
1926+
1927+
<para>
1928+
This function is now deprecated in favor
1929+
of <function>SPI_execute_plan_extended</function>.
18561930
</para>
18571931
</refsect1>
18581932

@@ -1894,17 +1968,6 @@ int SPI_execute_plan_with_receiver(SPIPlanPtr <parameter>plan</parameter>,
18941968
</para>
18951969
</listitem>
18961970
</varlistentry>
1897-
1898-
<varlistentry>
1899-
<term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
1900-
<listitem>
1901-
<para>
1902-
<literal>DestReceiver</literal> object that will receive any tuples
1903-
emitted by the query; if NULL, this function is exactly equivalent to
1904-
<function>SPI_execute_plan_with_paramlist</function>
1905-
</para>
1906-
</listitem>
1907-
</varlistentry>
19081971
</variablelist>
19091972
</refsect1>
19101973

@@ -1916,15 +1979,9 @@ int SPI_execute_plan_with_receiver(SPIPlanPtr <parameter>plan</parameter>,
19161979
</para>
19171980

19181981
<para>
1919-
When <parameter>dest</parameter> is NULL,
19201982
<varname>SPI_processed</varname> and
19211983
<varname>SPI_tuptable</varname> are set as in
1922-
<function>SPI_execute_plan</function>.
1923-
When <parameter>dest</parameter> is not NULL,
1924-
<varname>SPI_processed</varname> is set to zero and
1925-
<varname>SPI_tuptable</varname> is set to NULL. If a tuple count
1926-
is required, the caller's <literal>DestReceiver</literal> object must
1927-
calculate it.
1984+
<function>SPI_execute_plan</function> if successful.
19281985
</para>
19291986
</refsect1>
19301987
</refentry>

‎src/backend/commands/prepare.c

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -230,7 +230,7 @@ ExecuteQuery(ParseState *pstate,
230230
entry->plansource->query_string);
231231

232232
/* Replan if needed, and increment plan refcount for portal */
233-
cplan=GetCachedPlan(entry->plansource,paramLI,false,NULL);
233+
cplan=GetCachedPlan(entry->plansource,paramLI,NULL,NULL);
234234
plan_list=cplan->stmt_list;
235235

236236
/*
@@ -651,7 +651,8 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
651651
}
652652

653653
/* Replan if needed, and acquire a transient refcount */
654-
cplan=GetCachedPlan(entry->plansource,paramLI, true,queryEnv);
654+
cplan=GetCachedPlan(entry->plansource,paramLI,
655+
CurrentResourceOwner,queryEnv);
655656

656657
INSTR_TIME_SET_CURRENT(planduration);
657658
INSTR_TIME_SUBTRACT(planduration,planstart);
@@ -687,7 +688,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
687688
if (estate)
688689
FreeExecutorState(estate);
689690

690-
ReleaseCachedPlan(cplan,true);
691+
ReleaseCachedPlan(cplan,CurrentResourceOwner);
691692
}
692693

693694
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp