C++ 앱의 DB 쿼리 최적화: 인덱스 선택, 실행 계획, 통계 기반 비용 모델

들어가며: “프로덕션에서 쿼리가 3초 걸려요”

왜 쿼리 최적화인가

C++로 만든 REST API 서버에서 사용자 목록 조회가 수 초씩 걸리고, 트래픽이 늘면 DB CPU가 먼저 포화되는 상황을 가정해 봅니다. 쿼리 최적화는 이런 병목을 찾아 인덱스·실행 계획·통계·비용 모델을 활용해 해결하는 과정입니다. 예제는 PostgreSQL(libpq)과 SQLite를 기준으로 합니다. 관련 글: 데이터베이스 쿼리 최적화 #51-8, 데이터베이스 기초.

---

느린 API·풀 스캔·N+1: 쿼리가 느려지는 상황

상황: GET /users API가 1000명 조회 시 3.2초가 걸립니다. 각 사용자별로 주문을 추가 조회하는 N+1 패턴이 원인입니다.

// ❌ 문제: 1 + 1000 = 1001번 쿼리
void get_users_with_orders(PGconn* conn) {
    PGresult* users = PQexec(conn, "SELECT id, name FROM users");
    for (int i = 0; i < PQntuples(users); ++i) {
        int id = atoi(PQgetvalue(users, i, 0));
        PGresult* orders = PQexecParams(conn,
            "SELECT * FROM orders WHERE user_id = $1", 1, nullptr,
            params, nullptr, nullptr, 0);  // params: id 문자열을 담은 const char* 배열
        // ... 처리 ...
        PQclear(orders);
    }
    PQclear(users);
}

원인: 루프 안에서 사용자마다 별도 쿼리를 실행합니다. 쿼리 하나가 왕복·파싱·실행을 합쳐 3ms라면, 1000명이면 그것만으로 3초입니다. 해결: JOIN 또는 IN 배치 쿼리로 1~2번으로 줄입니다.

---

상황: orders 테이블의 status 컬럼에 인덱스가 있는데, EXPLAIN 결과 Seq Scan이 나옵니다.

-- 쿼리
SELECT * FROM orders WHERE LOWER(status) = 'pending';

주의사항: 대소문자 구분 규칙(Collation)이 바뀌면 함수 인덱스와 결과가 어긋날 수 있으니 마이그레이션 시 함께 검증하세요. 원인: LOWER(status)처럼 함수를 컬럼에 적용하면 인덱스가 사용되지 않습니다. 옵티마이저는 status의 원본 값으로 인덱스를 탐색할 수 없기 때문입니다. 해결: 함수 적용을 제거하거나, 함수 기반 인덱스 생성.

-- ✅ 함수 기반 인덱스 (PostgreSQL)
CREATE INDEX idx_orders_status_lower ON orders (LOWER(status));

---

상황: 테이블에 대량의 행이 추가되었는데, 옵티마이저는 여전히 예전 행 수로 추정해 Nested Loop 조인을 선택합니다. 실제 행 수로는 Hash Join이 더 빠릅니다. 원인: 플래너가 쓰는 통계(pg_class.reltuples, pg_stats의 컬럼 분포)가 갱신되지 않았습니다. autovacuum이 ANALYZE를 돌리기 전에 대량 적재 직후 쿼리가 실행되면 이런 일이 생깁니다. 해결: 대량 INSERT/UPDATE 직후에는 ANALYZE를 직접 실행하고, 평소에는 autovacuum의 자동 ANALYZE가 돌고 있는지 확인합니다.

ANALYZE orders;
-- 또는 특정 컬럼만
ANALYZE orders (user_id, created_at);

---

”복합 인덱스 순서를 잘못 설계했다”

상황: WHERE user_id = ? AND created_at > ? 쿼리에 idx_orders_created_user (created_at, user_id) 인덱스를 만들었습니다. 인덱스가 사용되지 않습니다. 원인: 복합 인덱스는 왼쪽 컬럼부터 정렬되어 있습니다. (created_at, user_id) 인덱스에서 created_at > ? 범위를 먼저 타면, 그 범위 안에 흩어진 모든 user_id 항목을 훑으며 걸러야 합니다. 해결: 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둡니다. 그러면 user_id = ?로 한 구간을 찾은 뒤 그 안에서 created_at 범위를 연속으로 읽을 수 있습니다. “선택도가 높은 컬럼을 앞에”라는 규칙이 흔히 인용되지만, 실제로 더 중요한 것은 조건의 종류(등호냐 범위냐)와 어떤 쿼리들이 이 인덱스를 공유하느냐입니다.

-- ✅ 올바른 순서: user_id (등호) → created_at (범위)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

---

”배치 INSERT가 너무 느리다”

상황: 로그 테이블에 1만 건을 하나씩 INSERT하는데 예상보다 훨씬 오래 걸립니다.

// ❌ 1만 번 개별 INSERT
for (int i = 0; i < 10000; ++i) {
    PQexecParams(conn, "INSERT INTO logs (ts, msg) VALUES ($1, $2)", ...);
}

원인: 자동 커밋 모드에서는 INSERT마다 트랜잭션이 커밋되고, 커밋마다 WAL을 디스크에 플러시(fsync)합니다. 여기에 1만 번의 네트워크 왕복이 더해집니다. 해결: BEGIN/COMMIT으로 트랜잭션 묶기, 또는 COPY 사용.

// ✅ 배치 INSERT
PQexec(conn, "BEGIN");
// 100개씩 배치 INSERT
PQexec(conn, "COMMIT");

