API 성능 개선 - SQL 튜닝

1. 배경 및 목적

API 성능 개선 - 비동기 연구 이후에 인덱싱 상태 관련 로직이 추가됨.

이후, 해당 API는 호출 시 평균 1.9초 이상의 시간이 소요되어, 서비스 응답 속도에 영향을 주고 있음.

• DB select 작업 : 최대 2.1s (약 75000건)
• Queue send 작업 : 최대 0.23s (약 500개 task)
• Redis set 작업: 최대 0.5s (약 500건)

이 중, DB 조회 작업에서 주요 병목 구간이 발견되었고, 조회되는 데이터가 많아질수록 더 오래 걸릴 것으로 예상됨.

이에 따라, 해당 API의 전반적인 SQL 쿼리 효율성을 검토하고 성능 개선을 시도하였음.

2. 개선 전 성능 현황

• DB select 작업 : 평균 1.03s (약 41000건)
• API 실행 시간 : 평균 2.165s (약 41000건)

3. 최종 성능 개선 결과

• DB select 작업 : 1.03s → 0.73s (약 41000건)
• API 실행 시간: 2.165s → 1.74s (약 41000건)
• 개선 폭: 약 30% ↓
• 주요 개선 포인트 : DB 인덱스 설정 (기여도 60%), 쿼리 튜닝 (기여도 40%)
• 조회되는 데이터 수가 많을수록 더욱 개선될 것으로 예상됨

4. 코드 배경

문서 종류(faq, word, board) 테이블마다 각각 select, update, delete된 아이템을 구하는 3개의 쿼리, 총 9번의 대용량 데이터 조회 쿼리가 발생함.

따라서 Table(..., autoload_with=engine) 을 사용해 문서 종류에 대한 유연성을 높이고, 쿼리 재사용성을 높임.

for table_name in ["faq", "word", "board"]:    # 예시입니다. 총 9번 쿼리
	def select_insert_items(table_name: str):
	   table = Table(table_name, ..., autoload_with=engine)
	   
	def select_update_items(table_name: str):
	   table = Table(table_name, ..., autoload_with=engine)
	   
	def select_delete_items(table_name: str):
	   table = Table(table_name, ..., autoload_with=engine)

5. 성능 개선 방법 - def select_insert_items() 최적화

5.1 DB 인덱스 설정

자세한 내용은 3. 인덱스 적용 사례 참고

성능 변화

• planning time: 0.616ms → 0.249ms
• execution time: 103.073ms → 72.571
• Parallel Seq Scan on document_item_faq d → Index Only Scan using idx_second_query on document_item_faq d2

5.2 쿼리 튜닝

코드

기존 코드 (코드 설명 포함)

table1 = is_indexed가 True가 없는 uuid만 조회

ranked_rows = uuid별로 가장 최신의 아이템 조회

metadata = MetaData()
table = Table(enum_doc_table_name, metadata, autoload_with=engine)

table1 = (
    select(table.c[document_uuid_column_name].label("document_uuid"))
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
        )
    )
    .group_by(table.c[document_uuid_column_name])
    .having(
        func.count(
            case((table.c[document_is_indexed_column_name] == True, 1))
        )
        == 0
    )
    .subquery()
)

latest_items_subq = (
    select(
        table.c["doc_uid"].label("document_uid"),
        table.c[enum_doc_uid_column].label("item_uid"),
        table.c[document_uuid_column_name].label("item_uuid"),
    )
    .distinct(table.c[document_uuid_column_name])
    .join(
        table1,
        table.c[document_uuid_column_name] == table1.c["document_uuid"],
    )
    .where(table.c[document_deleted_at_column_name].is_(None))
    .order_by(
        table.c[document_uuid_column_name],
        desc(table.c[document_updated_at_column_name]),
    )
)
return session.execute(latest_items_subq).all()

 

기존 코드 SQL

EXPLAIN ANALYZE
SELECT DISTINCT ON (d.docitmfaq_uuid)
    d.doc_uid AS document_uid,
    d.docitmfaq_uid AS item_uid,
    d.docitmfaq_uuid AS item_uuid
