[PostgreSQL] 테이블의 행(Row) 개수와 테이블 크기(Size) 구하기
PostgreSQL에서 테이블의 행(Row) 개수와 테이블 크기(Size) 구해보자
환경
- PostgreSQL
행(Row) 개수
- 쿼리에서 COUNT(*)를 이용
select count(*) from {table_name};
- pg_class를 통해 어림잡은 값 가져오기
select relname, reltuples from pg_class where relname='{table_name}';
select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relname='{table_name}' and c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;
크기(Size)
- pg_total_relation_size() 함수를 이용
select pg_size_pretty(pg_total_relation_size('{table_name}'));
하나의 쿼리로 행(Row) 개수와 크기(Size) 구하기
create or replace function count_rows_of_table(table_schema text, table_name text)
returns numeric
language plpgsql
as
$$
declare
count numeric;
begin
execute format('select count(*) from %s.%s', table_schema, table_name)
into count;
return count;
end;
$$;
select n.nspname as table_schema, c.relname as table_name, c.reltuples as estimated_row_count, count_rows_of_table(n.nspname, c.relname) as exact_row_count
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;