갱신은 효율적으로

최근 표준 SQL에 MERGE 구문이 추가되는 등 활발한 확장이 이뤄지고 있음
어떻게 하면 보다 똑똑하게 데이터를 UPDATE 할 수 있을지 알아보자

1-1) 반복계를 피하고 가급적 인덱스를 사용하자 (쿼리 플랜의 중요성) SQL1

OmitTbl

KEYCOL	SEQ	VAL
A	1	50
A	2	NULL
A	3	NULL
A	4	70
A	5	NULL
A	6	900
B	1	10
B	2	20
B	3	NULL
B	4	3
B	5	NULL
B	6	NULL

after

KEYCOL	SEQ	VAL
A	1	50
A	2	50
A	3	50
A	4	70
A	5	70
A	6	900
B	1	10
B	2	20
B	3	20
B	4	3
B	5	3
B	6	3

업데이트 쿼리

UPDATE OmitTbl
SET val = (SELECT val
           FROM OmitTbl OT1
           WHERE OT1.keycol = OmitTbl.keycol //같은 keycol을 가짐
           AND OT1.seq = (
             SELECT MAX(seq) //1, 4 중 4
             FROM OmitTbl OT2
             WHERE OT2.keycol = OmitTbl.keycol
             AND OT2.seq < OmitTbl.seq //자신보다 작은 seq를 가짐
             AND OT2.val IS NOT NULL//val이 NULL이 아님
             )
           )
WHERE val IS NULL;

OmitTbl.seq가 2나 3인 경우 본인보다 작으면서 val이 NN인 seq:1의 val 값을 val에 지정한다
OmitTbl.seq가 5일때 본인보다 작으면서 val이 NN인 1,4 중 MAX인 seq:4의 val을 val에 지정한다

정리) 자기자신보다 작은 seq이면서 NN인 seq 중 가장 큰 값의 val를 UPDATE한다.

쿼리 플랜

QUERY PLAN
Update on omittbl (cost=0.00..107.44 rows=6 width=50)
-> Seq Scan on omittbl (cost=0.00..107.44 rows=6 width=50)
Filter: (val IS NULL)
SubPlan 3
-> Index Scan using pk_omittbl on omittbl ot1 (cost=6.34..14.36 rows=1 width=4)
Index Cond: ((keycol = omittbl.keycol) AND (seq = $3))
InitPlan 2 (returns $3)
-> Result (cost=6.17..6.18 rows=1 width=4)
InitPlan 1 (returns $2)
-> Limit (cost=0.15..6.17 rows=1 width=4)
-> Index Scan Backward using pk_omittbl on omittbl ot2 (cost=0.15..12.20 rows=2 width=4)
Index Cond: ((keycol = omittbl.keycol) AND (seq IS NOT NULL) AND (seq < omittbl.seq))
Filter: (val IS NOT NULL)

위 결과를 볼 때

keycol과 seq를 키로 사용하는 index scan이 이뤄지고 있다 총 3번의 scan이 이뤄지지만 인덱스를 타게 됨으로 성능의 향상을 기대할 수 있다

결론_

성능 향상을 위해서는 쿼리플랜을 일상화하고
가급적이면 반복계를 지양하며
인덱스를 타도록 쿼리를 만들어야한다.

레코드에서 필드로 갱신하는 예시 SQL2

ScoreRows

Student_id	subject	score
A001	영어	100
A001	국어	58
A001	수학	90
B002	영어	77
B002	국어	60
C001	영어	52
C003	국어	49
C003	사회	100

아래표에 데이터를 채우고자 한다

Student_id	score_en	score_nl	Score_mt
A001
B002
C003

결과

Student_id	score_en	score_nl	Score_mt
A001	100	58	90
B002	77	60
C003	52	49

Student_Id가 A001이고 subject가 영어인 Score를 ScoreRows에서 찾아 넣으면 되지 않을까? Student_Id가 B002이고 subject가 국어인 Score도 넣으면 되지 않을까?

2-1 ) 첫번째 방법

업데이트 쿼리