---

”동일 쿼리를 1만 번 실행하는데 파싱 비용이 누적된다”

상황: 사용자 로그인 API에서 SELECT * FROM users WHERE email = $1을 매우 자주 실행합니다. 쿼리 자체는 인덱스를 타서 빠른데, DB 서버 CPU 프로파일에서 파싱·계획 함수의 비중이 큽니다. 원인: 매 요청마다 쿼리 파싱·계획 수립을 반복. Prepared Statement를 사용하지 않아 동일 작업이 반복됩니다. 해결: Prepared Statement로 파싱을 한 번만 수행하고 바인딩만 바꿔 재사용합니다. PostgreSQL은 처음 몇 번은 파라미터 값마다 계획을 새로 세우고(custom plan), 비용이 비슷하면 이후 일반 계획(generic plan)을 재사용하므로 계획 비용도 줄어듭니다.

// ✅ Prepared Statement 사용 (PostgreSQL)
// 1. PREPARE로 한 번만 파싱
PQexec(conn, "PREPARE get_user (text) AS SELECT * FROM users WHERE email = $1");
// 2. EXECUTE로 바인딩만 변경해 반복 실행
const char* params[] = {"[email protected]"};
PQexecPrepared(conn, "get_user", 1, params, nullptr, nullptr, 0);

---

”대량 조인·정렬 쿼리가 디스크를 쓰거나 메모리를 과하게 쓴다”

상황: 큰 테이블끼리 JOIN하고 정렬하는 쿼리가 느리고, EXPLAIN ANALYZE에 Sort Method: external merge Disk나 Hash Join의 Batches: 16 같은 표시가 보입니다. 원인: 정렬과 해시 테이블이 work_mem을 넘으면 PostgreSQL은 임시 파일로 나눠 처리합니다(스왑이 아니라 자체 임시 파일). 반대로 work_mem을 크게 잡으면, 이 값이 쿼리 하나가 아니라 정렬·해시 노드 하나당 한도이므로 노드 수 × 동시 연결 수만큼 메모리가 늘어 서버 전체 메모리가 부족해질 수 있습니다. 해결: 먼저 통계를 갱신해 행 수 추정이 맞는지 확인하고, work_mem은 전역이 아니라 해당 세션이나 트랜잭션에서만 올립니다.

ANALYZE users;
ANALYZE orders;
-- 이 트랜잭션에서만 work_mem 상향
BEGIN;
SET LOCAL work_mem = '256MB';
-- 대량 조인 쿼리 실행
COMMIT;

---

원인별 진단 다이어그램

flowchart TB
    subgraph Problems[쿼리 성능 문제]
        P1[N+1 쿼리]
        P2[풀 스캔]
        P3[구식 통계]
        P4[잘못된 인덱스 순서]
        P5[개별 INSERT]
    end
    subgraph Solutions[해결책]
        S1[JOIN/IN 배치]
        S2[인덱스 추가/함수 제거]
        S3[ANALYZE]
        S4[복합 인덱스 순서 수정]
        S5[배치 INSERT/COPY]
    end
    P1 --> S1
    P2 --> S2
    P3 --> S3
    P4 --> S4
    P5 --> S5

---

단일·복합·커버링·부분 인덱스 선택

인덱스가 필요한 이유

인덱스 없이 WHERE user_id = 123을 검색하면 100만 행을 전부 읽어야 합니다. B-Tree 인덱스가 있으면 O(log N) 탐색으로 루트에서 리프까지 몇 페이지만 읽으면 됩니다. B-Tree는 노드 하나에 키가 수백 개씩 들어가므로, 100만 행이어도 트리 높이는 보통 3~4 수준입니다.

flowchart TB
    subgraph NoIndex[인덱스 없음]
        N1[행 1] --> N2[행 2]
        N2 --> N3[행 3]
        N3 --> N4[...]
        N4 --> N5[행 100만]
        N5 --> N6["Full Table Scan: 100만 행 읽음"]
    end
    subgraph WithIndex[인덱스 있음]
        I1["B-Tree 인덱스"] --> I2["높이 3~4 페이지 탐색"]
        I2 --> I3["직접 해당 행 접근"]
    end

인덱스 선택 기준표

컬럼 용도	인덱스 유형	예시	이유
WHERE 등호	단일 B-Tree	idx_users_email	정확히 일치 검색
WHERE 범위	복합 (등호 먼저)	idx_orders_user_created	user_id 등호 + created_at 범위
JOIN 키	양쪽 테이블	orders.user_id, users.id	조인 성능
ORDER BY	복합 인덱스	(user_id, created_at DESC)	정렬 생략
SELECT 컬럼 포함	커버링 인덱스	INCLUDE (amount, status)	테이블 접근 불필요

단일 컬럼 인덱스

-- 이메일로 사용자 검색 (등호 조건)
CREATE INDEX idx_users_email ON users(email);
-- 생성일 기준 정렬
CREATE INDEX idx_users_created_at ON users(created_at);

// C++에서 인덱스 생성 (SQLite)
#include <sqlite3.h>
void create_indexes(sqlite3* db) {
    const char* sql = R"(
        CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
        CREATE INDEX IF NOT EXISTS idx_users_created ON users(created_at);
    )";
    char* err = nullptr;
    sqlite3_exec(db, sql, nullptr, nullptr, &err);
    if (err) {
        sqlite3_free(err);
    }
}

복합 인덱스와 컬럼 순서