FROM public.document_item_faq d
JOIN (
    SELECT d2.docitmfaq_uuid
    FROM public.document_item_faq d2
    WHERE d2.doc_uid IN (393,394,414,415,416,417,418,419,496,497) 
      AND d2.docitmfaq_deleted_at IS NULL
    GROUP BY d2.docitmfaq_uuid
    HAVING COUNT(CASE WHEN d2.docitmfaq_is_indexed = TRUE THEN 1 ELSE NULL END) = 0
) AS unindexed ON d.docitmfaq_uuid = unindexed.docitmfaq_uuid
WHERE d.docitmfaq_deleted_at IS NULL
  AND d.doc_uid IN (393,394,414,415,416,417,418,419,496,497) 
ORDER BY d.docitmfaq_uuid, d.docitmfaq_updated_at DESC;

 

후보 코드1 (코드 설명)

indexed_uuid_subq = 해당 코드는 is_indexed가 True인 컬럼을 뽑아내고 그걸 제외한 uuid를 조회

ranked_rows = uuid별로 가장 최신의 아이템 조회

metadata = MetaData()
table = Table(enum_doc_table_name, metadata, autoload_with=engine)

indexed_uuid_subq = (
    select(table.c[document_uuid_column_name])
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_is_indexed_column_name] == True,
            table.c[document_deleted_at_column_name].is_(None),
        )
    )
    .distinct()
    .subquery()
)

ranked_rows = (
    select(
        table.c["doc_uid"].label("document_uid"),
        table.c[enum_doc_uid_column].label("item_uid"),
        table.c[document_uuid_column_name].label("item_uuid"),
        func.row_number()
        .over(
            partition_by=table.c[document_uuid_column_name],
            order_by=desc(table.c[document_updated_at_column_name]),
        )
        .label("rnum"),
    )
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
            ~table.c[document_uuid_column_name].in_(
                select(indexed_uuid_subq.c[document_uuid_column_name])
            ),
        )
    )
    .subquery()
)

final_query = select(
    ranked_rows.c["document_uid"],
    ranked_rows.c["item_uid"],
    ranked_rows.c["item_uuid"],
).where(ranked_rows.c["rnum"] == 1)

return session.execute(final_query).all()

 

후보 코드1 SQL

EXPLAIN ANALYZE
SELECT *
FROM (
    SELECT
        d.doc_uid AS document_uid,
        d.docitmfaq_uid AS item_uid,
        d.docitmfaq_uuid AS item_uuid,
        ROW_NUMBER() OVER (
            PARTITION BY d.docitmfaq_uuid
            ORDER BY d.docitmfaq_updated_at DESC
        ) AS rnum
    FROM public.document_item_faq d
    WHERE d.doc_uid IN (393,394,414,415,416,417,418,419,496,497) 
      AND d.docitmfaq_deleted_at IS NULL
      AND d.docitmfaq_uuid NOT IN (
          SELECT d2.docitmfaq_uuid
          FROM public.document_item_faq d2
          WHERE d2.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
            AND d2.docitmfaq_is_indexed = TRUE
            AND d2.docitmfaq_deleted_at IS NULL
      )
) sub
WHERE sub.rnum = 1;

 

후보 코드2 (코드 설명)

table1 = is_indexed가 True가 없는 uuid만 조회

ranked_rows = uuid별로 가장 최신의 아이템 조회

가독성 높은 기존 코드의 group by와 후보 코드1의 Row_number를 섞은 쿼리이다.

table1 = (
    select(table.c[document_uuid_column_name].label("document_uuid"))
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
        )
    )
    .group_by(table.c[document_uuid_column_name])
    .having(
        func.count(
            case((table.c[document_is_indexed_column_name] == True, 1))
        ) == 0
    )
    .subquery()
)

