PostgreSQL Top SQL 추출 스크립트

PostgreSQL Top SQL 추출 스크립트

---

do $$ declare    has_pg_stat_statements boolean; begin    select count(*)>0 from pg_extension where extname = 'pg_stat_statements' into has_pg_stat_statements;    if has_pg_stat_statements = false then         create extension pg_stat_statements;         raise notice 'create extension pg_stat_statements';    end if; end $$; select         queryid,         sum( calls ) as calls,         round(( sum( total_time )/ 1000 )::numeric, 2 ) as total_time,         round(( min( min_time )/ 1000 )::numeric, 2 ) as min_time,         round(( max( max_time )/ 1000 )::numeric, 2 ) as max_time,         round((( sum( mean_time )/ 1000 )/ count(*))::numeric, 2 ) as mean_time,         (                 sum( rows )/ count(*)         )::int as rows,         (                 sum( shared_blks_hit )/ count(*)         )::int as shared_blks_hit,         max( query ) as query from         pg_stat_statements ptt where         dbid in(                 select                         oid                 from                         pg_database                 where                         datname = current_database()         )         and strpos(                 query,                 'create'         )= 0         and strpos(                 query,                 'drop'         )= 0         and strpos(                 query,                 'copy'         )= 0         and strpos(                 query,                 'pg_stat_user'         )= 0         and strpos(                 query,                 'vacuum'         )= 0 group by         queryid order by         total_time desc,         mean_time desc limit 50;