*보안을 준수하기 위해 각종 명칭은 모두 디저트 명으로 대체했다

문제 상황

맡은지 한 달이 된 서비스에서 daily batch 작업 실행 중 심각한 DB 서버 병목 현상이 발생해 서비스 장애로 이어졌다.

증상

특정 쿼리가 5시간 동안 실행되며 결과를 반환하지 않음
후속 작업들이 대기 상태로 멈춰 batch 프로세스 전체가 중단
해당 프로세스 이후의 데이터 처리 파이프라인 진행 모두 중단
쿼리 자체는 기존과 동일했으나, 서버 재구축 이후 성능이 급격히 저하

pid   | usename    | application_name | client_addr    | duration        | state               | query_preview
-------+------------+------------------+----------------+-----------------+---------------------+--------------------------------------------------
 31405 | bakery_usr | psql             | 10.20.30.45    | 00:00:00        | active              | SELECT pid, usename, application_name, client_ad
 30798 | bakery_usr | psql             | 10.20.30.45    | 00:10:52.290625 | active              | select BB.RECIPE_ID,BB.PRODUCT_ID,BB.BAKER_ID,BB.OR
 29975 | bakery_usr |                  | 10.20.30.45    | 00:25:26.845155 | active              | select BB.RECIPE_ID,BB.PRODUCT_ID,BB.BAKER_ID,BB.OR
 28883 | bakery_usr |                  | 10.20.30.45    | 05:44:31.487827 | idle in transaction | select BB.RECIPE_ID,BB.PRODUCT_ID,BB.BAKER_ID,BB.OR

내가 맡게 된지 얼마 안 되어 서버가 초기화되는 일이 있었다(타 팀원이 limits.conf를 다른 곳으로 옮겨 PAM모듈이 깨짐, root 계정 및 싱글모드 진입 불가).

기존에는 캐싱 덕분인지 무엇 덕분인지 시간 내에 쿼리들이 간신히 돌아가고 있었으나 서버 재구축 및 DB 재기동 이후 시간 내에 절대 끝나지 않아 데이터 처리의 다음 과정들을 진행할 수 없어 꽤 심각한 문제가 되었다.

운영 인수인계를 받고 소스코드를 읽고 있을 때부터 쿼리가 이래도 되나 싶게 중첩 서브쿼리가 많았다. 나중에 개선은 해야할 것 같다고 생각하고 있었는데 정말 장애가 생겨서 개선해야 되게 되었다.

쿼리를 하나씩 분석한 결과 크게는 다음과 같은 문제들이 있었다.

1. 불필요한 서브쿼리 남발

최적화되지 않은 다중 서브쿼리가 반복적으로 실행됨
각 서브쿼리마다 독립적으로 테이블을 조회

2. 인덱스 부재로 인한 Full Table Scan

주요 테이블에 인덱스가 전혀 설정되어 있지 않음
매 쿼리마다 테이블 전체를 스캔하는 최악의 상황

3. 인덱스를 타지 못하는 쿼리 패턴

날짜 컬럼에 to_char() 함수나 ::date 캐스팅을 적용
함수 적용으로 인해 인덱스를 타지 못하는 구조

추가적으로 Django로 된 프로젝트인데 Java Spring 프로젝트만 하다가 들어간 나는 Python Django 프로젝트에 DB Connection Pool이 존재하지 않고 하나의 작업당 매번 연결 생성/ 해제를 한다는것에 매우 놀라게 된다…

이것도 차차 알아보고 해결을 해야할 듯 하다

def query_db(query, args=(), one=False):
    cur = db().cursor()              *# 매번 새로운 연결 생성*
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) \\
        for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()           *# 매번 연결 종료*
    return (r[0] if r else None) if one else r

해결 과정

서비스 장애를 최우선으로 해결하기 위해 쿼리 튜닝과 인덱스 최적화 작업에 착수했다.

그 후 20250804~20250806 데이터를 대상으로 테스트 쿼리를 돌려 결과가 100% 일치하는지 검증하는 과정을 거쳤다

1. 날짜 조건 최적화

Before: 인덱스를 타지 못하는 패턴

