5-4-4. Bitmap Scan의 효율성

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

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.*
FROM    startdbpg.tr_ord_big_shop T1
WHERE   t1.ord_dtm >= '2024-12-01'::date
AND     t1.ord_dtm <  '2025-01-01'::date;
SQL
복사

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

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexScan(t1) */
        t1.*
FROM    startdbpg.tr_ord_big_shop T1
WHERE   t1.ord_dtm >= '2024-12-01'::date
AND     t1.ord_dtm <  '2025-01-01'::date;
SQL
복사

-- [SQL-5-4-4-c]: [SQL-5-4-4-a]에서 실제 접근하는 고유 블록 개수 확인

SELECT count(DISTINCT (ctid::text::point)[0]) blk_cnt
FROM   startdbpg.tr_ord_big_shop T1
WHERE  t1.ord_dtm >= '2024-12-01'::date
AND    t1.ord_dtm <  '2025-01-01'::date;
SQL
복사

-- [SQL-5-4-4-d]: tr_ord_big으로 Bitmap Scan

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ BitmapScan(t1) */
        t1.*
FROM    startdbpg.tr_ord_big T1
WHERE   t1.ord_dtm >= '2024-12-01'::date
AND     t1.ord_dtm <  '2025-01-01'::date;
SQL
복사

-- [SQL-5-4-4-e]: tr_ord_big으로 Index Scan

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexScan(t1) */
        t1.*
FROM    startdbpg.tr_ord_big T1
WHERE   t1.ord_dtm >= '2024-12-01'::date
AND     t1.ord_dtm <  '2025-01-01'::date;
SQL
복사

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

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ BitmapScan(t1) */
        t1.*
FROM    startdbpg.tr_ord_big_shop T1
WHERE   t1.ord_dtm >= '2024-01-01'::date
AND     t1.ord_dtm <  '2024-04-01'::date;
SQL
복사

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

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ BitmapScan(t1) */
        t1.*
FROM    startdbpg.tr_ord_big_shop T1
WHERE   t1.ord_dtm >= '2024-01-01'::date
AND     t1.ord_dtm <  '2024-09-01'::date;
SQL
복사