규칙: 등호 조건 컬럼을 범위·정렬 컬럼보다 앞에 둡니다. 등호 컬럼이 여러 개면 이 인덱스를 함께 쓸 쿼리들이 공통으로 조건을 거는 컬럼을 앞에 둡니다.

-- ✅ 순서: user_id (등호) → created_at (범위·정렬)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- ❌ 잘못된 순서: created_at이 먼저면 user_id만 조건일 때 비효율
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);

사용 가능한 쿼리 패턴:

인덱스 (user_id, created_at)	사용 가능	비고
WHERE user_id = ?	✅	앞쪽 컬럼만 사용
WHERE user_id = ? AND created_at > ?	✅	둘 다 사용
WHERE created_at > ?	❌ (대부분)	선두 컬럼 조건이 없어 인덱스 전체를 훑어야 함. PostgreSQL 18의 skip scan처럼 선두 컬럼 값 종류가 적을 때 활용하는 기능도 있음
WHERE user_id = ? ORDER BY created_at DESC	✅	정렬 생략

커버링 인덱스 (Index Only Scan)

SELECT 컬럼을 모두 인덱스에 포함하면 테이블 접근 없이 인덱스만 읽습니다.

-- PostgreSQL: INCLUDE 절
CREATE INDEX idx_orders_user_cover ON orders(user_id) INCLUDE (amount, status);
-- SQLite: 복합 인덱스에 컬럼 포함
CREATE INDEX idx_orders_user_cover ON orders(user_id, amount, status);

-- 이 쿼리는 Index Only Scan 가능
SELECT user_id, amount, status FROM orders WHERE user_id = 123;

부분 인덱스 (Partial Index)

특정 조건을 만족하는 행만 인덱스에 포함합니다. 인덱스 크기 감소, 성능 향상.

-- status='active'인 주문만 인덱스
CREATE INDEX idx_orders_active ON orders(user_id) WHERE status = 'active';
-- 특정 날짜 이후 로그만 인덱스
CREATE INDEX idx_logs_recent ON logs(created_at) WHERE created_at > '2026-01-01';

부분 인덱스의 조건에는 NOW()처럼 실행할 때마다 값이 바뀌는 함수를 쓸 수 없습니다(IMMUTABLE 함수만 허용). “최근 30일”처럼 움직이는 범위가 필요하면 기준 날짜를 고정한 인덱스를 주기적으로 다시 만들거나, 날짜 기준 파티셔닝을 씁니다. 쿼리의 WHERE 조건이 인덱스 조건을 논리적으로 포함해야 플래너가 부분 인덱스를 선택한다는 점도 기억해야 합니다.

인덱스 사용 불가 케이스

-- ❌ 함수 적용 시 인덱스 미사용
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- ✅ 인덱스 사용
SELECT * FROM users WHERE email = '[email protected]';
-- ❌ 컬럼 쪽에 형변환이 일어나는 비교 (phone이 text 컬럼)
SELECT * FROM users WHERE phone::bigint = 1012345678;
-- ✅ 상수 쪽을 컬럼 타입에 맞춤
SELECT * FROM users WHERE phone = '1012345678';

PostgreSQL에서 id = '123'처럼 따옴표로 감싼 상수는 타입이 정해지지 않은 리터럴이라 컬럼 타입(integer)으로 해석되므로 인덱스를 정상적으로 씁니다. 문제가 되는 것은 위처럼 컬럼에 형변환이나 함수가 적용되는 경우입니다(MySQL에서 문자열 컬럼을 숫자와 비교하는 경우도 같습니다). email = ? OR name = ? 같은 OR 조건은 두 컬럼에 각각 인덱스가 있으면 PostgreSQL이 BitmapOr로 두 인덱스를 합쳐 쓸 수 있으므로, UNION으로 나누기 전에 실행 계획부터 확인합니다.

---

실행 계획 분석 (EXPLAIN)

실행 계획이란

DB 엔진이 쿼리를 어떻게 실행할지를 보여줍니다. EXPLAIN으로 풀 스캔·인덱스 스캔·조인 순서를 확인할 수 있습니다.

flowchart LR
    A[SQL 쿼리] --> B[파서]
    B --> C[쿼리 최적화기]
    C --> D[실행 계획]
    D --> E[Index Scan]
    D --> F[Seq Scan]
    D --> G[Nested Loop]

PostgreSQL EXPLAIN ANALYZE

-- 실제 실행 시간 포함 (ANALYZE)
-- 버퍼 접근 정보 (BUFFERS)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

인덱스 사용 시 (좋음):

Limit  (cost=0.42..8.44 rows=10 width=40) (actual time=0.05..0.08 rows=10 loops=1)
  ->  Index Scan using idx_orders_user_created on orders
        Index Cond: (user_id = 123)
        Buffers: shared hit=4
Planning Time: 0.12 ms
Execution Time: 0.15 ms

풀 스캔 시 (나쁨):

Limit  (cost=0.00..20834.00 rows=10 width=40) (actual time=45.2..182.3 rows=10 loops=1)
  ->  Seq Scan on orders
        Filter: (user_id = 123)
        Rows Removed by Filter: 999990
        Buffers: shared read=8000
Planning Time: 0.08 ms
Execution Time: 182.5 ms

실행 계획 용어 해석