WHERE to_char(created_at, 'YYYY-MM-DD') = '2025-01-15'
WHERE created_at::date = '2025-01-15'

→

After: 인덱스를 타는 패턴

WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16'

날짜 컬럼에 함수를 적용하지 않고 범위 조건으로 변경하여 인덱스를 정상적으로 활용할 수 있도록 수정했다.

2. 서브쿼리 제거 및 JOIN 최적화

반복적으로 실행되던 서브쿼리들을 적절한 JOIN 또는 CTE(Common Table Expression)로 변경하여 쿼리 실행 횟수를 대폭 줄였다.

원본 쿼리는 다음과 같다(보안 정책상 테이블명 및 칼럼명을 디저트로 변경함)

변경 전 쿼리

select concat('special ',' ', tp.macaron) as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special at ', tp.macaron, ' please') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' navigate') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜재시도계속sql릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' show me') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' start') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' recommend') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='초콜릿' OR dp.dessert_style='다크초콜릿')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special ',' ', tp.macaron) as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special at ', tp.macaron, ' please') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' navigate') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' show me') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' start') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' recommend') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미수급') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미수급' OR dp.dessert_style='미수급')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special ',' ', tp.macaron) as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special at ', tp.macaron, ' please') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' navigate') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' show me') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' start') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' recommend') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='미제공') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='미제공')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special ',' ', tp.macaron) as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special at ', tp.macaron, ' please') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' navigate') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening재시도계속sql_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' show me') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' start') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' recommend') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표') 
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special ',' ', tp.macaron) as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT
UNION ALL
select concat('special at ', tp.macaron, ' please') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' navigate') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' show me') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' start') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT
UNION ALL
select concat(tp.macaron, ' recommend') as DESSERT_TEXT
from (
    select to_char(sw.opening_date,'YYYYMMDD') as opening_date 
    from chocolate_shop as sw, macarons_topping as tp, chocolate_dessert as dp 
    where dp.dessert_item_idx=tp.dessert_item_idx  
      AND dp.baking_idx=sw.baking_idx  
      AND sw.shop_type='cafe'  
      AND dp.baking_status !='D' 
      AND sw.shop_open='true' 
      AND (dp.dessert_style='편성표 기반')  
      AND (to_char(sw.opening_date,'YYYYMMDD') >= '{STARTDATE}' 
           AND to_char(sw.opening_date,'YYYYMMDD') <= '{ENDDATE}') 
    order by to_char(sw.opening_date,'YYYYMMDD') desc 
    limit 1
) as AAA, chocolate_dessert as dp, public.macarons_topping as tp, public.chocolate_shop as sw
where tp.dessert_item_idx = dp.dessert_item_idx
  AND to_char(sw.opening_date,'YYYYMMDD') = AAA.opening_date
  AND dp.baking_idx = sw.baking_idx
  AND sw.recipe_complete = 'true'
  AND sw.recipe_topping = 'true'
  AND sw.recipe_frosting = 'true'
  AND sw.recipe_decoration = 'true'
  AND dp.dessert_category = 'cafe'
  AND tp.baking_status != 'D'
  AND (dp.dessert_style='편성표 기반')
GROUP BY DESSERT_TEXT;

→

변경 후 쿼리