UPDATE ScoreCols
SET score_en = (
  SELECT score
  FROM ScoreRows SR
  WHERE SR.student_id = ScoreCols.student_id
  AND subject = '영어'),
  score_nl = (
  SELECT score
  FROM ScoreRows SR
  WHERE SR.student_id = ScoreCols.student_id
  AND subject = '국어'),
  score_mt = (
  SELECT score
  FROM ScoreRows SR
  WHERE SR.student_id = ScoreCols.student_id
  AND subject = '수학');

쿼리 플랜

QUERY PLAN
Update on scorecols (cost=0.00..33357.20 rows=1360 width=38)
-> Seq Scan on scorecols (cost=0.00..33357.20 rows=1360 width=38)
SubPlan 1
-> Index Scan using pk_scorerows on scorerows sr (cost=0.15..8.17 rows=1 width=4)
Index Cond: ((student_id = scorecols.student_id) AND ((subject)::text = '영어'::text))
SubPlan 2
-> Index Scan using pk_scorerows on scorerows sr_1 (cost=0.15..8.17 rows=1 width=4)
Index Cond: ((student_id = scorecols.student_id) AND ((subject)::text = '국어'::text))
SubPlan 3
-> Index Scan using pk_scorerows on scorerows sr_2 (cost=0.15..8.17 rows=1 width=4)
Index Cond: ((student_id = scorecols.student_id) AND ((subject)::text = '수학'::text))

굉장히 간단하고 명확한 쿼리
총 3번의 스캔이 필요함으로 성능이 좋지 못함.
과목수가 더 늘어난다면? 계속해서 서브쿼리를 늘릴것인가?

2-2 ) 위 결과를 해결하기 위한 방법 -> 다중 필드 할당

다중 필드 할당이란?

SET 절에 (score_en, score_nl, score_mt)와 같이 리스트형식으로 지정하는 방법

리스트 전체를 하나의 조작단위로 만들 수 있다.

> POSTGRESQL UPDATE Synopsis


[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

업데이트 쿼리

UPDATE ScoreCols
  SET(score_en, score_nl, score_mt) = (
    SELECT MAX(CASE WHEN subject = '영어'
               THEN score
               ELSE NULL
               END
               ) AS score_en,
           MAX(CASE WHEN subject = '국어'
               THEN score
               ELSE NULL
               END
               ) AS score_nl,
           MAX(CASE WHEN subject = '수학'
               THEN score
               ELSE NULL
               END
               ) AS score_mt
    FROM ScoreRows SR
    WHERE SR.student_id = ScoreCols.student_id
  );

쿼리플랜

QUERY PLAN
Update on scorecols (cost=0.00..17359.23 rows=1360 width=70)
-> Seq Scan on scorecols (cost=0.00..17359.23 rows=1360 width=70)
SubPlan 1 (returns $1,$2,$3)
-> Aggregate (cost=12.74..12.75 rows=1 width=12)
-> Bitmap Heap Scan on scorerows sr (cost=4.19..12.66 rows=5 width=38)
Recheck Cond: (student_id = scorecols.student_id)
-> Bitmap Index Scan on pk_scorerows (cost=0.00..4.19 rows=5 width=0)
Index Cond: (student_id = scorecols.student_id)

ScoreRows

Student_id	subject	score
A001	영어	100
A001	국어	58
A001	수학	90
B002	영어	77
B002	국어	60
C001	영어	52
C003	국어	49
C003	사회	100

select 결과 조회

Student_id	score_en	score_nl	Score_mt
A001	100	NULL	NULL
A001	NULL	58	NULL
A001	NULL	NULL	90
B002	77	NULL	NULL
B002	NULL	60	NULL
C003	52	NULL	NULL
C003	NULL	49	NULL
C003	NULL	NULL	NULL

A001:(100, 58, 90), B002:(77,60,NULL), C003(52,49,NULL) -> UPDATE
한번에 갱신 -> 성능 향상
과목이 늘어나도 서브쿼리의 개수가 늘어나지 않아 향후 성능이 악화될 염려가 적음

2-3 ) NOT NULL의 제약사항을 추가해보자

