-- [SQL-5-3-6-a]: 하루 데이터 조회, Index Scan
EXPLAIN (COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= '2024-01-01'::date
AND t1.ord_dtm < '2024-01-02'::date;
SQL
복사
-- [SQL-5-3-6-b]: 6년 데이터 조회, Seq Scan
EXPLAIN (COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= '2019-01-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
SQL
복사
-- [SQL-5-3-6-c]
-- (재실행 시) 기존 Prepared Statements 제거
-- DEALLOCATE prep_sql1;
-- Prepared Statements 생성
PREPARE prep_sql1(text,text) AS
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= $1::date
AND t1.ord_dtm < $2::date;
SQL
복사
-- [SQL-5-3-6-d]
-- Prepared Statements 실행(하루 데이터)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('2024-01-01','2024-01-02');
SQL
복사
-- [SQL-5-3-6-e]
-- Prepared Statements 실행(6년 데이터)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('2019-01-01','2025-01-01');
SQL
복사
-- [SQL-5-3-6-f]: [SQL-5-3-6-e]를 연속해서 실행
-- Prepared Statements 실행(6년 데이터)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('2019-01-01','2025-01-01');
SQL
복사