Postgresql - Statistiques d'exécution de requêtes avec `pg_stat_statements`

pg_stat_statements_query.sql

WITH stats_reset AS (
        SELECT EXTRACT(
            EPOCH FROM (
                CURRENT_TIMESTAMP -
                (
                    SELECT stats_reset FROM pg_stat_statements_info
                )
            )
        ) / 3600 AS hours_since_stats_reset
),

t AS (
    SELECT
        pg_user.usename AS db_user_name,
        UPPER(LEFT(query, 80)) AS query, 
        SUM(total_exec_time) / 1000 AS total_time,
        SUM(total_exec_time) / SUM(calls) / 1000 AS mean_time,
        SUM(total_exec_time) / 1000 / (SELECT hours_since_stats_reset FROM stats_reset) AS time_per_hour,
        SUM(calls) AS calls,
        SUM(calls) / (SELECT hours_since_stats_reset FROM stats_reset) AS  calls_per_hour,
        SUM(total_exec_time) / (SELECT SUM(total_exec_time) FROM pg_stat_statements) AS share_of_total_time
    FROM pg_stat_statements
    JOIN pg_user
    ON pg_user.usesysid = pg_stat_statements.userid
    GROUP BY 1, 2
)

SELECT
    *,
    SUM(share_of_total_time) OVER (ORDER BY total_time DESC) AS cumulative_share_of_total_time
FROM t
ORDER BY total_time DESC
LIMIT 20