용어	의미	대응
Seq Scan	전체 테이블 스캔	인덱스 추가
Index Scan	인덱스 사용, 테이블 접근	양호
Index Only Scan	인덱스만 읽음 (커버링)	최적
Bitmap Index Scan	인덱스로 비트맵 생성 후 테이블 접근	대량 행 시
Nested Loop	중첩 루프 조인	작은 테이블에 유리
Hash Join	해시 조인	대용량 조인, 등호 조건
Merge Join	정렬 병합 조인	정렬된 데이터

C++에서 EXPLAIN 실행

#include <libpq-fe.h>
#include <iostream>
#include <string>
void explain_query(PGconn* conn, const std::string& sql) {
    std::string explain_sql = "EXPLAIN (ANALYZE, BUFFERS) " + sql;
    PGresult* res = PQexec(conn, explain_sql.c_str());
    if (PQresultStatus(res) == PGRES_TUPLES_OK) {
        for (int i = 0; i < PQntuples(res); ++i) {
            const char* row = PQgetvalue(res, i, 0);
            if (row) std::cout << row << "\n";
        }
    }
    PQclear(res);
}

SQLite EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN
SELECT id, name FROM users WHERE email = '[email protected]';

-- 인덱스 사용 시
SEARCH users USING INDEX idx_users_email (email=?)
-- 인덱스 미사용 시
SCAN users

실행 계획에서 볼 신호

확인 항목	좋은 신호	나쁜 신호	조치
스캔 방식	Index Scan, Index Only Scan	Seq Scan	인덱스 추가/수정
Rows Removed by Filter	반환 행 수보다 작음	반환 행 수의 수십 배 이상	WHERE 컬럼 인덱스
Buffers shared hit/read	hit 위주	read 위주	작업 집합이 메모리에 맞는지, 읽는 페이지 수 자체를 줄일 수 있는지
rows (추정) vs actual rows	비슷함	몇 배 이상 차이	ANALYZE, 통계 대상 확대
Planning Time	Execution Time보다 작음	Execution Time과 비슷하거나 큼	Prepared Statement, 조인 수 검토

절대 시간 기준은 서비스의 응답 목표에 따라 다르므로, 위 표는 상대적인 신호로만 봅니다. 특히 추정 행 수와 실제 행 수의 차이는 잘못된 계획의 가장 흔한 원인이라 가장 먼저 확인할 만합니다.

// C++에서 SQLite EXPLAIN
void explain_sqlite(sqlite3* db, const std::string& sql) {
    sqlite3_stmt* stmt = nullptr;
    std::string explain_sql = "EXPLAIN QUERY PLAN " + sql;
    sqlite3_prepare_v2(db, explain_sql.c_str(), -1, &stmt, nullptr);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char* detail = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
        if (detail) std::cout << detail << "\n";
    }
    sqlite3_finalize(stmt);
}

---

통계와 비용 모델

통계가 실행 계획에 미치는 영향

옵티마이저는 테이블·인덱스 통계를 기반으로 비용을 추정합니다. 통계가 오래되면 잘못된 실행 계획이 선택됩니다.

flowchart TB
    A[pg_stat_user_tables] --> B[옵티마이저]
    C[pg_stats] --> B
    B --> D[비용 추정]
    D --> E[실행 계획 선택]

ANALYZE로 통계 갱신

-- 전체 데이터베이스
ANALYZE;
-- 특정 테이블
ANALYZE orders;
-- 특정 컬럼만 (대용량 테이블)
ANALYZE orders (user_id, created_at);

// C++에서 ANALYZE 실행
void refresh_statistics(PGconn* conn) {
    PGresult* res = PQexec(conn, "ANALYZE orders");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        std::cerr << "ANALYZE failed: " << PQerrorMessage(conn) << "\n";
    }
    PQclear(res);
}

pg_stat 시스템 카탈로그

-- 테이블별 스캔 통계
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- 인덱스별 사용 통계
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

컬럼	의미
seq_scan	Sequential Scan 횟수
seq_tup_read	Seq Scan으로 읽은 행 수
idx_scan	Index Scan 횟수
idx_tup_fetch	Index로 가져온 행 수

pg_stat 활용: 풀 스캔 빈도 모니터링

-- Seq Scan이 많은 테이블 찾기 (인덱스 추가 후보)
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
       seq_scan::float / NULLIF(seq_scan + idx_scan, 0) AS seq_ratio
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 10;

-- 사용되지 않는 인덱스 찾기 (제거 후보)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

// C++에서 pg_stat 조회해 모니터링
void log_table_stats(PGconn* conn) {
    const char* sql = R"(
        SELECT relname, seq_scan, idx_scan, seq_tup_read, idx_tup_fetch
        FROM pg_stat_user_tables
        WHERE seq_scan > 1000 OR seq_tup_read > 100000
    )";
    PGresult* res = PQexec(conn, sql);
    for (int i = 0; i < PQntuples(res); ++i) {
        printf("Table: %s, seq_scan=%s, idx_scan=%s\n",
               PQgetvalue(res, i, 0), PQgetvalue(res, i, 1), PQgetvalue(res, i, 2));
    }
    PQclear(res);
}

비용 모델 (PostgreSQL)

PostgreSQL 옵티마이저 비용 추정 알고리즘:

Sequential Scan 비용 = (페이지 수 × seq_page_cost) + (행 수 × cpu_tuple_cost)
Index Scan 비용 = (인덱스 접근 × random_page_cost) + (테이블 접근 × random_page_cost) + (행 처리 × cpu_tuple_cost)

예시 계산:
orders 테이블: 10M 행, 125K 페이지

Seq Scan:
= 125,000 × 1.0 + 10,000,000 × 0.01
= 225,000 cost units

