PostgreSQL 쿼리 튜닝: 인덱스, LATERAL 사용

※ PostgreSQL Query Tuning: Using Indexes and LATERAL.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 인덱스의 중요성을 보여주는 튜닝 사례입니다. (실제 운영 환경에서 튜닝 후 약 110배 성능 향상되었습니다.)

---

1. AS-IS 쿼리

▸ 이 쿼리는 기준 시각 이후로 조건에 맞는 계정별 가장 최근 사용 내역을 뽑는 쿼리로 하루에 수천만 번 이상 동작합니다.

-- 테이블과 컬럼명을 가칭으로 바꾼 쿼리입니다.
SELECT table_a.id,
       max(table_b.created_at) as last_at
FROM table_a
INNER JOIN table_b
  ON table_b.a_id = table_a.id
  AND table_b.created_at >= $1::timestamp
WHERE table_a.enabled = $2
  AND table_a.b IN ($3, $4)
  AND table_a.c_id = $5
  AND table_a.c_status IN ($6, $7)
GROUP BY table_a.id;

-- 바인드를 리터럴로 치환한 쿼리입니다.
-- 실제로는 더 복잡한 데이터가 들어갑니다.
SELECT table_a.id,
       max(table_b.created_at) as last_at
FROM table_a
INNER JOIN table_b
  ON table_b.a_id = table_a.id
  AND table_b.created_at >= '2025-08-18 08:00:00'::timestamp
WHERE table_a.enabled = 't'
  AND table_a.b IN (3, 4)
  AND table_a.c_id = 9
  AND table_a.c_status IN (2, 3)
GROUP BY table_a.id;

 

▸ 실행계획

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=540668.32..541682.77 rows=2618 width=16) (actual time=1665.496..1679.898 rows=2593 loops=1)
   Group Key: table_a.id
   Buffers: shared hit=123783 read=250502
   ->  Gather Merge  (cost=540668.32..541616.98 rows=7923 width=16) (actual time=1665.483..1678.798 rows=8413 loops=1)
         Workers Planned: 4
         Workers Launched: 3
         Buffers: shared hit=123783 read=250502
         ->  Sort  (cost=539668.26..539673.21 rows=1981 width=16) (actual time=1658.375..1658.584 rows=2103 loops=4)
               Sort Key: table_a.id
               Sort Method: quicksort  Memory: 162kB
               Buffers: shared hit=123783 read=250502
               Worker 0:  Sort Method: quicksort  Memory: 163kB
               Worker 1:  Sort Method: quicksort  Memory: 164kB
               Worker 2:  Sort Method: quicksort  Memory: 112kB
               ->  Parallel Hash Join  (cost=13675.30..539559.78 rows=1981 width=16) (actual time=40.387..1656.446 rows=2103 loops=4)
                     Hash Cond: (table_b.a_id = table_a.id)
                     Buffers: shared hit=123672 read=250502
                     ->  Parallel Seq Scan on table_b  (cost=0.00..523898.40 rows=756596 width=16) (actual time=0.046..1512.071 rows=771926 loops=4)
                           Filter: (created_at >= '2025-08-18 08:00:00'::timestamp without time zone)
                           Rows Removed by Filter: 11728074
                           Buffers: shared hit=123259 read=244389
                     ->  Parallel Hash  (cost=13661.67..13661.67 rows=1091 width=8) (actual time=38.684..38.685 rows=678 loops=4)
                           Buckets: 4096  Batches: 1  Memory Usage: 192kB
                           Buffers: shared hit=266 read=6113
                           ->  Parallel Seq Scan on table_a  (cost=0.00..13661.67 rows=1091 width=8) (actual time=0.144..38.327 rows=678 loops=4)
                                 Filter: (enabled AND (b = ANY ('{3,4}'::integer[])) AND (c_status = ANY ('{2,3}'::integer[])) AND (c_id = 9))
                                 Rows Removed by Filter: 249322
                                 Buffers: shared hit=266 read=6113
 Planning:
   Buffers: shared hit=170 read=8
 Planning Time: 1.102 ms
 Execution Time: 1680.161 ms
(32 rows)

-- 테스트 환경에서는 1.7초 정도로 나오지만 실제 운영 환경에서는 30초 내외로 걸리는 쿼리였습니다.

• 실행계획을 보시면 적절한 인덱스가 없어서 테이블 풀스캔을 해버리고 정렬 비용이 많이 든다는 게 문제였습니다. 그로 인해 발생되는 Disk I/O가 엄청난 쿼리입니다.
• 흐름: table_b Parallel Seq Scan, 필터(creatted_at) → table_a와 해시 조인 → sort → GroupAggregate

---

2. 인덱스 생성

-- table_a: WHERE 필터 컬럼 + GROUP BY id
CREATE INDEX CONCURRENTLY idx_table_a_filters ON table_a (enabled, b, c_id, c_status, id);

-- table_b: 조인 + created_at 범위/정렬
CREATE INDEX CONCURRENTLY idx_table_b_sort ON table_b (a_id, created_at DESC);

• 첫 번째 인덱스는 table_a의 조건이 충분히 선별적일 때 table_a를 작게 만든 뒤 table_b에 붙이도록 도와주는 인덱스로 드라이빙 시 불필요한 힙 접근을 감소시킵니다. (필터로 대상을 좁힌 뒤 id를 뽑는 의도입니다.)
• 두 번째 인덱스는 계정별 최신 1건을 정렬/집계 없이 가져올 수 있게 만듭니다.

 