WITH latest_opening_date AS (
    SELECT sw.opening_date
    FROM chocolate_shop as sw
    INNER JOIN chocolate_dessert as dp ON dp.baking_idx = sw.baking_idx
    INNER JOIN macarons_topping as tp ON dp.dessert_item_idx = tp.dessert_item_idx
    WHERE sw.shop_type = 'cafe'
      AND dp.baking_status != 'D'
      AND sw.shop_open = 'true'
      AND (dp.dessert_style = '초콜릿' OR dp.dessert_style = '다크초콜릿')
      AND sw.opening_date >= '{STARTDATE}'
      AND sw.opening_date <= '{ENDDATE}'
    ORDER BY sw.opening_date DESC
    LIMIT 1
),
chocolate_data AS (
    SELECT DISTINCT tp.macaron
    FROM latest_opening_date lod
    INNER JOIN chocolate_shop as sw ON sw.opening_date = lod.opening_date
    INNER JOIN chocolate_dessert as dp ON dp.baking_idx = sw.baking_idx
    INNER JOIN macarons_topping as tp ON tp.dessert_item_idx = dp.dessert_item_idx
    WHERE sw.recipe_complete = 'true'
      AND sw.recipe_topping = 'true'
      AND sw.recipe_frosting = 'true'
      AND sw.recipe_decoration = 'true'
      AND dp.dessert_category = 'cafe'
      AND tp.baking_status != 'D'
      AND (dp.dessert_style = '초콜릿' OR dp.dessert_style = '다크초콜릿')
)
SELECT 'special ' || macaron AS DESSERT_TEXT FROM chocolate_data
UNION ALL
SELECT 'special at ' || macaron || ' please' AS DESSERT_TEXT FROM chocolate_data
UNION ALL
SELECT macaron || ' navigate' AS DESSERT_TEXT FROM chocolate_data
UNION ALL
SELECT macaron || ' show me' AS DESSERT_TEXT FROM chocolate_data
UNION ALL
SELECT macaron || ' start' AS DESSERT_TEXT FROM chocolate_data
UNION ALL
SELECT macaron || ' recommend' AS DESSERT_TEXT FROM chocolate_data;

📝 개선 효과 369ms -> 62ms (83% 향상)

58.2% 향상 쿼리

bakery_system=# SELECT 'Original Count' as type, COUNT(*) as cnt FROM (
    select pr.product_recipe_idx 
    from (
        select to_char(bk.batch_complete_date,'YYYYMMDD') as batch_complete_date 
        from bakery_batch as bk,
             bakery_topping_recipe as tf,
             bakery_product_recipe as pr 
        where pr.product_recipe_idx = tf.product_recipe_idx 
          AND pr.batch_idx = bk.batch_idx 
          AND bk.batch_type = 'bread' 
          AND pr.batch_history != 'D' 
          AND bk.batch_stat = 'true' 
          AND (pr.original_bread_category = '크루아상' OR pr.original_bread_category = '바게트') 
          AND (to_char(bk.batch_complete_date,'YYYYMMDD') >= '20250616' 
               AND to_char(bk.batch_complete_date,'YYYYMMDD') <= '20250720') 
        order by to_char(bk.batch_complete_date,'YYYYMMDD') desc 
        limit 1
    ) AAA, 
    bakery_product_recipe as pr,
    bakery_topping_recipe as tf,
    bakery_batch as bk
    where pr.product_recipe_idx = tf.product_recipe_idx 
      AND pr.batch_idx = bk.batch_idx 
      AND to_char(bk.batch_complete_date,'YYYYMMDD') = AAA.batch_complete_date 
      AND bk.recipe_batch_stat = 'true' 
      AND bk.recipe_batch_stat_topping = 'true' 
      AND bk.recipe_batch_stat_filling = 'true' 
      AND bk.recipe_batch_stat_deco = 'true' 
      AND pr.original_bread_type = 'bread' 
      AND tf.batch_history != 'D' 
      AND (pr.original_bread_category = '크루아상' OR pr.original_bread_category = '바게트') 
      AND pr.batch_history != 'D' 
    GROUP BY pr.product_recipe_idx
) x;

     type      | cnt 
----------------+-----
 Original Count |  42
(1 row)

Time: 401.969 ms

→