ranked_rows = (
    select(
        table.c["doc_uid"].label("document_uid"),
        table.c[enum_doc_uid_column].label("item_uid"),
        table.c[document_uuid_column_name].label("item_uuid"),
        func.row_number().over(
            partition_by=table.c[document_uuid_column_name],
            order_by=desc(table.c[document_updated_at_column_name]),
        ).label("rnum")
    )
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
            table.c[document_uuid_column_name].in_(
                select(table1.c["document_uuid"])
            ),
        )
    )
).subquery()

final_query = select(
    ranked_rows.c["document_uid"],
    ranked_rows.c["item_uid"],
    ranked_rows.c["item_uuid"],
).where(ranked_rows.c["rnum"] == 1)

 

후보 코드2 SQL

EXPLAIN ANALYZE
WITH unindexed_uuids AS (
    SELECT d.docitmfaq_uuid
    FROM public.document_item_faq d
    WHERE d.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
      AND d.docitmfaq_deleted_at IS NULL
    GROUP BY d.docitmfaq_uuid
    HAVING COUNT(CASE WHEN d.docitmfaq_is_indexed = TRUE THEN 1 END) = 0
),
ranked_docs AS (
    SELECT
        d.doc_uid AS document_uid,
        d.docitmfaq_uid AS item_uid,
        d.docitmfaq_uuid AS item_uuid,
        ROW_NUMBER() OVER (
            PARTITION BY d.docitmfaq_uuid
            ORDER BY d.docitmfaq_updated_at DESC
        ) AS rnum
    FROM public.document_item_faq d
    WHERE d.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
      AND d.docitmfaq_deleted_at IS NULL
      AND d.docitmfaq_uuid IN (SELECT docitmfaq_uuid FROM unindexed_uuids)
)
SELECT document_uid, item_uid, item_uuid
FROM ranked_docs
WHERE rnum = 1;

 

 

쿼리 비교시 비교 사항

실행 계획	EXPLAIN 또는 EXPLAIN ANALYZE로 어느 쿼리가 더 빠른지 확인
인덱스 유무	어떤 컬럼에 인덱스가 있는가?
반환 row 수	두 쿼리의 결과가 정확히 일치하는가?
캐시 여부	같은 쿼리를 반복 실행하면 캐시가 개입되어 왜곡될 수 있음
병렬 실행 여부	일부 DB는 GROUP BY에 병렬 실행 사용함

 

결과

후보	구조적 특징	성능 순위	핵심 연산	특징	Planning Time & Execution Time	인덱스 활용
기존코드	JOIN + DISTINCT ON	2위	정렬 기반 최신 row 선택	- 서브 쿼리 대신 join을 사용해 더 많은 최적화 여지가 있음 - 가독성 좋음	0.678ms & 73.882ms	Bitmap heap scan Bitmap index scan
후보코드1 (선정)	NOT IN + ROW_NUMBER()	1위 ✅	윈도우 함수 기반 최신 row 선택	- row_number를 이용해 DB에 부하↓	0.496ms & 53.376ms	Bitmap heap scan Bitmap index scan Index only scan
후보코드2	IN + ROW_NUMBER()	3위 ❌	윈도우 함수 + 필터링	- 서브쿼리와 row_number를 사용함(기존코드와 후보코드1 혼합)	0.794ms & 84.194ms	Bitmap heap scan Bitmap index scan Bitmap heap scan Bitmap index scan

각 쿼리에 맞게 적절히 인덱스를 섞은 결과, 후보코드1에서 인덱스와의 성능이 좋았고 인덱스를 잘 타는 모습을 보임

기존 코드와 후보코드1 비교 결과

row_number는 인덱스를 잘 사용할시, 성능이 매우 좋아진다.

• row_number와 인덱스를 적절히 섞으면 성능이 압도적으로 좋아진다. 하지만 그와 반대로 row_number에 인덱스가 없을시, 기존 쿼리보다 느려진다.

기존 코드와 후보코드2 비교 결과

서브쿼리보다 join 성능이 더 좋은 경우가 더 많다.

