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

Commita17da19

Browse files
committed
Invent a "one-shot" variant of CachedPlans for better performance.
SPI_execute() and related functions create a CachedPlan, execute it once,and immediately discard it, so that the functionality offered byplancache.c is of no value in this code path. And performance measurementsshow that the extra data copying and invalidation checking done byplancache.c slows down simple queries by 10% or more compared to 9.1.However, enough of the SPI code is shared with functions that do need plancaching that it seems impractical to bypass plancache.c altogether.Instead, let's invent a variant version of cached plans that preserves99% of the API but doesn't offer any of the actual functionality, nor theoverhead. This puts SPI_execute() performance back on par, or maybe evenslightly better, than it was before. This change should resolve recentcomplaints of performance degradation from Dong Ye, Pavel Stehule, andothers.By avoiding data copying, this change also reduces the amount of memoryneeded to execute many-statement SPI_execute() strings, as for instance ina recent complaint from Tomas Vondra.An additional benefit of this change is that multi-statement SPI_execute()query strings are now processed fully serially, that is we completeexecution of earlier statements before running parse analysis and planningon following ones. This eliminates a long-standing POLA violation, in thatDDL that affects the behavior of a later statement will now behave asexpected.Back-patch to 9.2, since this was a performance regression compared to 9.1.(In 9.2, place the added struct fields so as to avoid changing the offsetsof existing fields.)Heikki Linnakangas and Tom Lane
1 parent48e0b8a commita17da19

File tree

5 files changed

+329
-57
lines changed

5 files changed

+329
-57
lines changed

‎doc/src/sgml/spi.sgml

Lines changed: 15 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -326,9 +326,7 @@ SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
326326
</para>
327327

328328
<para>
329-
You can pass multiple commands in one string, but later commands cannot
330-
depend on the creation of objects earlier in the string, because the
331-
whole string will be parsed and planned before execution begins.
329+
You can pass multiple commands in one string;
332330
<function>SPI_execute</function> returns the
333331
result for the command executed last. The <parameter>count</parameter>
334332
limit applies to each command separately, but it is not applied to
@@ -392,7 +390,8 @@ typedef struct
392390
TupleDesc tupdesc; /* row descriptor */
393391
HeapTuple *vals; /* rows */
394392
} SPITupleTable;
395-
</programlisting><structfield>vals</> is an array of pointers to rows. (The number
393+
</programlisting>
394+
<structfield>vals</> is an array of pointers to rows. (The number
396395
of valid entries is given by <varname>SPI_processed</varname>.)
397396
<structfield>tupdesc</> is a row descriptor which you can pass to
398397
SPI functions dealing with rows. <structfield>tuptabcxt</>,
@@ -432,7 +431,8 @@ typedef struct
432431
<term><literal>long <parameter>count</parameter></literal></term>
433432
<listitem>
434433
<para>
435-
maximum number of rows to process or return
434+
maximum number of rows to process or return,
435+
or <literal>0</> for no limit
436436
</para>
437437
</listitem>
438438
</varlistentry>
@@ -671,7 +671,8 @@ int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count<
671671
<term><literal>long <parameter>count</parameter></literal></term>
672672
<listitem>
673673
<para>
674-
maximum number of rows to process or return
674+
maximum number of rows to process or return,
675+
or <literal>0</> for no limit
675676
</para>
676677
</listitem>
677678
</varlistentry>
@@ -809,7 +810,8 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>,
809810
<term><literal>long <parameter>count</parameter></literal></term>
810811
<listitem>
811812
<para>
812-
maximum number of rows to process or return
813+
maximum number of rows to process or return,
814+
or <literal>0</> for no limit
813815
</para>
814816
</listitem>
815817
</varlistentry>
@@ -1452,7 +1454,8 @@ int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>
14521454
<term><literal>long <parameter>count</parameter></literal></term>
14531455
<listitem>
14541456
<para>
1455-
maximum number of rows to process or return
1457+
maximum number of rows to process or return,
1458+
or <literal>0</> for no limit
14561459
</para>
14571460
</listitem>
14581461
</varlistentry>
@@ -1569,7 +1572,8 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
15691572
<term><literal>long <parameter>count</parameter></literal></term>
15701573
<listitem>
15711574
<para>
1572-
maximum number of rows to process or return
1575+
maximum number of rows to process or return,
1576+
or <literal>0</> for no limit
15731577
</para>
15741578
</listitem>
15751579
</varlistentry>
@@ -1669,7 +1673,8 @@ int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values<
16691673
<term><literal>long <parameter>count</parameter></literal></term>
16701674
<listitem>
16711675
<para>
1672-
maximum number of rows to process or return
1676+
maximum number of rows to process or return,
1677+
or <literal>0</> for no limit
16731678
</para>
16741679
</listitem>
16751680
</varlistentry>

‎src/backend/executor/spi.c

Lines changed: 128 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -48,8 +48,9 @@ static int_SPI_curid = -1;
4848
staticPortalSPI_cursor_open_internal(constchar*name,SPIPlanPtrplan,
4949
ParamListInfoparamLI,boolread_only);
5050

51-
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan,
52-
ParamListInfoboundParams);
51+
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan);
52+
53+
staticvoid_SPI_prepare_oneshot_plan(constchar*src,SPIPlanPtrplan);
5354

