*보안을 준수하기 위해 모두 디저트 명으로 대체했다
문제 상황
맡은지 한 달이 된 서비스에서 daily batch 작업 실행 중 심각한 DB 서버 병목 현상이 발생해 서비스 장애로 이어졌다.
증상
- 특정 쿼리가 5시간 동안 실행되며 결과를 반환하지 않음
- 후속 작업들이 대기 상태로 멈춰 batch 프로세스 전체가 중단
- 해당 프로세스 이후의 데이터 처리 파이프라인 진행 모두 중단
- 쿼리 자체는 기존과 동일했으나, 서버 재구축 이후 성능이 급격히 저하
pg_stat_activity 확인 결과
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배치 프로그램이 느리게 실행된 원인
0. 서버 초기화의 스노우볼
내가 맡게 된지 얼마 안 되어 서버가 초기화되는 일이 있었다(타 팀원이 limits.conf를 다른 곳으로 옮겨 PAM모듈이 깨짐, root 계정 및 싱글모드 진입 불가).
서버 초기화 이후 포스트그레 재기동을 하고 나니 시간 안에 돌아가지 않게 되었다.
1. 디스크 I/O의 폭증
아래와 같은 3개의 원인이 맞물려 디스크 I/O가 폭증하게 되었다.
1-1. 버퍼 캐시가 사라짐, OS 캐시도 사라짐
원래도 쿼리 자체는 비효율적이었지만 지속적으로 쌓인 캐시 덕분에 그나마 시간 안에 돌아가고 있었을 거라고 생각한다...
쿼리 돌리다가 장애가 나서 다 취소하고 Explain Analyze를 해보았더니 다음과 같이 엄청난 수의 데이터를 물리적 디스크에서 읽어오고 있었다.
Buffers: shared hit=4110 read=40831-2. 테이블 풀 스캔이 너무 많이 발생하는 문제
다음과 같은 쿼리상의 3중고로 테이블 풀스캔이 불필요하게 많이 발생했다!!!
1. 불필요한 서브쿼리 남발
- 최적화되지 않은 다중 서브쿼리가 반복적으로 실행됨
- 쿼리 예시 1) 카테고리 5개 × 발화 패턴 6개 = 30개의 UNION ALL이었는데, 각 UNION마다 동일한 서브쿼리로 최신 날짜를 조회해서 같은 서브쿼리가 30번 실행되는 구조로 짜여져 있었다.
- 쿼리 예시 2) Correlated Subquery의 지옥
- SELECT 절 안에 바깥 쿼리의 컬럼을 참조하는 서브쿼리가 3개나 들어있음
- 바깥 쿼리의 결과가 1만 건일 때, 해당 테이블을 3만 번 개별 조회하는 구조
- 각 서브쿼리마다 독립적으로 테이블을 조회
2. 인덱스 부재로 인한 Full Table Scan
- 주요 테이블에 인덱스가 전혀 설정되어 있지 않음
- 매 쿼리마다 테이블 전체를 스캔하는 최악의 상황
3. 인덱스를 타지 못하는 쿼리 패턴
- 날짜 컬럼에
to_char()함수나::date캐스팅을 적용 - 함수 적용으로 인해 인덱스를 생성해도 인덱스를 타지 못하는 구조
2. DB Connection을 매번 연결하고 끊음
추가적으로 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해결 과정
1. 캐시가 사라진 것
→ 해결 불가능2. 테이블 풀 스캔이 너무 많이 발생하는 문제
→ 쿼리 튜닝 및 인덱스 최적화 작업3. DB Connection을 매번 연결하고 끊음
→ DB Connection을 싱글톤 패턴으로 하나의 연결만 유지해서 매 쿼리마다 연결하고 끊지 않도록 수정_connection = None
def get_connection():
global _connection
if _connection is None or _connection.closed:
_connection = psycopg2.connect(...)
return _connection→ 장고 버전이 낮아 최신 버전의 장고에서만 출시된 Postgres용 DB Connection Pool 적용을 하지 못했고 미들웨어 적용을 하려다가 다른 프로젝트로 이동했다…
쿼리 튜닝 및 인덱스 최적화 작업
서비스 장애를 최우선으로 해결하기 위해 쿼리 튜닝과 인덱스 최적화 작업에 착수했다.
일단 디스크 I/O를 줄이기 위해서는 인덱스를 거는 것이 꼭 필요했는데,
데이터가 매일 쌓이는 구조고 데이터의 양이 많아 인덱스를 걸고 유지관리하는데 드는 비용도 꽤 들 것으로 생각되었다.
또 공통적으로 사용되는 칼럼이 매우 많아서 쿼리 패턴을 분석해보고 카디널리티가 높은 순서대로 SELECT문에 배치해서 가장 적은 수의 인덱스로 최대의 쿼리 효율을 뽑고 싶었다.
그래서 인덱스를 걸기 전 쿼리 튜닝부터 먼저 진행했다.
그 후 20250804~20250806 데이터를 대상으로 테스트 쿼리를 돌려 결과가 100% 일치하는지 검증하는 과정을 거쳤다.
핵심 개선 포인트들
1. 인덱스 사용 가능하도록 변경
- 날짜 컬럼의
to_char(),::date캐스팅 제거 - 인덱스 스캔 가능하도록 범위 조건으로 변경
2. 스칼라 서브쿼리 → JOIN
- 동일 테이블에 대한 반복 조회를 단 한 번의 JOIN으로 해결
- N+1 문제 완전 제거
3. CTE 활용
- 반복되는 서브쿼리를 CTE로 한 번만 실행
- 코드 가독성과 유지보수성 향상
4. 적절한 인덱스 설계
- 자주 사용되는 조회 조건에 인덱스 추가
- 복합 인덱스로 커버링 인덱스 효과
1. Range 쿼리들에 걸려있던 형변환 제거
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'이것만 보면 왜 굳이 범위 조건으로 인덱스를 타게 했을까 싶을 수 있다
애초에 왜 인덱스를 타지 못하게 저렇게 형변환하여 적용되어있을까 했더니
created_at이 timestamp 타입이었다.
timestamp 타입을 포스트그레가 '2025-01-15 00:00:00.000000'으로 해석하여 자정에 생성된 행만 잡힌다고 한다.
그래서 내일 자정 전까지의 모든 값을 가져와서 쓰면서 인덱스도 활용할 수 있도록 Range 쿼리로 변경했다.
2. 서브쿼리 제거 및 JOIN 최적화
반복적으로 실행되던 서브쿼리들을 적절하게 변경하여 쿼리 실행 횟수를 대폭 줄였다.(디스크 지켜!!)
총 7개의 쿼리를 변경했는데,
그 중 몇가지의 쿼리만 기재하였다. (보안 정책상 테이블명 및 칼럼명을 디저트로 변경함)
Query 1) 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문제점
-- FROM 절 안에 서브쿼리로 BATCH_IDX를 먼저 뽑고
FROM (
SELECT DISTINCT BK.BATCH_IDX
FROM BAKERY_BATCH, BAKERY_TOPPING_RECIPE, BAKERY_PRODUCT_RECIPE
WHERE ... AND TO_CHAR(BK.BATCH_COMPLETE_DATE,'YYYYMMDD') = '{DAILYDATE}'
) AS SPARKLE,
BAKERY_PRODUCT_RECIPE AS PR, -- 바깥에서 같은 테이블을 또 조회
BAKERY_TOPPING_RECIPE AS TF,
BAKERY_BATCH AS BK- 같은 테이블 세 개를 서브쿼리에서 한 번, 바깥에서 또 한 번 스캔하는 이중 구조
TO_CHAR()로 인해 날짜 인덱스를 전혀 못 탐
개선
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_COMPLETE_DATE::date = '{DAILYDATE}'::date
AND BK.BATCH_TYPE = 'bread'
...
)
SELECT topping_name FROM DAILY_BAKING
UNION ALL
SELECT 'bakery ' || topping_name FROM DAILY_BAKING개선점들
- 이중 테이블 스캔을 CTE + 명시적 INNER JOIN으로 단순화
- 형변환 제거해서 날짜 비교가 인덱스를 탈 수 있도록 함
- WHERE DW.WORK_COMPLETE_DATE::date = '2025-08-06'::date
개선 효과
- 응답 시간 353ms → 6ms (98.30% 개선)
- 메모리 효율 69% 향상
- 디스크 I/O 100% 제거 (캐시만 사용)
- 4,083 블록 → 0블록
- 캐시 사용 69% 감소
- 4,110 블록 → 1,262 블록
- 인덱스 활용 Sequential Scan → Index Scan
- 데이터 정합성 1,200건 완벽 일치 (100%)
Query 2) 83% 향상
변경 전 쿼리
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;문제점
-- 반복되는 서브쿼리~
select concat('special ',' ', tp.macaron)
from (
select to_char(sw.opening_date,'YYYYMMDD') as opening_date
from chocolate_shop, macarons_topping, chocolate_dessert
where ... AND dessert_style='초콜릿' AND 날짜범위조건
order by opening_date desc limit 1
) as AAA, chocolate_dessert, macarons_topping, chocolate_shop -- 또 같은 테이블
where ...
UNION ALL
select concat('special at ', tp.macaron, ' please')
from (같은 서브쿼리 또 실행) as AAA ... -- 반복- 카테고리 × 발화 패턴 6개 = 30개의 UNION ALL
- 각 UNION마다 동일한 서브쿼리로 최신 날짜를 독립적으로 조회
- 같은 연산이 30번 반복
개선 포인트
WITH latest_opening_date AS (
SELECT sw.opening_date
FROM chocolate_shop sw
INNER JOIN chocolate_dessert dp ON dp.baking_idx = sw.baking_idx
INNER JOIN macarons_topping tp ON dp.dessert_item_idx = tp.dessert_item_idx
WHERE dp.dessert_style IN ('초콜릿', '다크초콜릿')
AND sw.opening_date BETWEEN '{STARTDATE}' AND '{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 sw ON sw.opening_date = lod.opening_date
...
)
SELECT 'special ' || macaron FROM chocolate_data
UNION ALL
SELECT 'special at ' || macaron || ' please' FROM chocolate_data
...- 30번 실행되던 서브쿼리를 CTE 하나로 통합
- 날짜 조회를 CTE로 한 번만 수행하고 이후에는 이미 만들어진 결과를 참조하도록 함
개선 효과
- 실행 시간 369ms → 62ms (83% 개선)
Query 3) 58.2% 향상 쿼리
bakery_system=
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 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문제점
-- Before: comma join
from (...최신날짜 서브쿼리...) AAA,
bakery_product_recipe as pr,
bakery_topping_recipe as tf,
bakery_batch as bk
where pr.batch_idx = bk.batch_idx
AND to_char(bk.batch_complete_date,'YYYYMMDD') = AAA.batch_complete_date
...
GROUP BY pr.product_recipe_idx날짜 범위 내 최신 날짜를 구하는 서브쿼리와, 그 날짜로 실제 데이터를 조회하는 쿼리가 comma join으로 연결되어서 옵티마이저가 최적 실행 계획을 잡기 어려웠다.
개선 포인트
select DISTINCT pr.product_recipe_idx
from (...최신날짜 서브쿼리...) AAA
INNER JOIN bakery_product_recipe as pr
ON pr.batch_idx IN (
SELECT batch_idx FROM bakery_batch
WHERE to_char(batch_complete_date,'YYYYMMDD') = AAA.batch_complete_date
)
INNER JOIN bakery_batch as bk ON pr.batch_idx = bk.batch_idx
...- GROUP BY를 DISTINCT 한 번으로 줄임
- comma join을 명시적 INNER JOIN으로 변경 → 옵티마이저가 통계 정보를 제대로 활용하도록 함
개선 효과
- 401.969 ms → 167.866 ms (58.2% 향상)
- 데이터 100% 일치
3. 인덱스 추가
자주 조회되는 컬럼과 JOIN 조건에 사용되는 컬럼에 적절한 인덱스를 생성했다.
결과
- 쿼리 실행 시간: 5시간 → 수 분 이내로 단축
- Daily batch 작업이 정상적으로 완료되어 서비스 안정성 회복 및 장애 해결
- 전반적인 DB 서버 부하 감소
교훈
- 슬로우 쿼리는 언젠가 터진다 - 근본적인 쿼리 성능 문제는 반드시 개선해야 한다
- 인덱스 설계는 필수 - 테이블 설계 단계부터 적절한 인덱스를 함께 고려해야 한다
- 쿼리 작성 시 실행 계획 확인 -
EXPLAIN ANALYZE를 통해 쿼리가 인덱스를 제대로 활용하는지 확인하는 습관이 중요하다 - 정기적인 성능 모니터링 - 슬로우 쿼리 및 각 API 실행 시간을 주기적으로 모니터링하고 개선해야 한다
후속 조치
- 이후에 psql 쿼리 모니터링을 켰다(슬로우 쿼리 분석 및 인덱스 활용도를 알기위해)
- 저렇게 바꾼 쿼리들이 인덱스를 똑바로 타고 있는지 모두 Analyze를 찍어보고 ::date를 다소 제거했다(이 부분은 이번에 psql 16으로 버전업하게 되어 다시 체크를 해야할 것 같다…)
- API 실행 로그를 남기도록 Django 설정을 변경했다