SQL 성능을 개선하기 위해서 불가피하게 힌트를 사용해야 하는 경우가 있습니다. PostgreSQL에서 힌트를 사용할 때, 힌트에 명시하는 테이블 별칭은 반드시 소문자를 사용해야 합니다. 물론, 버젼이나 설정에 따라 다를 수는 있습니다. 테스트한 버젼은 다음과 같습니다.
•
16.4 (Debian 16.4-1.pgdg120+1)
테스트를 위해 다음 SQL을 실행해 실행계획을 확인해봅니다. 실행계획을 보면, Bitmap Index Scan을 사용하고 있습니다.
EXPLAIN
SELECT t1.member_id ,count(*) cnt
FROM tr_ord_big t1
WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD')
AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD')
GROUP BY t1.member_id;
GroupAggregate (cost=621.14..623.84 rows=154 width=14)
Group Key: member_id
-> Sort (cost=621.14..621.53 rows=155 width=6)
Sort Key: member_id
-> Bitmap Heap Scan on tr_ord_big t1 (cost=6.03..615.50 rows=155 width=6)
Recheck Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
-> Bitmap Index Scan on tr_ord_big_x01 (cost=0.00..5.99 rows=155 width=0)
Index Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
SQL
복사
PG에서 Bitmap Index Scan은 인덱스를 활용해 레코드에 접근할 때, 조금 더 효율적으로 처리하기 위한 방식입니다. 오라클의 TABLE ACCESS BY INDEX ROWID BATCHED와 비슷한 기능입니다. 오늘 이야기 하려는 것은 Bitmap Index Scan에 대한 내용은 아니므로 넘어가도록 하겠습니다. 위 실행계획을 Bitmap Index Scan이 아닌 일반적인 Index Scan으로 변경하기 위해서 다음과 같이 힌트를 적용해봅니다.
EXPLAIN
/*+ IndexScan(T1) */
SELECT t1.member_id ,count(*) cnt
FROM tr_ord_big t1
WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD')
AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD')
GROUP BY t1.member_id;
GroupAggregate (cost=621.14..623.84 rows=154 width=14)
Group Key: member_id
-> Sort (cost=621.14..621.53 rows=155 width=6)
Sort Key: member_id
-> Bitmap Heap Scan on tr_ord_big t1 (cost=6.03..615.50 rows=155 width=6)
Recheck Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
-> Bitmap Index Scan on tr_ord_big_x01 (cost=0.00..5.99 rows=155 width=0)
Index Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
SQL
복사
힌트를 적용했지만, 여전히 Bitmap Index Scan을 사용하고 있습니다. 힌트를 적용할 때, SQL의 테이블이 별칭이 소문자 t1인데, 힌트에서는 대문자인 T1을 사용하고 있기 때문입니다. 대소문자를 통일하면 힌트가 작동하는지 살펴보도록 하겠습니다.힌트를 소문자로 바꿔도 되겠지만, SQL의 테이블 별칭을 대문자 T1으로 변경해봅니다.
다음과 같습니다. 안타깝게도 여전히 Bitmap Index Scan을 사용하고 있습니다. 힌트가 무시된 것입니다.
EXPLAIN
/*+ IndexScan(T1) */
SELECT t1.member_id ,count(*) cnt
FROM tr_ord_big T1
WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD')
AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD')
GROUP BY t1.member_id;
GroupAggregate (cost=621.14..623.84 rows=154 width=14)
Group Key: member_id
-> Sort (cost=621.14..621.53 rows=155 width=6)
Sort Key: member_id
-> Bitmap Heap Scan on tr_ord_big t1 (cost=6.03..615.50 rows=155 width=6)
Recheck Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
-> Bitmap Index Scan on tr_ord_big_x01 (cost=0.00..5.99 rows=155 width=0)
Index Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text)))
SQL
복사
이번에는 힌트쪽의 테이블 별칭만 소문자(t1)로 변경해봅니다. SQL에서 테이블 별칭은 대문자인 T1을 적용한 상태지만 힌트가 작동해 Index Scan으로 실행계획이 변경된 것을 알 수 있습니다.
EXPLAIN
/*+ IndexScan(t1) */
SELECT t1.member_id ,count(*) cnt
FROM tr_ord_big T1
WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD')
AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD')
GROUP BY t1.member_id;
GroupAggregate (cost=633.01..635.71 rows=154 width=14)
Group Key: member_id
-> Sort (cost=633.01..633.40 rows=155 width=6)
Sort Key: member_id
-> Index Scan using tr_ord_big_x01 on tr_ord_big t1 (cost=0.44..627.37 rows=155 width=6)
Index Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20220104'::text, 'YYYYMMDD'::text))
SQL
복사
PG에서 힌트를 적용할 때는 반드시 소문자로 별칭을 활용하기 바랍니다.! 가능하면 테이블의 별칭도 소문자로 통일해주는 것이 좋겠죠.
이상입니다.