ScoreColsNN

student_id	score_en	score_nl	score_mt
A001	0	0	0
B002	0	0	0
C003	0	0	0
D004	0	0	0

업데이트 쿼리

UPDATE ScoreColsNN
  SET(score_en, score_nl, score_mt) = (
    SELECT COALESCE(MAX(CASE WHEN subject = '영어'
               THEN score
               ELSE NULL
               END
               ), 0) AS score_en,
           COALESCE(MAX(CASE WHEN subject = '국어'
               THEN score
               ELSE NULL
               END
               ), 0) AS score_nl,
           COALESCE(MAX(CASE WHEN subject = '수학'
               THEN score
               ELSE NULL
               END
               ), 0) AS score_mt
    FROM ScoreRows SR
    WHERE SR.student_id = ScoreColsNN.student_id)
WHERE EXISTS (
  SELECT * FROM ScoreRows
  WHERE student_id = ScoreColsNN.student_id
);

EXISTS 구문 추가 (학생이 존재하면)

COALESCE 추가 (과목이 없어 NULL인 경우 0으로 replace)

3-1 ) 필드에서 레코드로 변경하는 예시 SQL3

ScoreCols

student_id	score_en	score_nl	score_mt
A001	100	58	90
B002	77	60
C003	52	49
D004	10	70	100

ScoreRows

student_id	subject	Score
A001	영어
A001	국어
A001	수학
B002	영어
B002	국어
C003	영어
C003	국어
C003	사회

ScoreRows 결과

student_id	subject	Score
A001	영어	100
A001	국어	58
A001	수학	90
B002	영어	77
B002	국어	60
C003	영어	52
C003	국어	49
C003	사회

업데이트 쿼리

UPDATE ScoreRows
SET score = (
  SELECT CASE ScoreRows.subject
    WHEN '영어' THEN score_en
    WHEN '국어' THEN score_nl
    WHEN '수학' THEN score_mt
    ELSE NULL
    END
  FROM ScoreCols
  WHERE student_id = ScoreRows.student_id
);

특정 student_id에 대해서 (score_en, score_nl, score_mt) (ex 100, 58, 90)을 가져와 
subject가 영어라면 score_en을 subject가 국어랑 score_nl, subject가 수학이라면 score_mt를 업데이트한다

쿼리플랜

QUERY PLAN
Update on scorerows (cost=0.00..7706.25 rows=940 width=64)
-> Seq Scan on scorerows (cost=0.00..7706.25 rows=940 width=64)
SubPlan 1
-> Index Scan using pk_scorecols on scorecols (cost=0.15..8.18 rows=1 width=4)
Index Cond: (student_id = scorerows.student_id)

한번의 접근으로 업데이트가 끝나며 인덱스를 타고 있다. 정렬이나 해시도 없음으로 성능적으로 더이상 개선할 여지가 없을 정도로 좋은 실행계획임.

4-1 ) 같은 테이블의 다른 레코드로 갱신 SQL4

Stock

brand	sale_date	price
A철강	2008-07-01	1000
A철강	2008-07-04	1200
A철강	2008-08-12	800
B상사	2008-06-04	3000
B상사	2008-09-11	3000
C전기	2008-07-01	9000
D산업	2008-06-04	5000
D산업	2008-06-05	5000
D산업	2008-06-06	4800
D산업	2008-12-01	5100

Stock2 (현재 종가를 이전 종가와 비교해서 올랐다면 등 주요 '상승', 내렸다면 '하락', 그대로라면 '그대로' 를 trend에 표기)

brand	sale_date	price	trend

결과

brand	sale_date	price	trend
A철강	2008-07-01	1000
A철강	2008-07-04	1200	상승
A철강	2008-08-12	800	하락
B상사	2008-06-04	3000
B상사	2008-09-11	3000	그대로
C전기	2008-07-01	9000
D산업	2008-06-04	5000
D산업	2008-06-05	5000	그대로
D산업	2008-06-06	4800	하락
D산업	2008-12-01	5100	상승