bakery_system=# SELECT 'Optimized Count' as type, COUNT(*) as cnt FROM (
    select DISTINCT pr.product_recipe_idx 
    from (
        select to_char(bk.batch_complete_date,'YYYYMMDD') as batch_complete_date 
        from bakery_batch as bk 
        INNER JOIN bakery_product_recipe as pr ON pr.batch_idx = bk.batch_idx 
        INNER JOIN bakery_topping_recipe as tf ON pr.product_recipe_idx = tf.product_recipe_idx 
        where bk.batch_type = 'bread' 
          AND pr.batch_history != 'D' 
          AND bk.batch_stat = 'true' 
          AND (pr.original_bread_category = '크루아상' OR pr.original_bread_category = '바게트') 
          AND to_char(bk.batch_complete_date,'YYYYMMDD') BETWEEN '20250616' AND '20250720' 
        order by bk.batch_complete_date desc 
        limit 1
    ) AAA 
    INNER JOIN bakery_product_recipe as pr ON pr.batch_idx IN (
        SELECT batch_idx 
        FROM bakery_batch 
        WHERE batch_type = 'bread' 
          AND to_char(batch_complete_date,'YYYYMMDD') = AAA.batch_complete_date
    )
    INNER JOIN bakery_batch as bk ON pr.batch_idx = bk.batch_idx 
    where bk.recipe_batch_stat = 'true' 
      AND bk.recipe_batch_stat_topping = 'true' 
      AND bk.recipe_batch_stat_filling = 'true' 
      AND bk.recipe_batch_stat_deco = 'true' 
      AND pr.original_bread_type = 'bread' 
      AND (pr.original_bread_category = '크루아상' OR pr.original_bread_category = '바게트') 
      AND pr.batch_history != 'D' 
      AND to_char(bk.batch_complete_date,'YYYYMMDD') = AAA.batch_complete_date
) x;

      type       | cnt 
-----------------+-----
 Optimized Count |  42
(1 row)

Time: 167.866 ms

📝개선 효과

401.969ms→167.866 ms (58.2% 향상)

데이터 100% 일치

98.30% 개선

WITH DAILY_BAKING AS 
    (SELECT DISTINCT TF.TOPPING_NAME
     FROM 
         (SELECT DISTINCT BK.BATCH_IDX AS BATCH_IDX
          FROM BAKERY_BATCH AS BK,
               BAKERY_TOPPING_RECIPE AS TF,
               BAKERY_PRODUCT_RECIPE AS PR
          WHERE PR.PRODUCT_RECIPE_IDX = TF.PRODUCT_RECIPE_IDX
            AND PR.BATCH_IDX = BK.BATCH_IDX
            AND PR.BATCH_HISTORY != 'D'
            AND BK.BATCH_TYPE = 'bread'
            AND BK.BATCH_STAT = 'true'
            AND (TO_CHAR(BK.BATCH_COMPLETE_DATE,'YYYYMMDD') = '{DAILYDATE}')
          ORDER BY BK.BATCH_IDX DESC) AS AAA,
         BAKERY_PRODUCT_RECIPE AS PR,
         PUBLIC.BAKERY_TOPPING_RECIPE AS TF,
         PUBLIC.BAKERY_BATCH AS BK
     WHERE TF.PRODUCT_RECIPE_IDX = PR.PRODUCT_RECIPE_IDX
       AND BK.BATCH_IDX = AAA.BATCH_IDX
       AND PR.BATCH_IDX = BK.BATCH_IDX
       AND BK.RECIPE_BATCH_STAT = 'true'
       AND BK.RECIPE_BATCH_STAT_TOPPING = 'true'
       AND BK.RECIPE_BATCH_STAT_FILLING = 'true'
       AND BK.RECIPE_BATCH_STAT_DECO = 'true'
       AND PR.ORIGINAL_BREAD_TYPE = 'bread'
       AND TF.BATCH_HISTORY != 'D')
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME) FROM DAILY_BAKING
UNION ALL
SELECT CONCAT('bakery',' ',DAILY_BAKING.TOPPING_NAME) FROM DAILY_BAKING
UNION ALL
SELECT CONCAT('bakery에서 ',DAILY_BAKING.TOPPING_NAME,' 만들어줘') FROM DAILY_BAKING
UNION ALL
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME,' 이동') FROM DAILY_BAKING
UNION ALL
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME,' 구워줘') FROM DAILY_BAKING
UNION ALL
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME,' 만들어줘') FROM DAILY_BAKING
UNION ALL
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME,' 숙성해줘') FROM DAILY_BAKING
UNION ALL
SELECT CONCAT(DAILY_BAKING.TOPPING_NAME,' 추천해줘') FROM DAILY_BAKING;

→

