[postgresql] Postgres에서 모든 테이블의 행 수를 어떻게 찾습니까?

Postgres의 모든 테이블에 대한 행 수를 찾는 방법을 찾고 있습니다. 나는이 테이블을 한 번에 하나씩 할 수 있다는 것을 알고있다.

SELECT count(*) FROM table_name;

그러나 모든 테이블의 행 수를 확인한 다음 순서대로 정렬하여 내 모든 테이블의 크기를 알 수 있습니다.

답변

이러한 종류의 카운트를 얻는 방법에는 세 가지가 있으며 각각 고유 한 장단점이 있습니다.

실제 개수를 원하면 각 테이블에 대해 사용한 것과 같은 SELECT 문을 실행해야합니다. PostgreSQL은 행 가시성 정보를 다른 곳이 아닌 행 자체에 유지하므로 정확한 수는 일부 트랜잭션에만 관련 될 수 있기 때문입니다. 트랜잭션이 실행될 때 해당 트랜잭션에 표시되는 개수를 얻습니다. 데이터베이스의 모든 테이블에 대해 실행되도록 자동화 할 수는 있지만 그 정도의 정확도가 필요하지 않거나 오랫동안 기다릴 필요가 있습니다.

두 번째 방법은 통계 수집기가 언제든지 “실제”행 수 (추후 업데이트로 삭제 또는 폐기되지 않음)를 대략적으로 추적합니다. 이 값은 활동이 많을 때 약간 벗어날 수 있지만 일반적으로 좋은 추정치입니다.

SELECT schemaname,relname,n_live_tup
  FROM pg_stat_user_tables
  ORDER BY n_live_tup DESC;

또한 얼마나 많은 행이 죽었는지 보여줄 수 있습니다.

세 번째 방법은 테이블 통계를 업데이트하기 위해 PostgreSQL 8.3에서 정기적으로 autovacuum 프로세스에 의해 실행되는 시스템 ANALYZE 명령이 행 추정값을 계산한다는 것입니다. 당신은 이것처럼 이것을 잡을 수 있습니다 :

SELECT
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;

이러한 쿼리 중 어느 것이 더 나은지를 말하기는 어렵습니다. 일반적으로 나는 pg_class 내부 또는 pg_stat_user_tables 내부에서 더 유용한 정보가 있는지 여부에 따라 결정합니다. 기본적인 계산의 목적으로, 일반적으로 큰 것이 얼마나 큰지 알기 위해서는 충분히 정확해야합니다.

답변

각 테이블의 정확한 개수를 얻기 위해 함수가 필요없는 솔루션은 다음과 같습니다.

select table_schema,
       table_name,
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema,
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

query_to_xml전달 된 SQL 쿼리를 실행하고 결과 (해당 테이블의 행 수)와 함께 XML을 반환합니다. xpath()그런 다음 외부 는 해당 XML에서 카운트 정보를 추출하여 숫자로 변환합니다.

파생 테이블은 실제로 필요하지는 않지만 xpath()조금 이해하기 쉽게 만듭니다. 그렇지 않으면 전체 query_to_xml()를 xpath()함수에 전달해야합니다 .

답변

견적을 얻으려면 Greg Smith의 답변을 참조하십시오 .

정확한 수를 얻으려면 지금까지 다른 대답에는 몇 가지 문제가 있으며 그중 일부는 심각합니다 (아래 참조). 더 나은 버전이 있습니다.

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  LOOP
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

스키마 이름을 매개 변수로 사용하거나 public매개 변수를 지정하지 않은 경우를 사용합니다.

함수를 수정하지 않고 특정 스키마 목록이나 쿼리에서 오는 목록으로 작업하려면 다음과 같이 쿼리 내에서 호출 할 수 있습니다.

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

스키마, 테이블 및 행 수와 함께 3 열 출력이 생성됩니다.

이제이 함수가 피하는 다른 답변의 몇 가지 문제가 있습니다.

