-- [SQL-8-3-4-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT TO_CHAR(t1.ord_dtm,'YYYYMM') ord_ym ,t2.item_id
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbpg.tr_ord_big t1
INNER JOIN startdbpg.tr_ord_det_big t2
ON (t1.ord_no = t2.ord_no)
WHERE t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
GROUP BY TO_CHAR(t1.ord_dtm,'YYYYMM') ,t2.item_id;
SQL
복사
-- [SQL-8-3-4-b]: work_meme을 32MB로 늘린 후 [SQL-8-3-4-a]를 재실행
SHOW work_mem; -- 4MB
SET work_mem = '32MB'; -- 4MB의 8배인 32MB로 설정
-- [SQL-8-3-4-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT TO_CHAR(t1.ord_dtm,'YYYYMM') ord_ym ,t2.item_id
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbpg.tr_ord_big t1
INNER JOIN startdbpg.tr_ord_det_big t2
ON (t1.ord_no = t2.ord_no)
WHERE t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
GROUP BY TO_CHAR(t1.ord_dtm,'YYYYMM') ,t2.item_id;
SQL
복사
-- [SQL-8-3-4-c]
SELECT t1.attname -- 컬럼명
,t1.correlation -- 테이블의 실제 데이터 정렬과 해당 컬럼의 정렬에 대한 상관계수
FROM pg_stats t1
WHERE t1.tablename = 'tr_ord_big'
AND t1.attname in ('ord_dtm','ord_no')
ORDER BY t1.correlation DESC;
SQL
복사
-- [SQL-8-3-4-d]
SELECT MIN(x.ord_no::numeric) ord_no_min ,MAX(x.ord_no::numeric) ord_no_max
FROM startdbpg.tr_ord_big x
WHERE x.ord_dtm >= '2023-01-01'::date
AND x.ord_dtm < '2023-02-01'::date;
SQL
복사
-- [SQL-8-3-4-e]
SET work_mem = '4MB';
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT TO_CHAR(t1.ord_dtm,'YYYYMM') ord_ym ,t2.item_id
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbpg.tr_ord_big t1
INNER JOIN startdbpg.tr_ord_det_big t2
ON (t1.ord_no = t2.ord_no)
WHERE t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
AND t2.ord_no >= 10713997 -- > ord_no_min을 조건값으로 사용
AND t2.ord_no <= 11351304 -- > ord_no_max를 조건값으로 사용
GROUP BY TO_CHAR(t1.ord_dtm,'YYYYMM') ,t2.item_id;
SQL
복사
-- [SQL-8-3-4-f]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
WITH w1 AS MATERIALIZED (
SELECT MIN(x.ord_no::numeric) ord_no_min ,MAX(x.ord_no::numeric) ord_no_max
FROM startdbpg.tr_ord_big x
WHERE x.ord_dtm >= '2023-01-01'::date
AND x.ord_dtm < '2023-02-01'::date
)
SELECT TO_CHAR(t1.ord_dtm,'YYYYMM') ord_ym ,t2.item_id
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbpg.tr_ord_big t1
INNER JOIN startdbpg.tr_ord_det_big t2
ON (t1.ord_no = t2.ord_no)
WHERE t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
AND t2.ord_no >= (SELECT x1.ord_no_min FROM w1 x1)
AND t2.ord_no <= (SELECT x2.ord_no_max FROM w1 x2)
GROUP BY TO_CHAR(t1.ord_dtm,'YYYYMM') ,t2.item_id;
SQL
복사