WITH DAILY_BAKING AS (
    SELECT DISTINCT TF.topping_name
    FROM bakery_batch AS BK
    INNER JOIN bakery_product_recipe AS PR ON PR.BATCH_IDX = BK.BATCH_IDX
    INNER JOIN bakery_topping_recipe AS TF ON PR.PRODUCT_RECIPE_IDX = TF.PRODUCT_RECIPE_IDX
    WHERE BK.BATCH_TYPE = 'bread'
      AND BK.BATCH_STAT = 'true'
      AND BK.BATCH_COMPLETE_DATE::date = '{DAILYDATE}'::date
      AND BK.RECIPE_BATCH_STAT = 'true'
      AND BK.RECIPE_BATCH_STAT_TOPPING = 'true'
      AND BK.RECIPE_BATCH_STAT_FILLING = 'true'
      AND BK.RECIPE_BATCH_STAT_DECO = 'true'
      AND PR.ORIGINAL_BREAD_TYPE = 'bread'
      AND PR.BATCH_HISTORY != 'D'
      AND TF.BATCH_HISTORY != 'D'
)
SELECT topping_name FROM DAILY_BAKING
UNION ALL
SELECT 'bakery ' || topping_name FROM DAILY_BAKING
UNION ALL
SELECT 'bakery에서 ' || topping_name || ' 만들어줘' FROM DAILY_BAKING
UNION ALL
SELECT topping_name || ' 이동' FROM DAILY_BAKING
UNION ALL
SELECT topping_name || ' 구워줘' FROM DAILY_BAKING
UNION ALL
SELECT topping_name || ' 만들어줘' FROM DAILY_BAKING
UNION ALL
SELECT topping_name || ' 숙성해줘' FROM DAILY_BAKING
UNION ALL
SELECT topping_name || ' 추천해줘' FROM DAILY_BAKING;

원본 쿼리 실행 계획

Aggregate  (actual time=351.207..353.581 rows=1 loops=1)
  CTE daily_job
    ->  HashAggregate  (actual time=348.968..349.185 rows=150 loops=1)
          ->  Nested Loop  (actual time=2.820..348.717 rows=192 loops=1)
                ->  Nested Loop  (actual time=2.798..348.001 rows=130 loops=1)
                      ->  Nested Loop  (actual time=2.782..334.754 rows=195 loops=1)
                            ->  Seq Scan on dictionary_work dw
                                  Filter: (TO_CHAR(...) = '20250806')
  Buffers: shared hit=4110 read=4083
  Execution time: 353.581 ms

최적화 쿼리 실행 계획

Aggregate  (actual time=5.784..5.785 rows=1 loops=1)
  CTE daily_job
    ->  HashAggregate  (actual time=3.952..4.015 rows=150 loops=1)
          ->  Nested Loop  (actual time=3.117..3.839 rows=192 loops=1)
                ->  Nested Loop  (actual time=3.104..3.334 rows=110 loops=1)
                      ->  Index Scan using idx_dictionary_work_main
                            Index Cond: ((work_type = 'tv'::text) AND (work_stat = 'true'::text))
                            Filter: ((work_complete_date)::date = '2025-08-06'::date)
  Buffers: shared hit=1262
  Execution time: 5.784 ms

📝 개선 효과

개선점들

JOIN 구조 단순화
인덱스 활용 가능한 날짜 비교 WHERE DW.WORK_COMPLETE_DATE::date = '2025-08-06'::date
명시적 INNER JOIN 사용

개선 효과

응답 시간 353ms → 6ms (347ms 단축, 98.30% 개선)

메모리 효율 69% 향상

디스크 I/O 100% 제거 (캐시만 사용)

인덱스 활용 Sequential Scan → Index Scan

데이터 정합성 1,200건 완벽 일치 (100%)

3. 인덱스 추가

자주 조회되는 컬럼과 JOIN 조건에 사용되는 컬럼에 적절한 인덱스를 생성했다.

결과

예시로 든 쿼리들 포함 배치 작업에서 사용하는 10여개의 쿼리들을

데이터가 일치하는지 테스트 쿼리를 여러 날짜로 시행하고 실행 시간을 측정하며 튜닝한 결과

