Re: BUG #18790: Pg_stat_statements doesn't track schema.

On Thu, Jan 30, 2025 at 11:30 AM Raghvendra Mishra <raghshr1351(at)gmail(dot)com>
wrote:

> If multiple schema is used in a query then this information can be
> extracted by parsing the query.
>
But when the schema is being accessed by setting the search path then it
> becomes hard to find with which schema
> query belongs to in pg_stat_statements.
>

Even if you were to store the search_path as a separate field, there is no
promise that the items in it have not changed since the query was added to
pg_stat_statements. Your best bet is to schema-qualify your relations when
writing your queries. Then your pg_stat_statement output will contain the
information you want.

Note that you can use the queryid to figure out (with a little work) which
schemas were used for particular queries:

CREATE SCHEMA a; CREATE TABLE a.foo (id int);
CREATE SCHEMA b; CREATE TABLE b.foo (id int);
SET search_path = a; select * from foo;
SET search_path = b; select * from foo;
RESET search_path;
select query, queryid from pg_stat_statements where query ~ 'select \* from
foo';
query | queryid
-------------------+----------------------
select * from foo | 255924940643424438
select * from foo | -7783557204835816030
(2 rows)

greg=# explain verbose select * from a.foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on a.foo (cost=0.00..35.50 rows=2550 width=4)
Output: id
Query Identifier: 255924940643424438
(3 rows)

greg=# explain verbose select * from b.foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on b.foo (cost=0.00..35.50 rows=2550 width=4)
Output: id
Query Identifier: -7783557204835816030

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support