Index Scan (user_id = 123, 100행 반환):
= (4 인덱스 페이지 × 4.0) + (100 테이블 페이지 × 4.0) + (100 × 0.01)
= 16 + 400 + 1
= 417 cost units

→ Index Scan 선택! (추정 비용이 약 540분의 1. 비용 단위는 실제 시간이 아니라 플래너의 상대 추정치입니다)

PostgreSQL은 비용 단위로 실행 계획을 비교합니다. 기본 설정:

• seq_page_cost = 1.0: Sequential Scan 1페이지 비용
• random_page_cost = 4.0: Random I/O 1페이지 비용 (SSD는 1.1 권장)
• cpu_tuple_cost = 0.01: 행 1개 처리 비용

-- 비용 파라미터 확인
SHOW seq_page_cost;
SHOW random_page_cost;
-- SSD 환경 권장
ALTER SYSTEM SET random_page_cost = 1.1;

비용 해석 예시

Limit  (cost=0.42..8.44 rows=10 width=40)

• cost=0.42..8.44: 시작 비용 0.42, 총 비용 8.44
• rows=10: 추정 반환 행 수
• width=40: 평균 행 크기 (바이트)

비용이 높은 경우: cost=0.00..20834.00 → 2만 비용 단위, 풀 스캔 추정.

---

사용자별 최근 주문 조회를 3초에서 줄이기

예제: 사용자별 최근 주문 10건 조회

요구사항: 1000명 사용자 각각에 대해 최근 주문 10건을 조회. 3초 이내 응답.

Step 1: 현재 상태 분석

-- 테이블 구조
-- users: id, name, email, created_at (100만 행)
-- orders: id, user_id, amount, status, created_at (1000만 행)
-- 현재 쿼리 (N+1)
SELECT * FROM users;
-- 루프: SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10

EXPLAIN 결과:

• users: Seq Scan (인덱스 없음)
• orders: Seq Scan 1000번 (user_id 인덱스 없음)

Step 2: 인덱스 설계

-- users: 이메일 검색용 (선택)
CREATE INDEX idx_users_email ON users(email);
-- orders: user_id + created_at 복합 인덱스 (핵심)
-- user_id 등호 + created_at DESC 정렬
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

Step 3: N+1 해결 — IN 배치 쿼리

#include <libpq-fe.h>
#include <vector>
#include <string>
#include <unordered_map>
struct Order {
    int id, user_id, amount;
    std::string status;
};
std::unordered_map<int, std::vector<Order>> get_recent_orders_batch(
    PGconn* conn, const std::vector<int>& user_ids, int limit = 10)
{
    if (user_ids.empty()) return {};
    // IN 절 동적 생성
    std::string sql = "SELECT user_id, id, amount, status FROM (";
    sql += "SELECT user_id, id, amount, status, "
           "ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn ";
    sql += "FROM orders WHERE user_id = ANY($1::int[])) sub ";
    sql += "WHERE rn <= " + std::to_string(limit);
    // 배열 파라미터 (PostgreSQL)
    std::string array_str = "{";
    for (size_t i = 0; i < user_ids.size(); ++i) {
        if (i > 0) array_str += ",";
        array_str += std::to_string(user_ids[i]);
    }
    array_str += "}";
    const char* params[] = {array_str.c_str()};
    PGresult* res = PQexecParams(conn, sql.c_str(), 1, nullptr, params, nullptr, nullptr, 0);
    std::unordered_map<int, std::vector<Order>> result;
    if (PQresultStatus(res) == PGRES_TUPLES_OK) {
        for (int i = 0; i < PQntuples(res); ++i) {
            Order o;
            o.user_id = atoi(PQgetvalue(res, i, 0));
            o.id = atoi(PQgetvalue(res, i, 1));
            o.amount = atoi(PQgetvalue(res, i, 2));
            o.status = PQgetvalue(res, i, 3) ? PQgetvalue(res, i, 3) : "";
            result[o.user_id].push_back(o);
        }
    }
    PQclear(res);
    return result;
}

Step 4: 실행 계획 확인

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, id, amount, status FROM (
    SELECT user_id, id, amount, status,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
    FROM orders WHERE user_id = ANY(ARRAY[1,2,3,...,1000])
) sub WHERE rn <= 10;

기대 결과: Index Scan using idx_orders_user_created 또는 Bitmap Heap Scan.

Step 5: 방식별 비용 구조

방식	쿼리 수	주된 비용
N+1 (인덱스 없음)	1001	1000번의 왕복 + 쿼리마다 orders 전체 스캔
N+1 (인덱스 있음)	1001	1000번의 왕복 + 쿼리마다 인덱스 탐색
IN 배치 (인덱스 있음)	2	왕복 2번 + 1000명분 인덱스 탐색을 한 쿼리에서

순서가 이렇게 나오는 이유는 분명합니다. 인덱스는 쿼리 하나하나의 실행 시간을 줄이지만, N+1에서는 쿼리마다 네트워크 왕복과 파싱·계획 비용이 1001번 반복되므로 인덱스만으로는 한계가 있습니다. IN 배치는 왕복 자체를 2번으로 줄입니다. DB가 같은 호스트에 있으면 왕복 비용이 작아 격차가 줄고, 다른 데이터센터에 있으면 격차가 훨씬 커집니다. 실제 수치는 EXPLAIN (ANALYZE, BUFFERS)와 애플리케이션 쪽 타이머로 직접 확인하세요.

