1. Intro
파티셔닝에 이어 Index를 적용 후 대량의 데이터(약 1200만 개의 row)를 검색해야 하는 케이스가 있었습니다. 이 과정에서 월별 약 73만 개의 row가 파티셔닝 되어 나누어 저장돼 있고, 각 child table에 Index가 세팅돼 있는 부분 확인되었습니다. 그러나 검색할 때 생각 이상으로 쿼리가 오래 걸리는 부분에 의문이 들었습니다. 이 과정에서 Index 가 걸리지 않는 이유와 유의사항에 대해 알게 되어 내용을 정리하였습니다. 해당 포스트는 지속적으로 유의사항을 발견할 경우 업데이트 할 예정입니다.
2. Explain
유의사항을 알아보기 앞서, 먼저 간단하게 PostgreSQL에서 제공하는 EXPLAIN 명령어에 대해 알아보면 좋을 것 같습니다. EXPLAIN 명령어는 쿼리의 실행 계획을 보여주는 중요한 도구입니다. 이를 통해 데이터베이스가 쿼리를 어떻게 실행할 것인지 미리 확인할 수 있어, 쿼리 성능 최적화에 매우 유용합니다.
기본 사용법
EXPLAIN 만 사용
EXPLAIN SELECT * FROM users WHERE id = 1;
SQL
복사
EXPLAIN과 ANALYZE 함께 사용
이 옵션을 사용하면 실제로 쿼리를 실행하고 실행 시간 등의 상세 정보를 제공합니다.
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
SQL
복사
출력 정보
위의 명령어를 통해 출력해주는 정보는 다음과 같습니다.
•
출력정보
EXPLAIN SELECT COUNT(1) FROM users WHERE id > 1;
-- 출력
Aggregate (cost=1337.68..1337.69 rows=1 width=8)
-> Index Only Scan using user_pkey on users (cost=0.29..1268.96 rows=27491 width=0)
Index Cond: (id > 1)
SQL
복사
•
출력예제 2
EXPLAIN ANALYZE SELECT COUNT(1) FROM users WHERE id > 1;
-- 출력
Aggregate (cost=1337.68..1337.69 rows=1 width=8) (actual time=23.742..23.742 rows=1 loops=1)
-> Index Only Scan using user_pkey on users (cost=0.29..1268.96 rows=27491 width=0) (actual time=0.036..22.033 rows=27594 loops=1)
Index Cond: (id > 1)
Heap Fetches: 3730
Planning Time: 0.094 ms
Execution Time: 23.775 ms
SQL
복사
•
실행 계획 노드 (예: Seq Scan, Index Scan, Hash Join 등)
•
예상 비용과 행 수 (cost, rows, width)
•
실제 실행 시간 (ANALYZE 사용 시)
•
사용된 인덱스 정보
•
필터 조건
주요 옵션과 해석 방법
주요 옵션
•
VERBOSE : 더 상세한 정보 제공
•
COSTS : 각 노드의 예상 비용 표시 (기본값)
•
BUFFERS : 버퍼 사용량 정보 제공 (ANALYZE와 함께 사용)
•
FORMAT : 출력 형식 지정 (TEXT, XML, JSON, YAML)
해석 방법
•
읽는 순서는 안쪽에서 바깥쪽으로, 위에서 아래로 실행됨
•
비용(cost)은 낮을수록 좋음
◦
첫 번째 숫자는 시작 비용, 두 번째는 총 비용의 추정치
◦
cost=1337.68..1337.69에서 시작비용은 1337.68, 총 비용은 1337.69로 추정한다는 의미
•
행 수(rows)는 예상되는 반환 행 수
•
ANALYZE 사용 시 실행 시간이 표시되며, 실제 실행 시간을 나타냄
◦
Planning Time : 쿼리 계획을 세우는 데 걸리는 시간이므로 실행 시간과 무관
◦
Execution Time : 쿼리를 실행하는 실제 시간을 의미함
3. Check list
VACUUM
만약 테이블의 정보를 변경 했을 경우, 혹은 Index 데이터가 최신화돼 있지 않을 경우 Index Scan 을 제대로 수행할 수 없을 수 있습니다. 때문에 VACUUM을 실행하여 인덱스를 최신화 해주는 것이 바람직합니다.
VACUUM data_snapshot;
SQL
복사
Type Casting
SELECT 쿼리 내에서 CASE WHEN 내의 조건문이나, WHERE 조건문 내에 타입 형변환을 일으키는 요소가 있는지 확인해야 합니다. 아래는 예시입니다.
Timestamp into date
아래의 snapshot_date 컬럼은 timestamp 형 컬럼입니다. 이 경우 ::date 형태로 형변환을 하는 정보를 넣어주게 되면 Index Scan이 아닌 Seq Scan을 하게 됩니다.
•
쿼리 예제(Recommanded)
SELECT * FROM data_snapshot WHERE snapshot_date = '2025-01-01'
SQL
복사
위와 같이 형변환 없이는 Index Scan을 정상적으로 수행하는 걸 알 수 있습니다.
Index Scan using data_snapshot_p2025_05_snapshot_date_idx on data_snapshot_p2025_05 snapshot (cost=0.29..1049.82 rows=24887 width=443)
Index Cond: (snapshot_date = '2025-05-07 00:00:00'::timestamp without time zone)
SQL
복사
•
쿼리 예제(Bad example)
SELECT * FROM data_snapshot WHERE snapshot_date::date = '2025-01-01'
SQL
복사
하지만 형변환을 할 경우 모든 파티션을 검색하며 Seq Scan을 수행하는 것을 알 수 있습니다. 인덱스는 특정 데이터 타입에 대해 최적화되어 있는데, 타입 변환이 발생하면 인덱스를 효과적으로 활용하기 어려워집니다. 조금 더 풀어서 설명하자면, 인덱스는 컬럼의 원래 저장된 값을 기준으로 정렬되고 구성되어 있기 때문에 형변환 함수를 사용하여 검색할 경우 인덱스 사용이 어렵거나 아예 사용할 수 없을 수 있습니다.
Gather (cost=1000.00..337327.31 rows=62289 width=464)
Workers Planned: 2
-> Parallel Append (cost=0.00..330098.41 rows=25978 width=464)
-> Parallel Seq Scan on data_snapshot_parent_p2025_04 data_snapshot_37 (cost=0.00..18892.33 rows=1521 width=438)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
-> Parallel Seq Scan on data_snapshot_parent_p2025_03 data_snapshot_36 (cost=0.00..18887.86 rows=1520 width=447)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
-> Parallel Seq Scan on data_snapshot_parent_p2025_01 data_snapshot_34 (cost=0.00..17617.09 rows=1418 width=436)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
...
...
...
-> Parallel Seq Scan on data_snapshot_parent_p2028_04 data_snapshot_73 (cost=0.00..10.53 rows=1 width=1222)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
-> Parallel Seq Scan on data_snapshot_p2028_05 data_snapshot_74 (cost=0.00..10.53 rows=1 width=1222)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
-> Parallel Seq Scan on data_snapshot_parent_default data_snapshot_75 (cost=0.00..10.53 rows=1 width=1222)
Filter: ((snapshot_date)::date = '2025-05-07'::date)
SQL
복사