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

Commit94afbd5

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 parent78a5e73 commit94afbd5

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
@@ -395,7 +393,8 @@ typedef struct
395393
TupleDesc tupdesc; /* row descriptor */
396394
HeapTuple *vals; /* rows */
397395
} SPITupleTable;
398-
</programlisting><structfield>vals</> is an array of pointers to rows. (The number
396+
</programlisting>
397+
<structfield>vals</> is an array of pointers to rows. (The number
399398
of valid entries is given by <varname>SPI_processed</varname>.)
400399
<structfield>tupdesc</> is a row descriptor which you can pass to
401400
SPI functions dealing with rows. <structfield>tuptabcxt</>,
@@ -435,7 +434,8 @@ typedef struct
435434
<term><literal>long <parameter>count</parameter></literal></term>
436435
<listitem>
437436
<para>
438-
maximum number of rows to process or return
437+
maximum number of rows to process or return,
438+
or <literal>0</> for no limit
439439
</para>
440440
</listitem>
441441
</varlistentry>
@@ -674,7 +674,8 @@ int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count<
674674
<term><literal>long <parameter>count</parameter></literal></term>
675675
<listitem>
676676
<para>
677-
maximum number of rows to process or return
677+
maximum number of rows to process or return,
678+
or <literal>0</> for no limit
678679
</para>
679680
</listitem>
680681
</varlistentry>
@@ -812,7 +813,8 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>,
812813
<term><literal>long <parameter>count</parameter></literal></term>
813814
<listitem>
814815
<para>
815-
maximum number of rows to process or return
816+
maximum number of rows to process or return,
817+
or <literal>0</> for no limit
816818
</para>
817819
</listitem>
818820
</varlistentry>
@@ -1455,7 +1457,8 @@ int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>
14551457
<term><literal>long <parameter>count</parameter></literal></term>
14561458
<listitem>
14571459
<para>
1458-
maximum number of rows to process or return
1460+
maximum number of rows to process or return,
1461+
or <literal>0</> for no limit
14591462
</para>
14601463
</listitem>
14611464
</varlistentry>
@@ -1572,7 +1575,8 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
15721575
<term><literal>long <parameter>count</parameter></literal></term>
15731576
<listitem>
15741577
<para>
1575-
maximum number of rows to process or return
1578+
maximum number of rows to process or return,
1579+
or <literal>0</> for no limit
15761580
</para>
15771581
</listitem>
15781582
</varlistentry>
@@ -1672,7 +1676,8 @@ int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values<
16721676
<term><literal>long <parameter>count</parameter></literal></term>
16731677
<listitem>
16741678
<para>
1675-
maximum number of rows to process or return
1679+
maximum number of rows to process or return,
1680+
or <literal>0</> for no limit
16761681
</para>
16771682
</listitem>
16781683
</varlistentry>

‎src/backend/executor/spi.c

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

52-
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan,
53-
ParamListInfoboundParams);
52+
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan);
53+
54+
staticvoid_SPI_prepare_oneshot_plan(constchar*src,SPIPlanPtrplan);
5455

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

358-
_SPI_prepare_plan(src,&plan,NULL);
359+
_SPI_prepare_oneshot_plan(src,&plan);
359360

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

509-
_SPI_prepare_plan(src,&plan,paramLI);
510+
_SPI_prepare_oneshot_plan(src,&plan);
510511

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

550-
_SPI_prepare_plan(src,&plan,NULL);
551+
_SPI_prepare_plan(src,&plan);
551552

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

587-
_SPI_prepare_plan(src,&plan,NULL);
588+
_SPI_prepare_plan(src,&plan);
588589

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

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

605607
/*
@@ -1083,7 +1085,7 @@ SPI_cursor_open_with_args(const char *name,
10831085
paramLI=_SPI_convert_params(nargs,argtypes,
10841086
Values,Nulls);
10851087

1086-
_SPI_prepare_plan(src,&plan,paramLI);
1088+
_SPI_prepare_plan(src,&plan);
10871089

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

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

16681665
/*
16691666
* Setup error traceback support for ereport()
@@ -1726,13 +1723,80 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan, ParamListInfo boundParams)
17261723
plan->nargs,
17271724
plan->parserSetup,
17281725
plan->parserSetupArg,
1729-
cursor_options,
1726+
plan->cursor_options,
17301727
false);/* not fixed result */
17311728

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

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

17371801
/*
17381802
* Pop the error context stack
@@ -1770,7 +1834,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
17701834
* Setup error traceback support for ereport()
17711835
*/
17721836
spierrcontext.callback=_SPI_error_callback;
1773-
spierrcontext.arg=NULL;
1837+
spierrcontext.arg=NULL;/* we'll fill this below */
17741838
spierrcontext.previous=error_context_stack;
17751839
error_context_stack=&spierrcontext;
17761840

@@ -1816,6 +1880,47 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
18161880

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

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

23172424
/*
23182425
* Create a memory context for the plan, underneath the procedure context.
@@ -2330,6 +2437,7 @@ _SPI_make_plan_non_temp(SPIPlanPtr plan)
23302437
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
23312438
newplan->magic=_SPI_PLAN_MAGIC;
23322439
newplan->saved= false;
2440+
newplan->oneshot= false;
23332441
newplan->plancache_list=NIL;
23342442
newplan->plancxt=plancxt;
23352443
newplan->cursor_options=plan->cursor_options;
@@ -2379,6 +2487,9 @@ _SPI_save_plan(SPIPlanPtr plan)
23792487
MemoryContextoldcxt;
23802488
ListCell*lc;
23812489

2490+
/* One-shot plans can't be saved */
2491+
Assert(!plan->oneshot);
2492+
23822493
/*
23832494
* Create a memory context for the plan. We don't expect the plan to be
23842495
* very large, so use smaller-than-default alloc parameters. It's a
@@ -2395,6 +2506,7 @@ _SPI_save_plan(SPIPlanPtr plan)
23952506
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
23962507
newplan->magic=_SPI_PLAN_MAGIC;
23972508
newplan->saved= false;
2509+
newplan->oneshot= false;
23982510
newplan->plancache_list=NIL;
23992511
newplan->plancxt=plancxt;
24002512
newplan->cursor_options=plan->cursor_options;

0 commit comments

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