pg_stat_statements: faster search by queryid
Started by Karina Litskevich4 months ago6 messages
Hi hackers,
Aleksandra Bondar and I are proposing the following patch for
pg_stat_statements.
The idea.
---------
Currently, to get statistics on a specific query, you should execute
SELECT * FROM pg_stat_statements WHERE queryid = specific_queryid;
This takes a long time because the pg_stat_statements() function forms
tuples for all statistics it has first, and then they are filtered by
the WHERE clause. If we provide a function like
pg_stat_statements_by_queryid(queryid bigint), which would filter
statistics by queryid while scanning pgss_hash and return only
statistics with the specified queryid, that would be much faster.
We can also easily add filtration by userid and dbid, which would lead
us to a function like
pg_stat_statements_filtered(queryid bigint, userid Oid, dbid Oid). In
case some parameter is not specified, its default value is 0, and it
means that no filtration is needed on this parameter. Kind of like
pg_stat_statements_reset() chooses what statistics should be cleaned.
If no parameter is specified, pg_stat_statements_filtered() should
return all statistics that pg_stat_statements() would return.
This led me to the idea that we should rather extend the
pg_stat_statements() function than add a new function. The old way to
call pg_stat_statements() will produce the same results, and
specifying new parameters will produce filtered results.
The patch.
----------
The extended pg_stat_statements() function idea is implemented in the
patch attached. I can always rewrite the patch to add a new function
and leave pg_stat_statements() as it is, though, if you think it's
better to have a separate function for filtering.
We've only written the code so far and want to get your opinion on
that. If you like the idea, we'll also provide tests and docs. Any
suggestions are welcome.
Benchmarking.
-------------
We prepared a simple test case here to show performance improvement.
Download the attached script pg_stat_statements_prepare.sql and run
the following in psql.
CREATE EXTENSION pg_stat_statements;
-- Fill in pg_stat_statements statistics
\i /path/to/pgpro_stats_prepare_script.sql
-- Get random query ID
SELECT queryid AS rand_queryid
FROM pg_stat_statements
WHERE queryid IS NOT NULL
ORDER BY random()
LIMIT 1 \gset
-- Turn on time measuring
\timing
-- Get statistics in the old way
SELECT * FROM pg_stat_statements WHERE queryid = :rand_queryid;
-- Get statistics in the new way
SELECT * FROM pg_stat_statements(true, queryid => :rand_queryid);
I'm getting that the new way is at least two times faster on my
machine. I also compared the time for the old way on master with
and without the patch. I get that the difference is within standard
deviation.
Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/
Attachments:
v1-0001-pg_stat_statements-add-ability-to-filter-statisti.patchtext/x-patch; charset=US-ASCII; name=v1-0001-pg_stat_statements-add-ability-to-filter-statisti.patchDownload
From 1e4f15b9adbfeeef68a3810b1a325dc656549e16 Mon Sep 17 00:00:00 2001
From: Karina Litskevich <litskevichkarina@gmail.com>
Date: Wed, 3 Sep 2025 11:44:26 +0300
Subject: [PATCH v1] pg_stat_statements: add ability to filter statistics while
sacnning pgss_hash
Author: Aleksandra Bondar
Author: Karina Litskevich
---
.../pg_stat_statements--1.12--1.13.sql | 3 +
.../pg_stat_statements/pg_stat_statements.c | 61 +++++++++++++++----
2 files changed, 53 insertions(+), 11 deletions(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
index 2f0eaf14ec3..613835f8c99 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
@@ -13,6 +13,9 @@ DROP FUNCTION pg_stat_statements(boolean);
/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ IN userid oid DEFAULT 0,
+ IN dbid oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0,
OUT userid oid,
OUT dbid oid,
OUT toplevel bool,
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 0bb0f933399..ba25a477b2c 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -148,6 +148,19 @@ typedef struct pgssHashKey
bool toplevel; /* query executed at top level */
} pgssHashKey;
+/*
+ * Values to filter hashtable entries by. If a field is 0, this means no filtring
+ * is needed by this identifier.
+ */
+typedef struct pgssFilter
+{
+ Oid userid; /* user OID */
+ Oid dbid; /* database OID */
+ int64 queryid; /* query identifier */
+} pgssFilter;
+
+#define NO_FILTERING (&(pgssFilter) {0})
+
/*
* The actual stats counters kept within pgssEntry.
*/
@@ -362,7 +375,8 @@ static void pgss_store(const char *query, int64 queryId,
PlannedStmtOrigin planOrigin);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext);
+ bool showtext,
+ const pgssFilter *filter);
static Size pgss_memsize(void);
static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
int encoding, bool sticky);
@@ -1594,8 +1608,12 @@ Datum
pg_stat_statements_1_13(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
+ Oid userid = PG_GETARG_OID(1);
+ Oid dbid = PG_GETARG_OID(2);
+ int64 queryid = PG_GETARG_INT64(3);
+ pgssFilter filter = {userid, dbid, queryid};
- pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext, &filter);
return (Datum) 0;
}
@@ -1605,7 +1623,7 @@ pg_stat_statements_1_12(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1615,7 +1633,7 @@ pg_stat_statements_1_11(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1625,7 +1643,7 @@ pg_stat_statements_1_10(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1635,7 +1653,7 @@ pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1645,7 +1663,7 @@ pg_stat_statements_1_8(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1655,7 +1673,7 @@ pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1665,7 +1683,7 @@ pg_stat_statements_1_2(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext, NO_FILTERING);
return (Datum) 0;
}
@@ -1678,16 +1696,34 @@ Datum
pg_stat_statements(PG_FUNCTION_ARGS)
{
/* If it's really API 1.1, we'll figure that out below */
- pg_stat_statements_internal(fcinfo, PGSS_V1_0, true);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_0, true, NO_FILTERING);
return (Datum) 0;
}
+static inline bool
+entry_matches_filter(const pgssEntry *entry, const pgssFilter *filter)
+{
+ Assert(filter != NULL);
+
+ if (filter->queryid != 0 && entry->key.queryid != filter->queryid)
+ return false;
+
+ if (filter->userid != 0 && entry->key.userid != filter->userid)
+ return false;
+
+ if (filter->dbid != 0 && entry->key.dbid != filter->dbid)
+ return false;
+
+ return true;
+}
+
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext)
+ bool showtext,
+ const pgssFilter *filter)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
Oid userid = GetUserId();
@@ -1838,6 +1874,9 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
TimestampTz stats_since;
TimestampTz minmax_stats_since;
+ if (!entry_matches_filter(entry, filter))
+ continue;
+
memset(values, 0, sizeof(values));
memset(nulls, 0, sizeof(nulls));
--
2.34.1
Re: pg_stat_statements: faster search by queryid
Hi,
Thanks for raising this.
This takes a long time because the pg_stat_statements() function forms
tuples for all statistics it has first, and then they are filtered by
the WHERE clause.
I am curious about the specific use case where this becomes a concern.
How often are you querying pg_stat_statements for individual entries?
My initial thought is that this patch does not remove the issue of
loading the entire query text (just to return one or a few entries). I
suspect that repeatedly loading query text into memory for lookups
could be a bigger problem. One option would be to pass showtext=false
for such lookups, but that makes the function more cumbersome to use
and understand.
I do think having the ability to look up a specific entry based on a
key (that is, hash_search instead of hash_seq_search) would be useful.
But we also need to consider how to handle query text lookups, so we
are not forced to load the entire text file into memory.
For what it is worth, I have been thinking about what it would take to
move query texts into shared memory, which could make this type of
filtering more practical.
Just my 2c.
--
Sami Imseih
Amazon Web Services (AWS)
Re: pg_stat_statements: faster search by queryid
Thank you for your feedback.
My initial thought is that this patch does not remove the issue of
loading the entire query text (just to return one or a few entries).
This patch is not intended to address the issue of loading the file
with query texts. We only try to avoid forming and handling tuples
that we don't really need. Forming a tuple in
pg_stat_statements_internal() includes calling CStringGetTextDatum(),
which allocates memory and copies the query text. Avoiding that for
queries we don't need makes a big difference already.
I do think having the ability to look up a specific entry based on a
key (that is, hash_search instead of hash_seq_search) would be useful.
That's a great idea, thanks! I'm going to try that and include it in
the next version of the patch if I succeed.
For what it is worth, I have been thinking about what it would take to
move query texts into shared memory, which could make this type of
filtering more practical.
As far as I can tell, pg_stat_statements is storing query texts in an
external file to not have problems with long query texts. Here is a
quote from the docs:
The representative query texts are kept in an external disk file, and
do not consume shared memory. Therefore, even very lengthy query texts
can be stored successfully.
I can think of the idea of caching some of the texts (short enough) in
shared memory. That would require adding a GUC that limits query text
length or maybe that limits the total amount of memory to be used for
caching. So for an individual query, pg_stat_statements should first
look up its text in the cache and only go to the disk if it's not
found. I'm not sure I want to do that, though...
Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/
Re: pg_stat_statements: faster search by queryid
My initial thought is that this patch does not remove the issue of
loading the entire query text (just to return one or a few entries).This patch is not intended to address the issue of loading the file
with query texts. We only try to avoid forming and handling tuples
that we don't really need. Forming a tuple in
pg_stat_statements_internal() includes calling CStringGetTextDatum(),
which allocates memory and copies the query text. Avoiding that for
queries we don't need makes a big difference already.
Yes, but my point is, if someone repeatedly lookup up pg_stat_statements
with filters, they will end up loading the query text multiple times.
for example:
````
select * from pg_stat_statements where query_id in (10000, 20000, 30000);
```
will only load the query text once to retrieve these 3 query IDs.
If I instead do this, with the proposed patch:
```
select * from pg_stat_statements(true, queryid=>10000);
select * from pg_stat_statements(true, queryid=>20000);
select * from pg_stat_statements(true, queryid=>30000);
or
select * from pg_stat_activity a, pg_stat_statements(true, queryid=>a.query_id);
```
I will have to load the query text file into memory for every invocation of
pg_stat_statements.
For what it is worth, I have been thinking about what it would take to
move query texts into shared memory, which could make this type of
filtering more practical.As far as I can tell, pg_stat_statements is storing query texts in an
external file to not have problems with long query texts. Here is a
quote from the docs:The representative query texts are kept in an external disk file, and
do not consume shared memory. Therefore, even very lengthy query texts
can be stored successfully.
pg_stat_statements_internal must load the file when showtext = true, which
is the default.
```
qbuffer = qtext_load_file(&qbuffer_size);
```
--
Sami Imseih
Amazon Web Services (AWS)
Re: pg_stat_statements: faster search by queryid
I do think having the ability to look up a specific entry based on a
key (that is, hash_search instead of hash_seq_search) would be useful.That's a great idea, thanks! I'm going to try that and include it in
the next version of the patch if I succeed.
Here is the second version of the patch, as promised. I used hash_search
in case all three key arguments are provided, the same way as it is done
in the entry_reset() function. The diff doesn't look very pleasant.
Basically I just moved the code that forms one tuple in a new
pg_stat_statements_handle_entry() function to use it in
pg_stat_statements_internal().
I also had a second thought about adding a new struct just to pass three
key arguments as one filter to the internal function. In the v2 patch I
just pass the arguments as they are. I'm not sure which option is better.
Anyway, it should be the same in both entry_reset() and
pg_stat_statements_internal(), so if you say adding struct pgssFilter
was a good idea, I'll rewrite the patch to use it in both
pg_stat_statements_internal() and entry_reset().
On Thu, Sep 18, 2025 at 6:33 PM Sami Imseih <samimseih@gmail.com> wrote:
Yes, but my point is, if someone repeatedly lookup up pg_stat_statements
with filters, they will end up loading the query text multiple times.for example:
````
select * from pg_stat_statements where query_id in (10000, 20000, 30000);
```will only load the query text once to retrieve these 3 query IDs.
If I instead do this, with the proposed patch:
```
select * from pg_stat_statements(true, queryid=>10000);
select * from pg_stat_statements(true, queryid=>20000);
select * from pg_stat_statements(true, queryid=>30000);or
select * from pg_stat_activity a, pg_stat_statements(true, queryid=>a.query_id);```
I will have to load the query text file into memory for every invocation of
pg_stat_statements.
You are right. At some point, if information about multiple queries is
needed, a single select from pg_stat_statements followed by filtering
will be more efficient than calling pg_stat_statements with filters
multiple times. That's something that should be documented.
Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/
Attachments:
v2-0001-pg_stat_statements-add-ability-to-filter-statisti.patchtext/x-patch; charset=US-ASCII; name=v2-0001-pg_stat_statements-add-ability-to-filter-statisti.patchDownload
From 95b3bcc2957ad04f2581051104f37323f7d10afe Mon Sep 17 00:00:00 2001
From: Karina Litskevich <litskevichkarina@gmail.com>
Date: Wed, 3 Sep 2025 11:44:26 +0300
Subject: [PATCH v2] pg_stat_statements: add ability to filter statistics while
sacnning pgss_hash
Author: Aleksandra Bondar
Author: Karina Litskevich
---
.../pg_stat_statements--1.12--1.13.sql | 3 +
.../pg_stat_statements/pg_stat_statements.c | 504 ++++++++++--------
2 files changed, 291 insertions(+), 216 deletions(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
index 2f0eaf14ec3..613835f8c99 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
@@ -13,6 +13,9 @@ DROP FUNCTION pg_stat_statements(boolean);
/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ IN userid oid DEFAULT 0,
+ IN dbid oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0,
OUT userid oid,
OUT dbid oid,
OUT toplevel bool,
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 0bb0f933399..860a3b7c58e 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -362,7 +362,10 @@ static void pgss_store(const char *query, int64 queryId,
PlannedStmtOrigin planOrigin);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext);
+ bool showtext,
+ Oid userid,
+ Oid dbid,
+ int64 queryid);
static Size pgss_memsize(void);
static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
int encoding, bool sticky);
@@ -1594,8 +1597,12 @@ Datum
pg_stat_statements_1_13(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
+ Oid userid = PG_GETARG_OID(1);
+ Oid dbid = PG_GETARG_OID(2);
+ int64 queryid = PG_GETARG_INT64(3);
- pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext,
+ userid, dbid, queryid);
return (Datum) 0;
}
@@ -1605,7 +1612,7 @@ pg_stat_statements_1_12(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1615,7 +1622,7 @@ pg_stat_statements_1_11(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1625,7 +1632,7 @@ pg_stat_statements_1_10(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1635,7 +1642,7 @@ pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1645,7 +1652,7 @@ pg_stat_statements_1_8(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1655,7 +1662,7 @@ pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1665,7 +1672,7 @@ pg_stat_statements_1_2(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1678,19 +1685,243 @@ Datum
pg_stat_statements(PG_FUNCTION_ARGS)
{
/* If it's really API 1.1, we'll figure that out below */
- pg_stat_statements_internal(fcinfo, PGSS_V1_0, true);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_0, true, 0, 0, 0);
return (Datum) 0;
}
+static void
+pg_stat_statements_handle_entry(const ReturnSetInfo *rsinfo,
+ pgssEntry *entry,
+ bool showtext,
+ bool is_allowed_role,
+ Oid userid,
+ char *qbuffer,
+ Size qbuffer_size,
+ pgssVersion api_version)
+{
+ Datum values[PG_STAT_STATEMENTS_COLS];
+ bool nulls[PG_STAT_STATEMENTS_COLS];
+ int i = 0;
+ Counters tmp;
+ double stddev;
+ int64 queryid = entry->key.queryid;
+ TimestampTz stats_since;
+ TimestampTz minmax_stats_since;
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, 0, sizeof(nulls));
+
+ values[i++] = ObjectIdGetDatum(entry->key.userid);
+ values[i++] = ObjectIdGetDatum(entry->key.dbid);
+ if (api_version >= PGSS_V1_9)
+ values[i++] = BoolGetDatum(entry->key.toplevel);
+
+ if (is_allowed_role || entry->key.userid == userid)
+ {
+ if (api_version >= PGSS_V1_2)
+ values[i++] = Int64GetDatumFast(queryid);
+
+ if (showtext)
+ {
+ char *qstr = qtext_fetch(entry->query_offset,
+ entry->query_len,
+ qbuffer,
+ qbuffer_size);
+
+ if (qstr)
+ {
+ char *enc;
+
+ enc = pg_any_to_server(qstr,
+ entry->query_len,
+ entry->encoding);
+
+ values[i++] = CStringGetTextDatum(enc);
+
+ if (enc != qstr)
+ pfree(enc);
+ }
+ else
+ {
+ /* Just return a null if we fail to find the text */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* Query text not requested */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* Don't show queryid */
+ if (api_version >= PGSS_V1_2)
+ nulls[i++] = true;
+
+ /*
+ * Don't show query text, but hint as to the reason for not doing
+ * so if it was requested
+ */
+ if (showtext)
+ values[i++] = CStringGetTextDatum("<insufficient privilege>");
+ else
+ nulls[i++] = true;
+ }
+
+ /* copy counters to a local variable to keep locking time short */
+ SpinLockAcquire(&entry->mutex);
+ tmp = entry->counters;
+ SpinLockRelease(&entry->mutex);
+
+ /*
+ * The spinlock is not required when reading these two as they are
+ * always updated when holding pgss->lock exclusively.
+ */
+ stats_since = entry->stats_since;
+ minmax_stats_since = entry->minmax_stats_since;
+
+ /* Skip entry if unexecuted (ie, it's a pending "sticky" entry) */
+ if (IS_STICKY(tmp))
+ return;
+
+ /* Note that we rely on PGSS_PLAN being 0 and PGSS_EXEC being 1. */
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ if (kind == PGSS_EXEC || api_version >= PGSS_V1_8)
+ {
+ values[i++] = Int64GetDatumFast(tmp.calls[kind]);
+ values[i++] = Float8GetDatumFast(tmp.total_time[kind]);
+ }
+
+ if ((kind == PGSS_EXEC && api_version >= PGSS_V1_3) ||
+ api_version >= PGSS_V1_8)
+ {
+ values[i++] = Float8GetDatumFast(tmp.min_time[kind]);
+ values[i++] = Float8GetDatumFast(tmp.max_time[kind]);
+ values[i++] = Float8GetDatumFast(tmp.mean_time[kind]);
+
+ /*
+ * Note we are calculating the population variance here, not
+ * the sample variance, as we have data for the whole
+ * population, so Bessel's correction is not used, and we
+ * don't divide by tmp.calls - 1.
+ */
+ if (tmp.calls[kind] > 1)
+ stddev = sqrt(tmp.sum_var_time[kind] / tmp.calls[kind]);
+ else
+ stddev = 0.0;
+ values[i++] = Float8GetDatumFast(stddev);
+ }
+ }
+ values[i++] = Int64GetDatumFast(tmp.rows);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
+ if (api_version >= PGSS_V1_1)
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_read);
+ if (api_version >= PGSS_V1_1)
+ values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_written);
+ values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
+ values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
+ if (api_version >= PGSS_V1_1)
+ {
+ values[i++] = Float8GetDatumFast(tmp.shared_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.shared_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_11)
+ {
+ values[i++] = Float8GetDatumFast(tmp.local_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.local_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_8)
+ {
+ char buf[256];
+ Datum wal_bytes;
+
+ values[i++] = Int64GetDatumFast(tmp.wal_records);
+ values[i++] = Int64GetDatumFast(tmp.wal_fpi);
+
+ snprintf(buf, sizeof buf, UINT64_FORMAT, tmp.wal_bytes);
+
+ /* Convert to numeric. */
+ wal_bytes = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+ values[i++] = wal_bytes;
+ }
+ if (api_version >= PGSS_V1_12)
+ {
+ values[i++] = Int64GetDatumFast(tmp.wal_buffers_full);
+ }
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = Int64GetDatumFast(tmp.jit_functions);
+ values[i++] = Float8GetDatumFast(tmp.jit_generation_time);
+ values[i++] = Int64GetDatumFast(tmp.jit_inlining_count);
+ values[i++] = Float8GetDatumFast(tmp.jit_inlining_time);
+ values[i++] = Int64GetDatumFast(tmp.jit_optimization_count);
+ values[i++] = Float8GetDatumFast(tmp.jit_optimization_time);
+ 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);
+ }
+ if (api_version >= PGSS_V1_12)
+ {
+ values[i++] = Int64GetDatumFast(tmp.parallel_workers_to_launch);
+ values[i++] = Int64GetDatumFast(tmp.parallel_workers_launched);
+ }
+ if (api_version >= PGSS_V1_13)
+ {
+ values[i++] = Int64GetDatumFast(tmp.generic_plan_calls);
+ values[i++] = Int64GetDatumFast(tmp.custom_plan_calls);
+ }
+ if (api_version >= PGSS_V1_11)
+ {
+ values[i++] = TimestampTzGetDatum(stats_since);
+ values[i++] = TimestampTzGetDatum(minmax_stats_since);
+ }
+
+ Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
+ api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
+ api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
+ api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+ 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 :
+ api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 :
+ api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 :
+ -1 /* fail if you forget to update this assert */ ));
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext)
+ bool showtext,
+ Oid userid,
+ Oid dbid,
+ int64 queryid)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- Oid userid = GetUserId();
+ Oid current_userid = GetUserId();
bool is_allowed_role = false;
char *qbuffer = NULL;
Size qbuffer_size = 0;
@@ -1703,7 +1934,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
* Superusers or roles with the privileges of pg_read_all_stats members
* are allowed
*/
- is_allowed_role = has_privs_of_role(userid, ROLE_PG_READ_ALL_STATS);
+ is_allowed_role = has_privs_of_role(current_userid, ROLE_PG_READ_ALL_STATS);
/* hash table must exist already */
if (!pgss || !pgss_hash)
@@ -1826,218 +2057,59 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
}
}
- hash_seq_init(&hash_seq, pgss_hash);
- while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ if (userid != 0 && dbid != 0 && queryid != INT64CONST(0))
{
- Datum values[PG_STAT_STATEMENTS_COLS];
- bool nulls[PG_STAT_STATEMENTS_COLS];
- int i = 0;
- Counters tmp;
- double stddev;
- int64 queryid = entry->key.queryid;
- TimestampTz stats_since;
- TimestampTz minmax_stats_since;
-
- memset(values, 0, sizeof(values));
- memset(nulls, 0, sizeof(nulls));
-
- values[i++] = ObjectIdGetDatum(entry->key.userid);
- values[i++] = ObjectIdGetDatum(entry->key.dbid);
- if (api_version >= PGSS_V1_9)
- values[i++] = BoolGetDatum(entry->key.toplevel);
-
- if (is_allowed_role || entry->key.userid == userid)
- {
- if (api_version >= PGSS_V1_2)
- values[i++] = Int64GetDatumFast(queryid);
-
- if (showtext)
- {
- char *qstr = qtext_fetch(entry->query_offset,
- entry->query_len,
- qbuffer,
- qbuffer_size);
-
- if (qstr)
- {
- char *enc;
-
- enc = pg_any_to_server(qstr,
- entry->query_len,
- entry->encoding);
-
- values[i++] = CStringGetTextDatum(enc);
-
- if (enc != qstr)
- pfree(enc);
- }
- else
- {
- /* Just return a null if we fail to find the text */
- nulls[i++] = true;
- }
- }
- else
- {
- /* Query text not requested */
- nulls[i++] = true;
- }
- }
- else
- {
- /* Don't show queryid */
- if (api_version >= PGSS_V1_2)
- nulls[i++] = true;
-
- /*
- * Don't show query text, but hint as to the reason for not doing
- * so if it was requested
- */
- if (showtext)
- values[i++] = CStringGetTextDatum("<insufficient privilege>");
- else
- nulls[i++] = true;
- }
+ /* If all the parameters are available, use the fast path. */
+ pgssHashKey key;
+ memset(&key, 0, sizeof(pgssHashKey));
+ key.userid = userid;
+ key.dbid = dbid;
+ key.queryid = queryid;
- /* copy counters to a local variable to keep locking time short */
- SpinLockAcquire(&entry->mutex);
- tmp = entry->counters;
- SpinLockRelease(&entry->mutex);
+ /* Find the non-top-level entry. */
+ key.toplevel = false;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /*
- * The spinlock is not required when reading these two as they are
- * always updated when holding pgss->lock exclusively.
- */
- stats_since = entry->stats_since;
- minmax_stats_since = entry->minmax_stats_since;
+ if (entry)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
- /* Skip entry if unexecuted (ie, it's a pending "sticky" entry) */
- if (IS_STICKY(tmp))
- continue;
+ /* Also find the top-level entry. */
+ key.toplevel = true;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /* Note that we rely on PGSS_PLAN being 0 and PGSS_EXEC being 1. */
- for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ if (entry)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
+ }
+ else if (userid != 0 || dbid != 0 || queryid != INT64CONST(0))
+ {
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
- if (kind == PGSS_EXEC || api_version >= PGSS_V1_8)
- {
- values[i++] = Int64GetDatumFast(tmp.calls[kind]);
- values[i++] = Float8GetDatumFast(tmp.total_time[kind]);
- }
-
- if ((kind == PGSS_EXEC && api_version >= PGSS_V1_3) ||
- api_version >= PGSS_V1_8)
+ if ((!userid || entry->key.userid == userid) &&
+ (!dbid || entry->key.dbid == dbid) &&
+ (!queryid || entry->key.queryid == queryid))
{
- values[i++] = Float8GetDatumFast(tmp.min_time[kind]);
- values[i++] = Float8GetDatumFast(tmp.max_time[kind]);
- values[i++] = Float8GetDatumFast(tmp.mean_time[kind]);
-
- /*
- * Note we are calculating the population variance here, not
- * the sample variance, as we have data for the whole
- * population, so Bessel's correction is not used, and we
- * don't divide by tmp.calls - 1.
- */
- if (tmp.calls[kind] > 1)
- stddev = sqrt(tmp.sum_var_time[kind] / tmp.calls[kind]);
- else
- stddev = 0.0;
- values[i++] = Float8GetDatumFast(stddev);
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
}
}
- values[i++] = Int64GetDatumFast(tmp.rows);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
- if (api_version >= PGSS_V1_1)
- values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
- values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
- values[i++] = Int64GetDatumFast(tmp.local_blks_read);
- if (api_version >= PGSS_V1_1)
- values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
- values[i++] = Int64GetDatumFast(tmp.local_blks_written);
- values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
- values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
- if (api_version >= PGSS_V1_1)
- {
- values[i++] = Float8GetDatumFast(tmp.shared_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.shared_blk_write_time);
- }
- if (api_version >= PGSS_V1_11)
- {
- values[i++] = Float8GetDatumFast(tmp.local_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.local_blk_write_time);
- }
- if (api_version >= PGSS_V1_10)
- {
- values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
- }
- if (api_version >= PGSS_V1_8)
- {
- char buf[256];
- Datum wal_bytes;
-
- values[i++] = Int64GetDatumFast(tmp.wal_records);
- values[i++] = Int64GetDatumFast(tmp.wal_fpi);
-
- snprintf(buf, sizeof buf, UINT64_FORMAT, tmp.wal_bytes);
-
- /* Convert to numeric. */
- wal_bytes = DirectFunctionCall3(numeric_in,
- CStringGetDatum(buf),
- ObjectIdGetDatum(0),
- Int32GetDatum(-1));
- values[i++] = wal_bytes;
- }
- if (api_version >= PGSS_V1_12)
- {
- values[i++] = Int64GetDatumFast(tmp.wal_buffers_full);
- }
- if (api_version >= PGSS_V1_10)
- {
- values[i++] = Int64GetDatumFast(tmp.jit_functions);
- values[i++] = Float8GetDatumFast(tmp.jit_generation_time);
- values[i++] = Int64GetDatumFast(tmp.jit_inlining_count);
- values[i++] = Float8GetDatumFast(tmp.jit_inlining_time);
- values[i++] = Int64GetDatumFast(tmp.jit_optimization_count);
- values[i++] = Float8GetDatumFast(tmp.jit_optimization_time);
- 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);
- }
- if (api_version >= PGSS_V1_12)
- {
- values[i++] = Int64GetDatumFast(tmp.parallel_workers_to_launch);
- values[i++] = Int64GetDatumFast(tmp.parallel_workers_launched);
- }
- if (api_version >= PGSS_V1_13)
- {
- values[i++] = Int64GetDatumFast(tmp.generic_plan_calls);
- values[i++] = Int64GetDatumFast(tmp.custom_plan_calls);
- }
- if (api_version >= PGSS_V1_11)
- {
- values[i++] = TimestampTzGetDatum(stats_since);
- values[i++] = TimestampTzGetDatum(minmax_stats_since);
- }
-
- Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
- api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
- api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
- api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
- 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 :
- api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 :
- api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 :
- -1 /* fail if you forget to update this assert */ ));
-
- tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+ else
+ {
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
}
LWLockRelease(pgss->lock);
--
2.34.1
Re: pg_stat_statements: faster search by queryid
Here is the third version of the patch. Aleksandra Bondar and I added
some tests. I noticed that the new filtering test runs longer than other
pg_stat_statements tests because of the creation of two databases in it.
I'm thinking of reducing this test a little bit to create only one new
database.
Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/
Attachments:
v3-0001-pg_stat_statements-add-ability-to-filter-statisti.patchtext/x-patch; charset=US-ASCII; name=v3-0001-pg_stat_statements-add-ability-to-filter-statisti.patchDownload
From 6d3f685355ba678890431e67a307980d6f6a0259 Mon Sep 17 00:00:00 2001
From: Karina Litskevich <litskevichkarina@gmail.com>
Date: Wed, 3 Sep 2025 11:44:26 +0300
Subject: [PATCH v3] pg_stat_statements: add ability to filter statistics while
sacnning pgss_hash
Author: Aleksandra Bondar
Author: Karina Litskevich
---
contrib/pg_stat_statements/Makefile | 2 +-
.../pg_stat_statements/expected/filtering.out | 353 ++++++++++++
.../pg_stat_statements--1.12--1.13.sql | 3 +
.../pg_stat_statements/pg_stat_statements.c | 504 ++++++++++--------
contrib/pg_stat_statements/sql/filtering.sql | 214 ++++++++
5 files changed, 859 insertions(+), 217 deletions(-)
create mode 100644 contrib/pg_stat_statements/expected/filtering.out
create mode 100644 contrib/pg_stat_statements/sql/filtering.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index fe0478ac552..59858086b69 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -21,7 +21,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
REGRESS = select dml cursors utility level_tracking planning \
user_activity wal entry_timestamp privileges extended \
- parallel plancache cleanup oldextversions squashing
+ parallel plancache filtering cleanup oldextversions squashing
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/filtering.out b/contrib/pg_stat_statements/expected/filtering.out
new file mode 100644
index 00000000000..6aba78d9b7e
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/filtering.out
@@ -0,0 +1,353 @@
+--
+-- Setup
+--
+-- Databases and roles to test filtering by their oids
+CREATE DATABASE regression_db1;
+CREATE DATABASE regression_db2;
+CREATE ROLE regress_user1 LOGIN SUPERUSER;
+CREATE ROLE regress_user2 LOGIN SUPERUSER;
+SELECT oid AS db1_oid FROM pg_database WHERE datname = 'regression_db1' \gset
+SELECT oid AS db2_oid FROM pg_database WHERE datname = 'regression_db2' \gset
+SELECT oid AS user1_oid FROM pg_authid WHERE rolname = 'regress_user1' \gset
+SELECT oid AS user2_oid FROM pg_authid WHERE rolname = 'regress_user2' \gset
+-- Role to run all other queries
+CREATE ROLE regress_user LOGIN SUPERUSER;
+SET ROLE regress_user;
+-- Reset statistics to start clean
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+--
+-- Run all test queries
+--
+-- db1, user1
+\c regression_db1
+SET ROLE regress_user1;
+SELECT 'multiple_query_text' as multiple_test;
+ multiple_test
+---------------------
+ multiple_query_text
+(1 row)
+
+SELECT 'text_A'::text, 1;
+ text | ?column?
+--------+----------
+ text_A | 1
+(1 row)
+
+-- db1, user2
+SET ROLE regress_user2;
+SELECT 'multiple_query_text' as multiple_test;
+ multiple_test
+---------------------
+ multiple_query_text
+(1 row)
+
+SELECT 'text_B'::text, 1, 2;
+ text | ?column? | ?column?
+--------+----------+----------
+ text_B | 1 | 2
+(1 row)
+
+-- db2, user1
+\c regression_db2
+SET ROLE regress_user1;
+SELECT 'multiple_query_text' as multiple_test;
+ multiple_test
+---------------------
+ multiple_query_text
+(1 row)
+
+SELECT 'text_C'::text, 1, 2, 3;
+ text | ?column? | ?column? | ?column?
+--------+----------+----------+----------
+ text_C | 1 | 2 | 3
+(1 row)
+
+-- db2, user2
+SET ROLE regress_user2;
+SELECT 'multiple_query_text' as multiple_test;
+ multiple_test
+---------------------
+ multiple_query_text
+(1 row)
+
+SELECT 'text_D'::text, 1, 2, 3, 4;
+ text | ?column? | ?column? | ?column? | ?column?
+--------+----------+----------+----------+----------
+ text_D | 1 | 2 | 3 | 4
+(1 row)
+
+-- Switch to db and user other then db1, db2, user1, user2 to run tests
+\c contrib_regression
+SET ROLE regress_user;
+--
+-- Test 1: All zeroes (default values) should returns all records (no filtering)
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+--------------------+----------------------------------------------------+-------+------
+ regress_user | contrib_regression | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
+ regress_user | contrib_regression | SET ROLE regress_user | 1 | 0
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+ regress_user1 | regression_db1 | SET ROLE regress_user1 | 1 | 0
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1::text, $2, $3, $4 | 1 | 1
+ regress_user1 | regression_db2 | SET ROLE regress_user1 | 1 | 0
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1::text, $2, $3 | 1 | 1
+ regress_user2 | regression_db1 | SET ROLE regress_user2 | 1 | 0
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1::text, $2, $3, $4, $5 | 1 | 1
+ regress_user2 | regression_db2 | SET ROLE regress_user2 | 1 | 0
+(14 rows)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+--------------------+----------------------------------------------------+-------+------
+ regress_user | contrib_regression | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
+ regress_user | contrib_regression | SELECT rolname, datname, query, calls, rows +| 1 | 14
+ | | FROM pg_stat_statements($1) pgss +| |
+ | | JOIN pg_roles ON (pgss.userid = pg_roles.oid) +| |
+ | | JOIN pg_database ON (pgss.dbid = pg_database.oid) +| |
+ | | ORDER BY rolname, datname, query COLLATE "C" | |
+ regress_user | contrib_regression | SET ROLE regress_user | 1 | 0
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+ regress_user1 | regression_db1 | SET ROLE regress_user1 | 1 | 0
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1::text, $2, $3, $4 | 1 | 1
+ regress_user1 | regression_db2 | SET ROLE regress_user1 | 1 | 0
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1::text, $2, $3 | 1 | 1
+ regress_user2 | regression_db1 | SET ROLE regress_user2 | 1 | 0
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1::text, $2, $3, $4, $5 | 1 | 1
+ regress_user2 | regression_db2 | SET ROLE regress_user2 | 1 | 0
+(15 rows)
+
+--
+-- Test 2: Filter by userid only
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+-----------------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+ regress_user1 | regression_db1 | SET ROLE regress_user1 | 1 | 0
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1::text, $2, $3, $4 | 1 | 1
+ regress_user1 | regression_db2 | SET ROLE regress_user1 | 1 | 0
+(6 rows)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+---------------------------------+-------+------
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1::text, $2, $3 | 1 | 1
+ regress_user2 | regression_db1 | SET ROLE regress_user2 | 1 | 0
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1::text, $2, $3, $4, $5 | 1 | 1
+ regress_user2 | regression_db2 | SET ROLE regress_user2 | 1 | 0
+(6 rows)
+
+--
+-- Test 3: Filter by dbid only
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db1_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+----------------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+ regress_user1 | regression_db1 | SET ROLE regress_user1 | 1 | 0
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1::text, $2, $3 | 1 | 1
+ regress_user2 | regression_db1 | SET ROLE regress_user2 | 1 | 0
+(6 rows)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db2_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+---------------------------------+-------+------
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1::text, $2, $3, $4 | 1 | 1
+ regress_user1 | regression_db2 | SET ROLE regress_user1 | 1 | 0
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1::text, $2, $3, $4, $5 | 1 | 1
+ regress_user2 | regression_db2 | SET ROLE regress_user2 | 1 | 0
+(6 rows)
+
+--
+-- Get query IDs
+--
+SELECT queryid AS query1_id FROM pg_stat_statements
+WHERE query = 'SELECT $1::text, $2' \gset
+SELECT queryid AS query2_id FROM pg_stat_statements
+WHERE query = 'SELECT $1::text, $2, $3, $4, $5' \gset
+SELECT queryid AS multiple_query_id FROM pg_stat_statements
+WHERE query = 'SELECT $1 as multiple_test' LIMIT 1 \gset
+--
+-- Test 4: Filter by queryid only
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+---------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+(1 row)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :query2_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+---------------------------------+-------+------
+ regress_user2 | regression_db2 | SELECT $1::text, $2, $3, $4, $5 | 1 | 1
+(1 row)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+----------------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+(4 rows)
+
+--
+-- Test 5: Filter by userid and dbid
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db2_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+-----------------------------+-------+------
+ regress_user1 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+ regress_user1 | regression_db2 | SELECT $1::text, $2, $3, $4 | 1 | 1
+ regress_user1 | regression_db2 | SET ROLE regress_user1 | 1 | 0
+(3 rows)
+
+--
+-- Test 6: Filter by userid and queryid
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+----------------------------+-------+------
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db2 | SELECT $1 as multiple_test | 1 | 1
+(2 rows)
+
+--
+-- Test 7: Filter by dbid and queryid
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db1_oid, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+----------------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+(2 rows)
+
+--
+-- Test 8: Filter by userid and dbid and queryid
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db1_oid, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+---------------------+-------+------
+ regress_user1 | regression_db1 | SELECT $1::text, $2 | 1 | 1
+(1 row)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, :db1_oid, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------------+----------------+----------------------------+-------+------
+ regress_user2 | regression_db1 | SELECT $1 as multiple_test | 1 | 1
+(1 row)
+
+--
+-- Test 9: No matching queries
+--
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------+---------+-------+-------+------
+(0 rows)
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db1_oid, :query2_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+ rolname | datname | query | calls | rows
+---------+---------+-------+-------+------
+(0 rows)
+
+--
+-- Cleanup
+--
+DROP DATABASE regression_db1;
+DROP DATABASE regression_db2;
+RESET ROLE;
+DROP ROLE regress_user1;
+DROP ROLE regress_user2;
+DROP ROLE regress_user;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
index 2f0eaf14ec3..613835f8c99 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
@@ -13,6 +13,9 @@ DROP FUNCTION pg_stat_statements(boolean);
/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ IN userid oid DEFAULT 0,
+ IN dbid oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0,
OUT userid oid,
OUT dbid oid,
OUT toplevel bool,
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index f2187167c5c..f96c4705967 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -364,7 +364,10 @@ static void pgss_store(const char *query, int64 queryId,
PlannedStmtOrigin planOrigin);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext);
+ bool showtext,
+ Oid userid,
+ Oid dbid,
+ int64 queryid);
static Size pgss_memsize(void);
static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
int encoding, bool sticky);
@@ -1597,8 +1600,12 @@ Datum
pg_stat_statements_1_13(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
+ Oid userid = PG_GETARG_OID(1);
+ Oid dbid = PG_GETARG_OID(2);
+ int64 queryid = PG_GETARG_INT64(3);
- pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext,
+ userid, dbid, queryid);
return (Datum) 0;
}
@@ -1608,7 +1615,7 @@ pg_stat_statements_1_12(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_12, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1618,7 +1625,7 @@ pg_stat_statements_1_11(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1628,7 +1635,7 @@ pg_stat_statements_1_10(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1638,7 +1645,7 @@ pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1648,7 +1655,7 @@ pg_stat_statements_1_8(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_8, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1658,7 +1665,7 @@ pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1668,7 +1675,7 @@ pg_stat_statements_1_2(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
- pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_2, showtext, 0, 0, 0);
return (Datum) 0;
}
@@ -1681,19 +1688,243 @@ Datum
pg_stat_statements(PG_FUNCTION_ARGS)
{
/* If it's really API 1.1, we'll figure that out below */
- pg_stat_statements_internal(fcinfo, PGSS_V1_0, true);
+ pg_stat_statements_internal(fcinfo, PGSS_V1_0, true, 0, 0, 0);
return (Datum) 0;
}
+static void
+pg_stat_statements_handle_entry(const ReturnSetInfo *rsinfo,
+ pgssEntry *entry,
+ bool showtext,
+ bool is_allowed_role,
+ Oid userid,
+ char *qbuffer,
+ Size qbuffer_size,
+ pgssVersion api_version)
+{
+ Datum values[PG_STAT_STATEMENTS_COLS];
+ bool nulls[PG_STAT_STATEMENTS_COLS];
+ int i = 0;
+ Counters tmp;
+ double stddev;
+ int64 queryid = entry->key.queryid;
+ TimestampTz stats_since;
+ TimestampTz minmax_stats_since;
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, 0, sizeof(nulls));
+
+ values[i++] = ObjectIdGetDatum(entry->key.userid);
+ values[i++] = ObjectIdGetDatum(entry->key.dbid);
+ if (api_version >= PGSS_V1_9)
+ values[i++] = BoolGetDatum(entry->key.toplevel);
+
+ if (is_allowed_role || entry->key.userid == userid)
+ {
+ if (api_version >= PGSS_V1_2)
+ values[i++] = Int64GetDatumFast(queryid);
+
+ if (showtext)
+ {
+ char *qstr = qtext_fetch(entry->query_offset,
+ entry->query_len,
+ qbuffer,
+ qbuffer_size);
+
+ if (qstr)
+ {
+ char *enc;
+
+ enc = pg_any_to_server(qstr,
+ entry->query_len,
+ entry->encoding);
+
+ values[i++] = CStringGetTextDatum(enc);
+
+ if (enc != qstr)
+ pfree(enc);
+ }
+ else
+ {
+ /* Just return a null if we fail to find the text */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* Query text not requested */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* Don't show queryid */
+ if (api_version >= PGSS_V1_2)
+ nulls[i++] = true;
+
+ /*
+ * Don't show query text, but hint as to the reason for not doing
+ * so if it was requested
+ */
+ if (showtext)
+ values[i++] = CStringGetTextDatum("<insufficient privilege>");
+ else
+ nulls[i++] = true;
+ }
+
+ /* copy counters to a local variable to keep locking time short */
+ SpinLockAcquire(&entry->mutex);
+ tmp = entry->counters;
+ SpinLockRelease(&entry->mutex);
+
+ /*
+ * The spinlock is not required when reading these two as they are
+ * always updated when holding pgss->lock exclusively.
+ */
+ stats_since = entry->stats_since;
+ minmax_stats_since = entry->minmax_stats_since;
+
+ /* Skip entry if unexecuted (ie, it's a pending "sticky" entry) */
+ if (IS_STICKY(tmp))
+ return;
+
+ /* Note that we rely on PGSS_PLAN being 0 and PGSS_EXEC being 1. */
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ if (kind == PGSS_EXEC || api_version >= PGSS_V1_8)
+ {
+ values[i++] = Int64GetDatumFast(tmp.calls[kind]);
+ values[i++] = Float8GetDatumFast(tmp.total_time[kind]);
+ }
+
+ if ((kind == PGSS_EXEC && api_version >= PGSS_V1_3) ||
+ api_version >= PGSS_V1_8)
+ {
+ values[i++] = Float8GetDatumFast(tmp.min_time[kind]);
+ values[i++] = Float8GetDatumFast(tmp.max_time[kind]);
+ values[i++] = Float8GetDatumFast(tmp.mean_time[kind]);
+
+ /*
+ * Note we are calculating the population variance here, not
+ * the sample variance, as we have data for the whole
+ * population, so Bessel's correction is not used, and we
+ * don't divide by tmp.calls - 1.
+ */
+ if (tmp.calls[kind] > 1)
+ stddev = sqrt(tmp.sum_var_time[kind] / tmp.calls[kind]);
+ else
+ stddev = 0.0;
+ values[i++] = Float8GetDatumFast(stddev);
+ }
+ }
+ values[i++] = Int64GetDatumFast(tmp.rows);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
+ if (api_version >= PGSS_V1_1)
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_read);
+ if (api_version >= PGSS_V1_1)
+ values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
+ values[i++] = Int64GetDatumFast(tmp.local_blks_written);
+ values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
+ values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
+ if (api_version >= PGSS_V1_1)
+ {
+ values[i++] = Float8GetDatumFast(tmp.shared_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.shared_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_11)
+ {
+ values[i++] = Float8GetDatumFast(tmp.local_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.local_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
+ values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
+ }
+ if (api_version >= PGSS_V1_8)
+ {
+ char buf[256];
+ Datum wal_bytes;
+
+ values[i++] = Int64GetDatumFast(tmp.wal_records);
+ values[i++] = Int64GetDatumFast(tmp.wal_fpi);
+
+ snprintf(buf, sizeof buf, UINT64_FORMAT, tmp.wal_bytes);
+
+ /* Convert to numeric. */
+ wal_bytes = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(buf),
+ ObjectIdGetDatum(0),
+ Int32GetDatum(-1));
+ values[i++] = wal_bytes;
+ }
+ if (api_version >= PGSS_V1_12)
+ {
+ values[i++] = Int64GetDatumFast(tmp.wal_buffers_full);
+ }
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = Int64GetDatumFast(tmp.jit_functions);
+ values[i++] = Float8GetDatumFast(tmp.jit_generation_time);
+ values[i++] = Int64GetDatumFast(tmp.jit_inlining_count);
+ values[i++] = Float8GetDatumFast(tmp.jit_inlining_time);
+ values[i++] = Int64GetDatumFast(tmp.jit_optimization_count);
+ values[i++] = Float8GetDatumFast(tmp.jit_optimization_time);
+ 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);
+ }
+ if (api_version >= PGSS_V1_12)
+ {
+ values[i++] = Int64GetDatumFast(tmp.parallel_workers_to_launch);
+ values[i++] = Int64GetDatumFast(tmp.parallel_workers_launched);
+ }
+ if (api_version >= PGSS_V1_13)
+ {
+ values[i++] = Int64GetDatumFast(tmp.generic_plan_calls);
+ values[i++] = Int64GetDatumFast(tmp.custom_plan_calls);
+ }
+ if (api_version >= PGSS_V1_11)
+ {
+ values[i++] = TimestampTzGetDatum(stats_since);
+ values[i++] = TimestampTzGetDatum(minmax_stats_since);
+ }
+
+ Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
+ api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
+ api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
+ api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+ 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 :
+ api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 :
+ api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 :
+ -1 /* fail if you forget to update this assert */ ));
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
- bool showtext)
+ bool showtext,
+ Oid userid,
+ Oid dbid,
+ int64 queryid)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- Oid userid = GetUserId();
+ Oid current_userid = GetUserId();
bool is_allowed_role = false;
char *qbuffer = NULL;
Size qbuffer_size = 0;
@@ -1706,7 +1937,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
* Superusers or roles with the privileges of pg_read_all_stats members
* are allowed
*/
- is_allowed_role = has_privs_of_role(userid, ROLE_PG_READ_ALL_STATS);
+ is_allowed_role = has_privs_of_role(current_userid, ROLE_PG_READ_ALL_STATS);
/* hash table must exist already */
if (!pgss || !pgss_hash)
@@ -1829,218 +2060,59 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
}
}
- hash_seq_init(&hash_seq, pgss_hash);
- while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ if (userid != 0 && dbid != 0 && queryid != INT64CONST(0))
{
- Datum values[PG_STAT_STATEMENTS_COLS];
- bool nulls[PG_STAT_STATEMENTS_COLS];
- int i = 0;
- Counters tmp;
- double stddev;
- int64 queryid = entry->key.queryid;
- TimestampTz stats_since;
- TimestampTz minmax_stats_since;
-
- memset(values, 0, sizeof(values));
- memset(nulls, 0, sizeof(nulls));
-
- values[i++] = ObjectIdGetDatum(entry->key.userid);
- values[i++] = ObjectIdGetDatum(entry->key.dbid);
- if (api_version >= PGSS_V1_9)
- values[i++] = BoolGetDatum(entry->key.toplevel);
-
- if (is_allowed_role || entry->key.userid == userid)
- {
- if (api_version >= PGSS_V1_2)
- values[i++] = Int64GetDatumFast(queryid);
-
- if (showtext)
- {
- char *qstr = qtext_fetch(entry->query_offset,
- entry->query_len,
- qbuffer,
- qbuffer_size);
-
- if (qstr)
- {
- char *enc;
-
- enc = pg_any_to_server(qstr,
- entry->query_len,
- entry->encoding);
-
- values[i++] = CStringGetTextDatum(enc);
-
- if (enc != qstr)
- pfree(enc);
- }
- else
- {
- /* Just return a null if we fail to find the text */
- nulls[i++] = true;
- }
- }
- else
- {
- /* Query text not requested */
- nulls[i++] = true;
- }
- }
- else
- {
- /* Don't show queryid */
- if (api_version >= PGSS_V1_2)
- nulls[i++] = true;
-
- /*
- * Don't show query text, but hint as to the reason for not doing
- * so if it was requested
- */
- if (showtext)
- values[i++] = CStringGetTextDatum("<insufficient privilege>");
- else
- nulls[i++] = true;
- }
+ /* If all the parameters are available, use the fast path. */
+ pgssHashKey key;
+ memset(&key, 0, sizeof(pgssHashKey));
+ key.userid = userid;
+ key.dbid = dbid;
+ key.queryid = queryid;
- /* copy counters to a local variable to keep locking time short */
- SpinLockAcquire(&entry->mutex);
- tmp = entry->counters;
- SpinLockRelease(&entry->mutex);
+ /* Find the non-top-level entry. */
+ key.toplevel = false;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /*
- * The spinlock is not required when reading these two as they are
- * always updated when holding pgss->lock exclusively.
- */
- stats_since = entry->stats_since;
- minmax_stats_since = entry->minmax_stats_since;
+ if (entry)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
- /* Skip entry if unexecuted (ie, it's a pending "sticky" entry) */
- if (IS_STICKY(tmp))
- continue;
+ /* Also find the top-level entry. */
+ key.toplevel = true;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /* Note that we rely on PGSS_PLAN being 0 and PGSS_EXEC being 1. */
- for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ if (entry)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
+ }
+ else if (userid != 0 || dbid != 0 || queryid != INT64CONST(0))
+ {
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
- if (kind == PGSS_EXEC || api_version >= PGSS_V1_8)
- {
- values[i++] = Int64GetDatumFast(tmp.calls[kind]);
- values[i++] = Float8GetDatumFast(tmp.total_time[kind]);
- }
-
- if ((kind == PGSS_EXEC && api_version >= PGSS_V1_3) ||
- api_version >= PGSS_V1_8)
+ if ((!userid || entry->key.userid == userid) &&
+ (!dbid || entry->key.dbid == dbid) &&
+ (!queryid || entry->key.queryid == queryid))
{
- values[i++] = Float8GetDatumFast(tmp.min_time[kind]);
- values[i++] = Float8GetDatumFast(tmp.max_time[kind]);
- values[i++] = Float8GetDatumFast(tmp.mean_time[kind]);
-
- /*
- * Note we are calculating the population variance here, not
- * the sample variance, as we have data for the whole
- * population, so Bessel's correction is not used, and we
- * don't divide by tmp.calls - 1.
- */
- if (tmp.calls[kind] > 1)
- stddev = sqrt(tmp.sum_var_time[kind] / tmp.calls[kind]);
- else
- stddev = 0.0;
- values[i++] = Float8GetDatumFast(stddev);
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
}
}
- values[i++] = Int64GetDatumFast(tmp.rows);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
- if (api_version >= PGSS_V1_1)
- values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
- values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
- values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
- values[i++] = Int64GetDatumFast(tmp.local_blks_read);
- if (api_version >= PGSS_V1_1)
- values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
- values[i++] = Int64GetDatumFast(tmp.local_blks_written);
- values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
- values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
- if (api_version >= PGSS_V1_1)
- {
- values[i++] = Float8GetDatumFast(tmp.shared_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.shared_blk_write_time);
- }
- if (api_version >= PGSS_V1_11)
- {
- values[i++] = Float8GetDatumFast(tmp.local_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.local_blk_write_time);
- }
- if (api_version >= PGSS_V1_10)
- {
- values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
- values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
- }
- if (api_version >= PGSS_V1_8)
- {
- char buf[256];
- Datum wal_bytes;
-
- values[i++] = Int64GetDatumFast(tmp.wal_records);
- values[i++] = Int64GetDatumFast(tmp.wal_fpi);
-
- snprintf(buf, sizeof buf, UINT64_FORMAT, tmp.wal_bytes);
-
- /* Convert to numeric. */
- wal_bytes = DirectFunctionCall3(numeric_in,
- CStringGetDatum(buf),
- ObjectIdGetDatum(0),
- Int32GetDatum(-1));
- values[i++] = wal_bytes;
- }
- if (api_version >= PGSS_V1_12)
- {
- values[i++] = Int64GetDatumFast(tmp.wal_buffers_full);
- }
- if (api_version >= PGSS_V1_10)
- {
- values[i++] = Int64GetDatumFast(tmp.jit_functions);
- values[i++] = Float8GetDatumFast(tmp.jit_generation_time);
- values[i++] = Int64GetDatumFast(tmp.jit_inlining_count);
- values[i++] = Float8GetDatumFast(tmp.jit_inlining_time);
- values[i++] = Int64GetDatumFast(tmp.jit_optimization_count);
- values[i++] = Float8GetDatumFast(tmp.jit_optimization_time);
- 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);
- }
- if (api_version >= PGSS_V1_12)
- {
- values[i++] = Int64GetDatumFast(tmp.parallel_workers_to_launch);
- values[i++] = Int64GetDatumFast(tmp.parallel_workers_launched);
- }
- if (api_version >= PGSS_V1_13)
- {
- values[i++] = Int64GetDatumFast(tmp.generic_plan_calls);
- values[i++] = Int64GetDatumFast(tmp.custom_plan_calls);
- }
- if (api_version >= PGSS_V1_11)
- {
- values[i++] = TimestampTzGetDatum(stats_since);
- values[i++] = TimestampTzGetDatum(minmax_stats_since);
- }
-
- Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
- api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
- api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
- api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
- 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 :
- api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 :
- api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 :
- -1 /* fail if you forget to update this assert */ ));
-
- tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+ else
+ {
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ pg_stat_statements_handle_entry(rsinfo, entry, showtext,
+ is_allowed_role, current_userid,
+ qbuffer, qbuffer_size,
+ api_version);
}
LWLockRelease(pgss->lock);
diff --git a/contrib/pg_stat_statements/sql/filtering.sql b/contrib/pg_stat_statements/sql/filtering.sql
new file mode 100644
index 00000000000..b518f8ca770
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/filtering.sql
@@ -0,0 +1,214 @@
+--
+-- Setup
+--
+
+-- Databases and roles to test filtering by their oids
+CREATE DATABASE regression_db1;
+CREATE DATABASE regression_db2;
+CREATE ROLE regress_user1 LOGIN SUPERUSER;
+CREATE ROLE regress_user2 LOGIN SUPERUSER;
+
+SELECT oid AS db1_oid FROM pg_database WHERE datname = 'regression_db1' \gset
+SELECT oid AS db2_oid FROM pg_database WHERE datname = 'regression_db2' \gset
+SELECT oid AS user1_oid FROM pg_authid WHERE rolname = 'regress_user1' \gset
+SELECT oid AS user2_oid FROM pg_authid WHERE rolname = 'regress_user2' \gset
+
+-- Role to run all other queries
+CREATE ROLE regress_user LOGIN SUPERUSER;
+SET ROLE regress_user;
+
+-- Reset statistics to start clean
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+
+--
+-- Run all test queries
+--
+
+-- db1, user1
+\c regression_db1
+SET ROLE regress_user1;
+SELECT 'multiple_query_text' as multiple_test;
+SELECT 'text_A'::text, 1;
+
+-- db1, user2
+SET ROLE regress_user2;
+SELECT 'multiple_query_text' as multiple_test;
+SELECT 'text_B'::text, 1, 2;
+
+-- db2, user1
+\c regression_db2
+SET ROLE regress_user1;
+SELECT 'multiple_query_text' as multiple_test;
+SELECT 'text_C'::text, 1, 2, 3;
+
+-- db2, user2
+SET ROLE regress_user2;
+SELECT 'multiple_query_text' as multiple_test;
+SELECT 'text_D'::text, 1, 2, 3, 4;
+
+-- Switch to db and user other then db1, db2, user1, user2 to run tests
+\c contrib_regression
+SET ROLE regress_user;
+
+--
+-- Test 1: All zeroes (default values) should returns all records (no filtering)
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 2: Filter by userid only
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 3: Filter by dbid only
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db1_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db2_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+
+--
+-- Get query IDs
+--
+
+SELECT queryid AS query1_id FROM pg_stat_statements
+WHERE query = 'SELECT $1::text, $2' \gset
+
+SELECT queryid AS query2_id FROM pg_stat_statements
+WHERE query = 'SELECT $1::text, $2, $3, $4, $5' \gset
+
+SELECT queryid AS multiple_query_id FROM pg_stat_statements
+WHERE query = 'SELECT $1 as multiple_test' LIMIT 1 \gset
+
+--
+-- Test 4: Filter by queryid only
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :query2_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, 0, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 5: Filter by userid and dbid
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db2_oid, 0) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 6: Filter by userid and queryid
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 7: Filter by dbid and queryid
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, 0, :db1_oid, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+
+--
+-- Test 8: Filter by userid and dbid and queryid
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db1_oid, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, :db1_oid, :multiple_query_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+--
+-- Test 9: No matching queries
+--
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user2_oid, 0, :query1_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+SELECT rolname, datname, query, calls, rows
+FROM pg_stat_statements(true, :user1_oid, :db1_oid, :query2_id) pgss
+JOIN pg_roles ON (pgss.userid = pg_roles.oid)
+JOIN pg_database ON (pgss.dbid = pg_database.oid)
+ORDER BY rolname, datname, query COLLATE "C";
+
+
+--
+-- Cleanup
+--
+
+DROP DATABASE regression_db1;
+DROP DATABASE regression_db2;
+
+RESET ROLE;
+DROP ROLE regress_user1;
+DROP ROLE regress_user2;
+DROP ROLE regress_user;
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
--
2.34.1