• JOIN은 옵티마이저가 더 유연하게 계획을 세울 수 있음
• 서브쿼리는 중간 결과를 임시로 저장(MATERIALIZE: 중간테이블처럼 생성)하는 비용이 발생할 수 있음
  • materialized result가 메모리에 다 안 올라가면 디스크 I/O가 발생
• 서브쿼리는 인덱스 스캔 최적화가 어려움
• 서브 쿼리가 더 좋은 경우도 있음.상황 서브쿼리 추천 이유

  상황	서브쿼리 추천 이유
  서브쿼리 결과가 매우 작고 고정적일 때	옵티마이저가 먼저 materialize해서 빠르게 비교 가능
  서브쿼리를 EXISTS 또는 LIMIT 1 과 함께 사용하는 경우	내부 결과가 1개만 필요해서 빨리 종료 가능
  JOIN이 불필요한 중복 row를 많이 만들 경우	JOIN하면 row 수가 n배가 되는데, 서브쿼리는 그러지 않음

6. 성능 개선 방법 - def select_update_items() 최적화

6.1 쿼리 튜닝

코드

기존코드

metadata = MetaData()
table = Table(enum_doc_table_name, metadata, autoload_with=engine)

table1 = (
    select(table.c[document_uuid_column_name].label("document_uuid"))
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
        )
    )
    .group_by(table.c[document_uuid_column_name])
    .having(
        func.count(
            case((table.c[document_is_indexed_column_name] == True, 1))
        )
        >= 1
    )
    .subquery()
)

earliest_items_subq = (
    select(table)
    .distinct(table.c[document_uuid_column_name])
    .join(
        table1,
        table.c[document_uuid_column_name] == table1.c["document_uuid"],
    )
    .where(table.c[document_deleted_at_column_name].is_(None))
    .order_by(
        table.c[document_uuid_column_name],
        asc(table.c[document_updated_at_column_name]),
    )
    .subquery()
)

latest_items_subq = (
    select(table)
    .distinct(table.c[document_uuid_column_name])
    .join(
        table1,
        table.c[document_uuid_column_name] == table1.c["document_uuid"],
    )
    .where(table.c[document_deleted_at_column_name].is_(None))
    .order_by(
        table.c[document_uuid_column_name],
        desc(table.c[document_updated_at_column_name]),
    )
    .subquery()
)

stmt = select(
    earliest_items_subq.c["doc_uid"].label("doc_uid"),
    earliest_items_subq.c[enum_doc_uid_column].label("earliest_uid"),
    earliest_items_subq.c[document_uuid_column_name].label("earliest_uuid"),
    latest_items_subq.c[enum_doc_uid_column].label("latest_uid"),
    latest_items_subq.c[document_uuid_column_name].label("latest_uuid"),
).join(
    latest_items_subq,
    and_(
        earliest_items_subq.c[enum_doc_uid_column]
        != latest_items_subq.c[enum_doc_uid_column],
        earliest_items_subq.c[document_uuid_column_name]
        == latest_items_subq.c[document_uuid_column_name],
    ),
)

 

기존코드 SQL

EXPLAIN ANALYZE
WITH table1 AS (
    SELECT docitmfaq_uuid AS document_uuid
    FROM document_item_faq
    WHERE doc_uid IN (393,394,414,415,416,417,418,419,496,497)
      AND docitmfaq_deleted_at IS NULL
    GROUP BY docitmfaq_uuid
    HAVING COUNT(CASE WHEN docitmfaq_is_indexed = TRUE THEN 1 END) >= 1
),

earliest_items_subq AS (
    SELECT DISTINCT ON (docitmfaq_uuid) *
    FROM document_item_faq
    JOIN table1 ON document_item_faq.docitmfaq_uuid = table1.document_uuid
    WHERE docitmfaq_deleted_at IS NULL
    ORDER BY docitmfaq_uuid, docitmfaq_updated_at ASC
),

