Postgre에서 테이블의 행 수를 빠르게 검색하는 방법SQL

Postgre에서 테이블의 행 수를 빠르게 검색하는 방법SQL

백분율을 계산하려면 테이블의 행 수를 알아야 합니다.총 카운트가 사전 정의된 상수보다 클 경우 상수 값을 사용합니다.그렇지 않으면 실제 행 수를 사용합니다.

사용할 수 있습니다SELECT count(*) FROM table그러나 상수 값이 500,000이고 테이블에 5,000,000 행이 있으면 모든 행을 세는 데 많은 시간이 낭비됩니다.

상수 값을 초과하는 즉시 카운트를 중지할 수 있습니까?

저는 주어진 한계 이하의 정확한 행 수만 필요합니다.그렇지 않으면 카운트가 제한치를 초과할 경우에는 제한치를 대신 사용하여 최대한 빠른 답변을 원합니다.

이와 같은 것:

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

큰 테이블의 행 수 계산은 Postgre에서 느린 것으로 알려져 있습니다.SQL. MVCC 모델에서는 정확한 숫자를 얻으려면 전체 실시간 행 수가 필요합니다.계산이 사용자의 경우처럼 정확할 필요가 없는 경우 이 속도를 크게 높일 수 있는 해결 방법이 있습니다.

(정확한 카운트라도 동시 쓰기 로드에서는 도착 시 비활성화될 수 있습니다.)

정확한 개수

큰 테이블에는 느립니다.
동시 쓰기 작업을 사용하면 해당 쓰기 작업을 얻는 순간 오래되었을 수 있습니다.

SELECT count(*) AS exact_count FROM myschema.mytable;

추정

매우 빠른 속도:

SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

일반적으로 추정치는 매우 근접합니다.얼마나 가까운지는 또는 충분히 실행되는지에 따라 결정됩니다. 여기서 "충분한"은 테이블에 대한 쓰기 활동 수준에 따라 정의됩니다.

더 안전한 견적

위에서는 동일한 이름을 가진 테이블이 하나의 데이터베이스에 여러 개 있고 다른 스키마에 있을 가능성을 무시합니다.이를 설명하기 위해:

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema';

의 출연진.bigint을 지정합니다.real특히 중요한 숫자를 위해 숫자를 멋지게 세는 것.

더 나은 견적

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

더 빠르고, 더 단순하고, 더 안전하고, 더 우아합니다.개체 식별자 유형에 대한 설명서를 참조하십시오.

를 바꿉니다.'myschema.mytable'::regclass와 함께to_regclass('myschema.mytable')Postgres 9.4+에서 잘못된 테이블 이름에 대한 예외 대신 아무것도 가져오지 않습니다. 항목

• 지정된 스키마에 테이블이 있는지 확인하는 방법

더 나은 견적(추가 비용이 거의 없음)

분할된 테이블에는 다음과 같은 이유로 작동하지 않습니다.relpages테이블에 입니다(반면 테이한경항입 -1니다(편)).reltuples모든 파티션을 포함하는 실제 추정치 포함) - Postgres 14에서 테스트됨.
대신 모든 파티션에 대한 견적을 합산해야 합니다.

우리는 포스트그레스 계획자가 하는 일을 할 수 있습니다.매뉴얼의 행 추정 예제 인용:

이 숫자는 마지막 시점에서 최신입니다.VACUUM또는ANALYZE 다음 스캔이 입니다).그런 다음 플래너는 테이블의 실제 현재 페이지 수를 가져옵니다(테이블 스캔이 필요하지 않은 저렴한 작업입니다).이 그이다경과 ,relpages그리고나서reltuples이에 따라 현재 행 수 추정치에 도달하도록 축척됩니다.

는 포트그스사용을 사용합니다.estimate_rel_size에서 정의되며, 이는 또한 데이터가 없는 코너 케이스를 다룹니다.pg_class왜냐하면 그 관계는 진공상태가 아니었기 때문입니다.SQL에서도 비슷한 작업을 수행할 수 있습니다.

최소 형태

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

