RDB 마다 서로 최적화 방법이 다르고 쿼리 실행계획을 표현하는 용어들이 다릅니다. 오늘은 이러한 RDB 중 OLTP 서비스에서 정말 많이 사용되는 DB 중 하나인 PostgreSQL 의 Explain 에 대해 알아보겠습니다.
기본 용어
cost — 실행 비용을 나타내며 estimated start-up cost, estimated total cost 로 구성되어 있습니다, e.g., 0.00..445.00
estimated start-up cost — 출력 단계(output phase) 전 예상 소요비용
estimated total cost — 예상 검색 소요비용
rows — 조회된 데이터 갯수(열)
Filter — 노드의 필터 조건
Cond — Condition의 줄임말이며, 인덱스와 조인 등의 조건
전체조회 쿼리
일반적인 전체조회 쿼리의 실행결과는 아래와 같습니다. Seq Scan 은 순차적으로 모든 Pages를 읽어들였다는 의미입니다.
explain
select * from products;
Query Plan
-----------------------------------------------------------------
Seq Scan on products (cost=0.00..18509.00 rows=1000000 width=35)Seq Scan — 순차적으로 Pages 를 읽어들였다는 의미
cost — 예상 시작 소요비용 0.00 / 총 예상 소요비용 18509.00
rows — 1,000,000 건의 조회 발생
조건절이 포함된 쿼리
전체조회 쿼리와 똑같이 Seq Scan 순차적으로 페이지를 읽어들였으며, where 조건절에 해당되는 데이터만 필터링 했다는 의미로 Filter: (quantity > 10) 라고 실행계획에 표시되어있음을 확인할 수 있습니다.
explain
select * from products
where quantity > 10;
Query Plan
-----------------------------------------------------------------
Seq Scan on products (cost=0.00..21009.00 rows=900633 width=35)
Filter: (quantity > 10)Filter — Seq Scan 에서 quantity > 10 인 데이터만 필터링 했다는 의미
Index Unique Scan
Unique Field를 대상으로 한 where 조건 쿼리의 경우 Bitmap Index Scan 이 동작하지 않고 Index Scan 이 동작하는것을 볼 수 있습니다.
explain
select * from products where id = 3891;
Query Plan
-----------------------------------------------------------------
Index Scan using products_pkey on products (cost=0.42..8.44 rows=1 width=35)
Index Cond: (id = 3891)Index Scan — 인덱스 스캔 방식으로 데이터를 조회 했다는 의미
Index Cond — 인덱스 스캔에 대한 필터링 조건으로, (id = 3891) 인 데이터만 필터링 했다는 의미
Bitmap Index Scan 활용 사례
PostgreSQL 에서는 인덱스 컬럼의 CF(클러스터링 팩터)에 따라 Index Scan 혹은 Bitmap Index Scan 방식으로 동작하게 됩니다. Bitmap Index Scan 은 CF가 좋지 않을 경우 동작하는 스캔 방식으로 테이블 랜덤 I/O 를 줄이기 위해 고안되었습니다.
왜 쿼리 옵티마이저가 CF에 따라 다른 스캔 방식을 선택 하는지 공식문서에서 찾아보진 않았지만 당연히 페이지의 row 탐색 및 캐싱과 관련되어 있다 생각 합니다. 지금은 우선 “CF에 따라 스캔 방식이 선택된다” 정도로만 이해 하셔도 좋을것 같습니다.
테이블의 각 필드별 CF를 확인하고 싶다면, 포스팅 대상 테이블인 products 의 CF를 확인하기 위해 pg_stats 테이블을 조회하여 확인할 수 있습니다. correlation의 값이 1에 가까울수록 저장 순서(정렬)가 고르게 분포되어 있다는 의미이며, 숫자가 0에 가까울수록 저장 순서가 고르지 못하다는 의미입니다.
select attname, correlation from pg_stats where tablename = 'products';
위 이미지는 pg_stats 를 조회했을때 나온 결과값입니다. 참고로 -1에 가까울수록 데이터가 역순으로 저장되어 있다는 의미입니다. price의 경우 0보다 작은 -값이긴 하지만 그 값이 크진 않으므로 0으로 보는게 더 맞을것 같습니다.
등가조건 쿼리 예시
CF가 낮은 price 필드를 대상으로 조회 쿼리를 실행하면 범위 조건이 아닌 등가조건(equals)으로 필터링 했음에도 아래와 같이 Bitmap Index Scan 방식으로 쿼리가 수행된것을 확인할 수 있습니다.
explain
select * from products
where price = 50001;
Query Plan
-----------------------------------------------------------------
Bitmap Heap Scan on products (cost=4.52..51.32 rows=12 width=35)
Recheck Cond: (price = 50001)
-> Bitmap Index Scan on products_price_index (cost=0.00..4.51 rows=12 width=0)
Index Cond: (price = 50001)Bitmap Index Scan — Bitmap Index 방식으로 인덱스를 스캔했다
Index Cond — Bitmap Index Scan 조건
Recheck Cond — 수평 탐색 필터링 조건
인덱스의 정렬
인덱스는 선두컬럼 순서대로 트리 구조로 데이터가 이미 정렬되어 있기 때문에, 인덱싱을 통한 검색은 데이터 탐색 후 정렬 과정을 스킵 할 수도 있습니다.
아래 예시는 mart_code / product_type / product_name / quantity 순으로 정의된 복합 인덱스를 활용하는 예시입니다.
일반적인 정렬 스킵
mart_code 를 기준으로 정렬을 할 경우 인덱스 선두 컬럼이 mart_code 이기 때문에 정렬 동작은 스킵 됩니다. 아래 실행계획을 보면 인덱스 스캔 이외에 어떠한 동작도 발생되지 않았습니다.
explain
select * from products
order by mart_code;
Query Plan
-----------------------------------------------------------------
Index Scan using products_mart_code_type_name_quantity_index on products (cost=0.42..63334.58 rows=1000000 width=35)부분 정렬 스킵
mart_code, quantity 두 컬럼을 기준으로 정렬을 시도하면 선두컬럼인 mart_code로는 정렬을 할 수 있지만 그 다음 컬럼인 quantity는 앞에 나머지 선두컬럼인 product_type / product_name 이 있기 때문에 정렬을 스킵할 수 없습니다. 이럴 경우는 정렬이 부분 스킵 됩니다.
explain
select * from products
order by mart_code, quantity;
Query Plan
-----------------------------------------------------------------
Incremental Sort (cost=1298.15..142275.14 rows=1000000 width=35)
Sort Key: mart_code, quantity
Presorted Key: mart_code
-> Index Scan using products_mart_code_type_name_quantity_index on products (cost=0.42..63334.58 rows=1000000 width=35)Incremental Sort — 증분 정렬 이라고도 하며, 필자의 경우 부분 정렬 스킵 이라고 표현
Sort Key — 정렬 Skip 조건에 해당되지 않아, 데이터 정렬 동작을 수행하는 필드
Presorted Key — 인덱스 선두 컬럼에 해당되어 정렬 동작이 스킵되는 필드
Conclusions
이번 아티클 에서는 조건절 없는 select 쿼리의 실행계획 부터 일반적인 인덱스 스캔 조건들의 실행계획, 또 CF(클러스터링 팩터)에 따라 인덱스 스캔 동작이 바뀔 수 있다는 점과 정렬 동작의 스킵 과정에 대해 알아보았습니다.
아티클 초반부에 설명한 기본 용어 이외에 인덱스 탐색 과정들이 이해가 안되거나 쿼리를 실행했을때 DB 엔진이 어떤 과정들을 통해 쿼리를 실행 시키는지 자세한 동작 방식을 알고 싶으시다면 제가 작성한 SQL 동작 원리와 캐싱 그리고 I/O 아티클을 읽어보실것을 추천드립니다. 오라클 베이스로 설명한터라 조금은 구조가 다를 수 있지만 전반적으로 RDB 들이 어떤 방식으로 돌아가는지 이해하기 좋을것 같습니다.
다음 아티클에 이어서 PostgreSQL 의 Join 유형별 실행계획과 Analyze 에 대해 알아보도록 하겠습니다.
References