Step 6: Prepared Statement로 파싱 비용 제거

동일 쿼리를 반복 실행할 때 Prepared Statement를 사용하면 파싱·계획 수립을 한 번만 수행합니다. SQL 인젝션도 방지됩니다.

// PostgreSQL: PQprepare + PQexecPrepared
#include <libpq-fe.h>
#include <string>
class PreparedQuery {
    PGconn* conn_;
    std::string name_;
    int n_params_;
public:
    PreparedQuery(PGconn* conn, const std::string& name,
                  const std::string& sql, int n_params)
        : conn_(conn), name_(name), n_params_(n_params)
    {
        PGresult* res = PQprepare(conn_, name_.c_str(), sql.c_str(), n_params_, nullptr);
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            PQclear(res);
            throw std::runtime_error(PQerrorMessage(conn_));
        }
        PQclear(res);
    }
    PGresult* execute(const char* const* params) {
        return PQexecPrepared(conn_, name_.c_str(), n_params_, params, nullptr, nullptr, 0);
    }
};
// 사용 예: 연결당 한 번 준비하고 계속 재사용
// PreparedQuery get_user(conn, "get_user", "SELECT id, name FROM users WHERE email = $1", 1);
// const char* params[] = {"[email protected]"};
// PGresult* res = get_user.execute(params);
// 주의: Prepared Statement는 연결(세션)마다 따로 존재하므로 풀의 각 연결에서 준비해야 한다

// SQLite: sqlite3_prepare_v2 + sqlite3_bind_* + sqlite3_step
// 한 번 준비 후 재사용
sqlite3_stmt* stmt = nullptr;
sqlite3_prepare_v2(db, "SELECT id, name FROM users WHERE email = ?", -1, &stmt, nullptr);
// 루프에서 바인딩만 변경
for (const auto& email : emails) {
    sqlite3_bind_text(stmt, 1, email.c_str(), -1, SQLITE_TRANSIENT);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 결과 처리
    }
    sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);

Step 7: JOIN으로 N+1 완전 제거

IN 배치 대신 한 번의 JOIN으로 사용자와 주문을 함께 조회할 수 있습니다.

-- 사용자 + 최근 주문 10건 조회 (1회 쿼리)
WITH ranked AS (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) rn
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE u.created_at > '2025-01-01'  -- 필요한 사용자만
)
SELECT u.id, u.name, r.id AS order_id, r.amount, r.status
FROM users u
JOIN ranked r ON u.id = r.user_id AND r.rn <= 10
ORDER BY u.id, r.rn;

// C++에서 JOIN 결과 처리
std::vector<UserWithOrders> get_users_with_orders_join(PGconn* conn) {
    const char* sql = R"(
        WITH ranked AS (
            SELECT o.user_id, o.id AS order_id, o.amount, o.status,
                   ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) rn
            FROM orders o JOIN users u ON o.user_id = u.id
        )
        SELECT u.id, u.name, r.order_id, r.amount, r.status
        FROM users u
        JOIN ranked r ON u.id = r.user_id AND r.rn <= 10
        ORDER BY u.id, r.rn
    )";
    PGresult* res = PQexec(conn, sql);
    std::vector<UserWithOrders> result;
    int current_user_id = -1;
    UserWithOrders* current = nullptr;
    for (int i = 0; i < PQntuples(res); ++i) {
        int uid = atoi(PQgetvalue(res, i, 0));
        if (uid != current_user_id) {
            result.push_back({uid, PQgetvalue(res, i, 1), {}});
            current = &result.back();
            current_user_id = uid;
        }
        if (current)
            current->orders.push_back({atoi(PQgetvalue(res, i, 2)),
                                       atoi(PQgetvalue(res, i, 3)),
                                       PQgetvalue(res, i, 4)});
    }
    PQclear(res);
    return result;
}

Step 8: 실행 계획 단계별 분석

최적화 전후 실행 계획을 비교하는 절차입니다.

1. EXPLAIN (ANALYZE, BUFFERS) <쿼리> 실행
2. Seq Scan 확인 → Rows Removed by Filter가 크면 인덱스 추가 검토
3. Index Scan 확인 → Buffers: shared hit 비율이 높으면 캐시 hit
4. cost, actual time 비교 → cost는 추정, actual은 실제
5. Nested Loop vs Hash Join → 행 수에 따라 옵티마이저 선택

-- 통계 기반 실행 계획 비교 (ANALYZE 전후)
-- BEFORE: rows=1000 (구식 통계)
-- AFTER:  rows=10000000 (ANALYZE 후)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

---

N+1, 함수 적용, 구식 통계: 쿼리 성능을 망치는 실수

N+1 쿼리

증상: 사용자 목록 API 3초 이상. 원인: 루프 안에서 매번 DB 쿼리.

// ❌ 나쁜 예
for (auto& user : users) {
    auto orders = query_orders(conn, user.id);
}

해결: JOIN 또는 IN 배치 쿼리.

---

풀 스캔 (Seq Scan)

증상: EXPLAIN에서 Seq Scan, 100만 행 스캔. 원인: WHERE 조건 컬럼에 인덱스 없음.

-- 해결
CREATE INDEX idx_orders_user_id ON orders(user_id);

---

함수 적용으로 인덱스 미사용

증상: 인덱스가 있는데도 Seq Scan.

-- ❌ LOWER() 적용
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- ✅ 함수 제거 또는 함수 기반 인덱스
SELECT * FROM users WHERE email = '[email protected]';
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

---

