Skip to content

Commit a17da19

Browse files
tglsfdctglsfdc
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 by plancache.c is of no value in this code path. And performance measurements show that the extra data copying and invalidation checking done by plancache.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 plan caching that it seems impractical to bypass plancache.c altogether. Instead, let's invent a variant version of cached plans that preserves 99% of the API but doesn't offer any of the actual functionality, nor the overhead. This puts SPI_execute() performance back on par, or maybe even slightly better, than it was before. This change should resolve recent complaints of performance degradation from Dong Ye, Pavel Stehule, and others. By avoiding data copying, this change also reduces the amount of memory needed to execute many-statement SPI_execute() strings, as for instance in a 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 complete execution of earlier statements before running parse analysis and planning on following ones. This eliminates a long-standing POLA violation, in that DDL that affects the behavior of a later statement will now behave as expected. 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 offsets of existing fields.) Heikki Linnakangas and Tom Lane
1 parent 48e0b8a commit a17da19

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
static Portal SPI_cursor_open_internal(const char *name, SPIPlanPtr plan,
4949
ParamListInfo paramLI, bool read_only);
5050

51-
static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan,
52-
ParamListInfo boundParams);
51+
static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan);
52+
53+
static void _SPI_prepare_oneshot_plan(const char *src, SPIPlanPtr plan);
5354

5455
static int _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
5556
Snapshot snapshot, Snapshot crosscheck_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
return SPI_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
static void
1659-
_SPI_prepare_plan(const char *src, SPIPlanPtr plan, ParamListInfo boundParams)
1657+
_SPI_prepare_plan(const char *src, SPIPlanPtr plan)
16601658
{
16611659
List *raw_parsetree_list;
16621660
List *plancache_list;
16631661
ListCell *list_item;
16641662
ErrorContextCallback spierrcontext;
1665-
int cursor_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+
static void
1760+
_SPI_prepare_oneshot_plan(const char *src, SPIPlanPtr plan)
1761+
{
1762+
List *raw_parsetree_list;
1763+
List *plancache_list;
1764+
ListCell *list_item;
1765+
ErrorContextCallback spierrcontext;
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+
const char *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
MemoryContext oldcxt;
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)