From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Michael Paquier <michael@paquier.xyz>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Mon, 2 Dec 2019 13:07:51 +0300
Message-ID: <953a617e-bd6b-3099-2d03-7cf152d68efa@postgrespro.ru> (raw)
In-Reply-To: <20191201032603.GE2355@paquier.xyz>
References: <20190925200611.GA22668@alvherre.pgsql>
<2b7c0b2d-ef0e-d2c9-4c7d-6fa485a60f63@postgrespro.ru>
<20191201032603.GE2355@paquier.xyz>
On 01.12.2019 6:26, Michael Paquier wrote:
> On Thu, Sep 26, 2019 at 10:23:38AM +0300, Konstantin Knizhnik wrote:
>> Sorry,
>> New version of the patch with corrected expected output for rules test is
>> attached.
> It looks like the documentation is failing to build. Could you fix
> that? There may be other issues as well. I have moved the patch to
> next CF, waiting on author.
> --
> Michael
Sorry, issues with documentation are fixed.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:autoprepare-extended-4.patch (text/x-patch, 26.0K) downloaddiff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgmlnew file mode 100644index 0000000..c4d96e6--- /dev/null+++ b/doc/src/sgml/autoprepare.sgml@@ -0,0 +1,62 @@+<!-- doc/src/sgml/autoprepare.sgml -->++ <chapter id="autoprepare">+ <title>Autoprepared statements</title>++ <indexterm zone="autoprepare">+ <primary>autoprepared statements</primary>+ </indexterm>++ <para>+ <productname>PostgreSQL</productname> makes it possible <firstterm>prepare</firstterm>+ frequently used statements to eliminate cost of their compilation+ and optimization on each execution of the query. On simple queries+ (like ones in <filename>pgbench -S</filename>) using prepared statements+ increase performance more than two times.+ </para>++ <para>+ Unfortunately not all database applications are using prepared statements+ and, moreover, it is not always possible. For example, in case of using+ <productname>pgbouncer</productname> or any other session pooler,+ there is no session state (transactions of one client may be executed at different+ backends) and so prepared statements can not be used.+ </para>++ <para>+ Autoprepare mode allows to overcome this limitation.+ In this mode Postgres stores plans for all queries pass using extended protocol.+ Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.+ </para>++ <para>+ By default autoprepare mode is switched off. To enable it, assign non-zero+ value to GUC variable <varname>autoprepare_limit</varname> or <varname>autoprepare_memory_limit</varname>.+ This variables specify limit for number of autoprepared statement or memory used by them.+ Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.+ Please notice that event autoprepare is anabled, Postgres makes a decision about using+ generalized plan vs. customized execution plans based on the results+ of comparison of average time of five customized plans with+ time of generalized plan.+ </para>++ <para>+ Too large number of autoprepared statements can cause memory overflow+ (especially if there are many active clients, because prepared statements cache+ is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.+ Postgres is using LRU policy to keep in memory most frequently used queries.+ </para>++ <para>+ Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or+ session variables.+ </para>++ <para>+ It is possible to inspect autoprepared queries in the backend using+ <literal>pg_autoprepared_statements</literal> view. It shows original text of the+ query, types of the extracted parameters (replacing literals),+ query execution counter and used memory.+ </para>++ </chapter>diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 4ec13f3..725e3a9 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -5432,6 +5432,39 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ <varlistentry id="guc-autoprepare-limit" xreflabel="autoprepare_limit">+ <term><varname>autoprepare_limit</varname> (<type>integer</type>)+ <indexterm>+ <primary><varname>autoprepare_limit</varname> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Maximal number of autoprepared queries.+ Zero value disables autoprepare, -1 means unlimited number of autoprepared queries.+ Too large number of prepared queries can cause backend memory overflow and slowdown execution speed+ (because of increased lookup time). Default value is <literal>0</literal>.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-autoprepare-memory-limit" xreflabel="autoprepare_memory_limit">+ <term><varname>autoprepare_memory_limit</varname> (<type>integer</type>)+ <indexterm>+ <primary><varname>autoprepare_memory_limit</varname> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Maximal size of memory used by autoprepared queries.+ Zero value disables autoprepare, -1 means means that there is no memory limit.+ Default value is <literal>0</literal>. Calculating memory used by prepared queries adds some extra overhead,+ so positive value of this parameter may cause some slowdown.+ <varname>autoprepare_limit</varname> is much faster way to limit number of autoprepared statements.+ </para>+ </listitem>+ </varlistentry>+
</variablelist>
</sect2>
</sect1>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgmlindex 3da2365..4bd9f31 100644--- a/doc/src/sgml/filelist.sgml+++ b/doc/src/sgml/filelist.sgml@@ -22,6 +22,7 @@
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
<!ENTITY parallel SYSTEM "parallel.sgml">
+<!ENTITY autoprepare SYSTEM "autoprepare.sgml">
<!ENTITY perform SYSTEM "perform.sgml">
<!ENTITY queries SYSTEM "queries.sgml">
<!ENTITY rangetypes SYSTEM "rangetypes.sgml">
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgmlindex e59cba7..a479f36 100644--- a/doc/src/sgml/postgres.sgml+++ b/doc/src/sgml/postgres.sgml@@ -109,6 +109,7 @@
&mvcc;
&perform;
∥
+ &autoprepare;
</part>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sqlindex f7800f0..000cff3 100644--- a/src/backend/catalog/system_views.sql+++ b/src/backend/catalog/system_views.sql@@ -332,6 +332,9 @@ CREATE VIEW pg_prepared_xacts AS
CREATE VIEW pg_prepared_statements AS
SELECT * FROM pg_prepared_statement() AS P;
+CREATE VIEW pg_autoprepared_statements AS+ SELECT * FROM pg_autoprepared_statement() AS P;+
CREATE VIEW pg_seclabels AS
SELECT
l.objoid, l.classoid, l.objsubid,
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.cindex 47bae95..6aa528b 100644--- a/src/backend/commands/prepare.c+++ b/src/backend/commands/prepare.c@@ -48,7 +48,6 @@ static HTAB *prepared_queries = NULL;
static void InitQueryHashTable(void);
static ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *params,
const char *queryString, EState *estate);
-static Datum build_regtype_array(Oid *param_types, int num_params);
/*
* Implements the 'PREPARE' utility statement.
@@ -788,7 +787,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
* pointing to a one-dimensional Postgres array of regtypes. An empty
* array is returned as a zero-element array, not NULL.
*/
-static Datum+Datum
build_regtype_array(Oid *param_types, int num_params)
{
Datum *tmp_ary;
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.cindex 3b85e48..00d6298 100644--- a/src/backend/tcop/postgres.c+++ b/src/backend/tcop/postgres.c@@ -72,6 +72,8 @@
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+#include "utils/builtins.h"+#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
@@ -165,6 +167,8 @@ static ProcSignalReason RecoveryConflictReason;
static MemoryContext row_description_context = NULL;
static StringInfoData row_description_buf;
++
/* ----------------------------------------------------------------
* decls for routines only used in this file
* ----------------------------------------------------------------
@@ -191,6 +195,163 @@ static void enable_statement_timeout(void);
static void disable_statement_timeout(void);
+/* ----------------+ * autoprepare stuff+ * ----------------+ */+typedef struct+{+ char* query_string;+ Oid* param_types;+ int num_params;+} AutoprepareStatementKey;++typedef struct+{+ AutoprepareStatementKey key;+ CachedPlanSource* plan;+ dlist_node lru; /* double linked list to implement LRU */+ int64 exec_count; /* counter of execution of this query */+ Size used_memory; /* memory used by prepared statement */+} AutoprepareStatement;++static AutoprepareStatement* autoprepare_stmt;+static HTAB* autoprepare_hash;+static dlist_head autoprepare_lru;+static MemoryContext autoprepare_context;++#define AUTOPREPARE_HASH_SIZE 113++/*+ * Plan cache access statistic+ */+static size_t autoprepare_cached_plans;+static size_t autoprepare_used_memory;++static uint32 autoprepare_hash_fn(const void *key, Size keysize)+{+ uint32 hash = 0;+ int i;+ AutoprepareStatementKey* ask = (AutoprepareStatementKey*)key;+ for (i = 0; i < ask->num_params; i++)+ hash ^= ask->param_types[i];+ return string_hash(ask->query_string, strlen(ask->query_string)) ^ hash;+}++static int autoprepare_match_fn(const void *key1, const void *key2, Size keysize)+{+ AutoprepareStatementKey* ask1 = (AutoprepareStatementKey*)key1;+ AutoprepareStatementKey* ask2 = (AutoprepareStatementKey*)key2;+ if (ask1->num_params != ask2->num_params)+ return 1;+ if (memcmp(ask1->param_types, ask2->param_types, ask1->num_params*sizeof(Oid)) != 0)+ return 1;++ return strcmp(ask1->query_string, ask2->query_string);+}++/*+ * This set returning function reads all the autoprepared statements and+ * returns a set of (name, statement, prepare_time, param_types, from_sql).+ */+Datum+pg_autoprepared_statement(PG_FUNCTION_ARGS)+{+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;+ TupleDesc tupdesc;+ Tuplestorestate *tupstore;+ MemoryContext per_query_ctx;+ MemoryContext oldcontext;++ /* check to see if caller supports us returning a tuplestore */+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("set-valued function called in context that cannot accept a set")));+ if (!(rsinfo->allowedModes & SFRM_Materialize))+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("materialize mode required, but it is not " \+ "allowed in this context")));++ /* need to build tuplestore in query context */+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;+ oldcontext = MemoryContextSwitchTo(per_query_ctx);++ /*+ * build tupdesc for result tuples. This must match the definition of the+ * pg_prepared_statements view in system_views.sql+ */+ tupdesc = CreateTemplateTupleDesc(4);+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "statement",+ TEXTOID, -1, 0);+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parameter_types",+ REGTYPEARRAYOID, -1, 0);+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "exec_count",+ INT8OID, -1, 0);+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "used_memory",+ INT8OID, -1, 0);++ /*+ * We put all the tuples into a tuplestore in one scan of the hashtable.+ * This avoids any issue of the hashtable possibly changing between calls.+ */+ tupstore =+ tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random,+ false, work_mem);++ /* generate junk in short-term context */+ MemoryContextSwitchTo(oldcontext);++ /* hash table might be uninitialized */+ if (autoprepare_hash)+ {+ HASH_SEQ_STATUS hash_seq;+ AutoprepareStatement *autoprep_stmt;++ hash_seq_init(&hash_seq, autoprepare_hash);+ while ((autoprep_stmt = hash_seq_search(&hash_seq)) != NULL)+ {+ if (autoprep_stmt->plan != NULL)+ {+ Datum values[4];+ bool nulls[4];+ MemSet(nulls, 0, sizeof(nulls));++ values[0] = CStringGetTextDatum(autoprep_stmt->key.query_string);+ values[1] = build_regtype_array(autoprep_stmt->key.param_types,+ autoprep_stmt->key.num_params);+ values[2] = Int64GetDatum(autoprep_stmt->exec_count);+ values[3] = Int64GetDatum(autoprep_stmt->used_memory);++ tuplestore_putvalues(tupstore, tupdesc, values, nulls);+ }+ }+ }++ /* clean up and return the tuplestore */+ tuplestore_donestoring(tupstore);++ rsinfo->returnMode = SFRM_Materialize;+ rsinfo->setResult = tupstore;+ rsinfo->setDesc = tupdesc;++ return (Datum) 0;+}++void ResetAutoprepareCache(void)+{+ if (autoprepare_hash != NULL)+ {+ hash_destroy(autoprepare_hash);+ MemoryContextReset(autoprepare_context);+ dlist_init(&autoprepare_lru);+ autoprepare_cached_plans = 0;+ autoprepare_used_memory = 0;+ autoprepare_hash = 0;+ }+}+
/* ----------------------------------------------------------------
* routines to obtain user input
* ----------------------------------------------------------------
@@ -1394,14 +1555,105 @@ exec_parse_message(const char *query_string, /* string to execute */
}
else
{
- /* Unnamed prepared statement --- release any prior unnamed stmt */- drop_unnamed_stmt();- /* Create context for parsing */- unnamed_stmt_context =- AllocSetContextCreate(MessageContext,- "unnamed prepared statement",- ALLOCSET_DEFAULT_SIZES);- oldcontext = MemoryContextSwitchTo(unnamed_stmt_context);+ /* check if autoprepare is enabled */+ if (autoprepare_limit || autoprepare_memory_limit)+ {+ bool found;+ AutoprepareStatementKey key;+ key.query_string = (char*)query_string;+ key.param_types = paramTypes;+ key.num_params = numParams;++ /*+ * Construct autoprepare context if not constructed yet.+ */+ if (!autoprepare_context)+ autoprepare_context = AllocSetContextCreate(CacheMemoryContext,+ "autoprepare context",+ ALLOCSET_DEFAULT_SIZES);++ oldcontext = MemoryContextSwitchTo(autoprepare_context);++ if (autoprepare_hash == NULL) /*initialize hash if not done yet */+ {+ static HASHCTL info;+ info.keysize = sizeof(AutoprepareStatementKey);+ info.entrysize = sizeof(AutoprepareStatement);+ info.hash = autoprepare_hash_fn;+ info.match = autoprepare_match_fn;+ autoprepare_hash = hash_create("autoprepare_hash", autoprepare_limit != 0 ? autoprepare_limit : AUTOPREPARE_HASH_SIZE,+ &info, HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);+ dlist_init(&autoprepare_lru);+ }+ autoprepare_stmt = (AutoprepareStatement*)hash_search(autoprepare_hash, &key, HASH_ENTER, &found);+ if (found)+ {+ autoprepare_stmt->exec_count += 1;+ dlist_delete(&autoprepare_stmt->lru); /* accessed entry will be moved to the head of LRU list */+ if (autoprepare_stmt->plan != NULL && !autoprepare_stmt->plan->is_valid)+ {+ /* Drop invalidated plan: it will be reconstructed later */+ if (autoprepare_memory_limit)+ autoprepare_used_memory -= autoprepare_stmt->used_memory;+ DropCachedPlan(autoprepare_stmt->plan);+ autoprepare_stmt->plan = NULL;+ autoprepare_stmt->used_memory = 0;+ }+ else+ {+ dlist_insert_after(&autoprepare_lru.head, &autoprepare_stmt->lru); /* prepend entry to the head of LRU list */+ goto ParseCompleted;+ }+ }+ else+ {+ /* Initialize autoprepare hash entry */+ autoprepare_cached_plans += 1;+ autoprepare_stmt->plan = NULL;+ autoprepare_stmt->used_memory = 0;+ autoprepare_stmt->exec_count = 1;++ /* Copy query text and parameter type info to CacheMemoryContext */+ autoprepare_stmt->key.query_string = pstrdup(query_string);+ autoprepare_stmt->key.param_types = (Oid*)palloc(sizeof(Oid)*numParams);+ memcpy(autoprepare_stmt->key.param_types, paramTypes, sizeof(Oid)*numParams);++ /* LRU eviction policy */+ while ((autoprepare_limit > 0 && autoprepare_cached_plans > autoprepare_limit)+ || (autoprepare_memory_limit > 0 && autoprepare_used_memory > (size_t)autoprepare_memory_limit*1024))+ {+ /* Drop least recently accessed query */+ AutoprepareStatement* victim = dlist_container(AutoprepareStatement, lru, autoprepare_lru.head.prev);+ dlist_delete(&victim->lru);+ if (victim->plan)+ {+ if (autoprepare_memory_limit)+ autoprepare_used_memory -= victim->used_memory;+ DropCachedPlan(victim->plan);+ }+ hash_search(autoprepare_hash, victim, HASH_REMOVE, NULL);+ pfree(victim->key.query_string);+ pfree(victim->key.param_types);+ autoprepare_cached_plans -= 1;+ }+ }+ dlist_insert_after(&autoprepare_lru.head, &autoprepare_stmt->lru); /* prepend entry to the head of LRU list */++ /* Autoprepare statements also parsed in MessageContext */+ MemoryContextSwitchTo(MessageContext);+ }+ else+ {+ /* Unnamed prepared statement --- release any prior unnamed stmt */+ drop_unnamed_stmt();+ /* Create context for parsing */+ unnamed_stmt_context =+ AllocSetContextCreate(MessageContext,+ "unnamed prepared statement",+ ALLOCSET_DEFAULT_SIZES);+ oldcontext = MemoryContextSwitchTo(unnamed_stmt_context);+ autoprepare_stmt = NULL;+ }
}
/*
@@ -1540,13 +1792,21 @@ exec_parse_message(const char *query_string, /* string to execute */
}
else
{
- /*- * We just save the CachedPlanSource into unnamed_stmt_psrc.- */
SaveCachedPlan(psrc);
- unnamed_stmt_psrc = psrc;+ if (autoprepare_stmt)+ {+ autoprepare_stmt->plan = psrc;+ autoprepare_stmt->used_memory = CachedPlanMemoryUsage(autoprepare_stmt->plan);+ autoprepare_used_memory += autoprepare_stmt->used_memory;+ }+ else+ /*+ * We just save the CachedPlanSource into unnamed_stmt_psrc.+ */+ unnamed_stmt_psrc = psrc;
}
+ ParseCompleted:
MemoryContextSwitchTo(oldcontext);
/*
@@ -1634,7 +1894,7 @@ exec_bind_message(StringInfo input_message)
else
{
/* special-case the unnamed statement */
- psrc = unnamed_stmt_psrc;+ psrc = autoprepare_stmt ? autoprepare_stmt->plan : unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_PSTATEMENT),
@@ -2466,7 +2726,7 @@ exec_describe_statement_message(const char *stmt_name)
else
{
/* special-case the unnamed statement */
- psrc = unnamed_stmt_psrc;+ psrc = autoprepare_stmt ? autoprepare_stmt->plan : unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_PSTATEMENT),
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 3a03ca7..75db5b1 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -674,10 +674,12 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_AlterSystemStmt:
PreventInTransactionBlock(isTopLevel, "ALTER SYSTEM");
AlterSystemSetConfigFile((AlterSystemStmt *) parsetree);
+ ResetAutoprepareCache();
break;
case T_VariableSetStmt:
ExecSetVariableStmt((VariableSetStmt *) parsetree, isTopLevel);
+ ResetAutoprepareCache();
break;
case T_VariableShowStmt:
@@ -961,6 +963,8 @@ ProcessUtilitySlow(ParseState *pstate,
/* All event trigger calls are done only when isCompleteQuery is true */
needCleanup = isCompleteQuery && EventTriggerBeginCompleteQuery();
+ ResetAutoprepareCache();+
/* PG_TRY block is to ensure we call EventTriggerEndCompleteQuery */
PG_TRY();
{
diff --git a/src/backend/utils/cache/inval.c b/src/backend/utils/cache/inval.cindex f09e3a9..6168eee 100644--- a/src/backend/utils/cache/inval.c+++ b/src/backend/utils/cache/inval.c@@ -113,6 +113,7 @@
#include "utils/relmapper.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "tcop/pquery.h"
/*
@@ -646,6 +647,7 @@ InvalidateSystemCaches(void)
InvalidateCatalogSnapshot();
ResetCatalogCaches();
+ ResetAutoprepareCache();
RelationCacheInvalidate(); /* gets smgr and relmap too */
for (i = 0; i < syscache_callback_count; i++)
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.cindex abc3062..93225a5 100644--- a/src/backend/utils/cache/plancache.c+++ b/src/backend/utils/cache/plancache.c@@ -2022,3 +2022,39 @@ ResetPlanCache(void)
cexpr->is_valid = false;
}
}
++/*+ * CachedPlanMemUsage: Return memory used by the CachedPlanSource+ *+ * Returns the malloced memory used by the two MemoryContexts in+ * CachedPlanSource and (if available) the MemoryContext in the generic plan.+ * Does not care for the free memory in those MemoryContexts because it is very+ * unlikely that it is reused for anythink else anymore and can be considered+ * dead memory anyway. Also the size of the CachedPlanSource struct is added.+ *+ * This function is used only for the pg_prepared_statements view to allow+ * client applications to monitor memory used by prepared statements and to+ * selects candidates for eviction in memory contraint environments with+ * automatic preparation of often called queries.+ */+Size+CachedPlanMemoryUsage(CachedPlanSource *plan)+{+ MemoryContextCounters counters;+ MemoryContext context;++ counters.totalspace = 0;++ context = plan->context;+ context->methods->stats(context,NULL,NULL,&counters);++ context = plan->query_context;+ context->methods->stats(context,NULL,NULL,&counters);++ if( plan->gplan ) {+ context = plan->gplan->context;+ context->methods->stats(context,NULL,NULL,&counters);+ }++ return counters.totalspace;+}diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.cindex 5fccc96..1db741e 100644--- a/src/backend/utils/misc/guc.c+++ b/src/backend/utils/misc/guc.c@@ -540,6 +540,9 @@ int tcp_keepalives_interval;
int tcp_keepalives_count;
int tcp_user_timeout;
+int autoprepare_limit;+int autoprepare_memory_limit;+
/*
* SSL renegotiation was been removed in PostgreSQL 9.5, but we tolerate it
* being set to zero (meaning never renegotiate) for backward compatibility.
@@ -2286,6 +2289,31 @@ static struct config_int ConfigureNamesInt[] =
check_max_stack_depth, assign_max_stack_depth, NULL
},
+ /*+ * Limits for implicit preparing of frequently executed queries+ */+ {+ {"autoprepare_limit", PGC_USERSET, QUERY_TUNING_OTHER,+ gettext_noop("Maximal number of autoprepared queries."),+ gettext_noop("0 disable autoprepare, -1 means unlimited number of autoprepared queries. Too large number of prepared queries can cause backend memory overflow and slowdown execution speed (because of increased lookup time)")+ },+ &autoprepare_limit,+ 0, -1, INT_MAX,+ NULL, NULL, NULL+ },+ {+ {"autoprepare_memory_limit", PGC_USERSET, QUERY_TUNING_OTHER,+ gettext_noop("Maximal size of memory used by autoprepared queries."),+ gettext_noop("0 disables autoprepare, -1 means that there is no memory limit. "+ "Calculating memory used by prepared queries adds some extra overhead, "+ "so non-zero value of this parameter may cause some slowdown. autoprepare_limit is much faster way to limit number of autoprepared statements"),+ GUC_UNIT_KB+ },+ &autoprepare_memory_limit,+ 0, -1, INT_MAX,+ NULL, NULL, NULL+ },+
{
{"temp_file_limit", PGC_SUSET, RESOURCES_DISK,
gettext_noop("Limits the total size of all temporary files used by each process."),
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.datindex ac8f64b..b759b15 100644--- a/src/include/catalog/pg_proc.dat+++ b/src/include/catalog/pg_proc.dat@@ -7617,6 +7617,13 @@
proargmodes => '{o,o,o,o,o}',
proargnames => '{name,statement,prepare_time,parameter_types,from_sql}',
prosrc => 'pg_prepared_statement' },
+{ oid => '4035', descr => 'get the autoprepared statements for this session',+ proname => 'pg_autoprepared_statement', prorows => '1000', proretset => 't',+ provolatile => 's', proparallel => 'r', prorettype => 'record',+ proargtypes => '', proallargtypes => '{text,_regtype,int8,int8}',+ proargmodes => '{o,o,o,o}',+ proargnames => '{statement,parameter_types,exec_count,used_memory}',+ prosrc => 'pg_autoprepared_statement' },
{ oid => '2511', descr => 'get the open cursors for this session',
proname => 'pg_cursor', prorows => '1000', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/commands/prepare.h b/src/include/commands/prepare.hindex 2ce8324..11322ce 100644--- a/src/include/commands/prepare.h+++ b/src/include/commands/prepare.h@@ -56,5 +56,6 @@ extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
extern List *FetchPreparedStatementTargetList(PreparedStatement *stmt);
extern void DropAllPreparedStatements(void);
+extern Datum build_regtype_array(Oid *param_types, int num_params);
#endif /* PREPARE_H */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.hindex 9e24cfa..db3e448 100644--- a/src/include/tcop/pquery.h+++ b/src/include/tcop/pquery.h@@ -42,4 +42,6 @@ extern uint64 PortalRunFetch(Portal portal,
long count,
DestReceiver *dest);
+extern void ResetAutoprepareCache(void);+
#endif /* PQUERY_H */
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.hindex 50098e6..f892f6e 100644--- a/src/include/utils/guc.h+++ b/src/include/utils/guc.h@@ -259,6 +259,9 @@ extern double log_xact_sample_rate;
extern char *backtrace_functions;
extern char *backtrace_symbol_list;
+extern int autoprepare_limit;+extern int autoprepare_memory_limit;+
extern int temp_file_limit;
extern int num_temp_buffers;
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.hindex de2555e..dc73e59 100644--- a/src/include/utils/plancache.h+++ b/src/include/utils/plancache.h@@ -222,4 +222,6 @@ extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner);
extern CachedExpression *GetCachedExpression(Node *expr);
extern void FreeCachedExpression(CachedExpression *cexpr);
+extern Size CachedPlanMemoryUsage(CachedPlanSource *plansource);+
#endif /* PLANCACHE_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.outindex c9cc569..87da01b 100644--- a/src/test/regress/expected/rules.out+++ b/src/test/regress/expected/rules.out@@ -1307,6 +1307,11 @@ mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot
FROM mvtest_tv;
mvtest_tvvmv| SELECT mvtest_tvvm.grandtot
FROM mvtest_tvvm;
+pg_autoprepared_statements| SELECT p.statement,+ p.parameter_types,+ p.exec_count,+ p.used_memory+ FROM pg_autoprepared_statement() p(statement, parameter_types, exec_count, used_memory);
pg_available_extension_versions| SELECT e.name,
e.version,
(x.extname IS NOT NULL) AS installed,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru, michael@paquier.xyz, alvherre@2ndquadrant.com, hlinnaka@iki.fi, thomas.munro@gmail.com
Subject: Re: [HACKERS] Cached plans and statement generalization
In-Reply-To: <953a617e-bd6b-3099-2d03-7cf152d68efa@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox