6-2-5. Sort 제거보다 WHERE 절에 집중

-- [SQL-6-2-5-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-04-01'::date
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_no DESC
LIMIT 50;
SQL
복사

-- [SQL-6-2-5-b]

-- tr_ord_big 전체 마지막(가장 큰) ord_no: 26,291,772
SELECT  MAX(t1.ord_no) last_ord_no 
FROM    startdbpg.tr_ord_big t1; 

-- [SQL-6-2-5-a] 조건에 만족하는 마지막(가장 큰) ord_no: 12,549,672
SELECT  MAX(t1.ord_no) last_ord_no_where
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-04-01'::date
AND     t1.shop_id = 'S100';
SQL
복사

-- [SQL-6-2-5-c]: [SQL-6-2-5-a]에 IndexScan 힌트 추가

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexScan(t1 tr_ord_big_x08) */
        t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-04-01'::date
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_no DESC
LIMIT 50;
SQL
복사

-- [SQL-6-2-5-1-a]: [SQL-6-2-5-a]의 ORDER BY를 변형

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.*
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_dtm >= '2023-03-01'::date
AND     t1.ord_dtm <  '2023-04-01'::date
AND     t1.shop_id = 'S100'
ORDER BY t1.ord_no::numeric DESC -- > ord_no를 일부러 numeric 처리
LIMIT 50;
SQL
복사