안전하고 명시적임

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
     * (pg_catalog.pg_relation_size(c.oid)
      / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_catalog.pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

빈 로 깨지지 않습니다.VACUUM또는ANALYZE의 설명서:

테이블을 아직 진공청소기로 청소하거나 분석한 적이 없다면,reltuples를 포함합니다.-1행 수를 알 수 없음을 나타냅니다.

가 " " " 를 반환하는 NULL, 친구들아.ANALYZE또는VACUUM표에 대해 반복합니다. (또는 Postgres와 같은 열 유형을 기준으로 행 너비를 추정할 수 있지만, 이는 지루하고 오류가 발생하기 쉽습니다.)

가 " " " 를 반환하는 0테이블이 비어 있는 것 같습니다.는 하만나 는지나.ANALYZE확실히 하기 위해. (그리고 아마도 당신의 것을 확인할 것입니다.autovacuum설정).

일반적으로 은 8192입니다. current_setting('block_size')::int희귀한 예외에 대해 설명합니다.

테이블 및 스키마 자격을 통해 모든 기능에 대한 면역 기능을 제공합니다.search_path범위를 지정합니다.

어느 쪽이든 쿼리는 일관되게 0.1ms 미만이 걸립니다.

추가 웹 리소스:

• Postgres Wiki FAQ
• 카운트 추정치 및 카운트(*) 성능에 대한 Postgres Wiki 페이지

TABLESAMPLE SYSTEM (n) Postgres 9.5+에서

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

@a_horse가 언급한 것처럼, 추가된 절은SELECT은 령은통다있때수있다습니유용의 가 있을 때 할 수 .pg_class어떤 이유로 인해 충분히 최신 상태가 아닙니다.예:

• 아니요.autovacuum입니다.
• 지라직 INSERT/UPDATE/DELETE.
• TEMPORARY(포지않음되함에▁(autovacuum).

이것은 임의의 n%(1예제에서) 블럭을 선택하고 행을 카운트합니다.표본이 클수록 비용이 증가하고 오류가 줄어듭니다.정확도는 더 많은 요인에 따라 달라집니다.

• 행 크기의 분포입니다.주어진 블록이 일반적인 행보다 더 넓은 행을 유지하는 경우 카운트는 일반적인 행보다 낮습니다.
• a 드데튜또aFILLFACTOR블록당 공간을 차지합니다.표 전체에 고르게 분포되어 있지 않으면 추정치가 어긋날 수 있습니다.
• 일반 반올림 오류입니다.

일적으 추는치정로의 pg_class더 빠르고 정확할 것입니다.

실제 질문에 대한 답변

먼저 테이블의 행 수를 알아야 합니다. 총 카운트가 사전 정의된 상수보다 클 경우,

그리고 그게...

카운트가 내 상수 값을 통과하는 순간 카운트를 중지할 수 있습니다(행 카운트가 더 크다는 것을 알리기 위해 카운트를 완료할 때까지 기다리지 않습니다).

예. 하위 쿼리를 다음과 함께 사용할 수 있습니다.

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

Postgres는 실제로 지정된 한계를 초과하여 카운트를 중지합니다. 최대 n개 행(예에서는 500000개)에 대한 정확한 현재 카운트를 얻을 수 있습니다.예상보다 빠르지는 않습니다.pg_class 래도그.

Postgres 앱에서 다음을 실행하여 한 번 실행했습니다.

EXPLAIN SELECT * FROM foo;

그런 다음 정규식 또는 유사한 논리로 출력을 검사합니다.단순 SELECT *의 경우 출력의 첫 번째 줄은 다음과 같습니다.

Seq Scan on uids  (cost=0.00..1.21 rows=8 width=75)

당신은 할 수 .rows=(\d+) 행 수만 합니다.SELECT COUNT(*)추정치가 임계값의 1.5배 미만인 경우(또는 응용 프로그램에 적합하다고 판단되는 숫자).

쿼리의 복잡성에 따라 이 숫자는 점점 더 정확하지 않을 수 있습니다.사실, 제 응용 프로그램에서, 결합과 복잡한 조건을 추가하면서, 그것은 너무 부정확해졌고, 100개의 힘 안에서 얼마나 많은 행을 되돌렸는지 알 수조차 없었습니다. 그래서 우리는 그 전략을 포기해야만 했습니다.

그러나 Pg가 어느 정도의 합리적인 오차 범위 내에서 반환되는 행 수를 예측할 수 있을 정도로 쿼리가 간단하다면 사용자에게 도움이 될 수 있습니다.

이 블로그에서 가져온 참조입니다.

아래를 사용하여 행 수를 쿼리할 수 있습니다.

pg_class 사용:

 SELECT reltuples::bigint AS EstimatedCount
    FROM   pg_class
    WHERE  oid = 'public.TableName'::regclass;

pg_stat_user_tables 사용:

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

텍스트 열의 너비는 얼마입니까?

GROUP BY를 사용하면 데이터 검색(최소한 인덱스 검색)을 피할 수 있는 작업이 많지 않습니다.

다음을 추천합니다.

1. 가능한 경우 스키마를 변경하여 텍스트 데이터 중복을 제거합니다.이렇게 하면 '다수' 테이블의 좁은 외부 키 필드에서 카운트가 발생합니다.

2. 또는 텍스트의 해시를 사용하여 생성된 열을 만든 다음 해시 열을 기준으로 그룹화합니다.이는 워크로드를 줄이기 위한 것입니다(좁은 열 인덱스를 통해 검색).

편집:

원래 질문이 편집 내용과 완전히 일치하지 않습니다.GROUP BY와 함께 사용할 경우 COUNT가 전체 테이블의 항목 수가 아닌 그룹당 항목 수를 반환한다는 것을 알고 계실지 모르겠습니다.

당신은 또한 그냥.SELECT MAX(id) FROM <table_name>가 id인 모든 합니다.

오라클에서 다음을 사용할 수 있습니다.rownum반환되는 행 수를 제한합니다.다른 SQL에도 비슷한 구조가 존재하는 것 같습니다., 로 제한하고, 500001의 행 수를 할 수 .count(*)그러면:

SELECT (case when cnt > 500000 then 500000 else cnt end) myCnt
FROM (SELECT count(*) cnt FROM table WHERE rownum<=500001)

SQL Server(2005 이상)의 경우 빠르고 안정적인 방법은 다음과 같습니다.

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('MyTableName')   
AND (index_id=0 or index_id=1);

sys.dm _db_db_dll_dll에 대한 자세한 내용은 MSDN에서 설명합니다.

쿼리는 분할된 테이블의 모든 부분에서 행을 추가합니다.

index_id=0은 정렬되지 않은 테이블(Heap)이고 index_id=1은 정렬된 테이블(인덱스)입니다.

훨씬 더 빠른(그러나 신뢰할 수 없는) 방법이 여기에 자세히 설명되어 있습니다.

언급URL : https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql