pw2_ss_top_alt_v9.4.sql

explain analyse
with q_data as (
    select
    (extract (epoch from now()) * 1e9)::int8 as epoch_ns,
    queryid::text as tag_queryid,
    /*
    NB! if security conscious about exposing query texts replace the below expression with a dash ('-') OR
    use the stat_statements_no_query_text metric instead, created specifically for this use case.
    */
    --max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query,
    array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users,
    sum(s.calls)::int8 as calls,
    round(sum(s.total_time)::numeric, 3)::double precision as total_time,
    sum(shared_blks_hit)::int8 as shared_blks_hit,
    sum(shared_blks_read)::int8 as shared_blks_read,
    sum(shared_blks_written)::int8 as shared_blks_written,
    sum(shared_blks_dirtied)::int8 as shared_blks_dirtied,
    sum(temp_blks_read)::int8 as temp_blks_read,
    sum(temp_blks_written)::int8 as temp_blks_written,
    round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time,
    round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time
    from
    pg_stat_statements s
    where
    calls > 5
    and total_time > 0
    and dbid = (select oid from pg_database where datname = current_database())
    and not upper(s.query) like any (array ['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;',
    'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%'])
    group by
    queryid
), q_queryid_text as (
    select queryid::text, query::varchar(16000)
    from pg_stat_statements
    where dbid = (select oid from pg_database where datname = current_database())
)

select
    b.*,
    (select ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g'))
     from q_queryid_text where q_queryid_text.queryid = b.tag_queryid limit 1) as tag_query
from (
    select * from (
    select
    *
    from
    q_data
    where
    total_time > 0
    order by
    total_time desc
    limit 100
    ) a
    union
    select * from (
    select
    *
    from
    q_data
    order by
    calls desc
    limit 100
    ) a
    union
    select * from (
    select
    *
    from
    q_data
    where
    shared_blks_read > 0
    order by
    shared_blks_read desc
    limit 100
    ) a
    union
    select * from (
    select
    *
    from
    q_data
    where
    shared_blks_written > 0
    order by
    shared_blks_written desc
    limit 100
    ) a
    union
    select * from (
    select
    *
    from
    q_data
    where
    temp_blks_read > 0
    order by
    temp_blks_read desc
    limit 100
    ) a
    union
    select * from (
    select
    *
    from
    q_data
    where
    temp_blks_written > 0
    order by
    temp_blks_written desc
    limit 100
    ) a
) b;