latest_items_subq AS (
    SELECT DISTINCT ON (docitmfaq_uuid) *
    FROM document_item_faq
    JOIN table1 ON document_item_faq.docitmfaq_uuid = table1.document_uuid
    WHERE docitmfaq_deleted_at IS NULL
    ORDER BY docitmfaq_uuid, docitmfaq_updated_at DESC
)

SELECT 
    earliest_items_subq.doc_uid AS doc_uid,
    earliest_items_subq.docitmfaq_uid AS earliest_uid,
    earliest_items_subq.docitmfaq_uuid AS earliest_uuid,
    latest_items_subq.docitmfaq_uid AS latest_uid,
    latest_items_subq.docitmfaq_uuid AS latest_uuid
FROM earliest_items_subq
JOIN latest_items_subq
  ON earliest_items_subq.docitmfaq_uuid = latest_items_subq.docitmfaq_uuid
 AND earliest_items_subq.docitmfaq_uid != latest_items_subq.docitmfaq_uid;

 

후보코드1

uuid_subquery = (
    select(document_item_faq.c.docitmfaq_uuid)
    .where(
        and_(
            document_item_faq.c.doc_uid.in_(document_uids),
            document_item_faq.c.docitmfaq_deleted_at.is_(None),
        )
    )
    .group_by(document_item_faq.c.docitmfaq_uuid)
    .having(
        func.count(
            case((document_item_faq.c.docitmfaq_is_indexed == True, 1))
        ) == 1
    )
)

ranked_subquery = (
    select(
        document_item_faq.c.doc_uid,
        document_item_faq.c.docitmfaq_uuid,
        over(
            first_value(document_item_faq.c.docitmfaq_uid),
            partition_by=document_item_faq.c.docitmfaq_uuid,
            order_by=asc(document_item_faq.c.docitmfaq_updated_at)
        ).label("earliest_uid"),
        over(
            first_value(document_item_faq.c.docitmfaq_uid),
            partition_by=document_item_faq.c.docitmfaq_uuid,
            order_by=desc(document_item_faq.c.docitmfaq_updated_at)
        ).label("latest_uid"),
        over(
            func.row_number(),
            partition_by=document_item_faq.c.docitmfaq_uuid,
            order_by=asc(document_item_faq.c.docitmfaq_updated_at)
        ).label("rnum")
    )
    .where(
        and_(
            document_item_faq.c.doc_uid.in_(document_uids),
            document_item_faq.c.docitmfaq_deleted_at.is_(None),
            document_item_faq.c.docitmfaq_uuid.in_(uuid_subquery)
        )
    )
).subquery("ranked")

final_stmt = (
    select(
        ranked_subquery.c.doc_uid,
        ranked_subquery.c.earliest_uid,
        ranked_subquery.c.docitmfaq_uuid.label("earliest_uuid"),
        ranked_subquery.c.latest_uid,
        ranked_subquery.c.docitmfaq_uuid.label("latest_uuid"),
    )
    .where(ranked_subquery.c.rnum == 1)
)

 

후보코드 SQL

EXPLAIN ANALYZE
SELECT
    ranked.doc_uid,
    ranked.earliest_uid,
    ranked.docitmfaq_uuid AS earliest_uuid,
    ranked.latest_uid,
    ranked.docitmfaq_uuid AS latest_uuid
FROM (SELECT
        d.doc_uid,
        d.docitmfaq_uuid,
        FIRST_VALUE(d.docitmfaq_uid) OVER (
            PARTITION BY d.docitmfaq_uuid
            ORDER BY d.docitmfaq_updated_at ASC
        ) AS earliest_uid,
        FIRST_VALUE(d.docitmfaq_uid) OVER (
            PARTITION BY d.docitmfaq_uuid
            ORDER BY d.docitmfaq_updated_at DESC
        ) AS latest_uid,
        ROW_NUMBER() OVER (
            PARTITION BY d.docitmfaq_uuid
            ORDER BY d.docitmfaq_updated_at ASC
        ) AS rnum
    FROM public.document_item_faq d
    WHERE d.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
      AND d.docitmfaq_deleted_at IS NULL
      AND d.docitmfaq_uuid IN (
          SELECT d.docitmfaq_uuid
				  FROM public.document_item_faq d
				  WHERE d.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
			      	AND d.docitmfaq_deleted_at IS NULL
				  GROUP BY d.docitmfaq_uuid
				  HAVING COUNT(CASE WHEN d.docitmfaq_is_indexed = TRUE THEN 1 END) = 1
		      )
		 ) as ranked