5455
staticint_SPI_execute_plan(SPIPlanPtrplan,ParamListInfoparamLI,
5556
Snapshotsnapshot,Snapshotcrosscheck_snapshot,
@@ -354,7 +355,7 @@ SPI_execute(const char *src, bool read_only, long tcount)
354355
plan.magic=_SPI_PLAN_MAGIC;
355356
plan.cursor_options=0;
356357

357-
_SPI_prepare_plan(src,&plan,NULL);
358+
_SPI_prepare_oneshot_plan(src,&plan);
358359

359360
res=_SPI_execute_plan(&plan,NULL,
360361
InvalidSnapshot,InvalidSnapshot,
@@ -505,7 +506,7 @@ SPI_execute_with_args(const char *src,
505506
paramLI=_SPI_convert_params(nargs,argtypes,
506507
Values,Nulls);
507508

508-
_SPI_prepare_plan(src,&plan,paramLI);
509+
_SPI_prepare_oneshot_plan(src,&plan);
509510

510511
res=_SPI_execute_plan(&plan,paramLI,
511512
InvalidSnapshot,InvalidSnapshot,
@@ -546,7 +547,7 @@ SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes,
546547
plan.parserSetup=NULL;
547548
plan.parserSetupArg=NULL;
548549

549-
_SPI_prepare_plan(src,&plan,NULL);
550+
_SPI_prepare_plan(src,&plan);
550551

551552
/* copy plan to procedure context */
552553
result=_SPI_make_plan_non_temp(&plan);
@@ -583,7 +584,7 @@ SPI_prepare_params(const char *src,
583584
plan.parserSetup=parserSetup;
584585
plan.parserSetupArg=parserSetupArg;
585586

586-
_SPI_prepare_plan(src,&plan,NULL);
587+
_SPI_prepare_plan(src,&plan);
587588

588589
/* copy plan to procedure context */
589590
result=_SPI_make_plan_non_temp(&plan);
@@ -598,7 +599,8 @@ SPI_keepplan(SPIPlanPtr plan)
598599
{
599600
ListCell*lc;
600601

601-
if (plan==NULL||plan->magic!=_SPI_PLAN_MAGIC||plan->saved)
602+
if (plan==NULL||plan->magic!=_SPI_PLAN_MAGIC||
603+
plan->saved||plan->oneshot)
602604
returnSPI_ERROR_ARGUMENT;
603605

604606
/*
@@ -1082,7 +1084,7 @@ SPI_cursor_open_with_args(const char *name,
10821084
paramLI=_SPI_convert_params(nargs,argtypes,
10831085
Values,Nulls);
10841086

1085-
_SPI_prepare_plan(src,&plan,paramLI);
1087+
_SPI_prepare_plan(src,&plan);
10861088

10871089
/* We needn't copy the plan; SPI_cursor_open_internal will do so */
10881090

@@ -1644,10 +1646,6 @@ spi_printtup(TupleTableSlot *slot, DestReceiver *self)
16441646
*
16451647
* At entry, plan->argtypes and plan->nargs (or alternatively plan->parserSetup
16461648
* and plan->parserSetupArg) must be valid, as must plan->cursor_options.
1647-
* If boundParams isn't NULL then it represents parameter values that are made
1648-
* available to the planner (as either estimates or hard values depending on
1649-
* their PARAM_FLAG_CONST marking). The boundParams had better match the
1650-
* param type information embedded in the plan!
16511649
*
16521650
* Results are stored into *plan (specifically, plan->plancache_list).
16531651
* Note that the result data is all in CurrentMemoryContext or child contexts
@@ -1656,13 +1654,12 @@ spi_printtup(TupleTableSlot *slot, DestReceiver *self)
16561654
* parsing is also left in CurrentMemoryContext.
16571655
*/
16581656
staticvoid
1659-
_SPI_prepare_plan(constchar*src,SPIPlanPtrplan,ParamListInfoboundParams)
1657+
_SPI_prepare_plan(constchar*src,SPIPlanPtrplan)
16601658
{
16611659
List*raw_parsetree_list;
16621660
List*plancache_list;
16631661
ListCell*list_item;
16641662
ErrorContextCallbackspierrcontext;
1665-
intcursor_options=plan->cursor_options;
16661663

16671664
/*
16681665
* Setup error traceback support for ereport()
@@ -1725,13 +1722,80 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan, ParamListInfo boundParams)
17251722
plan->nargs,
17261723
plan->parserSetup,
17271724
plan->parserSetupArg,
1728-
cursor_options,
1725+
plan->cursor_options,
17291726
false);/* not fixed result */
17301727

17311728
plancache_list=lappend(plancache_list,plansource);
17321729
}
17331730

17341731
plan->plancache_list=plancache_list;
1732+
plan->oneshot= false;
1733+
1734+
/*
1735+
* Pop the error context stack
1736+
*/
1737+
error_context_stack=spierrcontext.previous;
1738+
}
1739+
1740+
/*
1741+
* Parse, but don't analyze, a querystring.
1742+
*
1743+
* This is a stripped-down version of _SPI_prepare_plan that only does the
1744+
* initial raw parsing. It creates "one shot" CachedPlanSources
1745+
* that still require parse analysis before execution is possible.
1746+
*
1747+
* The advantage of using the "one shot" form of CachedPlanSource is that
1748+
* we eliminate data copying and invalidation overhead. Postponing parse
1749+
* analysis also prevents issues if some of the raw parsetrees are DDL
1750+
* commands that affect validity of later parsetrees. Both of these
1751+
* attributes are good things for SPI_execute() and similar cases.
1752+
*
1753+
* Results are stored into *plan (specifically, plan->plancache_list).
1754+
* Note that the result data is all in CurrentMemoryContext or child contexts
1755+
* thereof; in practice this means it is in the SPI executor context, and
1756+
* what we are creating is a "temporary" SPIPlan. Cruft generated during
1757+
* parsing is also left in CurrentMemoryContext.
1758+
*/
1759+
staticvoid
1760+
_SPI_prepare_oneshot_plan(constchar*src,SPIPlanPtrplan)
1761+
{
1762+
List*raw_parsetree_list;
1763+
List*plancache_list;
1764+
ListCell*list_item;
1765+
ErrorContextCallbackspierrcontext;
1766+
1767+
/*
1768+
* Setup error traceback support for ereport()
1769+
*/
1770+
spierrcontext.callback=_SPI_error_callback;
1771+
spierrcontext.arg= (void*)src;
1772+
spierrcontext.previous=error_context_stack;
1773+
error_context_stack=&spierrcontext;
1774+
1775+
/*
1776+
* Parse the request string into a list of raw parse trees.
1777+
*/
1778+
raw_parsetree_list=pg_parse_query(src);
1779+
1780+
/*
1781+
* Construct plancache entries, but don't do parse analysis yet.
1782+
*/
1783+
plancache_list=NIL;
1784+
1785+
foreach(list_item,raw_parsetree_list)
1786+
{
1787+
Node*parsetree= (Node*)lfirst(list_item);
1788+
CachedPlanSource*plansource;
1789+
1790+
plansource=CreateOneShotCachedPlan(parsetree,
1791+
src,
1792+
CreateCommandTag(parsetree));
1793+
1794+
plancache_list=lappend(plancache_list,plansource);
1795+
}
1796+
1797+
plan->plancache_list=plancache_list;
1798+
plan->oneshot= true;
17351799

17361800
/*
17371801
* Pop the error context stack
@@ -1769,7 +1833,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
17691833
* Setup error traceback support for ereport()
17701834
*/
17711835
spierrcontext.callback=_SPI_error_callback;
1772-
spierrcontext.arg=NULL;
1836+
spierrcontext.arg=NULL;/* we'll fill this below */
17731837
spierrcontext.previous=error_context_stack;
17741838
error_context_stack=&spierrcontext;
17751839

@@ -1815,6 +1879,47 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
18151879

18161880
spierrcontext.arg= (void*)plansource->query_string;
18171881

1882+
/*
1883+
* If this is a one-shot plan, we still need to do parse analysis.
1884+
*/
1885+
if (plan->oneshot)
1886+
{
1887+
Node*parsetree=plansource->raw_parse_tree;
1888+
constchar*src=plansource->query_string;
1889+
List*stmt_list;
1890+
1891+
/*
1892+
* Parameter datatypes are driven by parserSetup hook if provided,
1893+
* otherwise we use the fixed parameter list.
1894+
*/
1895+
if (plan->parserSetup!=NULL)
1896+
{
1897+
Assert(plan->nargs==0);
1898+
stmt_list=pg_analyze_and_rewrite_params(parsetree,
1899+
src,
1900+
plan->parserSetup,
1901+
plan->parserSetupArg);
1902+
}
1903+
else
1904+
{
1905+
stmt_list=pg_analyze_and_rewrite(parsetree,
1906+
src,
1907+
plan->argtypes,
1908+
plan->nargs);
1909+
}
1910+
1911+
/* Finish filling in the CachedPlanSource */
1912+
CompleteCachedPlan(plansource,
1913+
stmt_list,
1914+
NULL,
1915+
plan->argtypes,
1916+
plan->nargs,
1917+
plan->parserSetup,
1918+
plan->parserSetupArg,
1919+
plan->cursor_options,
1920+
false);/* not fixed result */
1921+
}
1922+
18181923
/*
18191924
* Replan if needed, and increment plan refcount. If it's a saved
18201925
* plan, the refcount must be backed by the CurrentResourceOwner.
@@ -2306,6 +2411,8 @@ _SPI_make_plan_non_temp(SPIPlanPtr plan)
23062411
/* Assert the input is a temporary SPIPlan */
23072412
Assert(plan->magic==_SPI_PLAN_MAGIC);
23082413
Assert(plan->plancxt==NULL);
2414+
/* One-shot plans can't be saved */
2415+
Assert(!plan->oneshot);
23092416

23102417
/*
23112418
* Create a memory context for the plan, underneath the procedure context.
@@ -2323,6 +2430,7 @@ _SPI_make_plan_non_temp(SPIPlanPtr plan)
23232430
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
23242431
newplan->magic=_SPI_PLAN_MAGIC;
23252432
newplan->saved= false;
2433+
newplan->oneshot= false;
23262434
newplan->plancache_list=NIL;
23272435
newplan->plancxt=plancxt;
23282436
newplan->cursor_options=plan->cursor_options;
@@ -2372,6 +2480,9 @@ _SPI_save_plan(SPIPlanPtr plan)
23722480
MemoryContextoldcxt;
23732481
ListCell*lc;
23742482

2483+
/* One-shot plans can't be saved */
2484+
Assert(!plan->oneshot);
2485+
23752486
/*
23762487
* Create a memory context for the plan. We don't expect the plan to be
23772488
* very large, so use smaller-than-default alloc parameters. It's a
@@ -2388,6 +2499,7 @@ _SPI_save_plan(SPIPlanPtr plan)
23882499
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
23892500
newplan->magic=_SPI_PLAN_MAGIC;
23902501
newplan->saved= false;
2502+
newplan->oneshot= false;
23912503
newplan->plancache_list=NIL;
23922504
newplan->plancxt=plancxt;
23932505
newplan->cursor_options=plan->cursor_options;

0 commit comments

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