[postgreSQL] 인덱스의 성능을 위하여 알아둘 것. 카디널리티

 카디널리티(Cardinality)란 흔히 나와있는 설명을 참고하면 컬럼의 중복된 수치를 말한다. 중복된 수치가 높으면 카디널리티가 높은 것 같지만 반대로 해석해야 해서 조금 헷갈리는데 그래서 난 카디널리티를 분포도로 이해하고 있다. 얼마나 데이터가 분포하였는가에 따라 넓게 분포되었다면 카디널리티가 높고, 아니라면 낮다.

예를 들어, 성별은 남성과 여성이라는 2개의 데이터 분포를 가질 수 있고, 주민등록번호는 대한민국 국민이 모두 고유하게 하나씩 가지고 있기 때문에 약 5천만 개의 데이터 분포를 가진다. 이 둘을 비교할 때 상대적으로 주민등록번호의 카디널리티가 높다. 위와 같이 직관적으로 컬럼의 카디널리티를 따져볼 수 있지만 실제 테이블의 컬럼들은 다양하게 구성되어있기 때문에 직접 구해보는 것이 좋다.

카디널리티가 높은 순으로 인덱스를 생성하는 것이
낮은 순으로 인덱스를 생성하는 것보다 성능 우위에 있다고 해서
테스트를 시도해보았다.

테스트를 위하여 100만개의 더미 데이터를 만들어 보았다.

CREATE TABLE dummy_test AS
SELECT
	dummy as idx1,
	'테스트' || dummy AS test_string1,
	'테스트' || (dummy%5) AS test_string2,
	'테스트' || (dummy%10) AS test_string3
FROM
	generate_series(1, 1000000) AS dummy;

카디널리티를 직접 구해보면, 다음과 같은 데이터 분포를 가지게 된다.

select 
	count(distinct(test_string1)), --count : 1,000,000
	count(distinct(test_string2)), --count : 5
	count(distinct(test_string3))  --count : 10
from dummy_test;

여기서 세가지 케이스를 테스트해보려고 한다.
CASE1. NO인덱싱,
CASE2. 카디널리티가 가장 낮은 컬럼 순서로 인덱싱,
CASE3. 카디널리티가 가장 높은 컬럼 순서로 인덱싱!

--test query
select * from dummy_test 
where 1=1
and test_string1 = '테스트105843' 
and test_string2 = '테스트3' 
and test_string3 = '테스트3';

--index query
CREATE INDEX DUMMY_TEST_IDX_CASE2 ON dummy_test (test_string2, test_string3, test_string1); --5,10,100만
CREATE INDEX DUMMY_TEST_IDX_CASE3 ON dummy_test (test_string1, test_string3, test_string2); --100만,10,5
DROP INDEX DUMMY_TEST_IDX_CASE2;

소요시간	Total Cost	1회	2회	3회	4회
CASE 1	43,815	225ms	215ms	218ms	222ms
CASE 2	8.45	43ms	34ms	38ms	42ms
CASE 3	8.45	42ms	39ms	41ms	40ms

테스트 결과, 인덱스는 효과가 있으나, 인덱스 순서에 대한 효과는 미미했다. 아니, 효과가 전혀 없었다.
100만 개가 너무 적었던 것은 아닐까? 1,000만 개로 더미 데이터 개수를 늘려서 같은 방식으로 테스트해보았다.

서버마다 인덱스 생성시간의 차이는 있겠지만 1000만개에 CASE2 인덱스를 생성하려고 하니 6분이 걸렸다. ㄷㄷㄷ

소요시간	Total Cost	1회	2회	3회	4회
CASE 1	268,447	1.128ms	1.131ms	1.187ms	1.132ms
CASE 2	8.58	42ms	40ms	41ms	44ms
CASE 3	8.58	45ms	50ms	50ms	46ms

테스크 결과, 동일했다. 

그 뒤로도,
TEST1. 아웃풋값이 여러개가 도출되는 SELECT 문에서 영향이 있나? 아웃풋 1개 => 1000개 X
TEST2. 인덱스를 설정하는 카디널리티가 가장 낮은 컬럼의 데이터 분포가 낮아야하나? 분포도 100 => 2개 X
TEST3. IN 절로 여러개를 찾아가는 구문을 넣어야하나? X
TEST4. 인덱스 내외 컬럼에서 정렬을 줘야하나? 내외 모두 X

아무리 테스트 해보았지만,
인덱스 컬럼의 순서가 성능 우위가 있는지는 증명해내지 못했다.

'기억보다 기록을' 블로그에서 mysql 인덱스 정리팁이란 글을 참고하면
분명 성능 우위가 있어보이는데 mysql과 postgresql의 동작원리가 다르기 때문인가 싶다.
이 부분에 대하여 다시 이견이 생긴다면 다시 정리해두기로 하고 마쳐야겠다.

p.s. 아시는 분은 댓글 부탁드려요!