출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
‘Chapter. 8 조인과 성능’을 진행하면서 지금까지 만들어진 인덱스는 아래와 같은 SQL로 확인할 수 있다.
SELECT t1.indexname ,t1.indexdef
FROM pg_indexes t1
WHERE t1.tablename IN ('tr_ord_big', 'ms_mbr_big', 'ms_shop_big')
ORDER BY t1.indexname;
indexname |indexdef |
--------------+----------------------------------------------------------------------------------+
ms_mbr_big_pk |CREATE UNIQUE INDEX ms_mbr_big_pk ON startdbpg.ms_mbr_big USING btree (mbr_id) |
ms_mbr_big_x21|CREATE INDEX ms_mbr_big_x21 ON startdbpg.ms_mbr_big USING btree (mbr_gd, join_tp) |
ms_shop_big_pk|CREATE UNIQUE INDEX ms_shop_big_pk ON startdbpg.ms_shop_big USING btree (shop_id) |
tr_ord_big_pk |CREATE UNIQUE INDEX tr_ord_big_pk ON startdbpg.tr_ord_big USING btree (ord_no) |
tr_ord_big_x21|CREATE INDEX tr_ord_big_x21 ON startdbpg.tr_ord_big USING btree (mbr_id) |
tr_ord_big_x22|CREATE INDEX tr_ord_big_x22 ON startdbpg.tr_ord_big USING btree (mbr_id, ord_dtm) |
tr_ord_big_x23|CREATE INDEX tr_ord_big_x23 ON startdbpg.tr_ord_big USING btree (ord_dtm) |
tr_ord_big_x24|CREATE INDEX tr_ord_big_x24 ON startdbpg.tr_ord_big USING btree (shop_id, ord_dtm)|
tr_ord_big_x25|CREATE INDEX tr_ord_big_x25 ON startdbpg.tr_ord_big USING btree (shop_id, mbr_id) |
tr_ord_big_x26|CREATE INDEX tr_ord_big_x26 ON startdbpg.tr_ord_big USING btree (prep_cmp_dtm) |
SQL
복사
이후 예제들에서는 조인 알고리즘 힌트와 Leading 힌트 없이 인덱스 힌트만으로 특정 조인 알고리즘을 유도할 것이다. 각 조인 알고리즘의 특징을 제대로 파악하고 있다면 어렵지 않게 해결할 수 있을 것이다.
적절한 인덱스 힌트를 고민하기 위해 [SQL-8-4-1-a]를 수시로 실행하면서 어떤 인덱스를 사용해야 할지 고민해 보기 바란다. SQL 튜닝에서 실행계획 다음으로 자주 보는 것이 바로 관련 테이블들의 인덱스 정보다.
8-4-1-1. 인덱스 힌트로 특정 조인 유도하기 1
아래 SQL에서 ms_shop_big을 드라이빙, tr_ord_big을 드리븐으로 NL 조인 처리하도록 해보자. 테이블에 이미 존재하는 인덱스를 참고([SQL-8-4-1-a])해 NL 조인에 적절한 인덱스를 찾아 물음표를 채워보자.
-- [SQL-8-4-1-1-a]
EXPLAIN (COSTS OFF)
/*+ IndexScan(t2 ?) */
SELECT t1.shop_id ,t1.shop_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM startdbpg.ms_shop_big t1
LEFT OUTER JOIN startdbpg.tr_ord_big t2
ON (t1.shop_id = t2.shop_id
AND t2.ord_dtm >= '2024-06-01'::date
AND t2.ord_dtm < '2024-07-01'::date
)
WHERE t1.shop_oper_tp = 'FLAG'
AND t1.chair_qty >= 50
ORDER BY t2.ord_no DESC;
SQL
복사
SQL에 어떤 인덱스 힌트를 적어야 할지 충분히 고민하고 직접 시도해 보기 바란다. 실제로 실행해 보고 자신의 생각이 맞았는지 확인해 보자.
(답안은 책을 통해 확인해 보기 바랍니다.)
8-4-1-2. 인덱스 힌트로 특정 조인 유도하기 2
아래 [SQL-8-4-1-2-a]가 다음과 같이 처리되도록 인덱스 힌트를 적용해 보자.
1.
t3와 t2를 먼저 NL 조인: t3를 드라이빙 t2를 드리븐으로 처리
2.
(t3 t2)와 t1을 NL 조인: (t3 t2)를 드라이빙 t1을 드리븐으로 처리
-- [SQL-8-4-1-2-a]
EXPLAIN (COSTS OFF)
/*+ IndexScan(t2 ?) IndexScan(t1 ?) */
SELECT t1.shop_id ,t1.shop_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM startdbpg.ms_shop_big t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.shop_id = t2.shop_id)
INNER JOIN startdbpg.ms_mbr_big t3
ON (t3.mbr_id = t2.mbr_id)
WHERE t2.ord_dtm >= '2024-06-01'::date
AND t2.ord_dtm < '2024-07-01'::date
AND t3.join_tp = 'INV'
AND t3.mbr_gd = 'PLAT'
ORDER BY t2.ord_no DESC;
SQL
복사
시간을 갖고 충분히 생각해 물음표를 채워보기 바란다. 그리고 직접 실행해 보고, 실행계획을 확인해 요구한 대로 NL 조인이 처리되었는지 확인해 보기 바란다.
(답안은 책을 통해 확인해 보기 바랍니다.)
8-4-1-2. 인덱스 힌트로 특정 조인 유도하기 3
아래 SQL을 해시 조인으로 처리되도록 인덱스 힌트를 적용하자. tr_ord_big(t2)에만 인덱스 힌트를 적용하면 된다.
-- [SQL-8-4-1-3-a]
EXPLAIN (COSTS OFF)
/*+ IndexScan(t2 ?) */
SELECT t1.mbr_id ,t1.nick_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM startdbpg.ms_mbr_big t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.mbr_id = t2.mbr_id
AND t2.ord_dtm >= '2024-09-01'::date
AND t2.ord_dtm < '2024-10-01'::date
)
WHERE t1.join_dtm = '2020-05-08'::date;
SQL
복사
충분히 생각한 후 답을 적어보고, 실제로 실행해 실행계획을 확인해 보기 바란다. 해시 조인에서는 조인 컬럼에 인덱스가 필요 없다는 점을 기억하기 바란다.
(답안은 책을 통해 확인해 보기 바랍니다.)