CREATE STATISTICS
CREATE STATISTICS
쿼리 플래너가 예상한 행 수와 실제 실행 결과가 크게 어긋나는 경험, 해 보셨나요? 보통 플래너는 컬럼마다 쌓인 통계를 보고 예측하지만, 컬럼들 사이의 관계나 특정 표현식의 분포까지는 알지 못할 때가 많아요. 이럴 때 쓰는 게 확장 통계(extended statistics)이고, 이걸 만들어 주는 명령이 바로 CREATE STATISTICS입니다. 지정한 테이블·외부 테이블·구체화 뷰의 데이터에 대한 확장 통계 객체를 현재 데이터베이스에 만들고, 그 객체의 소유자는 명령을 실행한 사용자가 돼요.
CREATE STATISTICS에는 두 가지 기본 형태가 있어요. 첫 번째는 단일 표현식에 대한 단변량(1차원) 통계를 모으는 용도인데, 표현식 인덱스와 비슷한 이점을 인덱스 유지 비용 없이 얻을 수 있어요. 이 형태에서는 통계 종류를 지정할 수 없어요. 여러 통계 종류가 모두 다변량 통계를 가리키기 때문이죠. 두 번째 형태는 여러 컬럼·표현식에 대한 다변량 통계를 모을 때 쓰고, 원하면 포함할 통계 종류도 지정할 수 있어요. 이 형태는 목록에 들어간 각 표현식에 대한 단변량 통계도 자동으로 만들어 줘요.
스키마 이름을 주면(예: CREATE STATISTICS myschema.mystat ...) 그 스키마에 생성되고, 생략하면 현재 스키마에 만들어져요. 이름을 줄 때는 같은 스키마 안의 다른 통계 객체 이름과 달라야 해요.
출처: PostgreSQL 문서
본문
Synopsis
CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
ON ( expression )
FROM table_name
CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
[ ( statistics_kind [, ... ] ) ]
ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
FROM table_name
Description
CREATE STATISTICS는 지정한 테이블·외부 테이블·구체화 뷰의 데이터를 추적하는 확장 통계 객체를 새로 만들어요. 통계 객체는 현재 데이터베이스에 생성되고, 명령을 실행한 사용자가 소유하게 돼요.
CREATE STATISTICS 명령에는 두 가지 기본 형태가 있어요. 첫 번째 형태는 단일 표현식에 대한 단변량 통계를 모으도록 해 주는데, 표현식 인덱스와 비슷한 이점을 인덱스 유지 비용 없이 얻을 수 있어요. 이 형태에서는 통계 종류를 지정할 수 없어요. 여러 통계 종류가 모두 다변량 통계만을 가리키기 때문이죠. 두 번째 형태는 여러 컬럼·표현식에 대한 다변량 통계를 모으도록 하고, 원하면 어떤 통계 종류를 포함할지 지정할 수도 있어요. 이 형태를 쓰면 목록에 들어간 표현식들에 대한 단변량 통계도 자동으로 만들어져요.
스키마 이름을 주면(예: CREATE STATISTICS myschema.mystat ...) 통계 객체는 그 스키마에 만들어져요. 그렇지 않으면 현재 스키마에 만들어져요. 이름을 주는 경우, 같은 스키마 안의 다른 통계 객체 이름과 달라야 해요.
Parameters
IF NOT EXISTS
같은 이름의 통계 객체가 이미 있어도 오류를 내지 않아요. 이 경우 통지(notice)가 발생할 뿐이에요. 여기서는 통계 객체의 이름만 고려하고, 정의 내용은 보지 않는다는 점을 유의하세요. IF NOT EXISTS를 지정하면 통계 이름이 필수예요.
statistics_name
만들 통계 객체의 이름(스키마 한정 가능)이에요. 이름을 생략하면 PostgreSQL이 부모 테이블 이름과 정의된 컬럼 이름·표현식을 바탕으로 적절한 이름을 골라요.
statistics_kind
이 통계 객체에서 계산할 다변량 통계 종류예요. 현재 지원하는 종류는 다음과 같아요.
ndistinct— n-중복(n-distinct) 통계를 켜요.dependencies— 함수 종속(functional dependency) 통계를 켜요.mcv— 최빈값 목록(most-common values)을 켜요.
이 절을 생략하면 지원하는 모든 통계 종류가 통계 객체에 포함돼요. 통계 정의에 단순 컬럼 참조가 아니라 복잡한 표현식이 포함돼 있으면 단변량 표현식 통계가 자동으로 만들어져요. 자세한 내용은 14.2.2절과 69.2절을 참고하세요.
column_name
계산된 통계가 다루게 될 테이블 컬럼의 이름이에요. 다변량 통계를 만들 때만 허용돼요. 컬럼 이름이나 표현식이 최소 두 개는 지정되어야 하고, 순서는 상관없어요.
expression
계산된 통계가 다루게 될 표현식이에요. 단일 표현식에 대한 단변량 통계를 만들 때 쓰거나, 다변량 통계를 만들기 위한 여러 컬럼 이름·표현식 목록의 일부로 쓸 수 있어요. 후자의 경우 목록에 있는 각 표현식에 대한 단변량 통계가 자동으로 만들어져요.
table_name
통계를 계산할 컬럼이 들어 있는 테이블의 이름(스키마 한정 가능)이에요. 상속과 파티션을 어떻게 처리하는지는 ANALYZE에서 설명하고 있으니 그쪽을 참고하세요.
Notes
통계 객체를 만들려면 그 테이블의 소유자여야 해요. 일단 만들고 나면 통계 객체의 소유권은 밑에 있는 테이블과 무관하게 독립적이에요.
표현식 통계는 표현식마다 따로 만들어지고, 표현식에 인덱스를 만드는 것과 비슷해요. 다만 인덱스 유지 비용은 피할 수 있죠. 표현식 통계는 통계 객체 정의에 들어 있는 각 표현식에 대해 자동으로 만들어져요.
확장 통계는 현재 플래너가 테이블 조인에 대한 선택률 추정에는 쓰지 않아요. 이 제한은 PostgreSQL의 향후 버전에서 없어질 가능성이 커요.
Examples
두 개의 함수 종속 컬럼, 즉 첫 번째 컬럼의 값을 알면 다른 컬럼의 값까지 결정되는 컬럼을 가진 테이블 t1을 만들고, 그 컬럼들에 대해 함수 종속 통계를 만들어 볼게요.
CREATE TABLE t1 (
a int,
b int
);
INSERT INTO t1 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
ANALYZE t1;
-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
-- now the row count estimate is more accurate:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
함수 종속 통계가 없으면 플래너는 두 WHERE 조건이 서로 독립적이라고 가정하고, 두 선택률을 곱해서 행 수를 훨씬 적게 추정해요. 이런 통계가 있으면 플래너는 두 WHERE 조건이 중복됨을 알아차리고 행 수를 과소 추정하지 않아요.
완벽하게 상관된(같은 데이터를 담은) 두 컬럼을 가진 테이블 t2를 만들고, 그 컬럼들에 대한 MCV 목록을 만들어 볼게요.
CREATE TABLE t2 (
a int,
b int
);
INSERT INTO t2 SELECT mod(i,100), mod(i,100)
FROM generate_series(1,1000000) s(i);
CREATE STATISTICS s2 (mcv) ON a, b FROM t2;
ANALYZE t2;
-- valid combination (found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
-- invalid combination (not found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
MCV 목록은 플래너에게 테이블에 흔히 나타나는 특정 값들에 대한 더 상세한 정보를 주고, 테이블에 없는 값 조합의 선택률에 대한 상한선도 주기 때문에 두 경우 모두 더 나은 추정이 가능해져요.
타임스탬프 컬럼 하나를 가진 테이블 t3을 만들고, 그 컬럼에 대한 표현식을 사용하는 쿼리를 실행해 볼게요. 확장 통계가 없으면 플래너는 표현식의 데이터 분포에 대한 정보가 없어서 기본 추정치에 의존해요. 월 단위로 잘라낸 날짜 값이 일 단위로 잘라낸 날짜 값에 의해 완전히 결정된다는 것도 알지 못하죠. 그다음 두 표현식에 대해 표현식 통계와 ndistinct 통계를 만들어 볼게요.
CREATE TABLE t3 (
a timestamp
);
INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
'2020-12-31'::timestamp,
'1 minute'::interval) s(i);
ANALYZE t3;
-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
AND '2020-06-30'::timestamp;
EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
FROM t3 GROUP BY 1, 2;
-- build ndistinct statistics on the pair of expressions (per-expression
-- statistics are built automatically)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
ANALYZE t3;
-- now the row count estimates are more accurate:
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
AND '2020-06-30'::timestamp;
EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
FROM t3 GROUP BY 1, 2;
표현식·ndistinct 통계가 없으면 플래너는 표현식의 고유 값 개수에 대한 정보가 없어서 기본 추정치에 의존해야 해요. 등호·범위 조건의 선택률을 0.5%로 가정하고, 표현식의 고유 값 개수도 컬럼과 같다고(즉 유일하다고) 가정하죠. 그 결과 처음 두 쿼리에서는 행 수가 크게 과소 추정돼요. 게다가 표현식 사이의 관계에 대한 정보가 없어서 플래너는 두 WHERE·GROUP BY 조건이 독립적이라고 보고 그 선택률을 서로 곱해서, 집계 쿼리에서 그룹 수를 심하게 과대 추정해요. 표현식에 대한 정확한 통계가 없다 보니 플래너가 컬럼의 ndistinct에서 유도한 기본 ndistinct 추정치를 써야 해서 문제가 더 커지죠. 이런 통계가 있으면 플래너는 조건들이 상관돼 있음을 알아차리고 훨씬 정확한 추정을 하게 돼요.
Compatibility
SQL 표준에는 CREATE STATISTICS 명령이 없어요.
더 알아보기 (Learn more)
ALTER STATISTICS— 통계 객체의 정의를 바꿀 때 써요.DROP STATISTICS— 통계 객체를 삭제할 때 써요.ANALYZE— 테이블의 통계 정보를 모으고 저장하는 명령이에요.- Planner Cost Estimation — 플래너가 통계를 어떻게 활용하는지 자세히 보고 싶다면 이쪽을 봐요.