A철강 2008-08-12 날짜의 Stock 기준으로 자신보다 작은 sale_date를 가진 레코드(2008-07-01, 2008-07-04) 중에 MAX(2008-07-01)의 price를 기준 price에서 뺀다

신규추가 쿼리

INSERT INTO Stocks2
SELECT brand, sale_date, price,
  CASE SIGN (price -
             (SELECT price
              FROM Stocks S1
              WHERE brand = Stocks.brand
              AND sale_date = (
                SELECT MAX(sale_date) 
                FROM Stocks S2
                WHERE brand = Stocks.brand
                AND sale_date < Stocks.sale_date) 
              )
            )
   WHEN -1 THEN '아래'
   WHEN 0 THEN '그대로'
   WHEN 1 THEN '위'
   END
FROM Stocks;

정리)
stock의 sale_date보다 작은 sale_date 중에 최대 sale_date의 price를 현재 price에서 뺀 차이를 SIGN 처리

SIGN은 양수일때 1, 음수일 때 -1, 0 일때 0을 리턴하는 조사함수

쿼리플랜

QUERY PLAN
Insert on stocks2 (cost=0.00..16696.60 rows=1160 width=58)
-> Seq Scan on stocks (cost=0.00..16696.60 rows=1160 width=58)
SubPlan 3
-> Index Scan using pk_stocks on stocks s1 (cost=6.34..14.36 rows=1 width=4)
Index Cond: (((brand)::text = (stocks.brand)::text) AND (sale_date = $3))
InitPlan 2 (returns $3)
-> Result (cost=6.17..6.18 rows=1 width=4)
InitPlan 1 (returns $2)
-> Limit (cost=0.15..6.17 rows=1 width=4)
-> Index Only Scan Backward using pk_stocks on stocks s2 (cost=0.15..12.20 rows=2 width=4)
Index Cond: ((brand = (stocks.brand)::text) AND (sale_date IS NOT NULL) AND (sale_date < stocks.sale_date))

3번의 스캔이 이뤄진다 (상관 서브쿼리의 경우 어쩔 수 없이 여러번의 스캔이 발생함)

4-2 ) 윈도우 함수로 스캔 횟수 줄이기

INSERT INTO Stocks2
SELECT brand, sale_date, price,
  CASE SIGN(price -
            MAX(price) OVER (PARTITION BY brand
                             ORDER BY sale_date
                             ROWS BETWEEN 1 PRECEDING
                             AND 1 PRECEDING)
            )
   WHEN -1 THEN '아래'
   WHEN 0 THEN '그대로'
   WHEN 1 THEN '위'
   END
FROM Stocks S2;

MAX(price) OVER (PARTITION BY brand
                 ORDER BY sale_date
                 ROWS BETWEEN 1 PRECEDING
                 AND 1 PRECEDING)
                 
한줄씩 다음으로 밀림

쿼리플랜

QUERY PLAN
Insert on stocks2 (cost=0.15..117.75 rows=1160 width=58)
-> Subquery Scan on "*SELECT*" (cost=0.15..117.75 rows=1160 width=58)
-> WindowAgg (cost=0.15..103.25 rows=1160 width=74)
-> Index Scan using pk_stocks on stocks s2 (cost=0.15..65.55 rows=1160 width=42)

4-3 ) SELECT-INSERT와 UPDATE 어떤게 더 좋을까?

SELECT-INSERT의 경우
- 장점 : UPDATE에 비해 성능이 더 좋다
- 단점 : 저장소의 용량을 2배 이상 소비한다
  - VIEW를 사용한다면?
  1. 장점 : VIEW를 사용하면 용량을 절약할 수 있고 정보를 항상 최신으로 유지(동기성)할 수 있다.
  2. 단점 : VIEW에 접근이 발생할 때마다 복잡한 연산이 수행되므로 VIEW에 접근하는 쿼리의 성능이 낮아진다.
  3. 결국 성능과 동기성(정보의 최신성)의 트레이드오프 (=반비례 관계).