복합 인덱스 순서 오류

증상: (created_at, user_id) 인덱스인데 WHERE user_id = ?만 사용. 해결: (user_id, created_at) 순서로 재생성.

-- ❌ user_id만 조건일 때 인덱스 비효율
DROP INDEX idx_orders_created_user;
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

---

Connection Leak

증상: too many connections, FATAL: remaining connection slots. 원인: 연결을 닫지 않음.

// ✅ RAII 가드 사용
PgConnectionGuard guard(pool);
PGconn* conn = guard.get();

---

SQL 인젝션

증상: 악의적 입력으로 데이터 유출.

// ❌ 절대 금지
std::string sql = "SELECT * FROM users WHERE id = " + user_input;
// ✅ 파라미터 바인딩
PQexecParams(conn, "SELECT * FROM users WHERE id = $1::int", 1, nullptr, params, nullptr, nullptr, 0);

---

구식 통계

증상: 대량 데이터 추가 후 잘못된 조인 방식 선택. 해결: ANALYZE 실행.

---

SELECT * 사용

증상: 불필요한 컬럼까지 전송, 커버링 인덱스 미활용.

-- ❌
SELECT * FROM orders WHERE user_id = 123;
-- ✅ 필요한 컬럼만
SELECT id, amount, status FROM orders WHERE user_id = 123;

---

인덱스 과다

증상: INSERT/UPDATE가 느려짐. 인덱스마다 갱신 비용 발생. 해결: 실제로 사용하는 쿼리 패턴에 맞춰 인덱스 최소화. 사용하지 않는 인덱스 제거.

---

캐시 무효화 누락

증상: 캐시된 데이터가 DB와 불일치. 해결: INSERT/UPDATE/DELETE 후 캐시 무효화 또는 갱신.

void update_user(..., LruCache<int, User>& cache, ...) {
    // DB 업데이트
    cache.put(id, new_user);  // 캐시 갱신
}

---

Prepared Statement 미사용

증상: 동일 쿼리 반복 시 DB CPU 사용률이 높고, pg_stat_statements.track_planning을 켰을 때 total_plan_time이 total_exec_time에 비해 큰 비중을 차지합니다. 해결: PQprepare + PQexecPrepared 또는 sqlite3_prepare_v2 재사용.

// ❌ 매번 파싱
for (int i = 0; i < 10000; ++i) {
    PQexecParams(conn, "SELECT * FROM users WHERE id = $1", 1, nullptr, params, nullptr, nullptr, 0);
}
// ✅ Prepared Statement
PQprepare(conn, "get_user", "SELECT * FROM users WHERE id = $1", 1, nullptr);
for (int i = 0; i < 10000; ++i) {
    PQexecPrepared(conn, "get_user", 1, params, nullptr, nullptr, 0);
}

---

IN 절 파라미터 과다

증상: WHERE id IN (1,2,...,10000)처럼 값을 SQL 문자열에 수천 개 이어 붙이면 쿼리 문자열이 커지고, 매번 다른 문자열이라 파싱·계획을 재사용할 수 없습니다. 파라미터 개수 한도(PostgreSQL 프로토콜은 65535개)에 걸리기도 합니다. 해결: 앞의 예제처럼 = ANY($1::int[])로 배열 파라미터 하나를 넘기거나, 일정 크기로 나눠 보내거나, 임시 테이블에 넣고 JOIN합니다.

// ✅ 500개씩 배치
const size_t BATCH = 500;
for (size_t i = 0; i < ids.size(); i += BATCH) {
    auto end = std::min(i + BATCH, ids.size());
    query_batch(conn, ids.data() + i, end - i);
}

---

트랜잭션 없이 배치 INSERT

증상: 대량 INSERT가 느리고, 각 INSERT마다 커밋과 WAL 플러시가 일어납니다. 해결: BEGIN/COMMIT으로 묶거나 COPY 사용.

// ✅ 트랜잭션으로 묶기
PQexec(conn, "BEGIN");
for (const auto& row : rows) {
    PQexecParams(conn, "INSERT INTO logs (...) VALUES ($1, $2)", ...);
}
PQexec(conn, "COMMIT");

---

인덱스 선택과 N+1 회피 정리

인덱스 선택 의사결정 플로우

flowchart TD
    A[쿼리 분석] --> B{WHERE 조건?}
    B -->|등호 1개| C[단일 B-Tree]
    B -->|등호+범위| D[복합: 등호 먼저]
    B -->|범위만| E[단일 또는 부분 인덱스]
    C --> G{SELECT 컬럼?}
    D --> G
    E --> G
    G -->|인덱스에 포함 가능| H[커버링 INCLUDE]
    G -->|아니오| I[일반 인덱스]

N+1 방지 패턴 요약

패턴	사용 시점	장점	단점
JOIN	1:N 관계, 한 번에 조회	쿼리 1회만	결과 중복, 메모리
IN 배치	N개 ID로 조회	유연함, 페이지네이션	IN 크기 제한
Lazy Loading	필요 시에만	초기 로딩 빠름	N+1 위험
Lazy + 캐시	반복 조회	캐시 hit 시 빠름	캐시 관리 복잡

---

연결 풀·Replica·COPY로 운영 부하 줄이기

연결 풀 + Prepared Statement

struct DbContext {
    PgConnectionPool& pool;
    PgConnectionGuard guard;
    PGconn* conn;
    PreparedStatementCache& stmt_cache;
    explicit DbContext(PgConnectionPool& p, PreparedStatementCache& c)
        : pool(p), guard(p), conn(guard.get()), stmt_cache(c) {}
};

