DEV Community

Minsun
Minsun

Posted on

How I Cut a 5-Hour Batch Job Down to 5 Minutes with PostgreSQL Query Optimization

#sql #query #tuning #postgres

*All names have been replaced with dessert-related terms for security compliance

Problem Overview

A serious DB server bottleneck occurred during a daily batch job on a service I had taken over just a month prior, leading to a service outage.

Symptoms

  • A specific query ran for 5 hours without returning results
  • Subsequent jobs were stuck in a waiting state, halting the entire batch process
  • All data pipeline processing downstream of that process was also halted
  • The query itself was unchanged from before, but performance had dropped sharply after a server rebuild

pg_stat_activity results 

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
Enter fullscreen mode Exit fullscreen mode

Root Cause of Slow Batch Execution

0. The Snowball Effect of a Server Reset

Shortly after I took over the service, the server was reset by another team member (they moved limits.conf to a different location, breaking the PAM module, making it impossible to access the root account or enter single-user mode).

After the server reset and PostgreSQL restart, I discovered that the buffer cache was gone.

1. Surge in Disk I/O

The following three causes combined to trigger a massive surge in disk I/O.

1-1. BCache Was Gone

The queries were inefficient to begin with, but the accumulated buffer cache / OS cache seemed to have been keeping them within an acceptable execution time.

With no cache, reading from disk instead of memory made the execution time completely unmanageable.

After canceling the failing queries and running EXPLAIN ANALYZE, I found an enormous number of blocks being read from physical disk:

Buffers: shared hit=4110 read=4083
Enter fullscreen mode Exit fullscreen mode

1-2. Excessive Full Table Scans

A triple combination of query-level problems was causing an unnecessarily high number of full table scans:

1. Overuse of unnecessary subqueries

  • Unoptimized multiple subqueries executing repeatedly
    • Query example 1) 5 categories × 6 utterance patterns = 30 UNION ALLs. Each UNION was querying the latest date via the same subquery independently, resulting in the same subquery running 30 times.
    • Query example 2) Correlated subquery hell
      • 3 subqueries inside the SELECT clause referencing outer query columns
      • When the outer query returned 10,000 rows, the inner table was queried 30,000 times individually
  • Each subquery was independently scanning the table

2. Full Table Scan due to missing indexes

  • No indexes set on major tables at all
  • Every query was scanning the entire table — worst-case scenario

3. Query patterns that cannot use indexes

  • to_char() function or ::date casting applied to date columns
  • Even if an index existed, these function applications prevented the index from being used

2. DB Connection Created and Dropped on Every Request

Additionally, this was a Django project. Coming from a Java Spring background, I was surprised to discover that Python Django projects have no DB connection pool by default and create/close a connection for every single operation.

def query_db(query, args=(), one=False):
    cur = db().cursor()              # New connection created every time
    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()           # Connection closed every time
    return (r[0] if r else None) if one else r
Enter fullscreen mode Exit fullscreen mode

Resolution Process

1. Buffer cache was gone 

→ Not fixable
Enter fullscreen mode Exit fullscreen mode

2. Excessive full table scans 

→ Query tuning and index optimization
Enter fullscreen mode Exit fullscreen mode

3. DB connection created and dropped on every request 

→ Modified to maintain a single connection using the singleton pattern, avoiding reconnect/disconnect on every query
Enter fullscreen mode Exit fullscreen mode 
_connection = None

def get_connection():
    global _connection
    if _connection is None or _connection.closed:
        _connection = psycopg2.connect(...)
    return _connection
Enter fullscreen mode Exit fullscreen mode

→ The Django version was too old to apply the Postgres DB connection pool available in newer versions of Django. I also attempted to apply middleware but was reassigned to another project before completing it.

Query Tuning and Index Optimization

To resolve the service outage as the top priority, I began query tuning and index optimization.

Indexes were essential to reducing disk I/O. However, given that data accumulates daily and the volume is large, maintaining indexes would also carry a non-trivial cost.

There were also many commonly used columns across queries, so I wanted to analyze query patterns and arrange columns in order of cardinality to achieve maximum query efficiency with the minimum number of indexes.

So I proceeded with query tuning first before adding indexes.

Afterward, I validated correctness by running test queries against data from 20250804–20250806 and verifying 100% result match.

Key Improvement Points

1. Modified to enable index usage

  • Removed to_char() and ::date casts from date columns
  • Changed to range conditions to allow index scans

2. Scalar subqueries → JOINs

  • Replaced repeated lookups on the same table with a single JOIN
  • Completely eliminated N+1 problems

3. CTE utilization

  • Replaced repeated subqueries with CTEs executed only once
  • Improved code readability and maintainability

4. Appropriate index design

  • Added indexes on frequently queried columns
  • Achieved covering index effect with composite indexes

1. Removing Type Casts on Range Queries

Before: pattern that cannot use index 

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

→

After: pattern that uses index 

WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16'
Enter fullscreen mode Exit fullscreen mode

At first glance, it might seem strange to use a range condition just to enable index usage.