테이블 및 스키마 이름은 형식 문자열이있는 quote_ident최신 format()기능을 사용 하거나 사용 하지 않고 따옴표없이 실행 가능한 SQL에 삽입하면 안됩니다 %I. 그렇지 않으면 일부 악의적 인 사람이 자신 tablename;DROP TABLE other_table의 테이블 이름을 테이블 이름으로 완벽하게 사용할 수 있습니다 .
SQL 삽입 및 재미있는 문자 문제가 없어도 테이블 이름은 대소 문자가 다른 변형으로 존재할 수 있습니다. 테이블의 이름은 경우 ABCD와 다른 하나 abcd는이 SELECT count(*) FROM...그렇지 인용 된 이름을 사용해야은 건너 뛸 ABCD카운트 abcd를 두 번 누릅니다. %I형식이 자동으로이 작업을 수행합니다.
information_schema.tablestable_type이 'BASE TABLE'(!) 인 경우에도 테이블 외에 사용자 정의 복합 유형을 나열합니다 . 결과적으로, 우리는 반복 할 수 없으며 information_schema.tables그렇지 않으면 우리는 가질 위험이 select count(*) from name_of_composite_type있으며 실패 할 것입니다. OTOH pg_class where relkind='r'는 항상 잘 작동합니다.
COUNT의 종류는 ()이다 bigint,하지 int. 21 억 5 천 개가 넘는 행이있는 테이블이 존재할 수 있습니다.
함수가 여러 열이있는 결과 집합을 반환하기 위해 영구 유형을 만들 필요는 없습니다. RETURNS TABLE(definition...)더 나은 대안입니다.

답변

잠재적으로 오래된 데이터가 마음에 들지 않으면 쿼리 최적화 프로그램에서 사용하는 것과 동일한 통계에 액세스 할 수 있습니다 .

다음과 같은 것 :

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

답변

헤 로쿠의 느린 행 카운터가 새로 고침 될 때까지 기다릴 수없고 어떤 헤 로쿠 계획이 필요한지를 평가하려는 사람들에게 해킹 된 실질적인 답변 :

기본적으로 당신이 실행하려는 \dt에서 psql, (가 같이 표시됩니다 좋아하는 텍스트 편집기로 결과를 복사 :

 public | auth_group                     | table | axrsosvelhutvw
 public | auth_group_permissions         | table | axrsosvelhutvw
 public | auth_permission                | table | axrsosvelhutvw
 public | auth_user                      | table | axrsosvelhutvw
 public | auth_user_groups               | table | axrsosvelhutvw
 public | auth_user_user_permissions     | table | axrsosvelhutvw
 public | background_task                | table | axrsosvelhutvw
 public | django_admin_log               | table | axrsosvelhutvw
 public | django_content_type            | table | axrsosvelhutvw
 public | django_migrations              | table | axrsosvelhutvw
 public | django_session                 | table | axrsosvelhutvw
 public | exercises_assignment           | table | axrsosvelhutvw

), 정규식 검색을 실행하고 다음과 같이 바꾸십시오.

^[^|]*\|\s+([^|]*?)\s+\| table \|.*$

에:

select '\1', count(*) from \1 union/g

그러면 다음과 매우 비슷한 결과가 나타납니다.

select 'auth_group', count(*) from auth_group union
select 'auth_group_permissions', count(*) from auth_group_permissions union
select 'auth_permission', count(*) from auth_permission union
select 'auth_user', count(*) from auth_user union
select 'auth_user_groups', count(*) from auth_user_groups union
select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union
select 'background_task', count(*) from background_task union
select 'django_admin_log', count(*) from django_admin_log union
select 'django_content_type', count(*) from django_content_type union
select 'django_migrations', count(*) from django_migrations union
select 'django_session', count(*) from django_session
;

(마지막을 제거 union하고 끝에 수동으로 세미콜론을 추가해야합니다)

그것을 실행 psql하고 완료했습니다.

            ?column?            | count
--------------------------------+-------
 auth_group_permissions         |     0
 auth_user_user_permissions     |     0
 django_session                 |  1306
 django_content_type            |    17
 auth_user_groups               |   162
 django_admin_log               |  9106
 django_migrations              |    19
[..]

답변

bash 의 대답 이 당신에게 맞는지 확실하지 않지만 FWIW …

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
                SELECT   '$TABLENAME',
                         count(*)
                FROM     $TABLENAME
                \""
    eval "$PGCOMMAND"
done

답변

나는 보통 통계, 특히 PostgreSQL에서 의존하지 않습니다.

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;