DEV Community: Minsun

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

Minsun — Fri, 20 Mar 2026 01:34:05 +0000

*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

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

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

Resolution Process

1. Buffer cache was gone

→ Not fixable

2. Excessive full table scans

→ Query tuning and index optimization

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

_connection = None

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

→ 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'

→

After: pattern that uses index

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

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;

→

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;

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

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

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

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

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;

→

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;

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

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
...

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

→

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

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

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
...

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

Slow queries will eventually blow up — fundamental query performance issues must be addressed proactively
Index design is non-negotiable — indexes should be considered from the table design stage
Check execution plans when writing queries — it is essential to habitually use EXPLAIN ANALYZE to verify that queries are properly utilizing indexes
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

2024 Retrospective: Learning How to Grow on Purpose

Minsun — Mon, 02 Feb 2026 02:37:18 +0000

Summary

The Transition: Balancing a Mechanical Engineering major with Software Engineering, managing the massive workload of two demanding disciplines.
Rapid Adaptation: Restarted algorithm practice in February; passed the coding test for Hana TI (Hana Bank’s IT subsidiary) in March and secured my current role at KT DS(Korea Telecom’s IT subsidiary) by April.
The Learning Curve: From receiving blunt peer feedback after crying in my first interview to navigating GM, AWS, and high-growth startups while starting a new job.
Strategic Choice: Staying at my current KT subsidiary role to leverage enterprise-scale AI development and a substantial compensation gap for technical compounding.

A Year of Expanding Horizons

2024 was the year I took the first real step into my career. For the first time in my life, I committed to the job-seeking process. After graduation, I had spent time avoiding the pressure of the market, but in February, I decided to face it head-on.

The momentum was unexpected. I picked up algorithm practice again in February. By March, I passed the coding test for Hana TI(Hana Bank’s IT subsidiary), and by April, I passed the test for my current company, KT DS(Korea Telecom’s IT subsidiary). This period was a crash course in how different organizations evaluate engineers.

The First Interview: Tears and Lessons at Hana TI

My first-ever interview was at Hana TI (Hana Bank’s IT subsidiary). I arrived at their training center in Cheongra, feeling incredibly nervous. Everyone was in full suits, and I could sense a high level of competition.

When it was my turn, my voice shook during the formal greetings. My mind went blank, and I couldn't even introduce my project properly. But what really broke me was the gap between me and the other candidates. They were all talking about their prestigious external experiences—Naver Boostcamp, Kakao Bootcamp, Samsung Bootcamp(SSAFY).

Compared to them, I felt like a complete amateur. I had nothing but my college graduation on my resume; no fancy boot camps, no high-profile external activities. I was so intimidated and felt so small that during the final remark, I actually started crying.

The interviewers were so kind that they even apologized, asking me, "Was this too much of a stress interview for you?" I felt so embarrassed because the atmosphere wasn't stressful at all; it was my own lack of confidence that overwhelmed me. After the interview, I exchanged numbers with another candidate for the feedback. Their feedback was blunt: "Your technical answers were logical, but you didn't need to apologize for your lack of experience." That day taught me to stop apologizing for my path and start owning my logic.

The Small SI: "We Need an Interpreter"

Shortly after, a small SI firm contacted me for my English scores. They proposed a hybrid role: backend development combined with interpretation for their Indian developers. I told them I didn't want to interpret, so they set up a developer interview instead. They offered me 28 million KRW ($21k). I declined, but getting a final offer helped rebuild the self-esteem I had lost in Cheongra.

Joining the Platform Division at KT DS

the process was a grueling marathon: Document Screening → Personality Test + Aptitude Test + Coding Test → PT Task Preparation → PT & Technical Interview → Health Check → Executive Interview.

The coding test and aptitude test were the first hurdles. The aptitude test was particularly demanding, featuring complex problem-solving taskst. For the PT interview, I had to create a presentation on a given topic in just one hour without any outside resources, all while being monitored on camera. I studied twelve potential topics in advance, and after the task, I practiced my five-minute speech until I had the script completely memorized.

