Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tdimX-0097tx-ES for pgsql-hackers@arkaria.postgresql.org; Fri, 31 Jan 2025 04:37:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tdimU-00FOqH-GF for pgsql-hackers@arkaria.postgresql.org; Fri, 31 Jan 2025 04:37:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tdimS-00FOq9-NZ for pgsql-hackers@lists.postgresql.org; Fri, 31 Jan 2025 04:37:26 +0000 Received: from fout-b7-smtp.messagingengine.com ([202.12.124.150]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tdimM-002P8I-0U for pgsql-hackers@lists.postgresql.org; Fri, 31 Jan 2025 04:37:23 +0000 Received: from phl-compute-09.internal (phl-compute-09.phl.internal [10.202.2.49]) by mailfout.stl.internal (Postfix) with ESMTP id D35DE114008C; Thu, 30 Jan 2025 23:37:16 -0500 (EST) Received: from phl-mailfrontend-01 ([10.202.2.162]) by phl-compute-09.internal (MEProxy); Thu, 30 Jan 2025 23:37:16 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1738298236; x=1738384636; bh=yGpXlbgD3G IbFOpZiHre3lWNpGSDeq9ZLhAqh5HU+gY=; b=SqTcGz4UGl3Axso4sZMjXYWal4 dJbVeoHLaBdZbyQdDG4xJMRHOvmTchaGZrBJ6RiSf2lHBrW4fVJtUX2uIzXlKX5P gopxs3sItu11VU2rDY42izF6uB3F10ELWXAvBpOG2bn+6spiLNn8cI0bT8O7YExP LoPHVndohg6T6tLf6eYJgvXHxX41dhNuGzgoZwnvBR60yOaE05Mg2W20uUc4f4jN LERKX1QKB4RLpwvj/mYyK+F+0t1dQFKhPJ7HrrbGUVKgW8AOj7wJwzmlJofYIkpc vZ9Befu0rLF/IHhuYtBNNVjqNxQscaFw7iNimTWDJdqbSo9qhDOc/BUn/Wew== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1738298236; x=1738384636; bh=yGpXlbgD3GIbFOpZiHre3lWNpGSDeq9ZLhA qh5HU+gY=; b=PpBAx6ZmyVCWSR0LfDQqFbYcVnQv4f3n2RbAtMEfFqNLc8QENOW 3J7LOoG3zfcncC06V6sCHoGq4G0s0K7kJW3NO/Qjs0TMp0tC8nZ8iSBIfE3xiaSU d4kx9ZaZD6CyHY0F6jXN+0+u5AqWHC6lMNJsZ6kBT+5Fko3P7wLiBfPovkjLxDNw mvjqsh5DikfW56lO4amRzYxBtv61dz3qtJVR5A68MAzWbdgpmtLLivstO1m+CHk/ BXgGZJuJ3fIAdiJQkmqm4mM/86zqQbL4wwEgMtkvLgB9fSwurkfgPnhTm2nO+RX9 /Yq/vlpWocrJerFZnNwSvJbEimcUPcZwQ7w== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefvddrtddtgdejjeelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdggtfgfnhhsuhgsshgtrhhisggvpdfu rfetoffkrfgpnffqhgenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnh htshculddquddttddmnegfrhhlucfvnfffucdlfeehmdenucfjughrpeffhffvvefukfhf gggtuggjsehgtderredttddvnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruc eomhhitghhrggvlhesphgrqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepgeff jeevgfevuddvjedtvddtieejheduueelvddufedtgfefjedvkeevkeeivddvnecuffhomh grihhnpehpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtnecu rfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiiipd hnsggprhgtphhtthhopeegpdhmohguvgepshhmthhpohhuthdprhgtphhtthhopehluhhk rghssehfihhtthhlrdgtohhmpdhrtghpthhtohepshgrmhhimhhsvghihhesghhmrghilh drtghomhdprhgtphhtthhopehpghhsqhhlqdhhrggtkhgvrhhssehlihhsthhsrdhpohhs thhgrhgvshhqlhdrohhrghdprhgtphhtthhopehmrghrkhhosehpghgrnhgrlhihiigvrd gtohhm X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 30 Jan 2025 23:37:13 -0500 (EST) Date: Fri, 31 Jan 2025 13:37:03 +0900 From: Michael Paquier To: Lukas Fittl Cc: Sami Imseih , PostgreSQL Hackers , Marko M Subject: Re: [PATCH] Optionally record Plan IDs to track plan changes for a query Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="9jON1huSk3geJfGO" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --9jON1huSk3geJfGO Content-Type: multipart/mixed; boundary="9/rB7VunoObk37cT" Content-Disposition: inline --9/rB7VunoObk37cT Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Jan 27, 2025 at 12:53:36PM +0900, Michael Paquier wrote: > Not sure about this part yet. I have looked at 0002 to begin with > something and it is really useful on its own. Stats kinds calling > this routine don't need to worry about the internals of dropping local > references or doing a seqscan on the shared hash table. However, what > you have sent lacks in flexibility to me, and the duplication with > pgstat_drop_all_entries is annoying. This had better be merged in a > single routine. After thinking more about this one, I still want this toy and hearing nothing I have applied it, with a second commit for the addition in injection_points to avoid multiple bullet points in a single commit. I have noticed post-commit that I have made a mistake in the credits of a632cd354d35 and ce5c620fb625 for your family name. Really sorry about that! This mistake is on me.. > What do you think? Attached is a rebased version of the three remaining patches. While looking at this stuff, I have noticed an extra cleanup that would be good to have, as a separate change: we could reformat a bit the plan header comments so as these do not require a rewrite when adding node_attr to them, like d575051b9af9. Sami's patch set posted at [1] has the same problem, making the proposals harder to parse and review, and the devil is in the details with these pg_node_attr() properties attached to the structures. That would be something to do on top of the proposed patch sets. Would any of you be interested in that? [1]: https://www.postgresql.org/message-id/CAA5RZ0sUPPOpkRZD=Za83op2ngcPC7dp249vcHA-X5YS7p3n8Q@mail.gmail.com -- Michael --9/rB7VunoObk37cT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0001-Allow-using-jumbling-logic-outside-of-query-jumbl.patch" Content-Transfer-Encoding: quoted-printable =46rom a232d30792f1a9a466d032cb12ddbdf328820e9a Mon Sep 17 00:00:00 2001 =46rom: Lukas Fittl Date: Tue, 31 Dec 2024 15:05:39 -0800 Subject: [PATCH v3 1/3] Allow using jumbling logic outside of query jumble unit file This can be useful either for jumbling expressions in other contexts (e.g. to calculate a plan jumble), or to allow extensions to use a modified jumbling logic more easily. This intentionally supports the use case where a separate jumbling logic does not care about recording constants, as the query jumble does. --- src/include/nodes/queryjumble.h | 6 +++ src/backend/nodes/queryjumblefuncs.c | 59 +++++++++++++++++----------- 2 files changed, 41 insertions(+), 24 deletions(-) diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumbl= e.h index 50eb9566587..5afa6f3605f 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -83,4 +83,10 @@ IsQueryIdEnabled(void) return query_id_enabled; } =20 +/* Functions intended for other users of jumbling (e.g. plan jumbling) */ +extern JumbleState *InitializeJumbleState(bool record_clocations); +extern void AppendJumble(JumbleState *jstate, const unsigned char *item, S= ize size); +extern void JumbleNode(JumbleState *jstate, Node *node); +extern uint64 HashJumbleState(JumbleState *jstate); + #endif /* QUERYJUMBLE_H */ diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query= jumblefuncs.c index b103a281936..545d8edcae2 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -51,10 +51,7 @@ int compute_query_id =3D COMPUTE_QUERY_ID_AUTO; */ bool query_id_enabled =3D false; =20 -static void AppendJumble(JumbleState *jstate, - const unsigned char *item, Size size); static void RecordConstLocation(JumbleState *jstate, int location); -static void _jumbleNode(JumbleState *jstate, Node *node); static void _jumbleA_Const(JumbleState *jstate, Node *node); static void _jumbleList(JumbleState *jstate, Node *node); static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node); @@ -109,28 +106,42 @@ CleanQuerytext(const char *query, int *location, int = *len) } =20 JumbleState * -JumbleQuery(Query *query) +InitializeJumbleState(bool record_clocations) { - JumbleState *jstate =3D NULL; - - Assert(IsQueryIdEnabled()); - - jstate =3D (JumbleState *) palloc(sizeof(JumbleState)); + JumbleState *jstate =3D (JumbleState *) palloc0(sizeof(JumbleState)); =20 /* Set up workspace for query jumbling */ jstate->jumble =3D (unsigned char *) palloc(JUMBLE_SIZE); jstate->jumble_len =3D 0; - jstate->clocations_buf_size =3D 32; - jstate->clocations =3D (LocationLen *) - palloc(jstate->clocations_buf_size * sizeof(LocationLen)); - jstate->clocations_count =3D 0; - jstate->highest_extern_param_id =3D 0; + + if (record_clocations) + { + jstate->clocations_buf_size =3D 32; + jstate->clocations =3D (LocationLen *) + palloc(jstate->clocations_buf_size * sizeof(LocationLen)); + } + + return jstate; +} + +uint64 +HashJumbleState(JumbleState *jstate) +{ + return DatumGetUInt64(hash_any_extended(jstate->jumble, + jstate->jumble_len, + 0)); +} + +JumbleState * +JumbleQuery(Query *query) +{ + JumbleState *jstate =3D InitializeJumbleState(true); + + Assert(IsQueryIdEnabled()); =20 /* Compute query ID and mark the Query node with it */ - _jumbleNode(jstate, (Node *) query); - query->queryId =3D DatumGetUInt64(hash_any_extended(jstate->jumble, - jstate->jumble_len, - 0)); + JumbleNode(jstate, (Node *) query); + query->queryId =3D HashJumbleState(jstate); =20 /* * If we are unlucky enough to get a hash of zero, use 1 instead for @@ -164,7 +175,7 @@ EnableQueryId(void) * AppendJumble: Append a value that is substantive in a given query to * the current jumble. */ -static void +void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) { unsigned char *jumble =3D jstate->jumble; @@ -205,7 +216,7 @@ static void RecordConstLocation(JumbleState *jstate, int location) { /* -1 indicates unknown or undefined location */ - if (location >=3D 0) + if (location >=3D 0 && jstate->clocations_buf_size > 0) { /* enlarge array if needed */ if (jstate->clocations_count >=3D jstate->clocations_buf_size) @@ -224,7 +235,7 @@ RecordConstLocation(JumbleState *jstate, int location) } =20 #define JUMBLE_NODE(item) \ - _jumbleNode(jstate, (Node *) expr->item) + JumbleNode(jstate, (Node *) expr->item) #define JUMBLE_LOCATION(location) \ RecordConstLocation(jstate, expr->location) #define JUMBLE_FIELD(item) \ @@ -239,8 +250,8 @@ do { \ =20 #include "queryjumblefuncs.funcs.c" =20 -static void -_jumbleNode(JumbleState *jstate, Node *node) +void +JumbleNode(JumbleState *jstate, Node *node) { Node *expr =3D node; =20 @@ -305,7 +316,7 @@ _jumbleList(JumbleState *jstate, Node *node) { case T_List: foreach(l, expr) - _jumbleNode(jstate, lfirst(l)); + JumbleNode(jstate, lfirst(l)); break; case T_IntList: foreach(l, expr) --=20 2.47.2 --9/rB7VunoObk37cT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0002-Optionally-record-a-plan_id-in-PlannedStmt-to-ide.patch" Content-Transfer-Encoding: quoted-printable =46rom 64e3f1c52cfe393ebe6c9bfa2b355207befa2bea Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Fri, 31 Jan 2025 13:01:59 +0900 Subject: [PATCH v3 2/3] Optionally record a plan_id in PlannedStmt to ident= ify plan shape When enabled via the new compute_plan_id GUC (default off), this utilizes the existing treewalk in setrefs.c after planning to calculate a hash (the "plan_id", or plan identifier) that can be used to identify which plan was chosen. The plan_id generally intends to be the same if a given EXPLAIN (without ANALYZE) output is the same. The plan_id includes both the top-level plan as well as all subplans. Execution statistics are excluded. If enabled, the plan_id is shown for currently running queries in pg_stat_activity, as well as recorded in EXPLAIN and auto_explain output. Other in core users or extensions can use this facility to show or accumulate statistics about the plans used by queries, to help identify plan regressions, or drive plan management decisions. Note that this commit intentionally does not include a facility to map a given plan_id to the EXPLAIN text output - it is a assumed that users can utilize the auto_explain extension to establish this mapping as needed, or extensions can record this via the existing planner hook. --- src/include/catalog/pg_proc.dat | 6 +- src/include/nodes/pathnodes.h | 3 + src/include/nodes/plannodes.h | 399 +++++++++++------- src/include/nodes/primnodes.h | 7 +- src/include/nodes/queryjumble.h | 34 +- src/include/utils/backend_status.h | 5 + src/backend/catalog/system_views.sql | 1 + src/backend/commands/explain.c | 16 + src/backend/executor/execMain.c | 10 +- src/backend/executor/execParallel.c | 1 + src/backend/nodes/gen_node_support.pl | 50 ++- src/backend/nodes/queryjumblefuncs.c | 78 +++- src/backend/optimizer/plan/planner.c | 18 + src/backend/optimizer/plan/setrefs.c | 9 + src/backend/postmaster/launch_backend.c | 3 + src/backend/tcop/postgres.c | 1 + src/backend/utils/activity/backend_status.c | 70 ++- src/backend/utils/adt/pgstatfuncs.c | 7 +- src/backend/utils/misc/guc_tables.c | 28 ++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/test/regress/expected/explain.out | 11 + src/test/regress/expected/rules.out | 9 +- src/test/regress/sql/explain.sql | 4 + doc/src/sgml/config.sgml | 34 ++ doc/src/sgml/monitoring.sgml | 16 + 25 files changed, 635 insertions(+), 186 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 5b8c2ad2a54..5547db7d2f1 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5568,9 +5568,9 @@ proname =3D> 'pg_stat_get_activity', prorows =3D> '100', proisstrict =3D= > 'f', proretset =3D> 't', provolatile =3D> 's', proparallel =3D> 'r', prorettype =3D> 'record', proargtypes =3D> 'int4', - proallargtypes =3D> '{int4,oid,int4,oid,text,text,text,text,text,timesta= mptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,t= ext,text,int4,text,numeric,text,bool,text,bool,bool,int4,int8}', - proargmodes =3D> '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o= ,o,o,o,o,o}', - proargnames =3D> '{pid,datid,pid,usesysid,application_name,state,query,w= ait_event_type,wait_event,xact_start,query_start,backend_start,state_change= ,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_t= ype,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_is= suer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', + proallargtypes =3D> '{int4,oid,int4,oid,text,text,text,text,text,timesta= mptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,t= ext,text,int4,text,numeric,text,bool,text,bool,bool,int4,int8,int8}', + proargmodes =3D> '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o= ,o,o,o,o,o,o}', + proargnames =3D> '{pid,datid,pid,usesysid,application_name,state,query,w= ait_event_type,wait_event,xact_start,query_start,backend_start,state_change= ,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_t= ype,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_is= suer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id,plan_= id}', prosrc =3D> 'pg_stat_get_activity' }, { oid =3D> '6318', descr =3D> 'describe wait events', proname =3D> 'pg_get_wait_events', procost =3D> '10', prorows =3D> '250', diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 52d44f43021..9603e0edda0 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -166,6 +166,9 @@ typedef struct PlannerGlobal =20 /* partition descriptors */ PartitionDirectory partition_directory pg_node_attr(read_write_ignore); + + /* optional jumble state for plan identifier claculation */ + struct JumbleState *plan_jumble_state pg_node_attr(read_write_ignore); } PlannerGlobal; =20 /* macro for fetching the Plan associated with a SubPlan node */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 8143744e89c..f5574baf379 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -53,6 +53,10 @@ typedef struct PlannedStmt =20 uint64 queryId; /* query identifier (copied from Query) */ =20 + uint64 planId; /* plan identifier (calculated if + * compute_plan_id is enabled, can also be set + * by plugins) */ + bool hasReturning; /* is it insert|update|delete|merge RETURNING? */ =20 bool hasModifyingCTE; /* has insert|update|delete|merge in WITH? */ @@ -121,44 +125,55 @@ typedef struct PlannedStmt */ typedef struct Plan { - pg_node_attr(abstract, no_equal, no_query_jumble) + pg_node_attr(abstract, no_equal) =20 NodeTag type; =20 /* * estimated execution costs for plan (see costsize.c for more info) */ - int disabled_nodes; /* count of disabled nodes */ - Cost startup_cost; /* cost expended before fetching any tuples */ - Cost total_cost; /* total cost (assuming all tuples fetched) */ + int disabled_nodes pg_node_attr(query_jumble_ignore); /* count of disab= led + * nodes */ + Cost startup_cost pg_node_attr(query_jumble_ignore); /* cost expended be= fore + * fetching any tuples */ + Cost total_cost pg_node_attr(query_jumble_ignore); /* total cost (assumi= ng + * all tuples fetched) */ =20 /* * planner's estimate of result size of this plan step */ - Cardinality plan_rows; /* number of rows plan is expected to emit */ - int plan_width; /* average row width in bytes */ + Cardinality plan_rows pg_node_attr(query_jumble_ignore); /* number of row= s plan + * is expected to emit */ + int plan_width pg_node_attr(query_jumble_ignore); /* average row width = in + * bytes */ =20 /* * information needed for parallel query */ - bool parallel_aware; /* engage parallel-aware logic? */ - bool parallel_safe; /* OK to use as part of parallel plan? */ + bool parallel_aware pg_node_attr(query_jumble_ignore); /* engage paralle= l-aware + * logic? */ + bool parallel_safe pg_node_attr(query_jumble_ignore); /* OK to use as pa= rt of + * parallel plan? */ =20 /* * information needed for asynchronous execution */ - bool async_capable; /* engage asynchronous-capable logic? */ + bool async_capable pg_node_attr(query_jumble_ignore); /* engage + * asynchronous-capable + * logic? */ =20 /* * Common structural data for all Plan types. */ - int plan_node_id; /* unique across entire final plan tree */ + int plan_node_id pg_node_attr(query_jumble_ignore); /* unique across en= tire + * final plan tree */ List *targetlist; /* target list to be computed at this node */ List *qual; /* implicitly-ANDed qual conditions */ - struct Plan *lefttree; /* input plan tree(s) */ - struct Plan *righttree; - List *initPlan; /* Init Plan nodes (un-correlated expr - * subselects) */ + struct Plan *lefttree pg_node_attr(query_jumble_ignore); /* input plan tr= ee(s) */ + struct Plan *righttree pg_node_attr(query_jumble_ignore); + List *initPlan pg_node_attr(query_jumble_ignore); /* Init Plan nodes + * (un-correlated expr + * subselects) */ =20 /* * Information for management of parameter-change-driven rescanning @@ -171,8 +186,8 @@ typedef struct Plan * params that affect the node (i.e., the setParams of its initplans). * These are _all_ the PARAM_EXEC params that affect this node. */ - Bitmapset *extParam; - Bitmapset *allParam; + Bitmapset *extParam pg_node_attr(query_jumble_ignore); + Bitmapset *allParam pg_node_attr(query_jumble_ignore); } Plan; =20 /* ---------------- @@ -234,31 +249,47 @@ typedef struct ModifyTable { Plan plan; CmdType operation; /* INSERT, UPDATE, DELETE, or MERGE */ - bool canSetTag; /* do we set the command tag/es_processed? */ - Index nominalRelation; /* Parent RT index for use of EXPLAIN */ - Index rootRelation; /* Root RT index, if partitioned/inherited */ + bool canSetTag pg_node_attr(query_jumble_ignore); /* do we set the comma= nd + * tag/es_processed? */ + Index nominalRelation pg_node_attr(query_jumble_ignore); /* Parent RT in= dex for + * use of EXPLAIN */ + Index rootRelation pg_node_attr(query_jumble_rt_index); /* Root RT index= , if + * partitioned/inherited */ bool partColsUpdated; /* some part key in hierarchy updated? */ - List *resultRelations; /* integer list of RT indexes */ - List *updateColnosLists; /* per-target-table update_colnos lists */ - List *withCheckOptionLists; /* per-target-table WCO lists */ - char *returningOldAlias; /* alias for OLD in RETURNING lists */ - char *returningNewAlias; /* alias for NEW in RETURNING lists */ - List *returningLists; /* per-target-table RETURNING tlists */ - List *fdwPrivLists; /* per-target-table FDW private data lists */ - Bitmapset *fdwDirectModifyPlans; /* indices of FDW DM plans */ - List *rowMarks; /* PlanRowMarks (non-locking only) */ - int epqParam; /* ID of Param for EvalPlanQual re-eval */ + List *resultRelations pg_node_attr(query_jumble_rt_index); /* integer = list of RT + * indexes */ + List *updateColnosLists pg_node_attr(query_jumble_ignore); /* per-targ= et-table + * update_colnos lists */ + List *withCheckOptionLists pg_node_attr(query_jumble_ignore); /* per-t= arget-table WCO + * lists */ + char *returningOldAlias pg_node_attr(query_jumble_ignore); /* alias fo= r OLD in + * RETURNING lists */ + char *returningNewAlias pg_node_attr(query_jumble_ignore); /* alias fo= r NEW in + * RETURNING lists */ + List *returningLists pg_node_attr(query_jumble_ignore); /* per-target-= table + * RETURNING tlists */ + List *fdwPrivLists pg_node_attr(query_jumble_ignore); /* per-target-ta= ble FDW + * private data lists */ + Bitmapset *fdwDirectModifyPlans pg_node_attr(query_jumble_ignore); /* in= dices of FDW DM + * plans */ + List *rowMarks pg_node_attr(query_jumble_ignore); /* PlanRowMarks + * (non-locking only) */ + int epqParam pg_node_attr(query_jumble_ignore); /* ID of Param for + * EvalPlanQual re-eval */ OnConflictAction onConflictAction; /* ON CONFLICT action */ List *arbiterIndexes; /* List of ON CONFLICT arbiter index OIDs */ List *onConflictSet; /* INSERT ON CONFLICT DO UPDATE targetlist */ List *onConflictCols; /* target column numbers for onConflictSet */ Node *onConflictWhere; /* WHERE for ON CONFLICT UPDATE */ - Index exclRelRTI; /* RTI of the EXCLUDED pseudo relation */ - List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ - List *mergeActionLists; /* per-target-table lists of actions for - * MERGE */ - List *mergeJoinConditions; /* per-target-table join conditions - * for MERGE */ + Index exclRelRTI pg_node_attr(query_jumble_ignore); /* RTI of the EXCLUD= ED + * pseudo relation */ + List *exclRelTlist pg_node_attr(query_jumble_ignore); /* tlist of the = EXCLUDED + * pseudo relation */ + List *mergeActionLists pg_node_attr(query_jumble_ignore); /* per-targe= t-table + * lists of actions for + * MERGE */ + List *mergeJoinConditions pg_node_attr(query_jumble_ignore); /* per-ta= rget-table join + * conditions for MERGE */ } ModifyTable; =20 struct PartitionPruneInfo; /* forward reference to struct below */ @@ -271,18 +302,20 @@ struct PartitionPruneInfo; /* forward reference to s= truct below */ typedef struct Append { Plan plan; - Bitmapset *apprelids; /* RTIs of appendrel(s) formed by this node */ - List *appendplans; - int nasyncplans; /* # of asynchronous plans */ + /* RTIs of appendrel(s) formed by this node */ + Bitmapset *apprelids pg_node_attr(query_jumble_ignore); + List *appendplans pg_node_attr(query_jumble_ignore); + /* # of asynchronous plans */ + int nasyncplans pg_node_attr(query_jumble_ignore); =20 /* * All 'appendplans' preceding this index are non-partial plans. All * 'appendplans' from this index onwards are partial plans. */ - int first_partial_plan; + int first_partial_plan pg_node_attr(query_jumble_ignore); =20 /* Index to PlannerInfo.partPruneInfos or -1 if no run-time pruning */ - int part_prune_index; + int part_prune_index pg_node_attr(query_jumble_ignore); } Append; =20 /* ---------------- @@ -295,29 +328,29 @@ typedef struct MergeAppend Plan plan; =20 /* RTIs of appendrel(s) formed by this node */ - Bitmapset *apprelids; + Bitmapset *apprelids pg_node_attr(query_jumble_ignore); =20 - List *mergeplans; + List *mergeplans pg_node_attr(query_jumble_ignore); =20 /* these fields are just like the sort-key info in struct Sort: */ =20 /* number of sort-key columns */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *sortColIdx pg_node_attr(array_size(numCols)); + AttrNumber *sortColIdx pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* OIDs of operators to sort them by */ - Oid *sortOperators pg_node_attr(array_size(numCols)); + Oid *sortOperators pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* OIDs of collations */ - Oid *collations pg_node_attr(array_size(numCols)); + Oid *collations pg_node_attr(array_size(numCols), query_jumble_ignore= ); =20 /* NULLS FIRST/LAST directions */ - bool *nullsFirst pg_node_attr(array_size(numCols)); + bool *nullsFirst pg_node_attr(array_size(numCols), query_jumble_ignore= ); =20 /* Index to PlannerInfo.partPruneInfos or -1 if no run-time pruning */ - int part_prune_index; + int part_prune_index pg_node_attr(query_jumble_ignore); } MergeAppend; =20 /* ---------------- @@ -333,22 +366,22 @@ typedef struct RecursiveUnion Plan plan; =20 /* ID of Param representing work table */ - int wtParam; + int wtParam pg_node_attr(query_jumble_ignore); =20 /* Remaining fields are zero/null in UNION ALL case */ =20 /* number of columns to check for duplicate-ness */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *dupColIdx pg_node_attr(array_size(numCols)); + AttrNumber *dupColIdx pg_node_attr(array_size(numCols), query_jumble_igno= re); =20 /* equality operators to compare with */ - Oid *dupOperators pg_node_attr(array_size(numCols)); - Oid *dupCollations pg_node_attr(array_size(numCols)); + Oid *dupOperators pg_node_attr(array_size(numCols), query_jumble_igno= re); + Oid *dupCollations pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* estimated number of groups in input */ - long numGroups; + long numGroups pg_node_attr(query_jumble_ignore); } RecursiveUnion; =20 /* ---------------- @@ -362,7 +395,7 @@ typedef struct RecursiveUnion typedef struct BitmapAnd { Plan plan; - List *bitmapplans; + List *bitmapplans pg_node_attr(query_jumble_ignore); } BitmapAnd; =20 /* ---------------- @@ -376,8 +409,8 @@ typedef struct BitmapAnd typedef struct BitmapOr { Plan plan; - bool isshared; - List *bitmapplans; + bool isshared pg_node_attr(query_jumble_ignore); + List *bitmapplans pg_node_attr(query_jumble_ignore); } BitmapOr; =20 /* @@ -392,7 +425,8 @@ typedef struct Scan pg_node_attr(abstract) =20 Plan plan; - Index scanrelid; /* relid is index into the range table */ + Index scanrelid pg_node_attr(query_jumble_rt_index); /* relid is index i= nto + * the range table */ } Scan; =20 /* ---------------- @@ -457,9 +491,11 @@ typedef struct IndexScan Scan scan; Oid indexid; /* OID of index to scan */ List *indexqual; /* list of index quals (usually OpExprs) */ - List *indexqualorig; /* the same in original form */ + List *indexqualorig pg_node_attr(query_jumble_ignore); /* the same in = original + * form */ List *indexorderby; /* list of index ORDER BY exprs */ - List *indexorderbyorig; /* the same in original form */ + List *indexorderbyorig pg_node_attr(query_jumble_ignore); /* the same = in original + * form */ List *indexorderbyops; /* OIDs of sort ops for ORDER BY exprs */ ScanDirection indexorderdir; /* forward or backward or don't care */ } IndexScan; @@ -500,9 +536,12 @@ typedef struct IndexOnlyScan Scan scan; Oid indexid; /* OID of index to scan */ List *indexqual; /* list of index quals (usually OpExprs) */ - List *recheckqual; /* index quals in recheckable form */ + List *recheckqual pg_node_attr(query_jumble_ignore); /* index quals in + * recheckable form */ List *indexorderby; /* list of index ORDER BY exprs */ - List *indextlist; /* TargetEntry list describing index's cols */ + List *indextlist pg_node_attr(query_jumble_ignore); /* TargetEntry list + * describing index's + * cols */ ScanDirection indexorderdir; /* forward or backward or don't care */ } IndexOnlyScan; =20 @@ -527,9 +566,11 @@ typedef struct BitmapIndexScan { Scan scan; Oid indexid; /* OID of index to scan */ - bool isshared; /* Create shared bitmap if set */ + bool isshared pg_node_attr(query_jumble_ignore); /* Create shared bitmap + * if set */ List *indexqual; /* list of index quals (OpExprs) */ - List *indexqualorig; /* the same in original form */ + List *indexqualorig pg_node_attr(query_jumble_ignore); /* the same in = original + * form */ } BitmapIndexScan; =20 /* ---------------- @@ -544,7 +585,8 @@ typedef struct BitmapIndexScan typedef struct BitmapHeapScan { Scan scan; - List *bitmapqualorig; /* index quals, in standard expr form */ + List *bitmapqualorig pg_node_attr(query_jumble_ignore); /* index quals= , in + * standard expr form */ } BitmapHeapScan; =20 /* ---------------- @@ -604,8 +646,8 @@ typedef enum SubqueryScanStatus typedef struct SubqueryScan { Scan scan; - Plan *subplan; - SubqueryScanStatus scanstatus; + Plan *subplan pg_node_attr(query_jumble_ignore); + SubqueryScanStatus scanstatus pg_node_attr(query_jumble_ignore); } SubqueryScan; =20 /* ---------------- @@ -646,8 +688,11 @@ typedef struct TableFuncScan typedef struct CteScan { Scan scan; - int ctePlanId; /* ID of init SubPlan for CTE */ - int cteParam; /* ID of Param representing CTE output */ + int ctePlanId pg_node_attr(query_jumble_ignore); /* ID of init SubPlan + * for CTE */ + int cteParam pg_node_attr(query_jumble_ignore); /* ID of Param + * representing CTE + * output */ } CteScan; =20 /* ---------------- @@ -667,7 +712,9 @@ typedef struct NamedTuplestoreScan typedef struct WorkTableScan { Scan scan; - int wtParam; /* ID of Param representing work table */ + int wtParam pg_node_attr(query_jumble_ignore); /* ID of Param + * representing work + * table */ } WorkTableScan; =20 /* ---------------- @@ -714,17 +761,26 @@ typedef struct ForeignScan { Scan scan; CmdType operation; /* SELECT/INSERT/UPDATE/DELETE */ - Index resultRelation; /* direct modification target's RT index */ - Oid checkAsUser; /* user to perform the scan as; 0 means to - * check as current user */ + Index resultRelation pg_node_attr(query_jumble_ignore); /* direct modifi= cation + * target's RT index */ + Oid checkAsUser pg_node_attr(query_jumble_ignore); /* user to perform t= he + * scan as; 0 means to + * check as current user */ Oid fs_server; /* OID of foreign server */ - List *fdw_exprs; /* expressions that FDW may evaluate */ - List *fdw_private; /* private data for FDW */ - List *fdw_scan_tlist; /* optional tlist describing scan tuple */ - List *fdw_recheck_quals; /* original quals not in scan.plan.qual */ - Bitmapset *fs_relids; /* base+OJ RTIs generated by this scan */ - Bitmapset *fs_base_relids; /* base RTIs generated by this scan */ - bool fsSystemCol; /* true if any "system column" is needed */ + List *fdw_exprs pg_node_attr(query_jumble_ignore); /* expressions that= FDW + * may evaluate */ + List *fdw_private pg_node_attr(query_jumble_ignore); /* private data f= or FDW */ + List *fdw_scan_tlist pg_node_attr(query_jumble_ignore); /* optional tl= ist + * describing scan tuple */ + List *fdw_recheck_quals pg_node_attr(query_jumble_ignore); /* original= quals not in + * scan.plan.qual */ + Bitmapset *fs_relids pg_node_attr(query_jumble_ignore); /* base+OJ RTIs + * generated by this + * scan */ + Bitmapset *fs_base_relids pg_node_attr(query_jumble_ignore); /* base RTI= s generated + * by this scan */ + bool fsSystemCol pg_node_attr(query_jumble_ignore); /* true if any "syst= em + * column" is needed */ } ForeignScan; =20 /* ---------------- @@ -745,20 +801,27 @@ struct CustomScanMethods; typedef struct CustomScan { Scan scan; - uint32 flags; /* mask of CUSTOMPATH_* flags, see - * nodes/extensible.h */ - List *custom_plans; /* list of Plan nodes, if any */ - List *custom_exprs; /* expressions that custom code may evaluate */ - List *custom_private; /* private data for custom code */ - List *custom_scan_tlist; /* optional tlist describing scan tuple */ - Bitmapset *custom_relids; /* RTIs generated by this scan */ + uint32 flags pg_node_attr(query_jumble_ignore); /* mask of CUSTOMPATH_* + * flags, see + * nodes/extensible.h */ + List *custom_plans pg_node_attr(query_jumble_ignore); /* list of Plan = nodes, + * if any */ + List *custom_exprs pg_node_attr(query_jumble_ignore); /* expressions t= hat + * custom code may + * evaluate */ + List *custom_private pg_node_attr(query_jumble_ignore); /* private dat= a for + * custom code */ + List *custom_scan_tlist pg_node_attr(query_jumble_ignore); /* optional= tlist + * describing scan tuple */ + Bitmapset *custom_relids pg_node_attr(query_jumble_ignore); /* RTIs gene= rated by + * this scan */ =20 /* * NOTE: The method field of CustomScan is required to be a pointer to a * static table of callback functions. So we don't copy the table itself, * just reference the original one. */ - const struct CustomScanMethods *methods; + const struct CustomScanMethods *methods pg_node_attr(query_jumble_ignore); } CustomScan; =20 /* @@ -795,7 +858,7 @@ typedef struct Join =20 Plan plan; JoinType jointype; - bool inner_unique; + bool inner_unique pg_node_attr(query_jumble_ignore); List *joinqual; /* JOIN quals (in addition to plan.qual) */ } Join; =20 @@ -818,7 +881,7 @@ typedef struct NestLoop =20 typedef struct NestLoopParam { - pg_node_attr(no_equal, no_query_jumble) + pg_node_attr(no_equal) =20 NodeTag type; int paramno; /* number of the PARAM_EXEC Param to set */ @@ -841,7 +904,7 @@ typedef struct MergeJoin Join join; =20 /* Can we skip mark/restore calls? */ - bool skip_mark_restore; + bool skip_mark_restore pg_node_attr(query_jumble_ignore); =20 /* mergeclauses as expression trees */ List *mergeclauses; @@ -897,13 +960,13 @@ typedef struct Memoize Plan plan; =20 /* size of the two arrays below */ - int numKeys; + int numKeys pg_node_attr(query_jumble_ignore); =20 /* hash operators for each key */ - Oid *hashOperators pg_node_attr(array_size(numKeys)); + Oid *hashOperators pg_node_attr(array_size(numKeys), query_jumble_ign= ore); =20 /* collations for each key */ - Oid *collations pg_node_attr(array_size(numKeys)); + Oid *collations pg_node_attr(array_size(numKeys), query_jumble_ignore= ); =20 /* cache keys in the form of exprs containing parameters */ List *param_exprs; @@ -912,7 +975,7 @@ typedef struct Memoize * true if the cache entry should be marked as complete after we store the * first tuple in it. */ - bool singlerow; + bool singlerow pg_node_attr(query_jumble_ignore); =20 /* * true when cache key should be compared bit by bit, false when using @@ -924,10 +987,10 @@ typedef struct Memoize * The maximum number of entries that the planner expects will fit in the * cache, or 0 if unknown */ - uint32 est_entries; + uint32 est_entries pg_node_attr(query_jumble_ignore); =20 /* paramids from param_exprs */ - Bitmapset *keyparamids; + Bitmapset *keyparamids pg_node_attr(query_jumble_ignore); } Memoize; =20 /* ---------------- @@ -1010,31 +1073,31 @@ typedef struct Agg AggSplit aggsplit; =20 /* number of grouping columns */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *grpColIdx pg_node_attr(array_size(numCols)); + AttrNumber *grpColIdx pg_node_attr(array_size(numCols), query_jumble_igno= re); =20 /* equality operators to compare with */ - Oid *grpOperators pg_node_attr(array_size(numCols)); - Oid *grpCollations pg_node_attr(array_size(numCols)); + Oid *grpOperators pg_node_attr(array_size(numCols), query_jumble_igno= re); + Oid *grpCollations pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* estimated number of groups in input */ - long numGroups; + long numGroups pg_node_attr(query_jumble_ignore); =20 /* for pass-by-ref transition data */ - uint64 transitionSpace; + uint64 transitionSpace pg_node_attr(query_jumble_ignore); =20 /* IDs of Params used in Aggref inputs */ - Bitmapset *aggParams; + Bitmapset *aggParams pg_node_attr(query_jumble_ignore); =20 /* Note: planner provides numGroups & aggParams only in HASHED/MIXED case= */ =20 /* grouping sets to use */ - List *groupingSets; + List *groupingSets pg_node_attr(query_jumble_ignore); =20 /* chained Agg/Sort nodes */ - List *chain; + List *chain pg_node_attr(query_jumble_ignore); } Agg; =20 /* ---------------- @@ -1046,43 +1109,43 @@ typedef struct WindowAgg Plan plan; =20 /* ID referenced by window functions */ - Index winref; + Index winref pg_node_attr(query_jumble_ignore); =20 /* number of columns in partition clause */ - int partNumCols; + int partNumCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *partColIdx pg_node_attr(array_size(partNumCols)); + AttrNumber *partColIdx pg_node_attr(array_size(partNumCols), query_jumble= _ignore); =20 /* equality operators for partition columns */ - Oid *partOperators pg_node_attr(array_size(partNumCols)); + Oid *partOperators pg_node_attr(array_size(partNumCols), query_jumble= _ignore); =20 /* collations for partition columns */ - Oid *partCollations pg_node_attr(array_size(partNumCols)); + Oid *partCollations pg_node_attr(array_size(partNumCols), query_jumbl= e_ignore); =20 /* number of columns in ordering clause */ - int ordNumCols; + int ordNumCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *ordColIdx pg_node_attr(array_size(ordNumCols)); + AttrNumber *ordColIdx pg_node_attr(array_size(ordNumCols), query_jumble_i= gnore); =20 /* equality operators for ordering columns */ - Oid *ordOperators pg_node_attr(array_size(ordNumCols)); + Oid *ordOperators pg_node_attr(array_size(ordNumCols), query_jumble_i= gnore); =20 /* collations for ordering columns */ - Oid *ordCollations pg_node_attr(array_size(ordNumCols)); + Oid *ordCollations pg_node_attr(array_size(ordNumCols), query_jumble_= ignore); =20 /* frame_clause options, see WindowDef */ - int frameOptions; + int frameOptions pg_node_attr(query_jumble_ignore); =20 /* expression for starting bound, if any */ - Node *startOffset; + Node *startOffset pg_node_attr(query_jumble_ignore); =20 /* expression for ending bound, if any */ - Node *endOffset; + Node *endOffset pg_node_attr(query_jumble_ignore); =20 /* qual to help short-circuit execution */ - List *runCondition; + List *runCondition pg_node_attr(query_jumble_ignore); =20 /* runCondition for display in EXPLAIN */ List *runConditionOrig; @@ -1090,25 +1153,25 @@ typedef struct WindowAgg /* these fields are used with RANGE offset PRECEDING/FOLLOWING: */ =20 /* in_range function for startOffset */ - Oid startInRangeFunc; + Oid startInRangeFunc pg_node_attr(query_jumble_ignore); =20 /* in_range function for endOffset */ - Oid endInRangeFunc; + Oid endInRangeFunc pg_node_attr(query_jumble_ignore); =20 /* collation for in_range tests */ - Oid inRangeColl; + Oid inRangeColl pg_node_attr(query_jumble_ignore); =20 /* use ASC sort order for in_range tests? */ - bool inRangeAsc; + bool inRangeAsc pg_node_attr(query_jumble_ignore); =20 /* nulls sort first for in_range tests? */ - bool inRangeNullsFirst; + bool inRangeNullsFirst pg_node_attr(query_jumble_ignore); =20 /* * false for all apart from the WindowAgg that's closest to the root of * the plan */ - bool topWindow; + bool topWindow pg_node_attr(query_jumble_ignore); } WindowAgg; =20 /* ---------------- @@ -1120,16 +1183,16 @@ typedef struct Unique Plan plan; =20 /* number of columns to check for uniqueness */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *uniqColIdx pg_node_attr(array_size(numCols)); + AttrNumber *uniqColIdx pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* equality operators to compare with */ - Oid *uniqOperators pg_node_attr(array_size(numCols)); + Oid *uniqOperators pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* collations for equality comparisons */ - Oid *uniqCollations pg_node_attr(array_size(numCols)); + Oid *uniqCollations pg_node_attr(array_size(numCols), query_jumble_ig= nore); } Unique; =20 /* ------------ @@ -1146,12 +1209,19 @@ typedef struct Unique typedef struct Gather { Plan plan; - int num_workers; /* planned number of worker processes */ - int rescan_param; /* ID of Param that signals a rescan, or -1 */ - bool single_copy; /* don't execute plan more than once */ - bool invisible; /* suppress EXPLAIN display (for testing)? */ - Bitmapset *initParam; /* param id's of initplans which are referred - * at gather or one of its child nodes */ + /* planned number of worker processes */ + int num_workers; + /* ID of Param that signals a rescan, or -1 */ + int rescan_param pg_node_attr(query_jumble_ignore); + /* don't execute plan more than once */ + bool single_copy pg_node_attr(query_jumble_ignore); + /* suppress EXPLAIN display (for testing)? */ + bool invisible pg_node_attr(query_jumble_ignore); + /* + * param id's of initplans which are referred at gather or one of its + * child nodes + */ + Bitmapset *initParam pg_node_attr(query_jumble_ignore); } Gather; =20 /* ------------ @@ -1166,30 +1236,30 @@ typedef struct GatherMerge int num_workers; =20 /* ID of Param that signals a rescan, or -1 */ - int rescan_param; + int rescan_param pg_node_attr(query_jumble_ignore); =20 /* remaining fields are just like the sort-key info in struct Sort */ =20 /* number of sort-key columns */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *sortColIdx pg_node_attr(array_size(numCols)); + AttrNumber *sortColIdx pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* OIDs of operators to sort them by */ - Oid *sortOperators pg_node_attr(array_size(numCols)); + Oid *sortOperators pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* OIDs of collations */ - Oid *collations pg_node_attr(array_size(numCols)); + Oid *collations pg_node_attr(array_size(numCols), query_jumble_ignore= ); =20 /* NULLS FIRST/LAST directions */ - bool *nullsFirst pg_node_attr(array_size(numCols)); + bool *nullsFirst pg_node_attr(array_size(numCols), query_jumble_ignore= ); =20 /* * param id's of initplans which are referred at gather merge or one of * its child nodes */ - Bitmapset *initParam; + Bitmapset *initParam pg_node_attr(query_jumble_ignore); } GatherMerge; =20 /* ---------------- @@ -1209,11 +1279,16 @@ typedef struct Hash * needed to put them into the hashtable. */ List *hashkeys; /* hash keys for the hashjoin condition */ - Oid skewTable; /* outer join key's table OID, or InvalidOid */ - AttrNumber skewColumn; /* outer join key's column #, or zero */ - bool skewInherit; /* is outer join rel an inheritance tree? */ + Oid skewTable pg_node_attr(query_jumble_ignore); /* outer join key's + * table OID, or + * InvalidOid */ + AttrNumber skewColumn pg_node_attr(query_jumble_ignore); /* outer join ke= y's + * column #, or zero */ + bool skewInherit pg_node_attr(query_jumble_ignore); /* is outer join rel= an + * inheritance tree? */ /* all other info is in the parent HashJoin node */ - Cardinality rows_total; /* estimate total rows if parallel_aware */ + Cardinality rows_total pg_node_attr(query_jumble_ignore); /* estimate tot= al rows + * if parallel_aware */ } Hash; =20 /* ---------------- @@ -1231,20 +1306,20 @@ typedef struct SetOp SetOpStrategy strategy; =20 /* number of columns to compare */ - int numCols; + int numCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *cmpColIdx pg_node_attr(array_size(numCols)); + AttrNumber *cmpColIdx pg_node_attr(array_size(numCols), query_jumble_igno= re); =20 /* comparison operators (either equality operators or sort operators) */ - Oid *cmpOperators pg_node_attr(array_size(numCols)); - Oid *cmpCollations pg_node_attr(array_size(numCols)); + Oid *cmpOperators pg_node_attr(array_size(numCols), query_jumble_igno= re); + Oid *cmpCollations pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* nulls-first flags if sorting, otherwise not interesting */ - bool *cmpNullsFirst pg_node_attr(array_size(numCols)); + bool *cmpNullsFirst pg_node_attr(array_size(numCols), query_jumble_ign= ore); =20 /* estimated number of groups in left input */ - long numGroups; + long numGroups pg_node_attr(query_jumble_ignore); } SetOp; =20 /* ---------------- @@ -1259,8 +1334,10 @@ typedef struct SetOp typedef struct LockRows { Plan plan; - List *rowMarks; /* a list of PlanRowMark's */ - int epqParam; /* ID of Param for EvalPlanQual re-eval */ + List *rowMarks pg_node_attr(query_jumble_ignore); /* a list of + * PlanRowMark's */ + int epqParam pg_node_attr(query_jumble_ignore); /* ID of Param for + * EvalPlanQual re-eval */ } LockRows; =20 /* ---------------- @@ -1275,25 +1352,25 @@ typedef struct Limit Plan plan; =20 /* OFFSET parameter, or NULL if none */ - Node *limitOffset; + Node *limitOffset pg_node_attr(query_jumble_ignore); =20 /* COUNT parameter, or NULL if none */ - Node *limitCount; + Node *limitCount pg_node_attr(query_jumble_ignore); =20 /* limit type */ - LimitOption limitOption; + LimitOption limitOption pg_node_attr(query_jumble_ignore); =20 /* number of columns to check for similarity */ - int uniqNumCols; + int uniqNumCols pg_node_attr(query_jumble_ignore); =20 /* their indexes in the target list */ - AttrNumber *uniqColIdx pg_node_attr(array_size(uniqNumCols)); + AttrNumber *uniqColIdx pg_node_attr(array_size(uniqNumCols), query_jumble= _ignore); =20 /* equality operators to compare with */ - Oid *uniqOperators pg_node_attr(array_size(uniqNumCols)); + Oid *uniqOperators pg_node_attr(array_size(uniqNumCols), query_jumble= _ignore); =20 /* collations for equality comparisons */ - Oid *uniqCollations pg_node_attr(array_size(uniqNumCols)); + Oid *uniqCollations pg_node_attr(array_size(uniqNumCols), query_jumbl= e_ignore); } Limit; =20 =20 diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 59e7bb26bbd..c09785a0728 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1074,8 +1074,6 @@ typedef struct SubLink */ typedef struct SubPlan { - pg_node_attr(no_query_jumble) - Expr xpr; /* Fields copied from original SubLink: */ SubLinkType subLinkType; /* see above */ @@ -1106,8 +1104,9 @@ typedef struct SubPlan List *parParam; /* indices of input Params from parent plan */ List *args; /* exprs to pass as parParam values */ /* Estimated execution costs: */ - Cost startup_cost; /* one-time setup cost */ - Cost per_call_cost; /* cost for each subplan evaluation */ + Cost startup_cost pg_node_attr(query_jumble_ignore); /* one-time setup c= ost */ + Cost per_call_cost pg_node_attr(query_jumble_ignore); /* cost for each s= ubplan + * evaluation */ } SubPlan; =20 /* diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumbl= e.h index 5afa6f3605f..6c3b787b46f 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -15,6 +15,7 @@ #define QUERYJUMBLE_H =20 #include "nodes/parsenodes.h" +#include "nodes/pathnodes.h" =20 /* * Struct for tracking locations/lengths of constants during normalization @@ -48,6 +49,9 @@ typedef struct JumbleState =20 /* highest Param id we've seen, in order to start normalization correctly= */ int highest_extern_param_id; + + /* planner global info for resolving RT indexes when plan jumbling */ + PlannerGlobal *glob; } JumbleState; =20 /* Values for the compute_query_id GUC */ @@ -59,15 +63,27 @@ enum ComputeQueryIdType COMPUTE_QUERY_ID_REGRESS, }; =20 +/* Values for the compute_plan_id GUC */ +enum ComputePlanIdType +{ + COMPUTE_PLAN_ID_OFF, + COMPUTE_PLAN_ID_ON, + COMPUTE_PLAN_ID_AUTO, + COMPUTE_PLAN_ID_REGRESS, +}; + /* GUC parameters */ extern PGDLLIMPORT int compute_query_id; +extern PGDLLIMPORT int compute_plan_id; =20 =20 extern const char *CleanQuerytext(const char *query, int *location, int *l= en); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); +extern void EnablePlanId(void); =20 extern PGDLLIMPORT bool query_id_enabled; +extern PGDLLIMPORT bool plan_id_enabled; =20 /* * Returns whether query identifier computation has been enabled, either @@ -83,8 +99,22 @@ IsQueryIdEnabled(void) return query_id_enabled; } =20 -/* Functions intended for other users of jumbling (e.g. plan jumbling) */ -extern JumbleState *InitializeJumbleState(bool record_clocations); +/* + * Returns whether plan identifier computation has been enabled, either + * directly in the GUC or by a module when the setting is 'auto'. + */ +static inline bool +IsPlanIdEnabled(void) +{ + if (compute_plan_id =3D=3D COMPUTE_PLAN_ID_OFF) + return false; + if (compute_plan_id =3D=3D COMPUTE_PLAN_ID_ON) + return true; + return plan_id_enabled; +} + +/* Functions called for plan jumbling or extensions doing their own jumbli= ng */ +extern JumbleState *InitializeJumbleState(bool record_clocations, PlannerG= lobal *glob); extern void AppendJumble(JumbleState *jstate, const unsigned char *item, S= ize size); extern void JumbleNode(JumbleState *jstate, Node *node); extern uint64 HashJumbleState(JumbleState *jstate); diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend= _status.h index d3d4ff6c5c9..437a4cec5bf 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -170,6 +170,9 @@ typedef struct PgBackendStatus =20 /* query identifier, optionally computed using post_parse_analyze_hook */ uint64 st_query_id; + + /* plan identifier, optionally computed after planning */ + uint64 st_plan_id; } PgBackendStatus; =20 =20 @@ -316,6 +319,7 @@ extern void pgstat_clear_backend_activity_snapshot(void= ); /* Activity reporting functions */ extern void pgstat_report_activity(BackendState state, const char *cmd_str= ); extern void pgstat_report_query_id(uint64 query_id, bool force); +extern void pgstat_report_plan_id(uint64 query_id, uint64 plan_id, bool fo= rce); extern void pgstat_report_tempfile(size_t filesize); extern void pgstat_report_appname(const char *appname); extern void pgstat_report_xact_timestamp(TimestampTz tstamp); @@ -323,6 +327,7 @@ extern const char *pgstat_get_backend_current_activity(= int pid, bool checkUser); extern const char *pgstat_get_crashed_backend_activity(int pid, char *buff= er, int buflen); extern uint64 pgstat_get_my_query_id(void); +extern uint64 pgstat_get_my_plan_id(void); extern BackendType pgstat_get_backend_type_by_proc_number(ProcNumber procN= umber); =20 =20 diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/sys= tem_views.sql index cddc3ea9b53..d3d5578d2c8 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -889,6 +889,7 @@ CREATE VIEW pg_stat_activity AS S.backend_xid, s.backend_xmin, S.query_id, + S.plan_id, S.query, S.backend_type FROM pg_stat_get_activity(NULL) AS S diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index c24e66f82e1..31a1761f539 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -966,6 +966,22 @@ ExplainPrintPlan(ExplainState *es, QueryDesc *queryDes= c) ExplainPropertyInteger("Query Identifier", NULL, (int64) queryDesc->plannedstmt->queryId, es); } + + /* + * COMPUTE_PLAN_ID_REGRESS means COMPUTE_PLAN_ID_YES, but we don't show + * the queryid in any of the EXPLAIN plans to keep stable the results + * generated by regression test suites. + */ + if (es->verbose && queryDesc->plannedstmt->planId !=3D UINT64CONST(0) && + compute_plan_id !=3D COMPUTE_PLAN_ID_REGRESS) + { + /* + * Output the queryid as an int64 rather than a uint64 so we match + * what would be seen in the BIGINT pg_stat_activity.plan_id column. + */ + ExplainPropertyInteger("Plan Identifier", NULL, (int64) + queryDesc->plannedstmt->planId, es); + } } =20 /* diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMai= n.c index 1d27b840ca9..7f16f38f240 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -120,13 +120,15 @@ ExecutorStart(QueryDesc *queryDesc, int eflags) { /* * In some cases (e.g. an EXECUTE statement or an execute message with the - * extended query protocol) the query_id won't be reported, so do it now. + * extended query protocol) the query_id and plan_id won't be reported, so + * do it now. * - * Note that it's harmless to report the query_id multiple times, as the - * call will be ignored if the top level query_id has already been - * reported. + * Note that it's harmless to report the identifiers multiple times, as + * the call will be ignored if the top level query_id / plan_id has + * already been reported. */ pgstat_report_query_id(queryDesc->plannedstmt->queryId, false); + pgstat_report_plan_id(queryDesc->plannedstmt->planId, queryDesc->planneds= tmt->queryId, false); =20 if (ExecutorStart_hook) (*ExecutorStart_hook) (queryDesc, eflags); diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/exe= cParallel.c index 9c313d81315..da2279579a8 100644 --- a/src/backend/executor/execParallel.c +++ b/src/backend/executor/execParallel.c @@ -174,6 +174,7 @@ ExecSerializePlan(Plan *plan, EState *estate) pstmt =3D makeNode(PlannedStmt); pstmt->commandType =3D CMD_SELECT; pstmt->queryId =3D pgstat_get_my_query_id(); + pstmt->planId =3D pgstat_get_my_plan_id(); pstmt->hasReturning =3D false; pstmt->hasModifyingCTE =3D false; pstmt->canSetTag =3D true; diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_= node_support.pl index 7c012c27f88..e7b04678e06 100644 --- a/src/backend/nodes/gen_node_support.pl +++ b/src/backend/nodes/gen_node_support.pl @@ -475,6 +475,7 @@ foreach my $infile (@ARGV) equal_ignore_if_zero query_jumble_ignore query_jumble_location + query_jumble_rt_index read_write_ignore write_only_relids write_only_nondefault_pathtarget @@ -1280,13 +1281,19 @@ _jumble${n}(JumbleState *jstate, Node *node) { my $t =3D $node_type_info{$n}->{field_types}{$f}; my @a =3D @{ $node_type_info{$n}->{field_attrs}{$f} }; + my $array_size_field; my $query_jumble_ignore =3D $struct_no_query_jumble; my $query_jumble_location =3D 0; + my $query_jumble_rt_index =3D 0; =20 # extract per-field attributes foreach my $a (@a) { - if ($a eq 'query_jumble_ignore') + if ($a =3D~ /^array_size\(([\w.]+)\)$/) + { + $array_size_field =3D $1; + } + elsif ($a eq 'query_jumble_ignore') { $query_jumble_ignore =3D 1; } @@ -1294,10 +1301,29 @@ _jumble${n}(JumbleState *jstate, Node *node) { $query_jumble_location =3D 1; } + elsif ($a eq 'query_jumble_rt_index') + { + $query_jumble_rt_index =3D 1; + } } =20 + next if $query_jumble_ignore; + + if ($query_jumble_rt_index) + { + if ($t eq 'List*') + { + print $jff "\tJUMBLE_RT_INDEX_LIST($f);\n" + unless $query_jumble_ignore; + } + else + { + print $jff "\tJUMBLE_RT_INDEX($f);\n" + unless $query_jumble_ignore; + } + } # node type - if (($t =3D~ /^(\w+)\*$/ or $t =3D~ /^struct\s+(\w+)\*$/) + elsif (($t =3D~ /^(\w+)\*$/ or $t =3D~ /^struct\s+(\w+)\*$/) and elem $1, @node_types) { print $jff "\tJUMBLE_NODE($f);\n" @@ -1317,6 +1343,26 @@ _jumble${n}(JumbleState *jstate, Node *node) print $jff "\tJUMBLE_STRING($f);\n" unless $query_jumble_ignore; } + elsif ($t =3D~ /^(\w+)(\*|\[\w+\])$/ and elem $1, @scalar_types) + { + if (!defined $array_size_field) + { + die "no array size defined for $n.$f of type $t\n"; + } + if ($node_type_info{$n}->{field_types}{$array_size_field} eq + 'List*') + { + print $jff + "\tJUMBLE_ARRAY($f, list_length(expr->$array_size_field));\n" + unless $query_jumble_ignore; + } + else + { + print $jff + "\tJUMBLE_ARRAY($f, expr->$array_size_field);\n" + unless $query_jumble_ignore; + } + } else { print $jff "\tJUMBLE_FIELD($f);\n" diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query= jumblefuncs.c index 545d8edcae2..482d1ea8283 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -35,12 +35,14 @@ #include "common/hashfn.h" #include "miscadmin.h" #include "nodes/queryjumble.h" +#include "parser/parsetree.h" #include "parser/scansup.h" =20 #define JUMBLE_SIZE 1024 /* query serialization buffer size */ =20 /* GUC parameters */ int compute_query_id =3D COMPUTE_QUERY_ID_AUTO; +int compute_plan_id =3D COMPUTE_PLAN_ID_AUTO; =20 /* * True when compute_query_id is ON or AUTO, and a module requests them. @@ -51,7 +53,18 @@ int compute_query_id =3D COMPUTE_QUERY_ID_AUTO; */ bool query_id_enabled =3D false; =20 +/* + * True when compute_plan_id is ON or AUTO, and a module requests them. + * + * Note that IsPlanIdEnabled() should be used instead of checking + * plan_id_enabled or plan_query_id directly when we want to know + * whether plan identifiers are computed in the core or not. + */ +bool plan_id_enabled =3D false; + static void RecordConstLocation(JumbleState *jstate, int location); +static void JumbleRangeTableIndex(JumbleState *jstate, Index rti); +static void JumbleRangeTableIndexList(JumbleState *jstate, List *l); static void _jumbleA_Const(JumbleState *jstate, Node *node); static void _jumbleList(JumbleState *jstate, Node *node); static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node); @@ -106,7 +119,7 @@ CleanQuerytext(const char *query, int *location, int *l= en) } =20 JumbleState * -InitializeJumbleState(bool record_clocations) +InitializeJumbleState(bool record_clocations, PlannerGlobal *glob) { JumbleState *jstate =3D (JumbleState *) palloc0(sizeof(JumbleState)); =20 @@ -121,6 +134,8 @@ InitializeJumbleState(bool record_clocations) palloc(jstate->clocations_buf_size * sizeof(LocationLen)); } =20 + jstate->glob =3D glob; + return jstate; } =20 @@ -135,7 +150,7 @@ HashJumbleState(JumbleState *jstate) JumbleState * JumbleQuery(Query *query) { - JumbleState *jstate =3D InitializeJumbleState(true); + JumbleState *jstate =3D InitializeJumbleState(true, NULL); =20 Assert(IsQueryIdEnabled()); =20 @@ -171,6 +186,19 @@ EnableQueryId(void) query_id_enabled =3D true; } =20 +/* + * Enables plan identifier computation. + * + * Third-party plugins can use this function to inform core that they requ= ire + * a query identifier to be computed. + */ +void +EnablePlanId(void) +{ + if (compute_plan_id !=3D COMPUTE_PLAN_ID_OFF) + plan_id_enabled =3D true; +} + /* * AppendJumble: Append a value that is substantive in a given query to * the current jumble. @@ -238,8 +266,17 @@ RecordConstLocation(JumbleState *jstate, int location) JumbleNode(jstate, (Node *) expr->item) #define JUMBLE_LOCATION(location) \ RecordConstLocation(jstate, expr->location) +#define JUMBLE_RT_INDEX(item) \ +do { \ + if (expr->item) \ + JumbleRangeTableIndex(jstate, expr->item); \ +} while(0) +#define JUMBLE_RT_INDEX_LIST(item) \ + JumbleRangeTableIndexList(jstate, expr->item) #define JUMBLE_FIELD(item) \ AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->= item)) +#define JUMBLE_ARRAY(item, len) \ + AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(*(expr= ->item)) * len) #define JUMBLE_FIELD_SINGLE(item) \ AppendJumble(jstate, (const unsigned char *) &(item), sizeof(item)) #define JUMBLE_STRING(str) \ @@ -388,3 +425,40 @@ _jumbleVariableSetStmt(JumbleState *jstate, Node *node) JUMBLE_FIELD(is_local); JUMBLE_LOCATION(location); } + +/* + * Jumble the target of a rangle table index, e.g. in a Scan or Modify node + */ +static void +JumbleRangeTableIndex(JumbleState *jstate, Index rti) +{ + RangeTblEntry *expr =3D rt_fetch(rti, jstate->glob->finalrtable); + + switch (expr->rtekind) + { + case RTE_RELATION: + JUMBLE_FIELD(relid); + break; + case RTE_CTE: + JUMBLE_STRING(ctename); + break; + default: + + /* + * Ignore other targets, the jumble includes something identifying + * about them already + */ + break; + } +} + +static void +JumbleRangeTableIndexList(JumbleState *jstate, List *l) +{ + ListCell *lc; + + Assert(l->type =3D=3D T_IntList); + + foreach(lc, l) + JumbleRangeTableIndex(jstate, lfirst_int(lc)); +} diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/p= lan/planner.c index 8a474a50be7..d14c3218e17 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -37,6 +37,7 @@ #ifdef OPTIMIZER_DEBUG #include "nodes/print.h" #endif +#include "nodes/queryjumble.h" #include "nodes/supportnodes.h" #include "optimizer/appendinfo.h" #include "optimizer/clauses.h" @@ -532,6 +533,16 @@ standard_planner(Query *parse, const char *query_strin= g, int cursorOptions, Assert(glob->finalrowmarks =3D=3D NIL); Assert(glob->resultRelations =3D=3D NIL); Assert(glob->appendRelations =3D=3D NIL); + + /* + * Initialize plan identifier jumble if needed + * + * Note the actual jumbling is done in the tree walk in + * set_plan_references + */ + if (IsPlanIdEnabled()) + glob->plan_jumble_state =3D InitializeJumbleState(false, glob); + top_plan =3D set_plan_references(root, top_plan); /* ... and the subplans (both regular subplans and initplans) */ Assert(list_length(glob->subplans) =3D=3D list_length(glob->subroots)); @@ -571,6 +582,13 @@ standard_planner(Query *parse, const char *query_strin= g, int cursorOptions, result->stmt_location =3D parse->stmt_location; result->stmt_len =3D parse->stmt_len; =20 + if (IsPlanIdEnabled()) + { + result->planId =3D HashJumbleState(glob->plan_jumble_state); + pfree(glob->plan_jumble_state->jumble); + pfree(glob->plan_jumble_state); + } + result->jitFlags =3D PGJIT_NONE; if (jit_enabled && jit_above_cost >=3D 0 && top_plan->total_cost > jit_above_cost) diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/p= lan/setrefs.c index 0868249be94..fc48337f1df 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -19,6 +19,7 @@ #include "catalog/pg_type.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "nodes/queryjumble.h" #include "optimizer/optimizer.h" #include "optimizer/pathnode.h" #include "optimizer/planmain.h" @@ -1295,6 +1296,14 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rto= ffset) break; } =20 + /* + * If enabled, append significant information to the plan identifier + * jumble (we do this here since we're already walking the tree in a + * near-final state) + */ + if (IsPlanIdEnabled()) + JumbleNode(root->glob->plan_jumble_state, (Node *) plan); + /* * Now recurse into child plans, if any * diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmast= er/launch_backend.c index a97a1eda6da..654acf5bf04 100644 --- a/src/backend/postmaster/launch_backend.c +++ b/src/backend/postmaster/launch_backend.c @@ -115,6 +115,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; + bool plan_id_enabled; int max_safe_fds; int MaxBackends; int num_pmchild_slots; @@ -744,6 +745,7 @@ save_backend_variables(BackendParameters *param, param->redirection_done =3D redirection_done; param->IsBinaryUpgrade =3D IsBinaryUpgrade; param->query_id_enabled =3D query_id_enabled; + param->plan_id_enabled =3D plan_id_enabled; param->max_safe_fds =3D max_safe_fds; =20 param->MaxBackends =3D MaxBackends; @@ -1004,6 +1006,7 @@ restore_backend_variables(BackendParameters *param) redirection_done =3D param->redirection_done; IsBinaryUpgrade =3D param->IsBinaryUpgrade; query_id_enabled =3D param->query_id_enabled; + plan_id_enabled =3D param->plan_id_enabled; max_safe_fds =3D param->max_safe_fds; =20 MaxBackends =3D param->MaxBackends; diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 5655348a2e2..6d8947bae9b 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -1106,6 +1106,7 @@ exec_simple_query(const char *query_string) size_t cmdtaglen; =20 pgstat_report_query_id(0, true); + pgstat_report_plan_id(0, 0, true); =20 /* * Get the command name for use in status display (it also becomes the diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/util= s/activity/backend_status.c index 731342799a6..1dfb7a58f87 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -379,6 +379,7 @@ pgstat_bestart(void) lbeentry.st_progress_command =3D PROGRESS_COMMAND_INVALID; lbeentry.st_progress_command_target =3D InvalidOid; lbeentry.st_query_id =3D UINT64CONST(0); + lbeentry.st_plan_id =3D UINT64CONST(0); =20 /* * we don't zero st_progress_param here to save cycles; nobody should @@ -533,6 +534,7 @@ pgstat_report_activity(BackendState state, const char *= cmd_str) /* st_xact_start_timestamp and wait_event_info are also disabled */ beentry->st_xact_start_timestamp =3D 0; beentry->st_query_id =3D UINT64CONST(0); + beentry->st_plan_id =3D UINT64CONST(0); proc->wait_event_info =3D 0; PGSTAT_END_WRITE_ACTIVITY(beentry); } @@ -588,12 +590,15 @@ pgstat_report_activity(BackendState state, const char= *cmd_str) beentry->st_state_start_timestamp =3D current_timestamp; =20 /* - * If a new query is started, we reset the query identifier as it'll only - * be known after parse analysis, to avoid reporting last query's - * identifier. + * If a new query is started, we reset the query and plan identifier as + * it'll only be known after parse analysis / planning, to avoid reporting + * last query's identifiers. */ if (state =3D=3D STATE_RUNNING) + { beentry->st_query_id =3D UINT64CONST(0); + beentry->st_plan_id =3D UINT64CONST(0); + } =20 if (cmd_str !=3D NULL) { @@ -644,6 +649,45 @@ pgstat_report_query_id(uint64 query_id, bool force) PGSTAT_END_WRITE_ACTIVITY(beentry); } =20 +/* -------- + * pgstat_report_plan_id() - + * + * Called to update top-level plan identifier. + * -------- + */ +void +pgstat_report_plan_id(uint64 plan_id, uint64 query_id, bool force) +{ + volatile PgBackendStatus *beentry =3D MyBEEntry; + + /* + * if track_activities is disabled, st_plan_id should already have been + * reset + */ + if (!beentry || !pgstat_track_activities) + return; + + /* + * We only report the top-level plan identifiers. The stored plan_id is + * reset when a backend calls pgstat_report_activity(STATE_RUNNING), or + * with an explicit call to this function using the force flag. If the + * saved plan identifier is not zero or the query identifier is 0, it + * means that it's not a top-level command, so ignore the one provided + * unless it's an explicit call to reset the identifier. + */ + if ((beentry->st_plan_id !=3D 0 || query_id =3D=3D 0) && !force) + return; + + /* + * Update my status entry, following the protocol of bumping + * st_changecount before and after. We use a volatile pointer here to + * ensure the compiler doesn't try to get cute. + */ + PGSTAT_BEGIN_WRITE_ACTIVITY(beentry); + beentry->st_plan_id =3D plan_id; + PGSTAT_END_WRITE_ACTIVITY(beentry); +} + =20 /* ---------- * pgstat_report_appname() - @@ -1040,6 +1084,26 @@ pgstat_get_my_query_id(void) return MyBEEntry->st_query_id; } =20 +/* ---------- + * pgstat_get_my_plan_id() - + * + * Return current backend's plan identifier. + */ +uint64 +pgstat_get_my_plan_id(void) +{ + if (!MyBEEntry) + return 0; + + /* + * There's no need for a lock around pgstat_begin_read_activity / + * pgstat_end_read_activity here as it's only called from + * pg_stat_get_activity which is already protected, or from the same + * backend which means that there won't be concurrent writes. + */ + return MyBEEntry->st_plan_id; +} + /* ---------- * pgstat_get_backend_type_by_proc_number() - * diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pg= statfuncs.c index e9096a88492..a3d5592beb6 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -330,7 +330,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) Datum pg_stat_get_activity(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_ACTIVITY_COLS 31 +#define PG_STAT_GET_ACTIVITY_COLS 32 int num_backends =3D pgstat_fetch_stat_numbackends(); int curr_backend; int pid =3D PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); @@ -641,6 +641,10 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) nulls[30] =3D true; else values[30] =3D UInt64GetDatum(beentry->st_query_id); + if (beentry->st_plan_id =3D=3D 0) + nulls[31] =3D true; + else + values[31] =3D UInt64GetDatum(beentry->st_plan_id); } else { @@ -670,6 +674,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) nulls[28] =3D true; nulls[29] =3D true; nulls[30] =3D true; + nulls[31] =3D true; } =20 tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/g= uc_tables.c index 38cb9e970d5..9ddb8e97310 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -309,6 +309,24 @@ static const struct config_enum_entry compute_query_id= _options[] =3D { {NULL, 0, false} }; =20 +/* + * Although only "on" and "off" are documented, we accept + * all the likely variants of "on" and "off". + */ +static const struct config_enum_entry compute_plan_id_options[] =3D { + {"auto", COMPUTE_PLAN_ID_AUTO, false}, + {"regress", COMPUTE_PLAN_ID_REGRESS, false}, + {"on", COMPUTE_PLAN_ID_ON, false}, + {"off", COMPUTE_PLAN_ID_OFF, false}, + {"true", COMPUTE_PLAN_ID_ON, true}, + {"false", COMPUTE_PLAN_ID_OFF, true}, + {"yes", COMPUTE_PLAN_ID_ON, true}, + {"no", COMPUTE_PLAN_ID_OFF, true}, + {"1", COMPUTE_PLAN_ID_ON, true}, + {"0", COMPUTE_PLAN_ID_OFF, true}, + {NULL, 0, false} +}; + /* * Although only "on", "off", and "partition" are documented, we * accept all the likely variants of "on" and "off". @@ -4873,6 +4891,16 @@ struct config_enum ConfigureNamesEnum[] =3D NULL, NULL, NULL }, =20 + { + {"compute_plan_id", PGC_SUSET, STATS_MONITORING, + gettext_noop("Enables in-core computation of plan identifiers."), + NULL + }, + &compute_plan_id, + COMPUTE_PLAN_ID_AUTO, compute_plan_id_options, + NULL, NULL, NULL + }, + { {"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Enables the planner to use constraints to optimize querie= s."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/ut= ils/misc/postgresql.conf.sample index 079efa1baa7..0634ae90dd3 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -641,6 +641,7 @@ # - Monitoring - =20 #compute_query_id =3D auto +#compute_plan_id =3D auto #log_statement_stats =3D off #log_parser_stats =3D off #log_planner_stats =3D off diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expec= ted/explain.out index ee31e41d508..8bfa3c1a5f9 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -699,6 +699,17 @@ select explain_filter('explain (verbose) create table = test_ctas as select 1'); Query Identifier: N (3 rows) =20 +-- Test compute_plan_id +set compute_plan_id =3D on; +select explain_filter('explain (verbose) select * from int8_tbl i8'); + explain_filter =20 +---------------------------------------------------------------- + Seq Scan on public.int8_tbl i8 (cost=3DN.N..N.N rows=3DN width=3DN) + Output: q1, q2 + Query Identifier: N + Plan Identifier: N +(4 rows) + -- Test SERIALIZE option select explain_filter('explain (analyze,buffers off,serialize) select * fr= om int8_tbl i8'); explain_filter = =20 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expecte= d/rules.out index 3361f6a69c9..6778b79e658 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1760,9 +1760,10 @@ pg_stat_activity| SELECT s.datid, s.backend_xid, s.backend_xmin, s.query_id, + s.plan_id, s.query, s.backend_type - FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, appl= ication_name, state, query, wait_event_type, wait_event, xact_start, query_= start, backend_start, state_change, client_addr, client_hostname, client_po= rt, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, ss= lbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ= , gss_enc, gss_delegation, leader_pid, query_id) + FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, appl= ication_name, state, query, wait_event_type, wait_event, xact_start, query_= start, backend_start, state_change, client_addr, client_hostname, client_po= rt, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, ss= lbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ= , gss_enc, gss_delegation, leader_pid, query_id, plan_id) LEFT JOIN pg_database d ON ((s.datid =3D d.oid))) LEFT JOIN pg_authid u ON ((s.usesysid =3D u.oid))); pg_stat_all_indexes| SELECT c.oid AS relid, @@ -1890,7 +1891,7 @@ pg_stat_gssapi| SELECT pid, gss_princ AS principal, gss_enc AS encrypted, gss_delegation AS credentials_delegated - FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, applic= ation_name, state, query, wait_event_type, wait_event, xact_start, query_st= art, backend_start, state_change, client_addr, client_hostname, client_port= , backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslb= its, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, = gss_enc, gss_delegation, leader_pid, query_id) + FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, applic= ation_name, state, query, wait_event_type, wait_event, xact_start, query_st= art, backend_start, state_change, client_addr, client_hostname, client_port= , backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslb= its, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, = gss_enc, gss_delegation, leader_pid, query_id, plan_id) WHERE (client_port IS NOT NULL); pg_stat_io| SELECT backend_type, object, @@ -2096,7 +2097,7 @@ pg_stat_replication| SELECT s.pid, w.sync_priority, w.sync_state, w.reply_time - FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, appl= ication_name, state, query, wait_event_type, wait_event, xact_start, query_= start, backend_start, state_change, client_addr, client_hostname, client_po= rt, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, ss= lbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ= , gss_enc, gss_delegation, leader_pid, query_id) + FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, appl= ication_name, state, query, wait_event_type, wait_event, xact_start, query_= start, backend_start, state_change, client_addr, client_hostname, client_po= rt, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, ss= lbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ= , gss_enc, gss_delegation, leader_pid, query_id, plan_id) JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flu= sh_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_s= tate, reply_time) ON ((s.pid =3D w.pid))) LEFT JOIN pg_authid u ON ((s.usesysid =3D u.oid))); pg_stat_replication_slots| SELECT s.slot_name, @@ -2130,7 +2131,7 @@ pg_stat_ssl| SELECT pid, ssl_client_dn AS client_dn, ssl_client_serial AS client_serial, ssl_issuer_dn AS issuer_dn - FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, applic= ation_name, state, query, wait_event_type, wait_event, xact_start, query_st= art, backend_start, state_change, client_addr, client_hostname, client_port= , backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslb= its, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, = gss_enc, gss_delegation, leader_pid, query_id) + FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, applic= ation_name, state, query, wait_event_type, wait_event, xact_start, query_st= art, backend_start, state_change, client_addr, client_hostname, client_port= , backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslb= its, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, = gss_enc, gss_delegation, leader_pid, query_id, plan_id) WHERE (client_port IS NOT NULL); pg_stat_subscription| SELECT su.oid AS subid, su.subname, diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explai= n.sql index 0bafa870496..d787ad2cda3 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -167,6 +167,10 @@ select explain_filter('explain (verbose) select * from= int8_tbl i8'); select explain_filter('explain (verbose) declare test_cur cursor for selec= t * from int8_tbl'); select explain_filter('explain (verbose) create table test_ctas as select = 1'); =20 +-- Test compute_plan_id +set compute_plan_id =3D on; +select explain_filter('explain (verbose) select * from int8_tbl i8'); + -- Test SERIALIZE option select explain_filter('explain (analyze,buffers off,serialize) select * fr= om int8_tbl i8'); select explain_filter('explain (analyze,serialize text,buffers,timing off)= select * from int8_tbl i8'); diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a782f109982..85a1e0d8d4b 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8406,6 +8406,40 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' W= ITH csv; =20 + + compute_plan_id (enum) + + compute_plan_id configuration parameter= + + + + + Enables in-core computation of a plan identifier. + Plan identifiers can be displayed in the pg_stat_a= ctivity + view or using EXPLAIN. + Note that an external module can alternatively be used if the + in-core plan identifier computation method is not acceptable. + In this case, in-core computation must be always disabled. + Valid values are off (always disabled), + on (always enabled), auto, + which lets modules that utilize plan identifiers enable + it automatically, and regress which + has the same effect as on, except that the + query identifier is not shown in the EXPLAIN ou= tput + in order to facilitate automated regression testing. + The default is auto. + + + + To ensure that only one plan identifier is calculated and + displayed, extensions that calculate plan identifiers should + throw an error if a plan identifier has already been computed. + + + + + log_statement_stats (boolean) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 4e917f159aa..c4ecf20cb2c 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -972,6 +972,22 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss = 11:34 0:00 postgres: ser =20 + + + plan_id bigint + + + Identifier of this backend's most recent query plan. If + state is active this + field shows the identifier of the currently executing query plan. In + all other states, it shows the identifier of last query plan that + was executed. Plan identifiers are not computed by default so this + field will be null unless + parameter is enabled or a third-party module that computes plan + identifiers is configured. + + + query text --=20 2.47.2 --9/rB7VunoObk37cT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0003-Add-pg_stat_plans-contrib-extension.patch" Content-Transfer-Encoding: quoted-printable =46rom 4ffc49cadac2519a7e40dabcf7df5cc888bd3144 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Fri, 31 Jan 2025 13:08:19 +0900 Subject: [PATCH v3 3/3] Add pg_stat_plans contrib extension This extension allows tracking per-plan call counts and execution time, as well as capturing the plan text, aka EXPLAIN (COSTS OFF), for the first execution of a given plan. This utilize the compute_plan_id functionality for tracking different plans. --- doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/pgstatplans.sgml | 425 ++++++++++ contrib/Makefile | 1 + contrib/meson.build | 1 + contrib/pg_stat_plans/Makefile | 29 + contrib/pg_stat_plans/expected/cleanup.out | 1 + contrib/pg_stat_plans/expected/privileges.out | 125 +++ contrib/pg_stat_plans/expected/select.out | 262 ++++++ contrib/pg_stat_plans/meson.build | 43 + contrib/pg_stat_plans/pg_stat_plans--1.0.sql | 33 + contrib/pg_stat_plans/pg_stat_plans.c | 779 ++++++++++++++++++ contrib/pg_stat_plans/pg_stat_plans.conf | 1 + contrib/pg_stat_plans/pg_stat_plans.control | 5 + contrib/pg_stat_plans/sql/cleanup.sql | 1 + contrib/pg_stat_plans/sql/privileges.sql | 59 ++ contrib/pg_stat_plans/sql/select.sql | 67 ++ 17 files changed, 1834 insertions(+) create mode 100644 doc/src/sgml/pgstatplans.sgml create mode 100644 contrib/pg_stat_plans/Makefile create mode 100644 contrib/pg_stat_plans/expected/cleanup.out create mode 100644 contrib/pg_stat_plans/expected/privileges.out create mode 100644 contrib/pg_stat_plans/expected/select.out create mode 100644 contrib/pg_stat_plans/meson.build create mode 100644 contrib/pg_stat_plans/pg_stat_plans--1.0.sql create mode 100644 contrib/pg_stat_plans/pg_stat_plans.c create mode 100644 contrib/pg_stat_plans/pg_stat_plans.conf create mode 100644 contrib/pg_stat_plans/pg_stat_plans.control create mode 100644 contrib/pg_stat_plans/sql/cleanup.sql create mode 100644 contrib/pg_stat_plans/sql/privileges.sql create mode 100644 contrib/pg_stat_plans/sql/select.sql diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index 7c381949a53..4a5a02c7049 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -157,6 +157,7 @@ CREATE EXTENSION extension_name; &pglogicalinspect; &pgprewarm; &pgrowlocks; + &pgstatplans; &pgstatstatements; &pgstattuple; &pgsurgery; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 66e6dccd4c9..b0afb33ce22 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -146,6 +146,7 @@ + diff --git a/doc/src/sgml/pgstatplans.sgml b/doc/src/sgml/pgstatplans.sgml new file mode 100644 index 00000000000..2d5faff15a9 --- /dev/null +++ b/doc/src/sgml/pgstatplans.sgml @@ -0,0 +1,425 @@ + + + + pg_stat_plans — track per-plan call counts, execution times = and EXPLAIN texts + + + pg_stat_plans + + + + The pg_stat_plans module provides a means for + tracking per-plan statistics and plan texts of all SQL statements execut= ed by + a server. + + + + The module must be loaded by adding pg_stat_plans to + in + postgresql.conf, because it requires additional sha= red memory. + This means that a server restart is needed to add or remove the module. + In addition, query and plan identifier calculation must be enabled in or= der for the + module to be active by setting both to + auto or on and + to auto or <= literal>on. + + + + When pg_stat_plans is active, it tracks + statistics across all databases of the server. To access and manipulate + these statistics, the module provides the pg_stat_plans + view and the utility functions pg_stat_plans_reset= and + pg_stat_plans. These are not available globally b= ut + can be enabled for a specific database with + CREATE EXTENSION pg_stat_plans. + + + + The <structname>pg_stat_plans</structname> View + + + The statistics gathered by the module are made available via a + view named pg_stat_plans. This view + contains one row for each distinct combination of database ID, user + ID, whether it's a top-level statement or not, query ID and plan ID + (up to the maximum number of distinct plans that the module can track). + The columns of the view are shown in . + + + + <structname>pg_stat_plans</structname> Columns + + + + + Column Type + + + Description + + + + + + + + userid oid + (references pg_auth= id.oid) + + + OID of user who executed the statement + + + + + + dbid oid + (references pg_da= tabase.oid) + + + OID of database in which the statement was executed + + + + + + toplevel bool + + + True if the query was executed as a top-level statement + (always true if pg_stat_plans.track is set to + top) + + + + + + queryid bigint + + + Hash code to identify identical normalized queries. + + + + + + planid bigint + + + Hash code to identify identical plan shapes. + + + + + + calls bigint + + + Number of times the plan was executed + + + + + + total_exec_time double precision + + + Total time spent executing the plan, in milliseconds + + + + + + plan text + + + Plan text of a representative plan. This is similar to the output of + EXPLAIN (COSTS OFF). Note the plan text will con= tain constant + values of the first plan recorded, but subsequent executions of the + same plan hash code (planid) with differ= ent + constant values will be tracked under the same entry. + + + + +
+ + + For security reasons, only superusers and roles with privileges of the + pg_read_all_stats role are allowed to see the plan t= ext, + queryid and planid + of queries executed by other users. Other users can see the statistics, + however, if the view has been installed in their database. + + + + Plannable queries (that is, SELECT, INSERT<= /command>, + UPDATE, DELETE, and MERG= E) + will have the same planid whenever they have= identical plan + structures according to an internal hash calculation. Typically, two pl= ans will be + considered the same for this purpose if they have the same + EXPLAIN (COSTS OFF) output and are semantically equi= valent except + for the values of literal constants appearing in the query plan. + + + + Note that queries that have not finished executing yet will show in + pg_stat_plans with their plan text, but without + the calls field being incremented. This can be + used to identify the query plan for a currently running statement by jo= ining + pg_stat_activity + with pg_stat_plans, see example usage in + . + + + + Consumers of pg_stat_plans should use + planid in combination with + queryid, dbid, + userid and toplevel + as a stable and reliable identifier for each entry, instead of using its + plan text. However, it is important to understand that there are only l= imited + guarantees around the stability of the planid + hash value. Since the identifier is derived from the plan tree, its va= lue + is a function of, among other things, the internal object identifiers + appearing in this representation. This has some counterintuitive implic= ations. + For example, pg_stat_plans will consider two + apparently-identical plans to be distinct, if they reference a table + that was dropped and recreated between the creation of the two plans. + The hashing process is also sensitive to differences in + machine architecture and other facets of the platform. + Furthermore, it is not safe to assume that planid + will be stable across major versions of PostgreSQL. + + + + Two servers participating in replication based on physical WAL replay c= an + be expected to have identical planid values = for + the same plan. However, logical replication schemes do not promise to + keep replicas identical in all relevant details, so + planid will not be a useful identifier for + accumulating costs across a set of logical replicas. + If in doubt, direct testing is recommended. + + + + Plan texts are stored in shared memory, and limited in length. To incre= ase + the maximum length of stored plan texts you can increase + pg_stat_plans.max_size. This value can be changed for + an individual connection, or set as a server-wide setting. + + + + + Functions + + + + + pg_stat_plans_reset() returns void + + pg_stat_plans_reset + + + + + + pg_stat_plans_reset discards statistics and pla= n texts + gathered so far by pg_stat_plans. + By default, this function can only be executed by superusers. + Access may be granted to others using GRANT. + + + + + + + pg_stat_plans(showplan boolean) returns setof record + + pg_stat_plans + function + + + + + + The pg_stat_plans view is defined in + terms of a function also named pg_stat_plans. + It is possible for clients to call + the pg_stat_plans function directly, and by + specifying showplan :=3D false have plan texts be + omitted (that is, the OUT argument that correspon= ds + to the view's plan column will return nul= ls). This + feature is intended to support external tools that might wish to avo= id + the overhead of repeatedly retrieving plan texts of indeterminate + length. Such tools can instead cache the first plan text observed + for each entry themselves, since that is + all pg_stat_plans itself does, and then retrieve + plan texts only as needed. + + + + + + + + Configuration Parameters + + + + + pg_stat_plans.max (integer) + + pg_stat_plans.max configuration paramete= r + + + + + + pg_stat_plans.max is the maximum number of + plans tracked by the module (i.e., the maximum number of rows + in the pg_stat_plans view). If more distin= ct + plans than that are observed, information about the least-executed + plans is discarded. The default value is 5000. + Only superusers can change this setting. Changing the setting requir= es + a reload of the server. + + + + + + + pg_stat_plans.max_size (integer) + + pg_stat_plans.max_size configuration par= ameter + + + + + + pg_stat_plans.max_size is the maximum length of + each plan text tracked by the module in bytes. Longer plan texts wil= l be truncated. + The default value is 2048 (2kB). + Only superusers can change this setting. + + + + + + + pg_stat_plans.track (enum) + + pg_stat_plans.track configuration parame= ter + + + + + + pg_stat_plans.track controls which plans + are counted by the module. + Specify top to track plans by top-level statement= s (those issued + directly by clients), all to also track nested st= atements + (such as statements invoked within functions), or none to + disable plan statistics collection. + The default value is top. + Only superusers can change this setting. + + + + + + + The module requires additional shared memory proportional to + pg_stat_plans.max for statistics, as well as + pg_stat_plans.max multiplied by + pg_stat_plans.max_size for plan texts. Note that this + memory is only consumed when entries are created, and not if + pg_stat_plans.track is set to none. + + + + These parameters must be set in postgresql.conf. + Typical usage might be: + + +# postgresql.conf +shared_preload_libraries =3D 'pg_stat_plans' + +compute_query_id =3D on +compute_plan_id =3D on +pg_stat_plans.max =3D 10000 +pg_stat_plans.max_size =3D 4096 +pg_stat_plans.track =3D all + + + + + + Sample Output + + +bench=3D# SELECT pg_stat_plans_reset(); + +$ pgbench -i bench +$ pgbench -c10 -t300 bench + +bench=3D# \x +bench=3D# SELECT plan, calls, total_exec_time + FROM pg_stat_plans ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+---------------------------------------------------------= -------- +plan | Update on pgbench_tellers = + + | -> Seq Scan on pgbench_tellers = + + | Filter: (tid =3D 5) +calls | 3000 +total_exec_time | 642.8880919999993 +-[ RECORD 2 ]---+---------------------------------------------------------= -------- +plan | Update on pgbench_branches = + + | -> Seq Scan on pgbench_branches = + + | Filter: (bid =3D 1) +calls | 1813 +total_exec_time | 476.64152700000005 +-[ RECORD 3 ]---+---------------------------------------------------------= -------- +plan | Update on pgbench_branches = + + | -> Index Scan using pgbench_branches_pkey on pgbench_= branches+ + | Index Cond: (bid =3D 1) +calls | 1187 +total_exec_time | 326.1257549999999 +-[ RECORD 4 ]---+---------------------------------------------------------= -------- +plan | Update on pgbench_accounts = + + | -> Index Scan using pgbench_accounts_pkey on pgbench_= accounts+ + | Index Cond: (aid =3D 48793) +calls | 3000 +total_exec_time | 21.664690000000093 +-[ RECORD 5 ]---+---------------------------------------------------------= -------- +plan | Insert on pgbench_history = + + | -> Result +calls | 3000 +total_exec_time | 4.365250999999957 + +session 1: + +bench# SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts; + +session 2: + +bench=3D# SELECT query, plan FROM pg_stat_activity + JOIN pg_stat_plans ON (usesysid =3D userid AND datid =3D dbid AND query_= id =3D queryid AND plan_id =3D planid) + WHERE query LIKE 'SELECT pg_sleep%'; + query | pl= an +-------------------------------------------------------+------------------= ------------------ + SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts; | Aggregate = + + | -> Seq Scan on= pgbench_accounts +(1 row) + + + + + + Authors + + + Lukas Fittl lukas@fittl.com. + + + + diff --git a/contrib/Makefile b/contrib/Makefile index 952855d9b61..8de010afdef 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -34,6 +34,7 @@ SUBDIRS =3D \ pg_freespacemap \ pg_logicalinspect \ pg_prewarm \ + pg_stat_plans \ pg_stat_statements \ pg_surgery \ pg_trgm \ diff --git a/contrib/meson.build b/contrib/meson.build index 1ba73ebd67a..0442ec2644a 100644 --- a/contrib/meson.build +++ b/contrib/meson.build @@ -49,6 +49,7 @@ subdir('pg_freespacemap') subdir('pg_logicalinspect') subdir('pg_prewarm') subdir('pgrowlocks') +subdir('pg_stat_plans') subdir('pg_stat_statements') subdir('pgstattuple') subdir('pg_surgery') diff --git a/contrib/pg_stat_plans/Makefile b/contrib/pg_stat_plans/Makefile new file mode 100644 index 00000000000..e073db95edd --- /dev/null +++ b/contrib/pg_stat_plans/Makefile @@ -0,0 +1,29 @@ +# contrib/pg_stat_plans/Makefile + +MODULE_big =3D pg_stat_plans +OBJS =3D \ + $(WIN32RES) \ + pg_stat_plans.o + +EXTENSION =3D pg_stat_plans +DATA =3D pg_stat_plans--1.0.sql +PGFILEDESC =3D "pg_stat_plans - track per-plan call counts, execution time= s and EXPLAIN texts" + +LDFLAGS_SL +=3D $(filter -lm, $(LIBS)) + +REGRESS_OPTS =3D --temp-config $(top_srcdir)/contrib/pg_stat_plans/pg_stat= _plans.conf +REGRESS =3D select privileges cleanup +# Disabled because these tests require "shared_preload_libraries=3Dpg_stat= _plans", +# which typical installcheck users do not have (e.g. buildfarm clients). +NO_INSTALLCHECK =3D 1 + +ifdef USE_PGXS +PG_CONFIG =3D pg_config +PGXS :=3D $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir =3D contrib/pg_stat_plans +top_builddir =3D ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_stat_plans/expected/cleanup.out b/contrib/pg_stat_p= lans/expected/cleanup.out new file mode 100644 index 00000000000..51565617cef --- /dev/null +++ b/contrib/pg_stat_plans/expected/cleanup.out @@ -0,0 +1 @@ +DROP EXTENSION pg_stat_plans; diff --git a/contrib/pg_stat_plans/expected/privileges.out b/contrib/pg_sta= t_plans/expected/privileges.out new file mode 100644 index 00000000000..3e21d6d7019 --- /dev/null +++ b/contrib/pg_stat_plans/expected/privileges.out @@ -0,0 +1,125 @@ +-- +-- Only superusers and roles with privileges of the pg_read_all_stats role +-- are allowed to see the plan text, queryid and planid of queries execute= d by +-- other users. Other users can see the statistics. +-- +CREATE ROLE regress_stats_superuser SUPERUSER; +CREATE ROLE regress_stats_user1; +CREATE ROLE regress_stats_user2; +GRANT pg_read_all_stats TO regress_stats_user2; +SET ROLE regress_stats_superuser; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + +SELECT 1 AS "ONE"; + ONE=20 +----- + 1 +(1 row) + +SET ROLE regress_stats_user1; +SELECT 1+1 AS "TWO"; + TWO=20 +----- + 2 +(1 row) + +-- +-- A superuser can read all columns of queries executed by others, +-- including plan text, queryid and planid. +-- +SET ROLE regress_stats_superuser; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + rolname | queryid_bool | planid_bool | = plan | calls=20 +-------------------------+--------------+-------------+-------------------= -----------------------------------------------------------------+------- + regress_stats_superuser | t | t | Result = | 1 + regress_stats_superuser | t | t | Result = | 1 + regress_stats_superuser | t | t | Sort = +| 0 + | | | Sort Key: pg_aut= hid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|=20 + | | | -> Hash Join = +|=20 + | | | Hash Cond:= (pg_stat_plans.userid =3D pg_authid.oid) +|=20 + | | | -> Functi= on Scan on pg_stat_plans +|=20 + | | | -> Hash = +|=20 + | | | -> = Seq Scan on pg_authid |=20 + regress_stats_user1 | t | t | Result = | 1 +(4 rows) + +-- +-- regress_stats_user1 has no privileges to read the plan text, queryid +-- or planid of queries executed by others but can see statistics +-- like calls and rows. +-- +SET ROLE regress_stats_user1; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + rolname | queryid_bool | planid_bool | = plan | calls=20 +-------------------------+--------------+-------------+-------------------= -----------------------------------------------------------------+------- + regress_stats_superuser | | | | 1 + regress_stats_superuser | | | | 1 + regress_stats_superuser | | | | 1 + regress_stats_user1 | t | t | Result = | 1 + regress_stats_user1 | t | t | Sort = +| 0 + | | | Sort Key: pg_aut= hid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|=20 + | | | -> Hash Join = +|=20 + | | | Hash Cond:= (pg_stat_plans.userid =3D pg_authid.oid) +|=20 + | | | -> Functi= on Scan on pg_stat_plans +|=20 + | | | -> Hash = +|=20 + | | | -> = Seq Scan on pg_authid |=20 +(5 rows) + +-- +-- regress_stats_user2, with pg_read_all_stats role privileges, can +-- read all columns, including plan text, queryid and planid, of queries +-- executed by others. +-- +SET ROLE regress_stats_user2; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + rolname | queryid_bool | planid_bool | = plan | calls=20 +-------------------------+--------------+-------------+-------------------= -----------------------------------------------------------------+------- + regress_stats_superuser | t | t | Result = | 1 + regress_stats_superuser | t | t | Result = | 1 + regress_stats_superuser | t | t | Sort = +| 1 + | | | Sort Key: pg_aut= hid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|=20 + | | | -> Hash Join = +|=20 + | | | Hash Cond:= (pg_stat_plans.userid =3D pg_authid.oid) +|=20 + | | | -> Functi= on Scan on pg_stat_plans +|=20 + | | | -> Hash = +|=20 + | | | -> = Seq Scan on pg_authid |=20 + regress_stats_user1 | t | t | Result = | 1 + regress_stats_user1 | t | t | Sort = +| 1 + | | | Sort Key: pg_aut= hid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|=20 + | | | -> Hash Join = +|=20 + | | | Hash Cond:= (pg_stat_plans.userid =3D pg_authid.oid) +|=20 + | | | -> Functi= on Scan on pg_stat_plans +|=20 + | | | -> Hash = +|=20 + | | | -> = Seq Scan on pg_authid |=20 + regress_stats_user2 | t | t | Sort = +| 0 + | | | Sort Key: pg_aut= hid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|=20 + | | | -> Hash Join = +|=20 + | | | Hash Cond:= (pg_stat_plans.userid =3D pg_authid.oid) +|=20 + | | | -> Functi= on Scan on pg_stat_plans +|=20 + | | | -> Hash = +|=20 + | | | -> = Seq Scan on pg_authid |=20 +(6 rows) + +-- +-- cleanup +-- +RESET ROLE; +DROP ROLE regress_stats_superuser; +DROP ROLE regress_stats_user1; +DROP ROLE regress_stats_user2; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + diff --git a/contrib/pg_stat_plans/expected/select.out b/contrib/pg_stat_pl= ans/expected/select.out new file mode 100644 index 00000000000..906d8ce90d6 --- /dev/null +++ b/contrib/pg_stat_plans/expected/select.out @@ -0,0 +1,262 @@ +-- +-- SELECT statements +-- +CREATE EXTENSION pg_stat_plans; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + +-- +-- simple statements +-- +SELECT 1 FROM pg_class LIMIT 1; + ?column?=20 +---------- + 1 +(1 row) + +SELECT 1 FROM pg_class WHERE relname =3D 'pg_class'; + ?column?=20 +---------- + 1 +(1 row) + +SET enable_indexscan =3D off; +SELECT 1 FROM pg_class WHERE relname =3D 'pg_class'; + ?column?=20 +---------- + 1 +(1 row) + +SET enable_indexscan =3D on; +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; + plan | calls=20 +--------------------------------------------------------------+------- + Bitmap Heap Scan on pg_class +| 1 + Recheck Cond: (relname =3D 'pg_class'::name) +|=20 + -> Bitmap Index Scan on pg_class_relname_nsp_index +|=20 + Index Cond: (relname =3D 'pg_class'::name) |=20 + Index Only Scan using pg_class_relname_nsp_index on pg_class+| 1 + Index Cond: (relname =3D 'pg_class'::name) |=20 + Limit +| 1 + -> Seq Scan on pg_class |=20 + Result | 1 + Sort +| 0 + Sort Key: pg_stat_plans.plan COLLATE "C" +|=20 + -> Function Scan on pg_stat_plans |=20 +(5 rows) + +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + +-- +-- subplans and CTEs +-- +WITH x AS MATERIALIZED (SELECT 1) +SELECT * FROM x; + ?column?=20 +---------- + 1 +(1 row) + +SELECT a.attname, + (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) + FROM pg_catalog.pg_attrdef d + WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasde= f) + FROM pg_catalog.pg_attribute a + WHERE a.attrelid =3D 'pg_class'::regclass + ORDER BY attnum LIMIT 1; + attname | pg_get_expr=20 +----------+------------- + tableoid |=20 +(1 row) + +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; + plan = | calls=20 +--------------------------------------------------------------------------= -----+------- + CTE Scan on x = +| 1 + CTE x = +|=20 + -> Result = |=20 + Limit = +| 1 + -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a = +|=20 + Index Cond: (attrelid =3D '1259'::oid) = +|=20 + SubPlan 1 = +|=20 + -> Result = +|=20 + One-Time Filter: a.atthasdef = +|=20 + -> Seq Scan on pg_attrdef d = +|=20 + Filter: ((adrelid =3D a.attrelid) AND (adnum =3D a.= attnum)) |=20 + Result = | 1 + Sort = +| 0 + Sort Key: pg_stat_plans.plan COLLATE "C" = +|=20 + -> Function Scan on pg_stat_plans = |=20 +(4 rows) + +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + +-- +-- partitoning +-- +create table lp (a char) partition by list (a); +create table lp_default partition of lp default; +create table lp_ef partition of lp for values in ('e', 'f'); +create table lp_ad partition of lp for values in ('a', 'd'); +create table lp_bc partition of lp for values in ('b', 'c'); +create table lp_g partition of lp for values in ('g'); +create table lp_null partition of lp for values in (null); +select * from lp; + a=20 +--- +(0 rows) + +select * from lp where a > 'a' and a < 'd'; + a=20 +--- +(0 rows) + +select * from lp where a > 'a' and a <=3D 'd'; + a=20 +--- +(0 rows) + +select * from lp where a =3D 'a'; + a=20 +--- +(0 rows) + +select * from lp where 'a' =3D a; /* commuted */ + a=20 +--- +(0 rows) + +select * from lp where a is not null; + a=20 +--- +(0 rows) + +select * from lp where a is null; + a=20 +--- +(0 rows) + +select * from lp where a =3D 'a' or a =3D 'c'; + a=20 +--- +(0 rows) + +select * from lp where a is not null and (a =3D 'a' or a =3D 'c'); + a=20 +--- +(0 rows) + +select * from lp where a <> 'g'; + a=20 +--- +(0 rows) + +select * from lp where a <> 'a' and a <> 'd'; + a=20 +--- +(0 rows) + +select * from lp where a not in ('a', 'd'); + a=20 +--- +(0 rows) + +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; + plan = | calls=20 +--------------------------------------------------------------------------= ------+------- + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + Filter: ((a =3D 'a'::bpchar) OR (a =3D 'c'::bpchar)) = +|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + Filter: ((a =3D 'a'::bpchar) OR (a =3D 'c'::bpchar)) = |=20 + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + Filter: ((a > 'a'::bpchar) AND (a <=3D 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + Filter: ((a > 'a'::bpchar) AND (a <=3D 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_default lp_3 = +|=20 + Filter: ((a > 'a'::bpchar) AND (a <=3D 'd'::bpchar)) = |=20 + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + Filter: ((a IS NOT NULL) AND ((a =3D 'a'::bpchar) OR (a =3D 'c'::= bpchar)))+|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + Filter: ((a IS NOT NULL) AND ((a =3D 'a'::bpchar) OR (a =3D 'c'::= bpchar))) |=20 + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + Filter: (a <> 'g'::bpchar) = +|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + Filter: (a <> 'g'::bpchar) = +|=20 + -> Seq Scan on lp_ef lp_3 = +|=20 + Filter: (a <> 'g'::bpchar) = +|=20 + -> Seq Scan on lp_default lp_4 = +|=20 + Filter: (a <> 'g'::bpchar) = |=20 + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + Filter: (a IS NOT NULL) = +|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + Filter: (a IS NOT NULL) = +|=20 + -> Seq Scan on lp_ef lp_3 = +|=20 + Filter: (a IS NOT NULL) = +|=20 + -> Seq Scan on lp_g lp_4 = +|=20 + Filter: (a IS NOT NULL) = +|=20 + -> Seq Scan on lp_default lp_5 = +|=20 + Filter: (a IS NOT NULL) = |=20 + Append = +| 1 + -> Seq Scan on lp_ad lp_1 = +|=20 + -> Seq Scan on lp_bc lp_2 = +|=20 + -> Seq Scan on lp_ef lp_3 = +|=20 + -> Seq Scan on lp_g lp_4 = +|=20 + -> Seq Scan on lp_null lp_5 = +|=20 + -> Seq Scan on lp_default lp_6 = |=20 + Append = +| 1 + -> Seq Scan on lp_bc lp_1 = +|=20 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_ef lp_2 = +|=20 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_g lp_3 = +|=20 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_default lp_4 = +|=20 + Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) = |=20 + Append = +| 1 + -> Seq Scan on lp_bc lp_1 = +|=20 + Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) = +|=20 + -> Seq Scan on lp_default lp_2 = +|=20 + Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) = |=20 + Append = +| 1 + -> Seq Scan on lp_bc lp_1 = +|=20 + Filter: (a <> ALL ('{a,d}'::bpchar[])) = +|=20 + -> Seq Scan on lp_ef lp_2 = +|=20 + Filter: (a <> ALL ('{a,d}'::bpchar[])) = +|=20 + -> Seq Scan on lp_g lp_3 = +|=20 + Filter: (a <> ALL ('{a,d}'::bpchar[])) = +|=20 + -> Seq Scan on lp_default lp_4 = +|=20 + Filter: (a <> ALL ('{a,d}'::bpchar[])) = |=20 + Result = | 1 + Seq Scan on lp_ad lp = +| 1 + Filter: ('a'::bpchar =3D a) = |=20 + Seq Scan on lp_ad lp = +| 1 + Filter: (a =3D 'a'::bpchar) = |=20 + Seq Scan on lp_null lp = +| 1 + Filter: (a IS NULL) = |=20 + Sort = +| 0 + Sort Key: pg_stat_plans.plan COLLATE "C" = +|=20 + -> Function Scan on pg_stat_plans = |=20 +(14 rows) + +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + t=20 +--- + t +(1 row) + diff --git a/contrib/pg_stat_plans/meson.build b/contrib/pg_stat_plans/meso= n.build new file mode 100644 index 00000000000..3bd884d9601 --- /dev/null +++ b/contrib/pg_stat_plans/meson.build @@ -0,0 +1,43 @@ +# Copyright (c) 2024, PostgreSQL Global Development Group + +pg_stat_plans_sources =3D files( + 'pg_stat_plans.c', +) + +if host_system =3D=3D 'windows' + pg_stat_plans_sources +=3D rc_lib_gen.process(win32ver_rc, extra_args: [ + '--NAME', 'pg_stat_plans', + '--FILEDESC', 'pg_stat_plans - track per-plan call counts, execution t= imes and EXPLAIN texts',]) +endif + +pg_stat_plans =3D shared_module('pg_stat_plans', + pg_stat_plans_sources, + kwargs: contrib_mod_args + { + 'dependencies': contrib_mod_args['dependencies'], + }, +) +contrib_targets +=3D pg_stat_plans + +install_data( + 'pg_stat_plans.control', + 'pg_stat_plans--1.0.sql', + kwargs: contrib_data_args, +) + +tests +=3D { + 'name': 'pg_stat_plans', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), + 'regress': { + 'sql': [ + 'select', + 'privileges', + 'cleanup', + ], + 'regress_args': ['--temp-config', files('pg_stat_plans.conf')], + # Disabled because these tests require + # "shared_preload_libraries=3Dpg_stat_plans", which typical + # runningcheck users do not have (e.g. buildfarm clients). + 'runningcheck': false, + } +} diff --git a/contrib/pg_stat_plans/pg_stat_plans--1.0.sql b/contrib/pg_stat= _plans/pg_stat_plans--1.0.sql new file mode 100644 index 00000000000..f08452b274b --- /dev/null +++ b/contrib/pg_stat_plans/pg_stat_plans--1.0.sql @@ -0,0 +1,33 @@ +/* contrib/pg_stat_plans/pg_stat_plans--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_stat_plans" to load this file. \quit + +-- Register functions. +CREATE FUNCTION pg_stat_plans_reset() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C PARALLEL SAFE; + +CREATE FUNCTION pg_stat_plans(IN showplan boolean, + OUT userid oid, + OUT dbid oid, + OUT toplevel bool, + OUT queryid bigint, + OUT planid bigint, + OUT calls int8, + OUT total_exec_time float8, + OUT plan text +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_plans_1_0' +LANGUAGE C STRICT VOLATILE PARALLEL SAFE; + +-- Register a view on the function for ease of use. +CREATE VIEW pg_stat_plans AS + SELECT * FROM pg_stat_plans(true); + +GRANT SELECT ON pg_stat_plans TO PUBLIC; + +-- Don't want this to be available to non-superusers. +REVOKE ALL ON FUNCTION pg_stat_plans_reset() FROM PUBLIC; diff --git a/contrib/pg_stat_plans/pg_stat_plans.c b/contrib/pg_stat_plans/= pg_stat_plans.c new file mode 100644 index 00000000000..5fa31fcbf3e --- /dev/null +++ b/contrib/pg_stat_plans/pg_stat_plans.c @@ -0,0 +1,779 @@ +/*------------------------------------------------------------------------= -- + * + * pg_stat_plans.c + * Track per-plan call counts, execution times and EXPLAIN texts + * across a whole database cluster. + * + * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * contrib/pg_stat_plans/pg_stat_plans.c + * + * -----------------------------------------------------------------------= -- + */ + +#include "postgres.h" + +#include "access/parallel.h" +#include "catalog/pg_authid.h" +#include "commands/explain.h" +#include "common/hashfn.h" +#include "funcapi.h" +#include "mb/pg_wchar.h" +#include "nodes/queryjumble.h" +#include "pgstat.h" +#include "optimizer/planner.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/guc.h" +#include "utils/pgstat_internal.h" +#include "utils/snapmgr.h" + +PG_MODULE_MAGIC; + +/* Current nesting depth of planner/ExecutorRun/ProcessUtility calls */ +static int nesting_level =3D 0; + +/* Saved hook values */ +static planner_hook_type prev_planner_hook =3D NULL; +static ExecutorStart_hook_type prev_ExecutorStart =3D NULL; +static ExecutorRun_hook_type prev_ExecutorRun =3D NULL; +static ExecutorFinish_hook_type prev_ExecutorFinish =3D NULL; +static ExecutorEnd_hook_type prev_ExecutorEnd =3D NULL; + +/*---- GUC variables ----*/ + +typedef enum +{ + PGSP_TRACK_NONE, /* track no plans */ + PGSP_TRACK_TOP, /* only plans for top level statements */ + PGSP_TRACK_ALL, /* all plans, including for nested statements */ +} PGSPTrackLevel; + +static const struct config_enum_entry track_options[] =3D +{ + {"none", PGSP_TRACK_NONE, false}, + {"top", PGSP_TRACK_TOP, false}, + {"all", PGSP_TRACK_ALL, false}, + {NULL, 0, false} +}; + +static int pgsp_max =3D 5000; /* max # plans to track */ +static int pgsp_max_size =3D 2048; /* max size of plan text to track (in + * bytes) */ +static int pgsp_track =3D PGSP_TRACK_TOP; /* tracking level */ + +#define pgsp_enabled(level) \ + (!IsParallelWorker() && \ + (compute_plan_id !=3D COMPUTE_PLAN_ID_OFF) && \ + (pgsp_track =3D=3D PGSP_TRACK_ALL || \ + (pgsp_track =3D=3D PGSP_TRACK_TOP && (level) =3D=3D 0))) + +#define USAGE_INCREASE 0.5 /* increase by this each time we report + * stats */ +#define USAGE_DECREASE_FACTOR (0.99) /* decreased every + * pgstat_dealloc_plans */ +#define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */ + +/*---- Function declarations ----*/ + +PG_FUNCTION_INFO_V1(pg_stat_plans_reset); +PG_FUNCTION_INFO_V1(pg_stat_plans_1_0); + +/* Structures for statistics of plans */ +typedef struct PgStatShared_PlanInfo +{ + /* key elements that identify a plan (together with the dboid) */ + uint64 planid; + uint64 queryid; + Oid userid; /* userid is tracked to allow users to see + * their own query plans */ + bool toplevel; /* query executed at top level */ + + dsa_pointer plan_text; /* pointer to DSA memory containing plan text */ + int plan_encoding; /* plan text encoding */ +} PgStatShared_PlanInfo; + +typedef struct PgStat_StatPlanEntry +{ + PgStat_Counter exec_count; + double exec_time; + double usage; /* Usage factor of the entry, used to + * prioritize which plans to age out */ + + /* Only used in shared structure, not in local pending stats */ + PgStatShared_PlanInfo info; +} PgStat_StatPlanEntry; + +typedef struct PgStatShared_Plan +{ + PgStatShared_Common header; + PgStat_StatPlanEntry stats; +} PgStatShared_Plan; + +static bool plan_stats_flush_cb(PgStat_EntryRef *entry_ref, bool nowait); + +static const PgStat_KindInfo plan_stats =3D { + .name =3D "plan_stats", + .fixed_amount =3D false, + + /* + * We currently don't write to a file since plan texts would get lost (and + * just the stats on their own aren't that useful) + */ + .write_to_file =3D false, + + /* + * Plan statistics are available system-wide to simplify monitoring + * scripts + */ + .accessed_across_databases =3D true, + + .shared_size =3D sizeof(PgStatShared_Plan), + .shared_data_off =3D offsetof(PgStatShared_Plan, stats), + .shared_data_len =3D sizeof(((PgStatShared_Plan *) 0)->stats), + .pending_size =3D sizeof(PgStat_StatPlanEntry), + .flush_pending_cb =3D plan_stats_flush_cb, +}; + +/* + * Compute stats entry idx from query ID and plan ID with an 8-byte hash. + * + * Whilst we could theorically just use the plan ID here, we intentionally + * add the query ID into the mix to ease interpreting the data in combinat= ion + * with pg_stat_statements. + */ +#define PGSTAT_PLAN_IDX(query_id, plan_id, user_id, toplevel) hash_combine= 64(toplevel, hash_combine64(query_id, hash_combine64(plan_id, user_id))) + +/* + * Kind ID reserved for statistics of plans. + */ +#define PGSTAT_KIND_PLANS PGSTAT_KIND_EXPERIMENTAL /* TODO: Assign */ + +/* + * Callback for stats handling + */ +static bool +plan_stats_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) +{ + PgStat_StatPlanEntry *localent; + PgStatShared_Plan *shfuncent; + + localent =3D (PgStat_StatPlanEntry *) entry_ref->pending; + shfuncent =3D (PgStatShared_Plan *) entry_ref->shared_stats; + + if (!pgstat_lock_entry(entry_ref, nowait)) + return false; + + shfuncent->stats.exec_count +=3D localent->exec_count; + shfuncent->stats.exec_time +=3D localent->exec_time; + shfuncent->stats.usage +=3D localent->usage; + + pgstat_unlock_entry(entry_ref); + + return true; +} + +static char * +pgsp_explain_plan(QueryDesc *queryDesc) +{ + ExplainState *es; + StringInfo es_str; + + es =3D NewExplainState(); + es_str =3D es->str; + + /* + * We turn off COSTS since identical planids may have very different + * costs, and it could be misleading to only show the first recorded + * plan's costs. + */ + es->costs =3D false; + es->format =3D EXPLAIN_FORMAT_TEXT; + + ExplainBeginOutput(es); + ExplainPrintPlan(es, queryDesc); + ExplainEndOutput(es); + + return es_str->data; +} + +static void +pgstat_gc_plan_memory() +{ + dshash_seq_status hstat; + PgStatShared_HashEntry *p; + + /* dshash entry is not modified, take shared lock */ + dshash_seq_init(&hstat, pgStatLocal.shared_hash, false); + while ((p =3D dshash_seq_next(&hstat)) !=3D NULL) + { + PgStatShared_Common *header; + PgStat_StatPlanEntry *statent; + + if (!p->dropped || p->key.kind !=3D PGSTAT_KIND_PLANS) + continue; + + header =3D dsa_get_address(pgStatLocal.dsa, p->body); + + if (!LWLockConditionalAcquire(&header->lock, LW_EXCLUSIVE)) + continue; + + statent =3D (PgStat_StatPlanEntry *) pgstat_get_entry_data(PGSTAT_KIND_P= LANS, header); + + /* + * Clean up this entry's plan text allocation, if we haven't done so + * already + */ + if (DsaPointerIsValid(statent->info.plan_text)) + { + dsa_free(pgStatLocal.dsa, statent->info.plan_text); + statent->info.plan_text =3D InvalidDsaPointer; + + /* Allow removal of the shared stats entry */ + pg_atomic_fetch_sub_u32(&p->refcount, 1); + } + + LWLockRelease(&header->lock); + } + dshash_seq_term(&hstat); + + /* Encourage other backends to clean up dropped entry refs */ + pgstat_request_entry_refs_gc(); +} + +typedef struct PlanDeallocEntry +{ + PgStat_HashKey key; + double usage; +} PlanDeallocEntry; + +/* + * list sort comparator for sorting into decreasing usage order + */ +static int +entry_cmp_lru(const union ListCell *lhs, const union ListCell *rhs) +{ + double l_usage =3D ((PlanDeallocEntry *) lfirst(lhs))->usage; + double r_usage =3D ((PlanDeallocEntry *) lfirst(rhs))->usage; + + if (l_usage > r_usage) + return -1; + else if (l_usage < r_usage) + return +1; + else + return 0; +} + +static void +pgstat_dealloc_plans() +{ + dshash_seq_status hstat; + PgStatShared_HashEntry *p; + List *entries =3D NIL; + ListCell *lc; + int nvictims; + + /* dshash entry is not modified, take shared lock */ + dshash_seq_init(&hstat, pgStatLocal.shared_hash, false); + while ((p =3D dshash_seq_next(&hstat)) !=3D NULL) + { + PgStatShared_Common *header; + PgStat_StatPlanEntry *statent; + PlanDeallocEntry *entry; + + if (p->dropped || p->key.kind !=3D PGSTAT_KIND_PLANS) + continue; + + header =3D dsa_get_address(pgStatLocal.dsa, p->body); + + if (!LWLockConditionalAcquire(&header->lock, LW_EXCLUSIVE)) + continue; + + statent =3D (PgStat_StatPlanEntry *) pgstat_get_entry_data(PGSTAT_KIND_P= LANS, header); + statent->usage *=3D USAGE_DECREASE_FACTOR; + + entry =3D palloc(sizeof(PlanDeallocEntry)); + entry->key =3D p->key; + entry->usage =3D statent->usage; + + LWLockRelease(&header->lock); + + entries =3D lappend(entries, entry); + } + dshash_seq_term(&hstat); + + /* Sort by usage ascending (lowest used entries are last) */ + list_sort(entries, entry_cmp_lru); + + /* At a minimum, deallocate 10 entries to make it worth our while */ + nvictims =3D Max(10, list_length(entries) * USAGE_DEALLOC_PERCENT / 100); + nvictims =3D Min(nvictims, list_length(entries)); + + /* Actually drop the entries */ + for_each_from(lc, entries, list_length(entries) - nvictims) + { + PlanDeallocEntry *entry =3D lfirst(lc); + + pgstat_drop_entry(entry->key.kind, entry->key.dboid, entry->key.objid); + } + + /* Clean up our working memory immediately */ + foreach(lc, entries) + { + PlanDeallocEntry *entry =3D lfirst(lc); + + pfree(entry); + } + pfree(entries); +} + +static void +pgstat_gc_plans() +{ + dshash_seq_status hstat; + PgStatShared_HashEntry *p; + bool have_dropped_entries =3D false; + size_t plan_entry_count =3D 0; + + /* TODO: Prevent concurrent GC cycles - flag an active GC run somehow */ + + /* + * Count our active entries, and whether there are any dropped entries we + * may need to clean up at the end. + */ + dshash_seq_init(&hstat, pgStatLocal.shared_hash, false); + while ((p =3D dshash_seq_next(&hstat)) !=3D NULL) + { + if (p->key.kind !=3D PGSTAT_KIND_PLANS) + continue; + + if (p->dropped) + have_dropped_entries =3D true; + else + plan_entry_count++; + } + dshash_seq_term(&hstat); + + /* + * If we're over the limit, delete entries with lowest usage factor. + */ + if (plan_entry_count > pgsp_max) + { + pgstat_dealloc_plans(); + have_dropped_entries =3D true; /* Assume we did some work */ + } + + /* If there are dropped entries, clean up their plan memory if needed */ + if (have_dropped_entries) + pgstat_gc_plan_memory(); +} + +static void +pgstat_report_plan_stats(QueryDesc *queryDesc, + PgStat_Counter exec_count, + double exec_time) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Plan *shstatent; + PgStat_StatPlanEntry *statent; + bool newly_created; + uint64 queryId =3D queryDesc->plannedstmt->queryId; + uint64 planId =3D queryDesc->plannedstmt->planId; + Oid userid =3D GetUserId(); + bool toplevel =3D (nesting_level =3D=3D 0); + + entry_ref =3D pgstat_prep_pending_entry(PGSTAT_KIND_PLANS, MyDatabaseId, + PGSTAT_PLAN_IDX(queryId, planId, userid, toplevel), &newly_cre= ated); + + shstatent =3D (PgStatShared_Plan *) entry_ref->shared_stats; + statent =3D &shstatent->stats; + + if (newly_created) + { + char *plan =3D pgsp_explain_plan(queryDesc); + size_t plan_size =3D Min(strlen(plan), pgsp_max_size); + + (void) pgstat_lock_entry(entry_ref, false); + + /* + * We may be over the limit, so run GC now before saving entry (we do + * this whilst holding the lock on the new entry so we don't remove it + * by accident) + */ + pgstat_gc_plans(); + + shstatent->stats.info.planid =3D planId; + shstatent->stats.info.queryid =3D queryId; + shstatent->stats.info.userid =3D userid; + shstatent->stats.info.toplevel =3D toplevel; + shstatent->stats.info.plan_text =3D dsa_allocate(pgStatLocal.dsa, plan_s= ize); + strlcpy(dsa_get_address(pgStatLocal.dsa, shstatent->stats.info.plan_text= ), plan, plan_size); + + shstatent->stats.info.plan_encoding =3D GetDatabaseEncoding(); + + /* + * Increase refcount here so entry can't get released without us + * dropping the plan text + */ + pg_atomic_fetch_add_u32(&entry_ref->shared_entry->refcount, 1); + + pgstat_unlock_entry(entry_ref); + + pfree(plan); + } + + statent->exec_count +=3D exec_count; + statent->exec_time +=3D exec_time; + statent->usage +=3D USAGE_INCREASE; +} + +/* + * Planner hook: forward to regular planner, but increase plan count and + * record query plan if needed. + */ +static PlannedStmt * +pgsp_planner(Query *parse, + const char *query_string, + int cursorOptions, + ParamListInfo boundParams) +{ + PlannedStmt *result; + + /* + * Increment the nesting level, to ensure that functions evaluated during + * planning are not seen as top-level calls. + */ + nesting_level++; + PG_TRY(); + { + if (prev_planner_hook) + result =3D prev_planner_hook(parse, query_string, cursorOptions, + boundParams); + else + result =3D standard_planner(parse, query_string, cursorOptions, + boundParams); + } + PG_FINALLY(); + { + nesting_level--; + } + PG_END_TRY(); + + return result; +} + +/* + * ExecutorStart hook: start up tracking if needed + */ +static void +pgsp_ExecutorStart(QueryDesc *queryDesc, int eflags) +{ + uint64 queryId =3D queryDesc->plannedstmt->queryId; + uint64 planId =3D queryDesc->plannedstmt->planId; + + if (prev_ExecutorStart) + prev_ExecutorStart(queryDesc, eflags); + else + standard_ExecutorStart(queryDesc, eflags); + + if (queryId !=3D UINT64CONST(0) && planId !=3D UINT64CONST(0) && + pgsp_enabled(nesting_level)) + { + /* + * Record initial entry now, so plan text is available for currently + * running queries + */ + pgstat_report_plan_stats(queryDesc, + 0, /* executions are counted in + * pgsp_ExecutorEnd */ + 0.0); + + /* + * Set up to track total elapsed time in ExecutorRun. Make sure the + * space is allocated in the per-query context so it will go away at + * ExecutorEnd. + */ + if (queryDesc->totaltime =3D=3D NULL) + { + MemoryContext oldcxt; + + oldcxt =3D MemoryContextSwitchTo(queryDesc->estate->es_query_cxt); + queryDesc->totaltime =3D InstrAlloc(1, INSTRUMENT_ALL, false); + MemoryContextSwitchTo(oldcxt); + } + } +} + +/* + * ExecutorRun hook: all we need do is track nesting depth + */ +static void +pgsp_ExecutorRun(QueryDesc *queryDesc, ScanDirection direction, uint64 cou= nt) +{ + nesting_level++; + PG_TRY(); + { + if (prev_ExecutorRun) + prev_ExecutorRun(queryDesc, direction, count); + else + standard_ExecutorRun(queryDesc, direction, count); + } + PG_FINALLY(); + { + nesting_level--; + } + PG_END_TRY(); +} + +/* + * ExecutorFinish hook: all we need do is track nesting depth + */ +static void +pgsp_ExecutorFinish(QueryDesc *queryDesc) +{ + nesting_level++; + PG_TRY(); + { + if (prev_ExecutorFinish) + prev_ExecutorFinish(queryDesc); + else + standard_ExecutorFinish(queryDesc); + } + PG_FINALLY(); + { + nesting_level--; + } + PG_END_TRY(); +} + +/* + * ExecutorEnd hook: store results if needed + */ +static void +pgsp_ExecutorEnd(QueryDesc *queryDesc) +{ + uint64 queryId =3D queryDesc->plannedstmt->queryId; + uint64 planId =3D queryDesc->plannedstmt->planId; + + if (queryId !=3D UINT64CONST(0) && planId !=3D UINT64CONST(0) && + queryDesc->totaltime && pgsp_enabled(nesting_level)) + { + /* + * Make sure stats accumulation is done. (Note: it's okay if several + * levels of hook all do this.) + */ + InstrEndLoop(queryDesc->totaltime); + + pgstat_report_plan_stats(queryDesc, + 1, + queryDesc->totaltime->total * 1000.0 /* convert to msec */ ); + } + + if (prev_ExecutorEnd) + prev_ExecutorEnd(queryDesc); + else + standard_ExecutorEnd(queryDesc); +} + +/* + * Module load callback + */ +void +_PG_init(void) +{ + /* + * In order to register for shared memory stats, we have to be loaded via + * shared_preload_libraries. If not, fall out without hooking into any of + * the main system. (We don't throw error here because it seems useful to + * allow the pg_stat_plans functions to be created even when the module + * isn't active. The functions must protect themselves against being + * called then, however.) + */ + if (!process_shared_preload_libraries_in_progress) + return; + + /* + * Inform the postmaster that we want to enable query_id calculation if + * compute_query_id is set to auto, as well as plan_id calculation if + * compute_plan_id is set to auto. + */ + EnableQueryId(); + EnablePlanId(); + + /* + * Define (or redefine) custom GUC variables. + */ + DefineCustomIntVariable("pg_stat_plans.max", + "Sets the maximum number of plans tracked by pg_stat_plans in share= d memory.", + NULL, + &pgsp_max, + 5000, + 100, + INT_MAX / 2, + PGC_SIGHUP, + 0, + NULL, + NULL, + NULL); + + DefineCustomIntVariable("pg_stat_plans.max_size", + "Sets the maximum size of plan texts tracked by pg_stat_plans in sh= ared memory.", + NULL, + &pgsp_max_size, + 2048, + 100, + 1048576, /* 1MB hard limit */ + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + DefineCustomEnumVariable("pg_stat_plans.track", + "Selects which plans are tracked by pg_stat_plans.", + NULL, + &pgsp_track, + PGSP_TRACK_TOP, + track_options, + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + MarkGUCPrefixReserved("pg_stat_plans"); + + /* + * Install hooks. + */ + prev_planner_hook =3D planner_hook; + planner_hook =3D pgsp_planner; + prev_ExecutorStart =3D ExecutorStart_hook; + ExecutorStart_hook =3D pgsp_ExecutorStart; + prev_ExecutorRun =3D ExecutorRun_hook; + ExecutorRun_hook =3D pgsp_ExecutorRun; + prev_ExecutorFinish =3D ExecutorFinish_hook; + ExecutorFinish_hook =3D pgsp_ExecutorFinish; + prev_ExecutorEnd =3D ExecutorEnd_hook; + ExecutorEnd_hook =3D pgsp_ExecutorEnd; + + pgstat_register_kind(PGSTAT_KIND_PLANS, &plan_stats); +} + +static bool +match_plans_entries(PgStatShared_HashEntry *entry, Datum match_data) +{ + return entry->key.kind =3D=3D PGSTAT_KIND_PLANS; +} + +/* + * Reset statement statistics. + */ +Datum +pg_stat_plans_reset(PG_FUNCTION_ARGS) +{ + pgstat_drop_matching_entries(match_plans_entries, 0); + + /* Free plan text memory and allow cleanup of dropped entries */ + pgstat_gc_plan_memory(); + + PG_RETURN_VOID(); +} + +#define PG_STAT_PLANS_COLS 8 + +Datum +pg_stat_plans_1_0(PG_FUNCTION_ARGS) +{ + bool showplan =3D PG_GETARG_BOOL(0); + ReturnSetInfo *rsinfo =3D (ReturnSetInfo *) fcinfo->resultinfo; + Oid userid =3D GetUserId(); + bool is_allowed_role =3D false; + + dshash_seq_status hstat; + PgStatShared_HashEntry *p; + + /* + * Superusers or roles with the privileges of pg_read_all_stats members + * are allowed + */ + is_allowed_role =3D has_privs_of_role(userid, ROLE_PG_READ_ALL_STATS); + + /* stats kind must be registered already */ + if (!pgstat_get_kind_info(PGSTAT_KIND_PLANS)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("pg_stat_plans must be loaded via \"shared_preload_libraries\"= "))); + + InitMaterializedSRF(fcinfo, 0); + + /* dshash entry is not modified, take shared lock */ + dshash_seq_init(&hstat, pgStatLocal.shared_hash, false); + while ((p =3D dshash_seq_next(&hstat)) !=3D NULL) + { + PgStat_StatPlanEntry *statent; + Datum values[PG_STAT_PLANS_COLS]; + bool nulls[PG_STAT_PLANS_COLS]; + int i =3D 0; + + if (p->dropped || p->key.kind !=3D PGSTAT_KIND_PLANS) + continue; + + memset(values, 0, sizeof(values)); + memset(nulls, 0, sizeof(nulls)); + + statent =3D pgstat_get_entry_data(p->key.kind, dsa_get_address(pgStatLoc= al.dsa, p->body)); + + values[i++] =3D ObjectIdGetDatum(statent->info.userid); + values[i++] =3D ObjectIdGetDatum(p->key.dboid); + values[i++] =3D BoolGetDatum(statent->info.toplevel); + if (is_allowed_role || statent->info.userid =3D=3D userid) + { + int64 queryid =3D statent->info.queryid; + int64 planid =3D statent->info.planid; + + values[i++] =3D Int64GetDatumFast(queryid); + values[i++] =3D Int64GetDatumFast(planid); + } + else + { + nulls[i++] =3D true; + nulls[i++] =3D true; + } + values[i++] =3D Int64GetDatumFast(statent->exec_count); + values[i++] =3D Float8GetDatumFast(statent->exec_time); + + if (showplan && (is_allowed_role || statent->info.userid =3D=3D userid)) + { + char *pstr =3D DsaPointerIsValid(statent->info.plan_text) ? dsa_get_= address(pgStatLocal.dsa, statent->info.plan_text) : NULL; + + if (pstr) + { + char *enc =3D pg_any_to_server(pstr, strlen(pstr), statent->info.pl= an_encoding); + + values[i++] =3D CStringGetTextDatum(enc); + + if (enc !=3D pstr) + pfree(enc); + } + else + { + nulls[i++] =3D true; + } + } + else if (showplan) + { + values[i++] =3D CStringGetTextDatum(""); + } + else + { + nulls[i++] =3D true; + } + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + } + dshash_seq_term(&hstat); + + return (Datum) 0; +} diff --git a/contrib/pg_stat_plans/pg_stat_plans.conf b/contrib/pg_stat_pla= ns/pg_stat_plans.conf new file mode 100644 index 00000000000..6750b3e2cc0 --- /dev/null +++ b/contrib/pg_stat_plans/pg_stat_plans.conf @@ -0,0 +1 @@ +shared_preload_libraries =3D 'pg_stat_plans' diff --git a/contrib/pg_stat_plans/pg_stat_plans.control b/contrib/pg_stat_= plans/pg_stat_plans.control new file mode 100644 index 00000000000..4db3a47239b --- /dev/null +++ b/contrib/pg_stat_plans/pg_stat_plans.control @@ -0,0 +1,5 @@ +# pg_stat_plans extension +comment =3D 'track per-plan call counts, execution times and EXPLAIN texts' +default_version =3D '1.0' +module_pathname =3D '$libdir/pg_stat_plans' +relocatable =3D true diff --git a/contrib/pg_stat_plans/sql/cleanup.sql b/contrib/pg_stat_plans/= sql/cleanup.sql new file mode 100644 index 00000000000..51565617cef --- /dev/null +++ b/contrib/pg_stat_plans/sql/cleanup.sql @@ -0,0 +1 @@ +DROP EXTENSION pg_stat_plans; diff --git a/contrib/pg_stat_plans/sql/privileges.sql b/contrib/pg_stat_pla= ns/sql/privileges.sql new file mode 100644 index 00000000000..aaad72a6553 --- /dev/null +++ b/contrib/pg_stat_plans/sql/privileges.sql @@ -0,0 +1,59 @@ +-- +-- Only superusers and roles with privileges of the pg_read_all_stats role +-- are allowed to see the plan text, queryid and planid of queries execute= d by +-- other users. Other users can see the statistics. +-- + +CREATE ROLE regress_stats_superuser SUPERUSER; +CREATE ROLE regress_stats_user1; +CREATE ROLE regress_stats_user2; +GRANT pg_read_all_stats TO regress_stats_user2; + +SET ROLE regress_stats_superuser; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; +SELECT 1 AS "ONE"; + +SET ROLE regress_stats_user1; +SELECT 1+1 AS "TWO"; + +-- +-- A superuser can read all columns of queries executed by others, +-- including plan text, queryid and planid. +-- + +SET ROLE regress_stats_superuser; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + +-- +-- regress_stats_user1 has no privileges to read the plan text, queryid +-- or planid of queries executed by others but can see statistics +-- like calls and rows. +-- + +SET ROLE regress_stats_user1; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + +-- +-- regress_stats_user2, with pg_read_all_stats role privileges, can +-- read all columns, including plan text, queryid and planid, of queries +-- executed by others. +-- + +SET ROLE regress_stats_user2; +SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS plani= d_bool, ss.plan, ss.calls + FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid =3D r.oid + ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls; + +-- +-- cleanup +-- + +RESET ROLE; +DROP ROLE regress_stats_superuser; +DROP ROLE regress_stats_user1; +DROP ROLE regress_stats_user2; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_plans/sql/select.sql b/contrib/pg_stat_plans/s= ql/select.sql new file mode 100644 index 00000000000..f0e803ad70c --- /dev/null +++ b/contrib/pg_stat_plans/sql/select.sql @@ -0,0 +1,67 @@ +-- +-- SELECT statements +-- + +CREATE EXTENSION pg_stat_plans; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + +-- +-- simple statements +-- + +SELECT 1 FROM pg_class LIMIT 1; + +SELECT 1 FROM pg_class WHERE relname =3D 'pg_class'; + +SET enable_indexscan =3D off; +SELECT 1 FROM pg_class WHERE relname =3D 'pg_class'; +SET enable_indexscan =3D on; + +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + +-- +-- subplans and CTEs +-- + +WITH x AS MATERIALIZED (SELECT 1) +SELECT * FROM x; + +SELECT a.attname, + (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) + FROM pg_catalog.pg_attrdef d + WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasde= f) + FROM pg_catalog.pg_attribute a + WHERE a.attrelid =3D 'pg_class'::regclass + ORDER BY attnum LIMIT 1; + +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; + +-- +-- partitoning +-- + +create table lp (a char) partition by list (a); +create table lp_default partition of lp default; +create table lp_ef partition of lp for values in ('e', 'f'); +create table lp_ad partition of lp for values in ('a', 'd'); +create table lp_bc partition of lp for values in ('b', 'c'); +create table lp_g partition of lp for values in ('g'); +create table lp_null partition of lp for values in (null); + +select * from lp; +select * from lp where a > 'a' and a < 'd'; +select * from lp where a > 'a' and a <=3D 'd'; +select * from lp where a =3D 'a'; +select * from lp where 'a' =3D a; /* commuted */ +select * from lp where a is not null; +select * from lp where a is null; +select * from lp where a =3D 'a' or a =3D 'c'; +select * from lp where a is not null and (a =3D 'a' or a =3D 'c'); +select * from lp where a <> 'g'; +select * from lp where a <> 'a' and a <> 'd'; +select * from lp where a not in ('a', 'd'); + +SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C"; +SELECT pg_stat_plans_reset() IS NOT NULL AS t; --=20 2.47.2 --9/rB7VunoObk37cT-- --9jON1huSk3geJfGO Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmecU24ACgkQnvQgOdby QH1vURAAi/JTiQw11bNZkdAUy0UoO5ti9WRfwwdhiJG+KW7QvAoKSLOdE+27fqIl KC/1QpKybBq3IMmQeix0abtMexwFsQzWzW4zFNENri0jsLJlFG/Q6py9pnboqzTo TF47DqddSm8/5iouoQXE7vs65j6CGbbfd6uhz1ArPdwiSlbX76jIHA4Ry+in+juG bwR4ys4n6aZt/Hl33zWKzrgDyuD0U+HnXtUV5vQQ3gx+NDmCagc77E62fVXzycHy zwCA/mxLIN16WbWNqdFnZg2DPfi95haEB9ntFwdcX3BXfEUpvjBvOIzO9sKmC2PE aVqGy0ouY0bcvbFlQz4+GbpemPu9c2WXLekcQqpwzTZx+zmEdvlXUCXswcvhL0JP 89xs/Zh4o9mSWLdJDxGuzJKhqUOiQVmKXnaHrDiSBQDe3zqGbRND4M8llaMYl+cw 8Q8bkAedMuki71JjuZRCB6u6LuI9HwzpJtqJ/dyACgDfc7zfHg9eGTq3sNOCqnZX U7dz0TIWwdljqpRefF1PV7JC0SRQ7pAumIYtI4Kc512qv9KNvtNkVVjekEOKaMSp 1/pAcv5SAHVl+THEFa1Popcsc6l7fq94KCFabldbx57QuBNo3i2PWGNdArbzy9dm OMPrpVe774Qj32SYjSMs2sLUnZLvaTXiO7tfekPyzJ2Ww26zJkY= =aBYf -----END PGP SIGNATURE----- --9jON1huSk3geJfGO--