WHERE ranked.rnum = 1;

 

결과

후보	구조적 특징	성능 순위	핵심 연산	특징	Planning Time & Execution Time	인덱스 활용
기존코드	JOIN + DISTINCT ON	2위	정렬 기반 최신 row 선택	- CTE는 기본적으로 인라인 최적화가 안 됨 - 조인 시 중간 결과 폭증 위험 - 전체 테이블 최소 3회 이상 스캔 (table1, earliest_items_subq, latest_items_subq 조회마다) - 가독성은 좋음	0.433ms &177.303ms	CTE Seq Scan(full scan) CTE Seq Scan(full scan)
후보코드1 (선정)	IN + ROW_NUMBER() + FIRST_VALUE	1위 ✅	윈도우 함수 기반 단일 스캔	- ROW_NUMBER를 활용해 UUID당 한 row만 선택 - JOIN, 중복 연산 없음 → 효율적 - 전체 테이블 1회 스캔	0.486ms & 25.217ms	Bitmap heap scan Bitmap index scan Bitmap heap scan Bitmap index scan

기존코드는 마지막 select에서 Join으로 uid가 같은 row들이 발생함. → 불필요한 row를 많이 만드므로 서브쿼리가 더 나을 수 있다.

또한 table1, earliest_items_subq, latest_items_subq 조회마다 from table로 전체 테이블을 스캔하며 WITH 구문 (CTE)은 기본적으로 인라인 최적화가 안 된다. (CTE 내부가 무겁거나 큰 결과라면 임시 저장/읽기 비용이 ↑)

따라서 후보 코드에서는 인라인 조건으로 만들고 전체 테이블 스캔 횟수를 줄이기 위해 윈도우 함수를 사용했다.

 

7. 성능 개선 방법 - def select_delete_items() 최적화

코드

기존코드 SQL

SELECT DISTINCT ON (docitmfaq_uuid)
     doc_uid AS document_uid,
     docitmfaq_uid AS item_uid,
     docitmfaq_uuid
FROM document_item_faq
WHERE
    doc_uid IN (393,394,414,415,416,417,418,419,496,497)
    AND docitmfaq_deleted_at IS NOT NULL
ORDER BY
    docitmfaq_uuid,
    docitmfaq_updated_at DESC;

후보코드 SQL

SELECT
    document_uid,
    item_uid,
    item_uuid
FROM (
    SELECT
        doc_uid AS document_uid,
        docitmfaq_uid AS item_uid,
        docitmfaq_uuid AS item_uuid,
        ROW_NUMBER() OVER (
            PARTITION BY docitmfaq_uuid
            ORDER BY docitmfaq_updated_at DESC
	      ) AS rn
    FROM document_item_faq
    WHERE
        doc_uid IN (393,394,414,415,416,417,418,419,496,497)
        AND docitmfaq_deleted_at IS NOT NULL
) as  rnn
WHERE rnn.rn = 1;

 

결과

두 쿼리 모두 간단한 편이기 때문에 2만건 조회에도 큰 차이가 발생하지 않았음.

조금이나마 차이가 있다면 서브쿼리가 없는 기존 코드에서 5초 정도 속도가 빨랐음.

후보 코드 채택 ❌

 

 

 

 

 

 

참고

https://junhkang.com/posts/5/

https://velog.io/@sweet_sumin/클러스터드-인덱스-Clustered-Index-넌-클러스터드-인덱스-Non-Clustered-Index

https://digitalbourgeois.tistory.com/1311