5-1) 갱신이 초래하는 트레이드오프 SQL5

주문마다 주문일(order_date)와 상품의 배송 예정일(delivery_date)의 차이를 구해 3일 이상 찾기

order_id를 기준으로 두 테이블 조인 후 OrderReceipts.delivery_date - Orders.order_date >= 3

Orders

order_id	order_shop	order_name	order_date
10000	서울	윤인성	2011/08/22
10001	인천	연하진	2011/09/01
10002	인천	패밀리마트	2011/09/20
10003	부천	한빛미디어	2011/08/25
10004	수원	동네슈퍼	2011/08/22
10005	성남	야근카페	2011/08/29

OrderReceipts

order_id	order_receipt_id	item_group	delivery_date
10000	1	식기	2011/08/24
10000	2	과자	2011/08/25
10000	3	소고기	2011/08/26
10001	1	어패류	2011/09/04
10002	1	과자	2011/09/22
10002	2	조미료 세트	2011/09/22
10003	1	쌀	2011/08/06
10003	2	소고기	2011/08/10
10003	3	식기	2011/08/10
10004	1	야채	2011/08/23
10005	1	음료수	2011/08/30
10005	2	과자	2011/08/30

조회 쿼리

SELECT O.order_id,
       O.order_name,
       ORC.delivery_Date - O.order_date AS diff
FROM Orders O
INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id
WHERE ORC.delivery_date - O.order_Date >= 3;

주문 별 최대 지연일이 궁금하면

SELECT O.order_id,
       MAX(O.order_name),
       MAX(ORC.delivery_date - O.order_date) AS max_diff
FROM Orders O
INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id
WHERE ORC.delivery_date - O.order_Date >= 3
GROUP BY O.order_id;

MAX(0.order_name)에서 MAX은 GROUP BY 를 위해서 쓰인 집계함수 MAX(ORC.deliverty_date - O.order_date)의 MAX는 실제 차이의 최대값을 구하기 위해서 쓰임.

5-2 ) 굳이 조회 쿼리를 계속 날려야 할까??

어드민이나 API서버에서 위와같은 쿼리를 지속적으로 날리는게 과연 맞을까?

콜럼버스의 달걀

Orders

order_id	order_shop	order_name	order_date	del_late_flag
10000	서울	윤인성	2011/08/22	1
10001	인천	연하진	2011/09/01	1
10002	인천	패밀리마트	2011/09/20	0
10003	부천	한빛미디어	2011/08/25	1
10004	수원	동네슈퍼	2011/08/22	1
10005	성남	야근카페	2011/08/29	0

위와 같이 지연 여부 필드를 하나 추가해서 조회하도록 하면 되지 않을까? -> 모델갱신
어떤 문제를 해결하고자 할 때 방법은 다양하다. 모든 문제를 SQL 만으로 해결할 필요는 없다.

6-1 ) 모델 갱신의 주의점

장점 : 복잡한 쿼리로 머리를 싸매지 않아도 된다.
단점
1. 높아지는 갱신비용
  - 당연하지만 del_late_flag 필드를 갱신하기 위해선 또 다른 처리가 필요하다
2. 갱신까지의 시간 랙(Time Rag) 발생
  - Orders 테이블의 배송지연 플래그 필드와 OrderReceipts 테이블의 배송 예정일 필드가 실시간으로 동기화되지 않으므로 차이가 발생할 수 있다
    1. 주문을 등록(Orders::주문일=2011/8/22,배송지연플래그=NULL)
    2. 주문 명세를 등록(OrderReceipts::배송예정일=NULL)
    3. 주문 명세를 갱신(OrderReceipts::배송예정일=2011/8/26)
    4. <지연>
    5. 주문 갱신(Order::배송지연플래그='1')
  - 실시간성이 매우 중요하다면 3번과 5번의 간격을 좁혀야하는데 완전 동기화를 원한다면 트랜젝션으로 묶어줘야한다.
    - 성능과 실시간성 사이에 트레이드오프가 발생한다
