'포스트그레스 TOP SQL 추출'에 해당되는 글 1건

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;
블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,