And you might wonder — why was it written with type casting in the first place?

The reason was that created_at was a timestamp type.

PostgreSQL interprets a bare date string like '2025-01-15' as '2025-01-15 00:00:00.000000', so only rows created at exactly midnight would be matched.

To fetch all values up until midnight of the following day while still being able to use the index, the query was changed to a range condition.

2. Subquery Removal and JOIN Optimization

Repeatedly executing subqueries were restructured to drastically reduce query execution count. (Protecting the disk!!)

A total of 7 queries were changed. A few of them are documented below. (Table and column names replaced with desserts per security policy.)

Query 1) 98.30% improvement 

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;
Enter fullscreen mode Exit fullscreen mode

→

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;
Enter fullscreen mode Exit fullscreen mode

Original query execution plan 

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
Enter fullscreen mode Exit fullscreen mode

Optimized query execution plan 

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
Enter fullscreen mode Exit fullscreen mode

Problem 

-- First, pull BATCH_IDX via subquery in FROM clause
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,   -- same table scanned again in outer query
BAKERY_TOPPING_RECIPE AS TF,
BAKERY_BATCH AS BK
Enter fullscreen mode Exit fullscreen mode
  • The same three tables were scanned once inside the subquery and again in the outer query — a double scan structure
  • TO_CHAR() prevented the date index from being used at all

Improvement 

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
Enter fullscreen mode Exit fullscreen mode

Improvements made

  • Simplified the double table scan into a CTE + explicit INNER JOINs
  • Removed type casting so the date comparison can use the index
    • WHERE DW.WORK_COMPLETE_DATE::date = '2025-08-06'::date

Results

  • Response time: 353ms → 6ms (98.30% improvement)
  • Memory efficiency: 69% improvement
  • Disk I/O: 100% eliminated (cache only)
    • 4,083 blocks → 0 blocks
  • Cache usage: 69% reduction
    • 4,110 blocks → 1,262 blocks
  • Index usage: Sequential Scan → Index Scan
  • Data accuracy: 1,200 rows — perfect 100% match

Query 2) 83% improvement

Before 

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='다크초콜릿')
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_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;
Enter fullscreen mode Exit fullscreen mode

→

After 

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;
Enter fullscreen mode Exit fullscreen mode

Problem 

-- Repeated subquery~
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 date_range_condition
    order by opening_date desc limit 1
) as AAA, chocolate_dessert, macarons_topping, chocolate_shop  -- same tables again
where ...
UNION ALL
select concat('special at ', tp.macaron, ' please')
from (same subquery executed again) as AAA ...  -- repeated
Enter fullscreen mode Exit fullscreen mode
  • categories × 6 utterance patterns = 30 UNION ALLs
    • Each UNION independently executed the same subquery to find the latest date
    • The same computation repeated 30 times

Improvement points 

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
...
Enter fullscreen mode Exit fullscreen mode
  • Consolidated the 30-time repeated subquery into a single CTE
  • The date lookup is executed only once as a CTE; subsequent steps reference the already-computed result

Results

  • Execution time: 369ms → 62ms (83% improvement)

Query 3) 58.2% improvement 

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
Enter fullscreen mode Exit fullscreen mode

→

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
Enter fullscreen mode Exit fullscreen mode

Problem 

-- Before: comma join
from (...latest_date_subquery...) 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
Enter fullscreen mode Exit fullscreen mode

The subquery for finding the latest date within the date range and the outer query for fetching the actual data were connected via a comma join, making it difficult for the optimizer to determine an optimal execution plan.

Improvement points 

select DISTINCT pr.product_recipe_idx 
from (...latest_date_subquery...) 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
...
Enter fullscreen mode Exit fullscreen mode
  • Replaced GROUP BY with a single DISTINCT
  • Changed comma join to explicit INNER JOIN → allows the optimizer to properly leverage statistics

Results

  • 401.969ms → 167.866ms (58.2% improvement)
  • 100% data accuracy

3. Adding Indexes

Appropriate indexes were created on frequently queried columns and columns used in JOIN conditions.

Final Results

  • Query execution time: reduced from 5 hours to within a few minutes
  • Daily batch job completed successfully — service stability restored and outage resolved
  • Overall DB server load reduced

Lessons Learned

  1. Slow queries will eventually blow up — fundamental query performance issues must be addressed proactively
  2. Index design is non-negotiable — indexes should be considered from the table design stage
  3. Check execution plans when writing queries — it is essential to habitually use EXPLAIN ANALYZE to verify that queries are properly utilizing indexes
  4. Regular performance monitoring — slow queries and API execution times should be periodically monitored and improved

Follow-up Actions

  • Enabled psql query monitoring afterward (for slow query analysis and index utilization tracking)
  • Ran EXPLAIN ANALYZE on all the rewritten queries to verify index usage, and removed some more ::date casts in other queries (this will need to be re-checked after upgrading to PostgreSQL 16)
  • Modified the Django configuration to log API execution times

Top comments (0)