개요
인덱스는 이미 정렬된 자료구조다.
그렇기 때문에 정렬작업인 Sort 오퍼레이션을 생략할 수 있다.
Sort 오퍼레이션이 발생하는 경우는 크게
필수 사전 지식
테이블 및 데이터 세팅 테이블 생성
create table employee
(
employee_id bigint generated by default as identity
constraint employee_pkey
primary key,
begin_date varchar(8),
employee_name varchar(255),
end_date varchar(8),
employee_code varchar(255)
);10000만 건 데이터 입력
DO $$
DECLARE
i INT := 0;
BEGIN
WHILE i < 10000 LOOP
INSERT INTO employee (begin_date, employee_name, end_date, employee_code)
VALUES (
TO_CHAR(NOW() - INTERVAL '1 year' * RANDOM(), 'YYYYMMDD'), -- 임의의 시작 날짜
'Employee ' || i, -- 임의의 직원 이름
TO_CHAR(NOW() - INTERVAL '6 months' * RANDOM(), 'YYYYMMDD'), -- 임의의 종료 날짜
'E' || LPAD(i::TEXT, 6, '0') -- 임의의 직원 코드
);
i := i + 1;
END LOOP;
END $$;Bitmap Scan 비활성화
인덱스의 동작원리를 좀 더 확실히 보기 위해서 비트맵 스캔을 비활성화한다.
set enable_bitmapscan=false;
Order By에서 Sort 오퍼레이션 생략하기
Order By가 포함된 쿼리는 Sort 오퍼레이션이 발생한다.
인덱스를 사용하면 Sort 오퍼레이션을 생략할 수 있다.
예를 들어 아래와 같은 쿼리가 있다고 가정하자.
위 실행계획에서 볼 수 있듯이 Sort 오퍼레이션이 발생한 걸 확인할 수 있다.
아래와 같이 인덱스를 생성하고 다시 실행계획을 살펴보자.
인덱스를 사용해서 Sort 오퍼레이션을 생략한 것을 확인할 수 있다.
그렇다면 Where절이 포함된 쿼리에서는 인덱스를 사용해서 Sort 오퍼레이션을 생략할 수 있을까?
예를 들어 아래와 같은 쿼리에 대해서 생각해 보자.
where절에 begin_date = '20231119'가 추가되었다.
현재 인덱스는 (begin_date, end_date)로 구성되어있다는 것은 begin_date로 정렬된 후, end_date로 정렬이 되어있음을 보장한다는 것이다.
그렇다는 건 begin_date가 20231119인 데이터들 사이에서도 end_date로 정렬되어 있음을 보장한다는 의미이다.
이 부분이 이해가 가지 않는다면 인덱스 (In PostgreSQL) 이 글의 내용을 참고하자.
아래 쿼리의 경우는 어떨까?
위와 같은 경우에도 Sort 오퍼레이션을 인덱스로 대체한 것을 확인할 수 있다.
위의 결과를 바탕으로 Sort 오퍼레이션을 생략할 수 있는 규칙을 정리해 보자.
where 조건절에 포함된 컬럼이 인덱스의 선두 컬럼으로 연속되어야 한다.
where 조건절에 포함된 컬럼은 등치(=) 조건이어야 한다.
select *
from employee
where A, B
order by C
위와 같은 쿼리는 아래와 같은 인덱스로 대체가능하다.
create index idx_employee_A_B_C
on employee(A, B, C)
당연히 아래와 같은 인덱스로도 대체 가능하다.
create index idx_employee_B_A_C
on employee(B, A, C)
하지만 아래와 같은 인덱스는 사용할 수 없다.
create index idx_employee_A_C_B
on employee(A, C, B)
이유는 A, B가 선두 컬럼으로 연속하지 않기 때문이다.
Group By에서 Sort 오퍼레이션 생략하기
Group By의 원리를 알아보자. 먼저 아래와 같은 쿼리를 수행한다고 가정하자.
select *
from 사원
group by 사원코드, 시작일자, 종료일자
옵티마이저는 위 쿼리를 실행하기 위해서 먼저 사원테이블의 데이터를 사원코드, 시작일자, 종료일자를 기준으로 정렬한다.
그림으로 보면 아래와 같이 표현할 수 있다.
위 그림처럼 먼저 정렬을 한 뒤에 그룹화를 진행한다.
실행 계획으로 예시를 들어보자
먼저 HashAggregate를 피하기 위해서 set enable_hashagg=false; 를 수행했다.
HashAggregate에 대해서는 PostgreSQL 실행계획 분석하기 5편 (Aggregates) 를 참고하자.
위 실행계획을 보면 Sort 오퍼레이션이 포함된 것을 확인할 수 있다.
Order By에서 처럼 인덱스를 사용해서 인덱스를 사용해서 Sort 오퍼레이션을 생략해 보자.
위와 같이 인덱스를 사용해서 Sort 오퍼레이션을 생략한 것을 확인할 수 있다.
distinct에서 Sort 오퍼레이션 생략하기
distinct를 처리하는 과정에서도 Sort 오퍼레이션이 포함된다.
위와 같이 Sort 오퍼레이션이 포함된 것을 확인할 수 있다.
이제 적절한 인덱스를 사용해서 Sort 오퍼레이션을 생략해 보자.
위와 같이 Sort 오퍼레이션을 생략할 수 있다. 아래의 경우는 어떨까?
아래와 같이 선두 컬럼의 순서가 맞지 않는다면 Sort 연산을 생략할 수 없다.
Where 조건절이 포함된 경우도 생각해 보자.
위와 같인 인덱스 컬럼 순서만 맞으면 인덱스를 사용할 수 있다.
하지만 아래와 같이 컬럼 순서가 맞지 않으면 Sort 오퍼레이션을 생략할 수 없다.
정리
인덱스는 정렬된 자료구조다.
그렇기 때문에 Sort 오퍼레이션과 같이 정렬을 진행하는 오퍼레이션을 대체할 수 있다.
하지만 인덱스의 구조를 정확히 이해하지 못하면 인덱스가 있다고 하더라도 활용하지 못하는 경우가 있다.
그렇기 때문에 인덱스 구조를 정확히 이해하고 위와 같은 경우에 인덱스를 적극 활용해 보는 것도 좋을 것 같다.
