> On Fri, Mar 31, 2023 at 07:39:27PM +0200, Dmitry Dolgov wrote:
> > On Wed, Mar 29, 2023 at 01:50:37PM +1300, David Rowley wrote:
> > On Sun, 12 Jun 2022 at 21:14, Dmitry Dolgov <9erthali...@gmail.com> wrote:
> > > I've noticed that JIT performance counter generation_counter seems to
> > > include
> > > actions, relevant for both jit_expressions and jit_tuple_deforming
> > > options. It
> > > means one can't directly see what is the influence of jit_tuple_deforming
> > > alone, which would be helpful when adjusting JIT options. To make it
> > > better a
> > > new counter can be introduced, does it make sense?
> >
> > I'm not so sure about this idea. As of now, if I look at EXPLAIN
> > ANALYZE's JIT summary, the individual times add up to the total time.
> >
> > If we add this deform time, then that's no longer going to be true as
> > the "Generation" time includes the newly added deform time.
> >
> > master:
> > JIT:
> > Functions: 600
> > Options: Inlining false, Optimization false, Expressions true, Deforming
> > true
> > Timing: Generation 37.758 ms, Inlining 0.000 ms, Optimization 6.736
> > ms, Emission 172.244 ms, Total 216.738 ms
> >
> > 37.758 + 6.736 + 172.244 = 216.738
> >
> > I think if I was a DBA wondering why JIT was taking so long, I'd
> > probably either be very astonished or I'd report a bug if I noticed
> > that all the individual component JIT times didn't add up to the total
> > time.
> >
> > I don't think the solution is to subtract the deform time from the
> > generation time either.
> >
> > Can't users just get this by looking at EXPLAIN ANALYZE with and
> > without jit_tuple_deforming?
>
> It could be done this way, but then users need to know that tuple
> deforming is included into generation time (I've skimmed through the
> docs, there seems to be no direct statements about that, although it
> could be guessed). At the same time I don't think it's very
> user-friendly approach -- after all it could be the same for other
> timings, i.e. only one counter for all JIT operations present,
> expecting users to experiment how would it change if this or that option
> will be different.
>
> I agree about adding up to the total time though. What about changing
> the format to something like this?
>
> Options: Inlining false, Optimization false, Expressions true, Deforming
> true
> Timing: Generation 37.758 ms (Deforming 1.234 ms), Inlining 0.000 ms,
> Optimization 6.736 ms, Emission 172.244 ms, Total 216.738 ms
>
> This way it doesn't look like deforming timing is in the same category
> as others, but rather a part of another value.
Here is the patch with the proposed variation.
>From 963b9a31f2241cfff766544685709d813145f33a Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <9erthali...@gmail.com>
Date: Sat, 11 Jun 2022 11:54:40 +0200
Subject: [PATCH v5 1/2] Add deform_counter
At the moment generation_counter includes time spent on both JITing
expressions and tuple deforming. Those are configured independently via
corresponding options (jit_expressions and jit_tuple_deforming), which
means there is no way to find out what fraction of time tuple deforming
alone is taking.
Add deform_counter dedicated to tuple deforming, which allows seeing
more directly the influence jit_tuple_deforming is having on the query.
---
src/backend/commands/explain.c | 7 ++++++-
src/backend/jit/jit.c | 1 +
src/backend/jit/llvm/llvmjit_expr.c | 6 ++++++
src/include/jit/jit.h | 3 +++
4 files changed, 16 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 5334c503e1..a134411209 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -893,6 +893,7 @@ ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji)
/* calculate total time */
INSTR_TIME_SET_ZERO(total_time);
+ /* don't add deform_counter, it's included into generation_counter */
INSTR_TIME_ADD(total_time, ji->generation_counter);
INSTR_TIME_ADD(total_time, ji->inlining_counter);
INSTR_TIME_ADD(total_time, ji->optimization_counter);
@@ -920,8 +921,9 @@ ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji)
{
ExplainIndentText(es);
appendStringInfo(es->str,
- "Timing: %s %.3f ms, %s %.3f ms, %s %.3f ms, %s %.3f ms, %s %.3f ms\n",
+ "Timing: %s %.3f ms (%s %.3f ms), %s %.3f ms, %s %.3f ms, %s %.3f ms, %s %.3f ms\n",
"Generation", 1000.0 * INSTR_TIME_GET_DOUBLE(ji->generation_counter),
+ "Deforming", 1000.0 * INSTR_TIME_GET_DOUBLE(ji->deform_counter),
"Inlining", 1000.0 * INSTR_TIME_GET_DOUBLE(ji->inlining_counter),
"Optimization", 1000.0 * INSTR_TIME_GET_DOUBLE(ji->optimization_counter),
"Emission", 1000.0 * INSTR_TIME_GET_DOUBLE(ji->emission_counter),
@@ -948,6 +950,9 @@ ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji)
ExplainPropertyFloat("Generation", "ms",
1000.0 * INSTR_TIME_GET_DOUBLE(ji->generation_counter),
3, es);
+ ExplainPropertyFloat("Deforming", "ms",
+ 1000.0 * INSTR_TIME_GET_DOUBLE(ji->deform_counter),
+ 3, es);
ExplainPropertyFloat("Inlining", "ms",
1000.0 * INSTR_TIME_GET_DOUBLE(ji->inlining_counter),
3, es);
diff --git a/src/backend/jit/jit.c b/src/backend/jit/jit.c
index fd1cf184c8..4da8fee20b 100644
--- a/src/backend/jit/jit.c
+++ b/src/backend/jit/jit.c
@@ -185,6 +185,7 @@ InstrJitAgg(JitInstrumentation *dst, JitInstrumentation *add)
{
dst->created_functions += add->created_functions;
INSTR_TIME_ADD(dst->generation_counter, add->generation_counter);
+ INSTR_TIME_ADD(dst->deform_counter, add->deform_counter);
INSTR_TIME_ADD(dst->inlining_counter, add->inlining_counter);
INSTR_TIME_ADD(dst->optimization_counter, add->optimization_counter);
INSTR_TIME_ADD(dst->emission_counter, add->emission_counter);
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index daefe66f40..e93b124863 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -121,7 +121,9 @@ llvm_compile_expr(ExprState *state)
LLVMValueRef v_aggnulls;
instr_time starttime;
+ instr_time deform_starttime;
instr_time endtime;
+ instr_time deform_endtime;
llvm_enter_fatal_on_oom();
@@ -315,10 +317,14 @@ llvm_compile_expr(ExprState *state)
*/
if (tts_ops && desc && (context->base.flags & PGJIT_DEFORM))
{
+ INSTR_TIME_SET_CURRENT(deform_starttime);
l_jit_deform =
slot_compile_deform(context, desc,
tts_ops,
op->d.fetch.last_var);
+ INSTR_TIME_SET_CURRENT(deform_endtime);
+ INSTR_TIME_ACCUM_DIFF(context->base.instr.deform_counter,
+ deform_endtime, deform_starttime);
}
if (l_jit_deform)
diff --git a/src/include/jit/jit.h b/src/include/jit/jit.h
index 14f2e36b37..ed381d8a1c 100644
--- a/src/include/jit/jit.h
+++ b/src/include/jit/jit.h
@@ -32,6 +32,9 @@ typedef struct JitInstrumentation
/* accumulated time to generate code */
instr_time generation_counter;
+ /* accumulated time to deform tuples, included into generation_counter */
+ instr_time deform_counter;
+
/* accumulated time for inlining */
instr_time inlining_counter;
--
2.32.0
>From 9d1b58fba2a2be6a786158496206b191d1edb9d6 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <9erthali...@gmail.com>
Date: Sat, 11 Jun 2022 12:25:28 +0200
Subject: [PATCH v5 2/2] Add deform_counter to pg_stat_statements
Similar to other JIT counters, expose deform_counter via
pg_stat_statements. Includes bumping its version to 1.11.
---
contrib/pg_stat_statements/Makefile | 1 +
contrib/pg_stat_statements/meson.build | 1 +
.../pg_stat_statements--1.10--1.11.sql | 69 +++++++++++++++++++
.../pg_stat_statements/pg_stat_statements.c | 34 ++++++++-
.../pg_stat_statements.control | 2 +-
doc/src/sgml/pgstatstatements.sgml | 19 +++++
6 files changed, 123 insertions(+), 3 deletions(-)
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 5578a9dd4e..eba4a95d91 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -7,6 +7,7 @@ OBJS = \
EXTENSION = pg_stat_statements
DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.10--1.11.sql \
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 3e3062ada9..15b7c7f2b0 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements
install_data(
'pg_stat_statements.control',
'pg_stat_statements--1.4.sql',
+ 'pg_stat_statements--1.10--1.11.sql',
'pg_stat_statements--1.9--1.10.sql',
'pg_stat_statements--1.8--1.9.sql',
'pg_stat_statements--1.7--1.8.sql',
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
new file mode 100644
index 0000000000..20bae80458
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
@@ -0,0 +1,69 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT toplevel bool,
+ OUT queryid bigint,
+ OUT query text,
+ OUT plans int8,
+ OUT total_plan_time float8,
+ OUT min_plan_time float8,
+ OUT max_plan_time float8,
+ OUT mean_plan_time float8,
+ OUT stddev_plan_time float8,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT min_exec_time float8,
+ OUT max_exec_time float8,
+ OUT mean_exec_time float8,
+ OUT stddev_exec_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8,
+ OUT temp_blk_read_time float8,
+ OUT temp_blk_write_time float8,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT jit_functions int8,
+ OUT jit_generation_time float8,
+ OUT jit_inlining_count int8,
+ OUT jit_inlining_time float8,
+ OUT jit_optimization_count int8,
+ OUT jit_optimization_time float8,
+ OUT jit_emission_count int8,
+ OUT jit_emission_time float8,
+ OUT jit_deform_count int8,
+ OUT jit_deform_time float8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_11'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 68215bb2e3..ee2f60d6d8 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -118,7 +118,8 @@ typedef enum pgssVersion
PGSS_V1_3,
PGSS_V1_8,
PGSS_V1_9,
- PGSS_V1_10
+ PGSS_V1_10,
+ PGSS_V1_11
} pgssVersion;
typedef enum pgssStoreKind
@@ -193,6 +194,9 @@ typedef struct Counters
double jit_generation_time; /* total time to generate jit code */
int64 jit_inlining_count; /* number of times inlining time has been
* > 0 */
+ double jit_deform_time; /* total time to deform tuples in jit code */
+ int64 jit_deform_count; /* number of times deform time has been > 0 */
+
double jit_inlining_time; /* total time to inline jit code */
int64 jit_optimization_count; /* number of times optimization time
* has been > 0 */
@@ -313,6 +317,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_11);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -1402,6 +1407,10 @@ pgss_store(const char *query, uint64 queryId,
e->counters.jit_functions += jitusage->created_functions;
e->counters.jit_generation_time += INSTR_TIME_GET_MILLISEC(jitusage->generation_counter);
+ if (INSTR_TIME_GET_MILLISEC(jitusage->deform_counter))
+ e->counters.jit_deform_count++;
+ e->counters.jit_deform_time += INSTR_TIME_GET_MILLISEC(jitusage->deform_counter);
+
if (INSTR_TIME_GET_MILLISEC(jitusage->inlining_counter))
e->counters.jit_inlining_count++;
e->counters.jit_inlining_time += INSTR_TIME_GET_MILLISEC(jitusage->inlining_counter);
@@ -1464,7 +1473,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_8 32
#define PG_STAT_STATEMENTS_COLS_V1_9 33
#define PG_STAT_STATEMENTS_COLS_V1_10 43
-#define PG_STAT_STATEMENTS_COLS 43 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_11 45
+#define PG_STAT_STATEMENTS_COLS 45 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1476,6 +1486,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_11(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_10(PG_FUNCTION_ARGS)
{
@@ -1606,6 +1626,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_10)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_11:
+ if (api_version != PGSS_V1_11)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1838,6 +1862,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
values[i++] = Int64GetDatumFast(tmp.jit_emission_count);
values[i++] = Float8GetDatumFast(tmp.jit_emission_time);
}
+ if (api_version >= PGSS_V1_11)
+ {
+ values[i++] = Int64GetDatumFast(tmp.jit_deform_count);
+ values[i++] = Float8GetDatumFast(tmp.jit_deform_time);
+ }
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
@@ -1846,6 +1875,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
+ api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 0747e48138..8a76106ec6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track planning and execution statistics of all SQL statements executed'
-default_version = '1.10'
+default_version = '1.11'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index b1214ee645..89ba13a7ed 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -420,6 +420,25 @@
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>jit_deform_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of tuple deform functions JIT-compiled by the statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>jit_deform_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Total time spent by the statement on JIT-compiling deform tuple
+ functions, in milliseconds
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>jit_inlining_count</structfield> <type>bigint</type>
--
2.32.0