In the first interview, I was so determined not to cry again. When the interviewers asked for a final remark, I blurted out, "I won't be giving a final remark because I cried while giving one in my last interview." The interviewers actually burst into laughter at my blunt honesty. Despite my voice shaking like a goat throughout the technical questions, I passed. For the executive round, I even stayed at a hotel near the site to ensure I was in peak condition. Despite my voice shaking like a goat, I passed. For the executive round, I even stayed at a hotel near the site to ensure I was in peak condition.

Then came the most anxiety-inducing period: The Placement. During the entire onboarding process, I was terrified. I constantly prayed that I wouldn't be sent to a team doing only legacy maintenance or, worse, be dispatched to a remote third-party client site as an SI developer. I wanted to build something modern; I wanted to grow.

My prayers were answered. I was assigned to the AI Team under the AI Department of the Platform Division. I am now part of the team developing AI Contact Centers (AICC)—including chatbots and voicebots—and backend systems for AI Speakers. Because we are a KT subsidiary, this is effectively in-house development for massive infrastructure.

General Motors (GM) - Infotainment Backend

While I was going through the final stages for KT DS, I was also in the middle of the General Motors (GM) recruitment process. I passed the phone screening, the coding test, and the AI English interview for their infotainment backend role. However, the final executive round in July collided with my onboarding orientation at KT DS. I was too worried about missing orientation and didn't think to ask for flexibility. In hindsight, I was following the rules too strictly. Today, I would use my leave to protect that high-leverage moment.

General Motors (GM) - Infotainment Backend

AWS Assessment Experience: The Role-Playing Simulation

AWS used a different filter. I received a coding assessment followed by behavioral simulations modeled after real production incidents.

The algorithm portion had three problems. I solved two but ran out of time on the last one, which required Binary Search. I took the test on a weekday after a 90-minute commute, feeling absolutely exhausted. The behavioral section—making decisions about debugging outages under pressure—was the most interesting part. It changed how I prepare for high-stakes interviews. I learned to treat execution conditions—like energy and environment—as part of the preparation itself. Failure, when read carefully, is just data.

What stood out was the behavioral simulation. It felt like a role-playing game where I had to choose options to debug errors encountered during work. It mirrored the reality of production incidents far more than standard coding tests.

Startup Interview #1: Red Flags and the Practical Gap

I had applied to this startup before joining my current company. I passed their coding test first, and the interview was scheduled right during my onboarding period at KT DS.

This company raised several red flags: the pay was extremely low, and after researching their financial status, I found they were in a very unstable position with high job insecurity. During the technical round, they asked why I solved a specific coding problem the way I did. I answered, "In my experience, PriorityQueue was always faster, so I used it." They immediately followed up: "Why is it fast? How is it implemented internally?" I couldn't answer. I knew it used a Heap, but I couldn't explain the mechanics under pressure. Even though the company itself wasn't the right fit, that moment was a wake-up call. I realized I was just "writing code" without knowing the "why."

Startup Interview #2: Reasoning Over Rote Memory