읽기/쓰기 분리 (Replica)

flowchart TB
    App[앱] --> Router[라우터]
    Router -->|SELECT| Replica[Replica]
    Router -->|INSERT/UPDATE| Primary[Primary]
    Primary -.->|복제| Replica

class ReadWriteSplit {
    PgConnectionPool* primary_;
    PgConnectionPool* replica_;
public:
    PGconn* acquire_read() { return replica_->acquire(); }
    PGconn* acquire_write() { return primary_->acquire(); }
};

배치 INSERT

PQexec(conn, "BEGIN");
for (size_t i = 0; i < batch.size(); i += 100) {
    // 100개씩 배치 INSERT
    batch_insert(conn, batch, i, std::min(i + 100, batch.size()));
}
PQexec(conn, "COMMIT");

쿼리 타임아웃

PQexec(conn, "SET statement_timeout = '5s'");  // 이 세션의 모든 쿼리에 적용

느린 쿼리 로깅

auto start = std::chrono::steady_clock::now();
PGresult* res = PQexecParams(conn, sql.c_str(), ...);
auto elapsed = std::chrono::duration_cast<std::chrono::milliseconds>(
    std::chrono::steady_clock::now() - start).count();
if (elapsed > 100) {
    log_warn("Slow query: {}ms - {}", elapsed, sql);
}

주기적 ANALYZE

-- pg_cron 또는 cron
-- 0 3 * * * psql -c "ANALYZE"

Prepared Statement 캐시

// 연결별 Prepared Statement 캐시
class PreparedStatementCache {
    PGconn* conn_;
    std::unordered_map<std::string, std::string> stmts_;
public:
    explicit PreparedStatementCache(PGconn* conn) : conn_(conn) {}
    PGresult* execute(const std::string& name, const std::string& sql,
                      int n_params, const char* const* params) {
        if (stmts_.find(name) == stmts_.end()) {
            std::string prepare_sql = "PREPARE " + name + " AS " + sql;
            PQexec(conn_, prepare_sql.c_str());
            stmts_[name] = sql;
        }
        return PQexecPrepared(conn_, name.c_str(), n_params, params, nullptr, nullptr, 0);
    }
};

COPY로 대량 로드

// PostgreSQL COPY: 행마다 INSERT를 보내는 대신 데이터 스트림 하나로 적재
// 주의: text 형식에서는 값 안의 탭·줄바꿈·백슬래시를 이스케이프해야 한다(여기서는 생략)
void bulk_insert_logs(PGconn* conn, const std::vector<Log>& logs) {
    PQexec(conn, "BEGIN");
    PGresult* res = PQexec(conn, "COPY logs (ts, msg, level) FROM STDIN WITH (FORMAT text)");
    if (PQresultStatus(res) != PGRES_COPY_IN) {
        PQclear(res);
        return;
    }
    PQclear(res);
    for (const auto& log : logs) {
        std::string row = log.ts + "\t" + log.msg + "\t" + log.level + "\n";
        PQputCopyData(conn, row.c_str(), static_cast<int>(row.size()));
    }
    PQputCopyEnd(conn, nullptr);
    // COPY 결과 확인: NULL이 나올 때까지 PQgetResult를 호출해야 연결이 다음 명령을 받는다
    bool ok = true;
    while (PGresult* r = PQgetResult(conn)) {
        if (PQresultStatus(r) != PGRES_COMMAND_OK) ok = false;
        PQclear(r);
    }
    PQclear(PQexec(conn, ok ? "COMMIT" : "ROLLBACK"));
}

읽기/쓰기 라우팅 (SQL 기반)

// SELECT는 replica, 그 외는 primary (단순화한 예)
// 실제로는 "WITH ... SELECT", 소문자 select, SELECT ... FOR UPDATE, 쓰기 직후의 읽기(복제 지연)를
// 문자열 검사로 구분할 수 없으므로, 호출하는 쪽에서 읽기/쓰기 의도를 명시적으로 넘기는 편이 안전하다
PGconn* acquire_for_query(PgConnectionPool* primary, PgConnectionPool* replica,
                          const std::string& sql) {
    return (sql.find("SELECT") == 0) ? replica->acquire() : primary->acquire();
}

---

자주 묻는 질문 (FAQ)

Q. EXPLAIN과 EXPLAIN ANALYZE는 무엇이 다른가요?

A. EXPLAIN은 쿼리를 실행하지 않고 플래너가 고른 계획과 추정 비용·행 수만 보여 줍니다. EXPLAIN ANALYZE는 쿼리를 실제로 실행해 노드별 실제 시간과 행 수를 함께 보여 주므로, 추정과 실제의 차이를 확인할 수 있습니다. 실제로 실행되므로 UPDATE·DELETE에 쓸 때는 BEGIN으로 감싸고 ROLLBACK해야 데이터가 바뀌지 않습니다.

---

참고 자료:

• PostgreSQL EXPLAIN
• Use The Index Luke
• pg_stat_user_tables

---

같이 보면 좋은 글

• C++ 앱의 느린 DB 쿼리 잡기
• C++에서 DB 연동하기
• C++ 데이터베이스 엔진 구현 | B-Tree·트랜잭션·쿼리 최적화 [#50-4]
• C++ 시리즈 전체 보기
• C++에서 PostgreSQL 연동: libpq vs libpqxx, 트랜잭션, Prepared Statement, JSONB와 연결 풀