Thread: pg_stat_statements: can we extend the object names to the qualifiednames?

Hi,
It would help to analyze performance issues if pg_stat_statements would 
extend the object names to the qualified names.
Currently if we have two schemas ( say s1 and s2) with the objects with 
the same name ( say tables t1) then
after the next executions:

set schema 's1';
select count(*) from t1; // returns 10
set schema 's2';
select count(*) from t1; // returns 1000000000

  we see in
select queryid, query from pg_stat_statements where query like '%from t1%'
something like this
3004391594 select count(*) from t1
1336375111 select count(*) from t1

We do see that the queries are different but we can't see why they are 
so much different in the execution time.
If the pg_stat_statements module would extend the object name to the 
qualified names like s1.t1 and s2.t2 then we would see the report as
3004391594 select count(*) from s1.t1
1336375111 select count(*) from s2.t1
with an immediate understanding of what's going on.

Obviously it would be even bigger help in the situations with the more 
complex queries where it will be mush more difficult to find
that the query was executed with the incorrect search_path settings.


Thank you,

Sergei Agalakov

A part of the answer would be to store
explain (verbose on) select count(*) from t1;
result in pg_stat_statements for the corresponding query...

(Verbose On) gives the "qualified names":

 QUERY PLAN
---------------------------------------
Aggregate  (cost=19.38..19.39 rows=1 width=8)
   Output: count(*)
   ->  Seq Scan on s1.t1  (cost=0.00..17.50 rows=750 width=0)
                            
 
         Output: a1, a2, a3, a4, a5, a6


Extension pg_store_plans
https://github.com/ossc-db/pg_store_plans
can do it.

Extesion auto_explain can help also.

There are also some tryies to extend pg_stat_statements 
with plans see 
https://www.postgresql-archive.org/FEATURE-PATCH-pg-stat-statements-with-plans-td5940964.html

Regards
PAscal






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2018-Nov-27, Sergei Agalakov wrote:

> We do see that the queries are different but we can't see why they are so
> much different in the execution time.
> If the pg_stat_statements module would extend the object name to the
> qualified names like s1.t1 and s2.t2 then we would see the report as
> 3004391594 select count(*) from s1.t1
> 1336375111 select count(*) from s2.t1
> with an immediate understanding of what's going on.

I think this is hard to do in a really useful manner -- if we qualify
all names in the query, it becomes mangled to the point that it's no
longer easily recognizable (users want to "grep" their applications to
see where the queries are).  Maybe we can have a second column that
shows the query with all object names schema-qualified, keeping the
original one that uses the query as entered by the user.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-Nov-27, legrand legrand wrote:

> There are also some tryies to extend pg_stat_statements 
> with plans see 
> https://www.postgresql-archive.org/FEATURE-PATCH-pg-stat-statements-with-plans-td5940964.html

Thread at http://postgr.es/m/9e43fd8f-4d35-4b9d-545c-f9011cd4aa5d@uni-muenster.de

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I like it. I don't want to break backward compatibility.
The new column could be named like query_qn, and will have here the 
unambitious text of the query
where all the objects names are extended to the qualified names.

Sergei Agalakov

On 11/27/2018 2:17 PM, Alvaro Herrera wrote:
> On 2018-Nov-27, Sergei Agalakov wrote:
>
>> We do see that the queries are different but we can't see why they are so
>> much different in the execution time.
>> If the pg_stat_statements module would extend the object name to the
>> qualified names like s1.t1 and s2.t2 then we would see the report as
>> 3004391594 select count(*) from s1.t1
>> 1336375111 select count(*) from s2.t1
>> with an immediate understanding of what's going on.
> I think this is hard to do in a really useful manner -- if we qualify
> all names in the query, it becomes mangled to the point that it's no
> longer easily recognizable (users want to "grep" their applications to
> see where the queries are).  Maybe we can have a second column that
> shows the query with all object names schema-qualified, keeping the
> original one that uses the query as entered by the user.
>

I don't agree.
If we already had a column with the execution plan in the 
pg_stat_statements then
it would be a workaround for the problem with the ambiguous names in the 
query text column.
But we don't have such column, and I don't want to create a dependency 
on the unimplemented feature.
I rather fancy an extra column query_qn with the query text where all 
the object names are substituted to the qualified names.
Remember that this problem isn't about only table names. The query may 
have views, functions, operands etc. from the 'wrong' schema.

Sergei Agalakov

On 11/27/2018 2:10 PM, legrand legrand wrote:
> A part of the answer would be to store
> explain (verbose on) select count(*) from t1;
> result in pg_stat_statements for the corresponding query...
>
> (Verbose On) gives the "qualified names":
>
>   QUERY PLAN
> ---------------------------------------
> Aggregate  (cost=19.38..19.39 rows=1 width=8)
>     Output: count(*)
>     ->  Seq Scan on s1.t1  (cost=0.00..17.50 rows=750 width=0)
>           Output: a1, a2, a3, a4, a5, a6
>
>
> Extension pg_store_plans
> https://github.com/ossc-db/pg_store_plans
> can do it.
>
> Extesion auto_explain can help also.
>
> There are also some tryies to extend pg_stat_statements
> with plans see
> https://www.postgresql-archive.org/FEATURE-PATCH-pg-stat-statements-with-plans-td5940964.html
>
> Regards
> PAscal
>
>
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>