5-4-1. 커버링 인덱스

-- [SQL-5-4-1-2-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t1.ord_st
        ,(SELECT s1.base_cd_nm FROM startdbpg.cm_base_cd s1
          WHERE  s1.base_cd_dv = 'ord_st' AND s1.base_cd = t1.ord_st) ord_st_nm
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2020-03-01'::date
AND     t1.ord_dtm <  '2020-03-04'::date
ORDER BY t1.ord_dtm;
SQL
복사

-- [SQL-5-4-1-2-b]: [SQL-5-4-1-2-a]에 힌트 추가

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexScan(s1) */ -- > IndexScan 힌트 적용
        t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t1.ord_st
        ,(SELECT s1.base_cd_nm FROM startdbpg.cm_base_cd s1
          WHERE  s1.base_cd_dv = 'ord_st' AND s1.base_cd = t1.ord_st) ord_st_nm
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2020-03-01'::date
AND     t1.ord_dtm <  '2020-03-04'::date
ORDER BY t1.ord_dtm;
SQL
복사

-- [SQL-5-4-1-2-c]: 인덱스 추가 후 힌트를 변경해 [SQL-5-4-1-2-a]를 재실행

CREATE INDEX cm_base_cd_x01 ON startdbpg.cm_base_cd(base_cd_dv, base_cd) INCLUDE(base_cd_nm);

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexOnlyScan(s1) */ -- > IndexOnlyScan 힌트 적용
        t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t1.ord_st
        ,(SELECT s1.base_cd_nm FROM startdbpg.cm_base_cd s1
          WHERE  s1.base_cd_dv = 'ord_st' AND s1.base_cd = t1.ord_st) ord_st_nm
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2020-03-01'::date
AND     t1.ord_dtm <  '2020-03-04'::date
ORDER BY t1.ord_dtm;
SQL
복사

-- [SQL-5-4-1-3-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,t1.ord_amt
FROM   startdbpg.tr_ord_big t1
WHERE  t1.ord_dtm >= '2024-01-01'::date
AND    t1.ord_dtm <  '2025-01-01'::date
AND    t1.mbr_id = 'M13861'
ORDER BY t1.shop_id;
SQL
복사

-- [SQL-5-4-1-3-b]: 인덱스 생성 후 [SQL-5-4-1-3-a]를 재실행
CREATE INDEX tr_ord_big_x06 ON startdbpg.tr_ord_big(mbr_id, ord_dtm) INCLUDE(shop_id, ord_amt);

-- [SQL-5-4-1-3-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,t1.ord_amt
FROM   startdbpg.tr_ord_big t1
WHERE  t1.ord_dtm >= '2024-01-01'::date
AND    t1.ord_dtm <  '2025-01-01'::date
AND    t1.mbr_id = 'M13861'
ORDER BY t1.shop_id;
SQL
복사

-- [SQL-5-4-1-3-c]

SELECT  pg_size_pretty(pg_relation_size('startdbpg.tr_ord_big_x06'::regclass)) ix_size;
SQL
복사

-- [SQL-5-4-1-4-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  COUNT(t1.ord_no) cnt
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-03-03'::date;
SQL
복사

-- [SQL-5-4-1-4-b]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  COUNT(*) cnt
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-03-03'::date;
SQL
복사