3-4-6. 어떤 인덱스를 사용할까?

-- [SQL-3-4-6-a]

SELECT  t1.ord_no ,t1.ord_dtm ,t1.mbr_id
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_no BETWEEN 1 AND 100000
AND     t1.ord_dtm >= '2019-03-16'::date
AND     t1.ord_dtm <  '2019-03-17'::date;
SQL
복사

-- [SQL-3-4-6-b]

SELECT  COUNT(*) cnt -- 100,000건
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_no BETWEEN 1 AND 100000;

SELECT  COUNT(*) cnt -- 36건
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2019-03-16'::date
AND     t1.ord_dtm <  '2019-03-17'::date;
SQL
복사

-- [SQL-3-4-6-c]: [SQL-3-4-6-a]의 실행계획 확인

EXPLAIN (COSTS OFF)
SELECT  t1.ord_no, t1.ord_dtm ,t1.mbr_id
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_no BETWEEN 1 AND 100000
AND     t1.ord_dtm >= '2019-03-16'::date
AND     t1.ord_dtm <  '2019-03-17'::date;
SQL
복사

-- [SQL-3-4-6-d]

SELECT  t1.ord_no ,t1.ord_dtm ,t1.mbr_id
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_no BETWEEN 23001 AND 23500
AND     t1.ord_dtm >= '2019-04-01'::date
AND     t1.ord_dtm <  '2019-05-01'::date;
SQL
복사