▸ 인덱스 생성 후 실행계획

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=4778.82..4805.01 rows=2618 width=16) (actual time=428.015..428.399 rows=2593 loops=1)
   Group Key: table_a.id
   Batches: 1  Memory Usage: 369kB
   Buffers: shared hit=14953 read=3616
   ->  Nested Loop  (cost=0.99..4739.21 rows=7923 width=16) (actual time=0.886..423.346 rows=8413 loops=1)
         Buffers: shared hit=14953 read=3616
         ->  Index Only Scan using idx_table_a_filters on table_a  (cost=0.42..84.78 rows=2618 width=8) (actual time=0.690..2.881 rows=2712 loops=1)
               Index Cond: ((enabled = true) AND (b = ANY ('{3,4}'::integer[])) AND (c_id = 9) AND (c_status = ANY ('{2,3}'::integer[])))
               Heap Fetches: 0
               Buffers: shared hit=8 read=17
         ->  Index Only Scan using idx_table_b_sort on table_b  (cost=0.56..1.74 rows=4 width=16) (actual time=0.151..0.154 rows=3 loops=2712)
               Index Cond: ((a_id = table_a.id) AND (created_at >= '2025-08-18 08:00:00'::timestamp without time zone))
               Heap Fetches: 0
               Buffers: shared hit=14945 read=3599
 Planning:
   Buffers: shared hit=232 read=9
 Planning Time: 2.570 ms
 Execution Time: 428.666 ms
(18 rows)

• 인덱스 생성 후 Nested Loop + Index Only Scan으로 4배 이상 속도가 좋아졌습니다. (실제 운영 쿼리는 30배 이상 향상되었습니다.)
※ DML 발생 시 Visibility Map(최초 SELECT 또는 VACUUM 필요)이 제대로 갱신되지 않으면 Heap Fetches가 증가할 수 있습니다.

 

---

3. TO-BE

▸ LATERAL로 튜닝한 쿼리입니다.

SELECT a.id, b.last_at
FROM (
  SELECT id
  FROM table_a
  WHERE enabled = $2
  AND b IN ($3, $4)
  AND c_id = $5
  AND c_status IN ($6, $7)
) AS a
JOIN LATERAL (
  SELECT created_at AS last_at
  FROM table_b
  WHERE a_id = a.id
  AND created_at >= $1::timestamptz
  ORDER BY created_at DESC
  LIMIT 1
) AS b ON TRUE;

-- 바인드를 리터럴로 치환한 쿼리입니다.
SELECT a.id, b.last_at
FROM (
  SELECT id
  FROM table_a
  WHERE enabled = 't'
  AND b IN (3, 4)
  AND c_id = 9
  AND c_status IN (2, 3)
) AS a
JOIN LATERAL (
  SELECT created_at AS last_at
  FROM table_b
  WHERE a_id = a.id
  AND created_at >= '2025-08-18 08:00:00'::timestamptz
  ORDER BY created_at DESC
  LIMIT 1
) AS b ON TRUE;

• table_a의 후보 id를 먼저 만들고, 각 id 마다 인덱스 앞부분 1건만 읽어오는 형태입니다.
• table_a의 후보가 작을수록 성능이 좋아집니다.
• $1 바인드는 table_b.created_at가 timestamptz라서 타입을 맞췄습니다. (플래너/인덱스 사용이 더 안정적으로 됩니다.)
• ORDER BY created_at DESC를 명시하여 통계 변화나 플랜 변동 시에도 안정성을 확보하였습니다.

 

▸ 튜닝 후 실행계획

                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.99..2362.44 rows=2618 width=16) (actual time=0.089..16.448 rows=2593 loops=1)
   Buffers: shared hit=13258
   ->  Index Only Scan using idx_table_a_filters on table_a  (cost=0.42..84.78 rows=2618 width=8) (actual time=0.034..0.489 rows=2712 loops=1)
         Index Cond: ((enabled = true) AND (b = ANY ('{3,4}'::integer[])) AND (c_id = 9) AND (c_status = ANY ('{2,3}'::integer[])))
         Heap Fetches: 0
         Buffers: shared hit=19
   ->  Limit  (cost=0.56..0.86 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=2712)
         Buffers: shared hit=13239
         ->  Index Only Scan using idx_table_b_sort on table_b  (cost=0.56..1.75 rows=4 width=8) (actual time=0.005..0.005 rows=1 loops=2712)
               Index Cond: ((a_id = table_a.id) AND (created_at >= '2025-08-18 08:00:00+09'::timestamp with time zone))
               Heap Fetches: 0
               Buffers: shared hit=13239
 Planning:
   Buffers: shared hit=8
 Planning Time: 0.394 ms
 Execution Time: 16.621 ms
(16 rows)

• table_a의 드라이빙 조건을 인덱스가 그대로 커버하고, table_b의 (a_id, created_at DESC) 정렬을 따르는 인덱스에서 상위 1건만 가져와서 해시/정렬/해시집계 작업이 전혀 없습니다.

---

아래 포스팅들을 참고하시면 조금 더 도움이 되실 것 같습니다.

ANSI SQL: LATERAL

PostgreSQL: 온라인 인덱스 작업(CONCURRENTLY)

---

오늘은 여기까지~