3. 모델 갱신비용 발생
  - 모델링을 처음하는거라면 괜찮지만 기존의 모델구조를 바꾸려면 비용이 발생한다.

7-1 ) 어떤 쿼리가 더 좋은가

주문마다 몇개의 상품이 주문되었는지 궁금하다

SELECT O.order_id,
       MAX(O.order_name) AS order_name,
       MAX(O.order_date) AS order_date,
       COUNT(*) AS item_count
FROM Orders O
INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id
GROUP BY O.order_id;

결과

order_id	order_name	order_date	item_count
10002	패밀리마트	2011-09-20	2
10005	야근카페	2011-08-29	2
10004	동네슈퍼	2011-08-22	1
10001	연하진	2011-09-01	1
10000	윤인성	2011-08-22	3
10003	한빛미디어	2011-08-05	3

윈도우 함수

SELECT O.order_id,
       O.order_name,
       O.order_date,
       COUNT(*) OVER (PARTITION BY O.order_id) AS item_count
FROM Orders O
INNER JOIN OrderReceipts ORC ON O.order_id = ORC.order_id;

쿼리플랜 비교 (성능은 동일)

HashAggregate (cost=51.27..55.27 rows=400 width=48)
Group Key: o.order_id
-> Hash Join (cost=19.00..44.67 rows=660 width=90)
Hash Cond: (orc.order_id = o.order_id)
-> Seq Scan on orderreceipts orc (cost=0.00..16.60 rows=660 width=4)
-> Hash (cost=14.00..14.00 rows=400 width=90)
-> Seq Scan on orders o (cost=0.00..14.00 rows=400 width=90)

------------------------------------------------------------------------------------------

QUERY PLAN
WindowAgg (cost=75.58..87.13 rows=660 width=98)
-> Sort (cost=75.58..77.23 rows=660 width=90)
Sort Key: o.order_id
-> Hash Join (cost=19.00..44.67 rows=660 width=90)
Hash Cond: (orc.order_id = o.order_id)
-> Seq Scan on orderreceipts orc (cost=0.00..16.60 rows=660 width=4)
-> Hash (cost=14.00..14.00 rows=400 width=90)
-> Seq Scan on orders o (cost=0.00..14.00 rows=400 width=90)

윈도우함수를 사용하는게 더 좋은 이유.
- 코드만 봐도 구하고자하는 바를 명확하게 알 수 있다 (가독성)
- 주문번호가 아닌 상품별 결과를 출력하고 싶을때도 최소한의 수정으로 대응할 수 있다 (확장성)
상품수(item_count) 필드를 추가하는건 어떨까? -> 모델 갱신
- 한번 등록한 주문이 수정되면 상품수도 함께 수정되야한다.
- 앞에서 언급한 동기화 문제를 고려해야한다.
넓은 시야를 가지고 어떻게 해결할지에 대한 고민을 해야한다.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

9장_갱신.md

9장_갱신.md

갱신은 효율적으로

1-1) 반복계를 피하고 가급적 인덱스를 사용하자 (쿼리 플랜의 중요성) SQL1

레코드에서 필드로 갱신하는 예시 SQL2

3-1 ) 필드에서 레코드로 변경하는 예시 SQL3

4-1 ) 같은 테이블의 다른 레코드로 갱신 SQL4

5-1) 갱신이 초래하는 트레이드오프 SQL5

6-1 ) 모델 갱신의 주의점

7-1 ) 어떤 쿼리가 더 좋은가

Files

9장_갱신.md

Latest commit

History

9장_갱신.md

File metadata and controls

갱신은 효율적으로

1-1) 반복계를 피하고 가급적 인덱스를 사용하자 (쿼리 플랜의 중요성) SQL1

레코드에서 필드로 갱신하는 예시 SQL2

3-1 ) 필드에서 레코드로 변경하는 예시 SQL3

4-1 ) 같은 테이블의 다른 레코드로 갱신 SQL4

5-1) 갱신이 초래하는 트레이드오프 SQL5

6-1 ) 모델 갱신의 주의점

7-1 ) 어떤 쿼리가 더 좋은가