Postgres. How to check the top queries that use temporary files?

#postgres #sql #devops #performance

For Postgres version up to 12:

SELECT
        (select datname from pg_database where oid=dbid) datname,
        interval '1 millisecond' * total_time AS total_exec_time,
        total_time / calls AS avg_exec_time_ms,
        temp_blks_written,
        substr(query, 1, 500) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

For Postgres version starting 13:

SELECT
        (select datname from pg_database where oid=dbid) datname,
        interval '1 millisecond' * total_exec_time AS total_exec_time,
        total_exec_time / calls AS avg_exec_time_ms,
        temp_blks_written,
        substr(query, 1, 500) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;