The startup pipelines became the intellectual center of my year. The most notable was an interview with an industry-leading startup (#1 in its sector) with over 1 million MAU. While the coding tests were manageable, the interview itself shifted from practical application to fundamental principles.

When the topic of Garbage Collection (GC) came up, the CTO asked a striking question: "How do you think the system actually knows that an object is alive?"

Since I didn’t have the textbook answer, she encouraged me to deduce it. I reasoned that objects might maintain a list of what is currently using them—if that list isn't empty, the object is alive. She challenged this, pointing out the massive inefficiency. I pivoted my logic and proposed Reference Counting: incrementing a counter when a reference is created and decrementing it when one is destroyed. If the count hits zero, the object is dead.

She nodded and said, "There are indeed systems that work that way." Throughout the discussion, she observed my thinking process and led me toward conclusions through subtle hints. It was exhilarating to be pushed to reason from first principles rather than just reciting from memory. I was deeply impressed by a culture that valued raw deduction over rote recall.

The Offer and the Hardest Calculation

I later heard that both the CTO and the Backend Lead were very impressed with my performance. However, when the offer arrived, it sparked a week of intense agony. Because of my financial situation, I had a firm baseline, and the offer was several million KRW below that.

The financial gap was substantial—nearly 18 million KRW (approx. $13,500) per year from current salary. But beyond the money, I was paralyzed by a much darker fear. I was battling severe imposter syndrome, fueled by the fact that I had to split my focus between Mechanical Engineering and Software Engineering for years.

I was terrified. I kept thinking: "If I leave this 'greenhouse' now, and the startup fails or I get fired because I can't keep up, will I even be able to get another job?" At that moment, I genuinely believed that my skills weren't good enough to pass another interview at a different company. I felt like my current employment was a stroke of luck that might not happen twice. The thought of being cast out into a freezing job market without a safety net was bone-chilling.

My family, seeing me struggle from the bottom up—from Liberal Arts to Mechanical Engineering, and then to Software Engineering—shared this fear. They were finally relieved to see me in a stable "open recruitment" role and were terrified I would "throw it all away" only to be crushed by the reality of a global recession. Every morning, the links to recession news in our family group chat felt like a warning of the doom awaiting me if I made the wrong choice.
Ultimately, my anxiety won over my excitement. I made the strategic decision to stay. Not to hide, but to build the inner strength and technical depth—mastering Effective Java, SQL Tuning, and System Architecture—until I no longer fear the wild. I chose to wait until my confidence is rooted in real mastery, not just luck.

2025: Forward, Deliberately

2024 was about friction and diagnosis.

Hana TI taught me to stop apologizing and show my logic.
GM & AWS taught me to protect my energy and timing.
The Startups taught me how to think from first principles.
KT DS taught me the value of in-house enterprise AI development.

I stayed in my greenhouse to grow roots strong enough for the next leap. 2025 will be about building the depth to match my ambitions.

@Slf4j = Facade Pattern + Service Locator Pattern

Minsun — Sun, 26 Jan 2025 02:37:42 +0000

For months, I initialized loggers at class level:
Logger logger = Logger.getLogger(CurrentClass.getName());

Then a coworker introduced me to @Slf4j, simplifying logging to just annotations and direct log.info/debug calls.

What is SLF4J?

Simple Logging Facade for Java implements the Facade pattern - providing a unified interface to complex subsystems, like a building's facade concealing internal complexity.

Example usage:

@Slf4j
public class MyService {
    public void doSomething() {
        log.info("Hello, SLF4J!");
    }
}

Why SLF4J?

Multiple logging options exist:

1.Log4j:

import org.apache.log4j.Logger;
private static final Logger logger = Logger.getLogger(MyClass.class);

2.java.util.logging:

import java.util.logging.Logger;
private static final Logger logger = Logger.getLogger(MyClass.class.getName());

3.Logback:

import ch.qos.logback.classic.Logger;
import org.slf4j.LoggerFactory;
private static final Logger logger = (Logger) LoggerFactory.getLogger(MyClass.class);

SLF4J requires only dependency changes, not code modifications, reducing coupling between application and logging implementations.

How SLF4J Works

SLF4J scans classpath for StaticLoggerBinder:

private static void bind() {
    try {
        Class.forName("org.slf4j.impl.StaticLoggerBinder");
    } catch (ClassNotFoundException e) {
        throw new NoClassDefFoundError("No logging implementation found");
    }
}

Binds implementation:

public class StaticLoggerBinder implements LoggerFactoryBinder {
    private static final StaticLoggerBinder SINGLETON = new StaticLoggerBinder();
    private LoggerFactory loggerFactory;

    private StaticLoggerBinder() {
        loggerFactory = new LogbackLoggerFactory();
    }
}

Service Locator Pattern Connection

While studying IoC alternatives to DI, I noticed similarities with the Service Locator pattern:

// Service Locator
public class ServiceLocator {
    private static Map<String, Service> services = new HashMap<>();
    public static Service getService(String name) {
        return services.get(name);
    }
}

// SLF4J's Similar Approach
public class LoggerFactory {
    public static Logger getLogger(Class<?> clazz) {
        return findPlatformLoggerBinder().getLogger(clazz);
    }
}

SLF4J combines both patterns:

Facade: Simplifies logging interface
Service Locator: Dynamically binds implementations

This dual-pattern approach enables flexible runtime binding while maintaining clean interfaces.