query column in pg_stat_statements and pg_stat_activity

From: Satalabaha Postgres <satalabaha(dot)postgres(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: query column in pg_stat_statements and pg_stat_activity
Date: 2024-06-04 06:46:09
Message-ID: CAJ_W8nZYp95YqiAmyHGutv6sxPFnHxdtpurNLg6HuGg=Wo4=Ug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I've written the below SQL query that joins pg_stat_statements with
pg_stat_activity using "queryid" as the join condition. Yet, the results
show that pg_stat_statements and pg_stat_activity are reporting two
distinct queries for the identical queryid. Can this occur?

select

pgss.queryid as "PGSS_QUERYID",

pgss.query as "PGSS_QUERY",

pgsa.query_id as "PGSA_QUERYID",

substring(pgsa.query,

1,

45) as "PGSA_QUERY"

from

pg_stat_statements pgss

join pg_stat_activity pgsa on

pgss.queryid = pgsa.query_id

and pgss.queryid = '2397681704071010949';

PGSS_QUERYID | PGSS_QUERY | PGSA_QUERYID |
PGSA_QUERY
---------------------+------------+---------------------+-----------------------------------------------
2397681704071010949 | BEGIN | 2397681704071010949 | select
projectper0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
projectper0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT
item_guid, count(item_guid) count FR
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT
item_guid, count(item_guid) count FR
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT distinct
ep.role FROM v_project_permis
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT distinct
ep.role FROM v_project_permis
2397681704071010949 | BEGIN | 2397681704071010949 | select
this_.CONTACT_SID as CONTACT1_362_1_,
2397681704071010949 | BEGIN | 2397681704071010949 | select
this_.CONTACT_SID as CONTACT1_362_1_,
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT
item_guid,category,root_guid, count(i
2397681704071010949 | BEGIN | 2397681704071010949 | SELECT
item_guid,category,root_guid, count(i
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
2397681704071010949 | BEGIN | 2397681704071010949 | select
folderperm0_.ENTITY_PERMISSION_SID as
Regards,

Satalabha

Browse pgsql-performance by date

  From Date Subject
Next Message Vitaliy Litovskiy 2024-06-10 06:59:04 Distinct performance dropped by multiple times in v16
Previous Message Chema 2024-05-30 04:03:16 Re: Plan selection based on worst case scenario