쿼리 실행 시간: 5시간 → 수 분 이내로 단축
Daily batch 작업이 정상적으로 완료되어 서비스 안정성 회복 및 장애 해결
전반적인 DB 서버 부하 감소

핵심 개선 포인트

1. 스칼라 서브쿼리 → JOIN

동일 테이블에 대한 반복 조회를 단 한 번의 JOIN으로 해결
N+1 문제 완전 제거

2. 함수 사용 제거

날짜 컬럼의 to_char(), ::date 캐스팅 제거
인덱스 스캔 가능하도록 범위 조건으로 변경

3. CTE 활용

반복되는 서브쿼리를 CTE로 한 번만 실행
코드 가독성과 유지보수성 향상

4. 적절한 인덱스 설계

자주 사용되는 조회 조건에 인덱스 추가
복합 인덱스로 커버링 인덱스 효과

교훈

이번 장애를 통해 얻은 교훈은 다음과 같다

슬로우 쿼리는 언젠가 터진다 - 근본적인 쿼리 성능 문제는 반드시 개선해야 한다
인덱스 설계는 필수 - 테이블 설계 단계부터 적절한 인덱스를 함께 고려해야 한다
쿼리 작성 시 실행 계획 확인 - EXPLAIN ANALYZE를 통해 쿼리가 인덱스를 제대로 활용하는지 확인하는 습관이 중요하다
정기적인 성능 모니터링 - 슬로우 쿼리 및 각 API 실행 시간을 주기적으로 모니터링하고 개선해야 한다

후속 조치

이후에 psql 쿼리 모니터링을 켰다(슬로우 쿼리 분석 및 인덱스 활용도를 알기위해)
저렇게 바꾼 쿼리들이 인덱스를 똑바로 타고 있는지 모두 Analyze를 찍어보고 ::date를 더 제거했다(이 부분은 이번에 psql 16으로 버전업하게 되어 다시 체크를 해야할 것 같다…)
API 실행 로그를 남기도록 Django 설정을 변경했다 (애초에 내가 맡기 전에 없었던 것이 문제이긴 하다)

저작자표시 (새창열림)

'Journal' 카테고리의 다른 글

웹소켓 Subscribe시에 Interceptor에서 채팅방 권한을 조회하도록 함 (1)	2024.11.05
Spring과 STOMP를 이용해서 웹소켓 연결하기 (0)	2024.09.26
Spring에서 MongoDB 이용할때의 Pagination : MongoTemplate 으로 List<Model> , Page<Model> 리턴 (1)	2024.09.22
QueryDSL로 검색 기능 구현 + 쿼리 빌드 단계적으로 하기 + LocalDateTime을 Controller의 Parameter로 받을 때 주의할 점 (0)	2024.09.05
@EnableJpaAuditing 이 한 곳에서만 정의되어야 하는 이유 (0)	2024.09.05

Greedy Developer

DB서버 병목 장애 해결기: 5시간 배치를 5분으로 만들기

문제 상황

증상

해결 과정

1. 날짜 조건 최적화

Before: 인덱스를 타지 못하는 패턴

→

After: 인덱스를 타는 패턴

2. 서브쿼리 제거 및 JOIN 최적화

변경 전 쿼리

→

변경 후 쿼리

58.2% 향상 쿼리

98.30% 개선

원본 쿼리 실행 계획

최적화 쿼리 실행 계획

개선점들

개선 효과

3. 인덱스 추가

결과

핵심 개선 포인트

교훈

후속 조치

'Journal' 카테고리의 다른 글

티스토리툴바

DB서버 병목 장애 해결기: 5시간 배치를 5분으로 만들기

문제 상황

증상

해결 과정

1. 날짜 조건 최적화

Before: 인덱스를 타지 못하는 패턴

→

After: 인덱스를 타는 패턴

2. 서브쿼리 제거 및 JOIN 최적화

변경 전 쿼리

→

변경 후 쿼리

58.2% 향상 쿼리

98.30% 개선

원본 쿼리 실행 계획

최적화 쿼리 실행 계획

개선점들

개선 효과

3. 인덱스 추가

결과

핵심 개선 포인트

교훈

후속 조치

'Journal' 카테고리의 다른 글

관련글

티스토리툴바