I create a patch that outputs affected rows in EXPLAIN that occur by INSERT/UPDATE/DELETE. Despite the fact that commands in EXPLAIN ANALYZE query are executed as usual, EXPLAIN doesn't show outputting affected rows as in these commands. The patch fixes this problem.
Examples: explain analyze insert into a values (1); QUERY PLAN ------------------------------------------------------------------------------------------ Insert on a (cost=0.00..0.01 rows=0 width=0) (actual time=0.076..0.077 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1) Planning Time: 0.025 ms Execution Time: 0.412 ms (4 rows) INSERT 0 1 QUERY PLAN ------------------------------------------------------------------------------------------------------ Update on a (cost=0.00..35.50 rows=0 width=0) (actual time=0.059..0.060 rows=0 loops=1) -> Seq Scan on a (cost=0.00..35.50 rows=2550 width=10) (actual time=0.012..0.013 rows=7 loops=1) Planning Time: 0.142 ms Execution Time: 0.666 ms (4 rows) UPDATE 7 explain analyze delete from a where n = 1; QUERY PLAN --------------------------------------------------------------------------------------------------- Delete on a (cost=0.00..41.88 rows=0 width=0) (actual time=0.147..0.147 rows=0 loops=1) -> Seq Scan on a (cost=0.00..41.88 rows=13 width=6) (actual time=0.120..0.123 rows=7 loops=1) Filter: (n = 1) Planning Time: 1.073 ms Execution Time: 0.178 ms (5 rows) DELETE 7 EXPLAIN queries without ANALYZE don't affect rows, so the output number is 0. explain update a set n = 2; QUERY PLAN ------------------------------------------------------------ Update on a (cost=0.00..35.50 rows=0 width=0) -> Seq Scan on a (cost=0.00..35.50 rows=2550 width=10) (2 rows) UPDATE 0 Maybe there is no need to add this row when EXPLAIN has no ANALYZE. So it is a discussion question. Also haven't fixed regress tests yet. Regards, Damir Belyalov Postgres Professional
From c6cbc6fa9ddf24f29bc19ff115224dd76e351db1 Mon Sep 17 00:00:00 2001 From: Damir Belyalov <d.belya...@postgrespro.ru> Date: Tue, 5 Sep 2023 15:04:01 +0300 Subject: [PATCH] Output affected rows in EXPLAIN. --- src/backend/commands/explain.c | 10 +++++++++- src/backend/tcop/cmdtag.c | 2 +- src/backend/tcop/pquery.c | 8 +++++++- src/backend/tcop/utility.c | 27 ++++++++++++++++++++++++++- src/bin/psql/common.c | 5 +++-- src/include/commands/explain.h | 3 ++- src/include/tcop/cmdtag.h | 1 + src/include/tcop/cmdtaglist.h | 3 +++ src/interfaces/libpq/fe-exec.c | 4 +++- 9 files changed, 55 insertions(+), 8 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 8570b14f62..453e545ba5 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -162,7 +162,7 @@ static void escape_yaml(StringInfo buf, const char *str); */ void ExplainQuery(ParseState *pstate, ExplainStmt *stmt, - ParamListInfo params, DestReceiver *dest) + ParamListInfo params, DestReceiver *dest, uint64 *processed) { ExplainState *es = NewExplainState(); TupOutputState *tstate; @@ -173,6 +173,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, bool timing_set = false; bool summary_set = false; + if (processed) + *processed = 0; + /* Parse options list. */ foreach(lc, stmt->options) { @@ -311,6 +314,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, end_tup_output(tstate); pfree(es->str->data); + + if (processed) + *processed = es->es_processed; } /* @@ -649,6 +655,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, */ INSTR_TIME_SET_CURRENT(starttime); + es->es_processed += queryDesc->estate->es_processed; + ExecutorEnd(queryDesc); FreeQueryDesc(queryDesc); diff --git a/src/backend/tcop/cmdtag.c b/src/backend/tcop/cmdtag.c index 4bd713a0b4..9e6fdbd8af 100644 --- a/src/backend/tcop/cmdtag.c +++ b/src/backend/tcop/cmdtag.c @@ -146,7 +146,7 @@ BuildQueryCompletionString(char *buff, const QueryCompletion *qc, */ if (command_tag_display_rowcount(tag) && !nameonly) { - if (tag == CMDTAG_INSERT) + if (tag == CMDTAG_INSERT || tag == CMDTAG_EXPLAIN_INSERT) { *bufp++ = ' '; *bufp++ = '0'; diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5565f200c3..ba0b33cc67 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -775,7 +775,13 @@ PortalRun(Portal portal, long count, bool isTopLevel, bool run_once, if (qc && portal->qc.commandTag != CMDTAG_UNKNOWN) { CopyQueryCompletion(qc, &portal->qc); - qc->nprocessed = nprocessed; + if (portal->qc.commandTag == CMDTAG_EXPLAIN || + portal->qc.commandTag == CMDTAG_EXPLAIN_INSERT || + portal->qc.commandTag == CMDTAG_EXPLAIN_UPDATE || + portal->qc.commandTag == CMDTAG_EXPLAIN_DELETE) + qc->nprocessed = portal->qc.nprocessed; + else + qc->nprocessed = nprocessed; } /* Mark portal not active */ diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index e3ccf6c7f7..8975d046f9 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -867,7 +867,32 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; case T_ExplainStmt: - ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest); + { + Query *query; + uint64 processed; + int explainTag; + + ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest, &processed); + + query = castNode(Query, ((ExplainStmt *) parsetree)->query); + switch (query->commandType) + { + case CMD_INSERT: + explainTag = CMDTAG_EXPLAIN_INSERT; + break; + case CMD_UPDATE: + explainTag = CMDTAG_EXPLAIN_UPDATE; + break; + case CMD_DELETE: + explainTag = CMDTAG_EXPLAIN_DELETE; + break; + default: + explainTag = CMDTAG_EXPLAIN; + break; + } + if (qc) + SetQueryCompletion(qc, explainTag, processed); + } break; case T_AlterSystemStmt: diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index ede197bebe..a66d9127c5 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -987,8 +987,9 @@ PrintQueryResult(PGresult *result, bool last, if (last || pset.show_all_results) { cmdstatus = PQcmdStatus(result); - if (strncmp(cmdstatus, "INSERT", 6) == 0 || - strncmp(cmdstatus, "UPDATE", 6) == 0 || + if (strncmp(cmdstatus, "EXPLAIN", 7) == 0 || + strncmp(cmdstatus, "INSERT", 6) == 0 || + strncmp(cmdstatus, "UPDATE", 6) == 0 || strncmp(cmdstatus, "DELETE", 6) == 0) PrintQueryStatus(result, printStatusFout); } diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index 3d3e632a0c..21fe5f7555 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -60,6 +60,7 @@ typedef struct ExplainState bool hide_workers; /* set if we find an invisible Gather */ /* state related to the current plan node */ ExplainWorkersState *workers_state; /* needed if parallel plan */ + uint64 es_processed; /* sum of queryDesc->estate->es_processed */ } ExplainState; /* Hook for plugins to get control in ExplainOneQuery() */ @@ -78,7 +79,7 @@ extern PGDLLIMPORT explain_get_index_name_hook_type explain_get_index_name_hook; extern void ExplainQuery(ParseState *pstate, ExplainStmt *stmt, - ParamListInfo params, DestReceiver *dest); + ParamListInfo params, DestReceiver *dest, uint64 *processed); extern ExplainState *NewExplainState(void); diff --git a/src/include/tcop/cmdtag.h b/src/include/tcop/cmdtag.h index 1e7514dcff..49f7ea85e7 100644 --- a/src/include/tcop/cmdtag.h +++ b/src/include/tcop/cmdtag.h @@ -30,6 +30,7 @@ typedef enum CommandTag typedef struct QueryCompletion { CommandTag commandTag; + CommandTag explainCommandTag; uint64 nprocessed; } QueryCompletion; diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h index e738ac1c09..fdc570a304 100644 --- a/src/include/tcop/cmdtaglist.h +++ b/src/include/tcop/cmdtaglist.h @@ -178,6 +178,9 @@ PG_CMDTAG(CMDTAG_DROP_USER_MAPPING, "DROP USER MAPPING", true, false, false) PG_CMDTAG(CMDTAG_DROP_VIEW, "DROP VIEW", true, false, false) PG_CMDTAG(CMDTAG_EXECUTE, "EXECUTE", false, false, false) PG_CMDTAG(CMDTAG_EXPLAIN, "EXPLAIN", false, false, false) +PG_CMDTAG(CMDTAG_EXPLAIN_INSERT, "INSERT", false, false, true) +PG_CMDTAG(CMDTAG_EXPLAIN_UPDATE, "UPDATE", false, false, true) +PG_CMDTAG(CMDTAG_EXPLAIN_DELETE, "DELETE", false, false, true) PG_CMDTAG(CMDTAG_FETCH, "FETCH", false, false, true) PG_CMDTAG(CMDTAG_GRANT, "GRANT", true, false, false) PG_CMDTAG(CMDTAG_GRANT_ROLE, "GRANT ROLE", false, false, false) diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index c6d80ec396..b85e9b8e04 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -3741,7 +3741,9 @@ PQcmdTuples(PGresult *res) if (!res) return ""; - if (strncmp(res->cmdStatus, "INSERT ", 7) == 0) + if (strncmp(res->cmdStatus, "EXPLAIN ", 8) == 0) + p = res->cmdStatus + 8; + else if (strncmp(res->cmdStatus, "INSERT ", 7) == 0) { p = res->cmdStatus + 7; /* INSERT: skip oid and space */ -- 2.34.1