PostgreSQL에서는 EXPLAIN 명령을 사용하여 쿼리 플랜을 확인할 수 있습니다. 이 명령은 쿼리의 성능을 파악하려 할 때 매우 유용합니다. 쿼리가 해당 명령으로 시작하는 경우, SQL 쿼리 내에서 직접 사용할 수 있습니다:

EXPLAIN
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

GitLab.com에서 이를 실행하면 다음과 같은 출력이 표시됩니다:

Aggregate  (cost=922411.76..922411.77 rows=1 width=8)
  ->  Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))

EXPLAIN만 사용하면 PostgreSQL은 실제로 쿼리를 실행하지 않고, 대신 사용 가능한 통계를 기반으로 추정 실행 플랜을 생성합니다. 이는 실제 플랜과 상당히 다를 수 있습니다. 다행히도 PostgreSQL은 쿼리를 함께 실행하는 옵션을 제공합니다. 이를 위해 EXPLAIN 대신 EXPLAIN ANALYZE를 사용해야 합니다:

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

이 경우 다음이 출력됩니다:

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
Planning time: 2.861 ms
Execution time: 3428.596 ms

이 플랜은 이전과 상당히 다르며, 훨씬 더 많은 데이터를 포함합니다. 단계별로 살펴보겠습니다.

EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로, 데이터를 수정하거나 타임아웃이 발생할 수 있는 쿼리를 사용할 때는 주의가 필요합니다. 쿼리가 데이터를 수정하는 경우, 다음과 같이 자동으로 롤백되는 트랜잭션으로 감싸는 것을 고려하세요:

BEGIN;
EXPLAIN ANALYZE
DELETE FROM users WHERE id = 1;
ROLLBACK;

EXPLAIN 명령은 BUFFERS와 같은 추가 옵션도 허용합니다:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

이 경우 다음이 출력됩니다:

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  Buffers: shared hit=208846
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
        Buffers: shared hit=208846
Planning time: 2.861 ms
Execution time: 3428.596 ms

자세한 내용은 공식 EXPLAIN 문서와 EXPLAIN 사용 가이드를 참조하세요.

노드#

모든 쿼리 플랜은 노드로 구성됩니다. 노드는 중첩될 수 있으며, 안쪽에서 바깥쪽 순서로 실행됩니다. 즉, 가장 안쪽 노드가 외부 노드보다 먼저 실행됩니다. 이는 중첩 함수 호출로 생각하면 이해하기 쉽습니다. 결과를 풀어내면서 반환하는 방식입니다. 예를 들어, Aggregate로 시작하고, 이어서 Nested Loop, 그다음 Index Only scan이 오는 플랜은 다음 Ruby 코드로 생각할 수 있습니다:

aggregate(
  nested_loop(
    index_only_scan()
    index_only_scan()
  )
)

노드는 -> 뒤에 노드 유형이 표시됩니다. 예를 들어:

Aggregate  (cost=922411.76..922411.77 rows=1 width=8)
  ->  Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))

여기서 처음 실행되는 노드는 Seq scan on projects입니다. Filter:는 노드 결과에 적용되는 추가 필터입니다. 필터는 Ruby의 Array#select와 매우 유사합니다: 입력 행을 받아 필터를 적용한 후 새로운 행 목록을 생성합니다. 노드가 완료되면, 그 위의 Aggregate를 수행합니다.

중첩 노드는 다음과 같이 표시됩니다:

Aggregate  (cost=176.97..176.98 rows=1 width=8) (actual time=0.252..0.252 rows=1 loops=1)
  Buffers: shared hit=155
  ->  Nested Loop  (cost=0.86..176.75 rows=87 width=0) (actual time=0.035..0.249 rows=36 loops=1)
        Buffers: shared hit=155
        ->  Index Only Scan using users_pkey on users users_1  (cost=0.43..4.95 rows=87 width=4) (actual time=0.029..0.123 rows=36 loops=1)
              Index Cond: (id < 100)
              Heap Fetches: 0
        ->  Index Only Scan using users_pkey on users  (cost=0.43..1.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=36)
              Index Cond: (id = users_1.id)
              Heap Fetches: 0
Planning time: 2.585 ms
Execution time: 0.310 ms

여기서는 먼저 두 개의 별도 "Index Only" 스캔을 수행한 후, 이 두 스캔의 결과에 대해 "Nested Loop"를 수행합니다.

노드 통계#

플랜의 각 노드에는 비용, 생성된 행 수, 수행된 루프 수 등과 같은 관련 통계가 있습니다. 예를 들어:

Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)

여기서 비용이 0.00..908044.47 범위에 있음을 확인할 수 있으며 (잠시 후 설명함), 이 노드에서 총 5,746,914개의 행이 생성될 것으로 추정됩니다 (EXPLAIN을 사용하고 EXPLAIN ANALYZE를 사용하지 않기 때문에 추정값입니다). width 통계는 각 행의 예상 너비를 바이트 단위로 나타냅니다.

costs 필드는 노드의 비용을 나타냅니다. 비용은 쿼리 플래너의 비용 파라미터에 의해 결정된 임의의 단위로 측정됩니다. 비용에 영향을 미치는 요소는 seq_page_cost, cpu_tuple_cost 등 다양한 설정에 따라 달라집니다. 비용 필드의 형식은 다음과 같습니다:

STARTUP COST..TOTAL COST

시작 비용(startup cost)은 노드를 시작하는 데 드는 비용을 나타내며, 총 비용(total cost)은 전체 노드의 비용을 나타냅니다. 일반적으로 값이 클수록 노드 비용이 더 높습니다.

EXPLAIN ANALYZE를 사용하면 이러한 통계에 실제 소요 시간(밀리초)과 기타 런타임 통계(예: 실제 생성된 행 수)도 포함됩니다:

Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)

여기서 5,746,969개의 행이 반환될 것으로 추정했지만, 실제로는 5,746,940개의 행이 반환되었음을 확인할 수 있습니다. 또한 이 순차 스캔만으로도 2.98초가 걸렸음을 알 수 있습니다.

EXPLAIN (ANALYZE, BUFFERS)를 사용하면 필터에 의해 제거된 행 수, 사용된 버퍼 수 등의 정보도 얻을 수 있습니다. 예를 들어:

Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
  Filter: (visibility_level = ANY ('{0,20}'::integer[]))
  Rows Removed by Filter: 65677
  Buffers: shared hit=208846

여기서 필터가 65,677개의 행을 제거해야 하며, 208,846개의 버퍼를 사용한다는 것을 알 수 있습니다. PostgreSQL의 각 버퍼는 8 KB(8192바이트)이므로, 위 노드는 1.6 GB의 버퍼를 사용합니다. 매우 많은 양입니다!

일부 통계는 루프당 평균값이고 다른 통계는 총값임을 유의하세요:

예를 들어:

 ->  Index Scan using users_pkey on public.users  (cost=0.43..3.44 rows=1 width=1318) (actual time=0.025..0.025 rows=1 loops=888)
       Index Cond: (users.id = issues.author_id)
       Buffers: shared hit=3543 read=9
       I/O Timings: read=17.760 write=0.000

여기서 이 노드가 3552개의 버퍼(3543 + 9)를 사용했으며, 888개의 행을 반환했고(888 * 1), 실제 소요 시간은 22.2밀리초(888 * 0.025)였음을 알 수 있습니다. 총 소요 시간 중 17.76밀리초는 캐시에 없는 데이터를 가져오기 위해 디스크에서 읽는 데 사용되었습니다.

노드 유형#

다양한 노드 유형이 있으며, 여기서는 가장 일반적인 몇 가지만 다룹니다.

사용 가능한 모든 노드와 설명은 PostgreSQL 소스 파일 plannodes.h에서 확인할 수 있습니다. pgMustard의 EXPLAIN 문서도 노드와 필드에 대한 자세한 정보를 제공합니다.

Seq Scan#

데이터베이스 테이블(의 일부)에 대한 순차 스캔입니다. 이는 데이터베이스 테이블에서 Array#each를 사용하는 것과 같습니다. 순차 스캔은 많은 행을 검색할 때 상당히 느릴 수 있으므로, 대형 테이블에서는 사용을 피하는 것이 좋습니다.

Index Only Scan#

테이블에서 아무것도 가져올 필요가 없는 인덱스 스캔입니다. 특정 경우에는 인덱스 only 스캔에서도 테이블의 데이터를 가져와야 할 수 있으며, 이 경우 노드에 Heap Fetches: 통계가 포함됩니다.

Index Scan#

테이블에서 일부 데이터를 검색해야 하는 인덱스 스캔입니다.

Bitmap Index Scan과 Bitmap Heap Scan#

비트맵 스캔은 순차 스캔과 인덱스 스캔의 중간에 해당합니다. 이는 일반적으로 인덱스 스캔에서 너무 많은 데이터를 읽게 되지만, 순차 스캔을 수행하기에는 너무 적은 경우에 사용됩니다. 비트맵 스캔은 비트맵 인덱스를 사용하여 작업을 수행합니다.

PostgreSQL 소스 코드에서는 비트맵 스캔에 대해 다음과 같이 설명합니다:

Bitmap Index Scan은 잠재적인 튜플 위치의 비트맵을 제공합니다. 힙 자체에는 접근하지 않습니다. 비트맵은 상위의 Bitmap Heap Scan 노드에서 사용되며, 경우에 따라 중간의 Bitmap Or 및/또는 Bitmap And 노드를 통해 다른 Bitmap Index Scan의 결과와 결합될 수 있습니다.

Limit#

입력 행에 LIMIT를 적용합니다.

Sort#

ORDER BY 구문으로 지정된 대로 입력 행을 정렬합니다.

Nested Loop#

Nested Loop는 선행 노드가 생성하는 모든 행에 대해 자식 노드를 실행합니다. 예를 들어:

->  Nested Loop  (cost=0.86..176.75 rows=87 width=0) (actual time=0.035..0.249 rows=36 loops=1)
      Buffers: shared hit=155
      ->  Index Only Scan using users_pkey on users users_1  (cost=0.43..4.95 rows=87 width=4) (actual time=0.029..0.123 rows=36 loops=1)
            Index Cond: (id < 100)
            Heap Fetches: 0
      ->  Index Only Scan using users_pkey on users  (cost=0.43..1.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=36)
            Index Cond: (id = users_1.id)
            Heap Fetches: 0

여기서 첫 번째 자식 노드(Index Only Scan using users_pkey on users users_1)는 36개의 행을 생성하며, 한 번 실행됩니다(rows=36 loops=1). 다음 노드는 1개의 행을 생성하지만(rows=1), 36번 반복됩니다(loops=36). 이는 이전 노드가 36개의 행을 생성했기 때문입니다.

즉, 자식 노드들이 계속해서 많은 행을 생성하면 nested loop로 인해 쿼리가 빠르게 느려질 수 있습니다.

쿼리 최적화#

이제 쿼리를 최적화하는 방법을 살펴보겠습니다. 다음 쿼리를 예시로 사용해보겠습니다:

SELECT COUNT(*)
FROM users
WHERE twitter != '';

이 쿼리는 Twitter 프로필이 설정된 사용자 수를 계산합니다. EXPLAIN (ANALYZE, BUFFERS)를 사용하여 실행해보겠습니다:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM users
WHERE twitter != '';

이 경우 다음 플랜이 생성됩니다:

Aggregate  (cost=845110.21..845110.22 rows=1 width=8) (actual time=1271.157..1271.158 rows=1 loops=1)
  Buffers: shared hit=202662
  ->  Seq Scan on users  (cost=0.00..844969.99 rows=56087 width=0) (actual time=0.019..1265.883 rows=51833 loops=1)
        Filter: ((twitter)::text <> ''::text)
        Rows Removed by Filter: 2487813
        Buffers: shared hit=202662
Planning time: 0.390 ms
Execution time: 1271.180 ms

이 쿼리 플랜에서 다음을 확인할 수 있습니다:

• users 테이블에 대해 순차 스캔을 수행해야 합니다.

• 이 순차 스캔은 Filter를 사용하여 2,487,813개의 행을 필터링합니다.

• 202,622개의 버퍼를 사용하며, 이는 1.58 GB의 메모리에 해당합니다.

• 이 모든 작업을 수행하는 데 1.2초가 걸립니다.

사용자를 단순히 카운트하는 것치고는 상당히 비용이 많이 듭니다!

변경을 시작하기 전에, users 테이블에 활용할 수 있는 기존 인덱스가 있는지 확인해보겠습니다. psql 콘솔에서 \d users를 실행한 후 Indexes: 섹션으로 스크롤하면 이 정보를 얻을 수 있습니다:

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_confirmation_token" UNIQUE, btree (confirmation_token)
    "index_users_on_email" UNIQUE, btree (email)
    "index_users_on_reset_password_token" UNIQUE, btree (reset_password_token)
    "index_users_on_static_object_token" UNIQUE, btree (static_object_token)
    "index_users_on_unlock_token" UNIQUE, btree (unlock_token)
    "index_on_users_name_lower" btree (lower(name::text))
    "index_users_on_admin" btree (admin)
    "index_users_on_created_at" btree (created_at)
    "index_users_on_email_trigram" gin (email gin_trgm_ops)
    "index_users_on_feed_token" btree (feed_token)
    "index_users_on_group_view" btree (group_view)
    "index_users_on_incoming_email_token" btree (incoming_email_token)
    "index_users_on_managing_group_id" btree (managing_group_id)
    "index_users_on_name" btree (name)
    "index_users_on_name_trigram" gin (name gin_trgm_ops)
    "index_users_on_public_email" btree (public_email) WHERE public_email::text <> ''::text
    "index_users_on_state" btree (state)
    "index_users_on_state_and_user_type" btree (state, user_type)
    "index_users_on_unconfirmed_email" btree (unconfirmed_email) WHERE unconfirmed_email IS NOT NULL
    "index_users_on_user_type" btree (user_type)
    "index_users_on_username" btree (username)
    "index_users_on_username_trigram" gin (username gin_trgm_ops)
    "tmp_idx_on_user_id_where_bio_is_filled" btree (id) WHERE COALESCE(bio, ''::character varying)::text IS DISTINCT FROM ''::text

여기서 twitter 칼럼에 인덱스가 없음을 확인할 수 있으며, 이 경우 PostgreSQL은 순차 스캔을 수행해야 합니다. 다음 인덱스를 추가하여 이를 해결해보겠습니다:

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

EXPLAIN (ANALYZE, BUFFERS)를 사용하여 쿼리를 다시 실행하면 다음 플랜이 표시됩니다:

Aggregate  (cost=61002.82..61002.83 rows=1 width=8) (actual time=297.311..297.312 rows=1 loops=1)
  Buffers: shared hit=51854 dirtied=19
  ->  Index Only Scan using twitter_test on users  (cost=0.43..60873.13 rows=51877 width=0) (actual time=279.184..293.532 rows=51833 loops=1)
        Filter: ((twitter)::text <> ''::text)
        Rows Removed by Filter: 2487830
        Heap Fetches: 26037
        Buffers: shared hit=51854 dirtied=19
Planning time: 0.191 ms
Execution time: 297.334 ms

이제 1.2초 대신 300밀리초 미만으로 데이터를 가져올 수 있습니다. 그러나 여전히 51,854개의 버퍼를 사용하고 있으며, 이는 약 400 MB의 메모리에 해당합니다. 300밀리초도 이렇게 단순한 쿼리에는 상당히 느린 편입니다. 이 쿼리가 왜 아직도 비용이 많이 드는지 이해하기 위해 다음을 살펴보겠습니다:

Index Only Scan using twitter_test on users  (cost=0.43..60873.13 rows=51877 width=0) (actual time=279.184..293.532 rows=51833 loops=1)
  Filter: ((twitter)::text <> ''::text)
  Rows Removed by Filter: 2487830

인덱스에 대해 인덱스 only 스캔을 시작했지만, 여전히 2,487,830개의 행을 필터링하는 Filter가 적용되어 있습니다. 왜 그럴까요? 인덱스를 어떻게 생성했는지 살펴보겠습니다:

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

빈 문자열을 포함하여 twitter 칼럼의 모든 가능한 값을 인덱싱하도록 PostgreSQL에 지시했습니다. 쿼리에서는 WHERE twitter != ''를 사용합니다. 즉, 인덱스가 성능을 향상시키기는 하지만(순차 스캔이 필요 없으므로), 여전히 빈 문자열을 만날 수 있습니다. 따라서 PostgreSQL은 해당 값을 제거하기 위해 인덱스 결과에 Filter를 반드시 적용해야 합니다.

다행히도 "부분 인덱스(partial indexes)"를 사용하면 이를 더욱 개선할 수 있습니다. 부분 인덱스는 데이터를 인덱싱할 때 적용되는 WHERE 조건이 있는 인덱스입니다. 예를 들어:

CREATE INDEX CONCURRENTLY some_index ON users (email) WHERE id < 100

이 인덱스는 WHERE id < 100에 일치하는 행의 email 값만 인덱싱합니다. 부분 인덱스를 사용하여 Twitter 인덱스를 다음과 같이 변경할 수 있습니다:

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter) WHERE twitter != '';

생성 후 쿼리를 다시 실행하면 다음 플랜이 표시됩니다:

Aggregate  (cost=1608.26..1608.27 rows=1 width=8) (actual time=19.821..19.821 rows=1 loops=1)
  Buffers: shared hit=44036
  ->  Index Only Scan using twitter_test on users  (cost=0.41..1479.71 rows=51420 width=0) (actual time=0.023..15.514 rows=51833 loops=1)
        Heap Fetches: 1208
        Buffers: shared hit=44036
Planning time: 0.123 ms
Execution time: 19.848 ms

훨씬 좋아졌습니다! 이제 데이터를 가져오는 데 20밀리초밖에 걸리지 않으며, 버퍼도 약 344 MB만 사용합니다(원래 1.58 GB 대비). 이렇게 작동하는 이유는 이제 PostgreSQL이 Filter를 적용할 필요가 없기 때문입니다. 인덱스에는 비어 있지 않은 twitter 값만 포함되어 있기 때문입니다.

쿼리를 최적화하고 싶을 때마다 부분 인덱스를 추가해서는 안 된다는 점을 유념하세요. 모든 인덱스는 모든 쓰기 작업마다 업데이트되어야 하며, 인덱싱된 데이터의 양에 따라 상당한 공간이 필요할 수 있습니다. 따라서 먼저 재사용할 수 있는 기존 인덱스가 있는지 확인하세요. 없다면, 기존 인덱스를 약간 변경하여 기존 쿼리와 새 쿼리 모두에 적합하게 만들 수 있는지 확인하세요. 기존 인덱스를 어떤 방식으로도 사용할 수 없는 경우에만 새 인덱스를 추가하세요.

실행 플랜을 비교할 때 타이밍만을 중요한 지표로 삼지 마세요. 좋은 타이밍은 모든 최적화의 주요 목표이지만, 비교에 사용하기에는 너무 변동성이 클 수 있습니다(예: 캐시 상태에 많이 의존함). 쿼리를 최적화할 때는 일반적으로 처리하는 데이터의 양을 줄여야 합니다. 인덱스는 더 적은 페이지(버퍼)로 결과를 얻는 방법이므로, 최적화 시에는 사용된 버퍼 수(read와 hit)를 살펴보고 이 숫자를 줄이는 데 집중하세요. 타이밍 감소는 버퍼 수 감소의 결과입니다. Database Lab Engine은 플랜이 프로덕션과 구조적으로 동일함을 보장하지만(전체 버퍼 수도 프로덕션과 동일), 캐시 상태와 I/O 속도의 차이로 인해 타이밍이 달라질 수 있습니다.

최적화할 수 없는 쿼리#

쿼리 최적화 방법을 살펴봤으니, 이제 최적화하기 어려운 다른 쿼리를 살펴보겠습니다:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

EXPLAIN (ANALYZE, BUFFERS) 출력은 다음과 같습니다:

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  Buffers: shared hit=208846
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
        Buffers: shared hit=208846
Planning time: 2.861 ms
Execution time: 3428.596 ms

출력을 보면 다음 Filter가 있습니다:

Filter: (visibility_level = ANY ('{0,20}'::integer[]))
Rows Removed by Filter: 65677

필터에 의해 제거된 행의 수를 보면, 이 순차 스캔 + 필터를 인덱스 only 스캔으로 전환하기 위해 projects.visibility_level에 인덱스를 추가하고 싶은 생각이 들 수 있습니다.

안타깝게도 이렇게 해도 성능이 개선될 가능성은 낮습니다. 일부에서 믿는 것과 달리, 인덱스가 존재한다고 해서 PostgreSQL이 실제로 그것을 사용한다는 보장은 없습니다. 예를 들어, SELECT * FROM projects를 수행할 때는 인덱스를 사용하고 테이블에서 데이터를 가져오는 것보다 전체 테이블을 스캔하는 것이 훨씬 저렴합니다. 이러한 경우 PostgreSQL은 인덱스를 사용하지 않기로 결정할 수 있습니다.

둘째로, 쿼리가 수행하는 작업을 잠시 생각해보겠습니다: visibility level이 0 또는 20인 모든 프로젝트를 가져옵니다. 위 플랜에서 이로 인해 상당히 많은 행이 생성됨을 알 수 있지만 (5,745,940개), 전체 대비 얼마나 차지할까요? 다음 쿼리를 실행하여 알아보겠습니다:

SELECT visibility_level, count(*) AS amount
FROM projects
GROUP BY visibility_level
ORDER BY visibility_level ASC;

GitLab.com의 경우 다음이 생성됩니다:

 visibility_level | amount
------------------+---------
                0 | 5071325
               10 |   65678
               20 |  674801

전체 프로젝트 수는 5,811,804개이며, 그 중 5,746,126개가 level 0 또는 20입니다. 전체 테이블의 98%에 해당합니다!

따라서 무엇을 하든 이 쿼리는 전체 테이블의 98%를 검색합니다. 대부분의 시간이 정확히 그것을 수행하는 데 소비되므로, 이 쿼리를 개선하기 위해 할 수 있는 일은 사실상 실행하지 않는 것 외에는 없습니다.

여기서 중요한 점은, 일부에서는 순차 스캔이 보이면 즉시 인덱스를 추가하도록 권장하지만, 먼저 쿼리가 무엇을 하는지, 얼마나 많은 데이터를 검색하는지 등을 이해하는 것이 훨씬 더 중요하다는 것입니다. 결국, 이해하지 못하는 것을 최적화할 수는 없습니다.

카디널리티와 선택도#

앞에서 쿼리가 테이블 행의 98%를 검색해야 한다는 것을 확인했습니다. 데이터베이스에서 일반적으로 사용되는 두 가지 용어가 있습니다: 카디널리티(cardinality)와 선택도(selectivity)입니다. 카디널리티는 테이블의 특정 칼럼에 있는 고유값의 수를 나타냅니다.

선택도는 작업(예: 인덱스 스캔 또는 필터)에 의해 생성된 고유값의 수를 총 행 수에 대한 비율로 나타냅니다. 선택도가 높을수록 PostgreSQL이 인덱스를 사용할 가능성이 높습니다.

위 예에서는 고유값이 3개뿐입니다: 0, 10, 20. 즉, 카디널리티는 3입니다. 선택도 또한 매우 낮습니다: 0.0000003% (2 / 5,811,804). Filter가 두 개의 값(0과 20)만으로 필터링하기 때문입니다. 이렇게 낮은 선택도 값으로는 PostgreSQL이 인덱스를 사용하는 것이 가치 없다고 결정하는 것이 놀랍지 않습니다. 거의 고유한 행이 생성되지 않기 때문입니다.

쿼리 재작성#

위 쿼리는 있는 그대로는 최적화하기 어렵거나, 최적화가 가능하더라도 효과가 미미합니다. 하지만 쿼리의 목적을 약간 변경하면 어떨까요? visibility_level이 0 또는 20인 모든 프로젝트를 가져오는 대신, 사용자가 어떻게든 상호작용한 프로젝트를 가져온다면 어떨까요?

GitLab 16.7 이전에는 GitLab이 user_interacted_projects라는 테이블을 사용하여 사용자와 프로젝트 간의 상호작용을 추적했습니다. 이 테이블의 스키마는 다음과 같았습니다:

Table "public.user_interacted_projects"
   Column   |  Type   | Modifiers
------------+---------+-----------
 user_id    | integer | not null
 project_id | integer | not null
Indexes:
    "index_user_interacted_projects_on_project_id_and_user_id" UNIQUE, btree (project_id, user_id)
    "index_user_interacted_projects_on_user_id" btree (user_id)
Foreign-key constraints:
    "fk_rails_0894651f08" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    "fk_rails_722ceba4f7" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE

이 테이블을 프로젝트에 JOIN하여 특정 사용자의 프로젝트를 가져오도록 쿼리를 재작성해보겠습니다:

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM projects
INNER JOIN user_interacted_projects ON user_interacted_projects.project_id = projects.id
WHERE projects.visibility_level IN (0, 20)
AND user_interacted_projects.user_id = 1;

여기서 수행하는 작업은 다음과 같습니다:

• 프로젝트를 가져옵니다.

• user_interacted_projects를 INNER JOIN하여, user_interacted_projects에 해당하는 행이 있는 projects의 행만 남깁니다.

• visibility_level이 0 또는 20인 프로젝트로 제한하고, ID가 1인 사용자가 상호작용한 프로젝트로 제한합니다.

이 쿼리를 실행하면 다음 플랜이 표시됩니다:

 Aggregate  (cost=871.03..871.04 rows=1 width=8) (actual time=9.763..9.763 rows=1 loops=1)
   ->  Nested Loop  (cost=0.86..870.52 rows=203 width=0) (actual time=1.072..9.748 rows=143 loops=1)
         ->  Index Scan using index_user_interacted_projects_on_user_id on user_interacted_projects  (cost=0.43..160.71 rows=205 width=4) (actual time=0.939..2.508 rows=145 loops=1)
               Index Cond: (user_id = 1)
         ->  Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)
               Index Cond: (id = user_interacted_projects.project_id)
               Filter: (visibility_level = ANY ('{0,20}'::integer[]))
               Rows Removed by Filter: 0
 Planning time: 2.614 ms
 Execution time: 9.809 ms

이제 데이터를 가져오는 데 10밀리초 미만이 걸립니다. 또한 훨씬 적은 수의 프로젝트를 검색함을 알 수 있습니다:

Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)
  Index Cond: (id = user_interacted_projects.project_id)
  Filter: (visibility_level = ANY ('{0,20}'::integer[]))
  Rows Removed by Filter: 0

여기서 145번의 루프를 수행하며(loops=145), 각 루프에서 1개의 행이 생성됩니다(rows=1). 이는 이전보다 훨씬 적으며, 쿼리 성능이 크게 향상되었습니다!

플랜을 살펴보면 비용도 매우 낮음을 알 수 있습니다:

Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)

여기서 비용은 3.45에 불과하며, 이를 수행하는 데 7.25밀리초가 걸립니다(0.05 * 145). 다음 인덱스 스캔은 비용이 조금 더 높습니다:

Index Scan using index_user_interacted_projects_on_user_id on user_interacted_projects  (cost=0.43..160.71 rows=205 width=4) (actual time=0.939..2.508 rows=145 loops=1)

여기서 비용은 160.71(cost=0.43..160.71)이며, 약 2.5밀리초가 걸립니다 (actual time=.... 출력 기준).

가장 비용이 많이 드는 부분은 이 두 인덱스 스캔의 결과에 대해 수행되는 "Nested Loop"입니다:

Nested Loop  (cost=0.86..870.52 rows=203 width=0) (actual time=1.072..9.748 rows=143 loops=1)

여기서 203개의 행에 대해 870.52번의 디스크 페이지 페치를 수행하고, 9.748밀리초가 걸리며, 단일 루프에서 143개의 행이 생성됩니다.

여기서 핵심 교훈은, 때로는 쿼리를 더 좋게 만들기 위해 (일부를) 재작성해야 할 수 있다는 것입니다. 때로는 더 나은 성능을 위해 기능을 약간 변경해야 할 수도 있습니다.

나쁜 플랜의 특징#

이는 다소 대답하기 어려운 질문입니다. "나쁜"의 정의가 해결하려는 문제에 상대적이기 때문입니다. 그러나 대부분의 경우에 피해야 할 몇 가지 패턴이 있습니다:

• 대형 테이블에 대한 순차 스캔

• 많은 행을 제거하는 필터

• 많은 버퍼가 필요한 특정 단계 수행 (예: GitLab.com에서 512 MB 이상이 필요한 인덱스 스캔)

일반적인 가이드라인으로, 다음 조건을 충족하는 쿼리를 목표로 하세요:

• 10밀리초 이내에 완료됩니다. 요청당 SQL에 소비하는 목표 시간은 약 100밀리초이므로, 모든 쿼리는 가능한 한 빠르게 실행되어야 합니다.

• 워크로드에 비해 과도한 버퍼를 사용하지 않습니다. 예를 들어, 10개의 행을 검색하는 데 1 GB의 버퍼가 필요해서는 안 됩니다.

• 디스크 I/O 작업에 오랜 시간을 소비하지 않습니다. 이 데이터가 EXPLAIN ANALYZE 출력에 포함되려면 track_io_timing 설정이 활성화되어 있어야 합니다.

• 집계 없이 행을 검색할 때 LIMIT를 적용합니다(예: SELECT * FROM users).

• 특히 반환되는 행 수를 제한하는 LIMIT가 없는 경우, 너무 많은 행을 필터링하는 Filter를 사용하지 않습니다. 필터는 일반적으로 (부분) 인덱스를 추가하여 제거할 수 있습니다.

이것은 가이드라인이며 엄격한 요구 사항이 아닙니다. 서로 다른 요구 사항이 서로 다른 쿼리를 필요로 할 수 있기 때문입니다. 유일한 규칙은 EXPLAIN (ANALYZE, BUFFERS)와 다음과 같은 관련 도구를 사용하여 (가능하면 프로덕션 유사 데이터베이스를 사용하여) 항상 쿼리를 측정해야 한다는 것입니다:

• explain.depesz.com

• explain.dalibo.com/

쿼리 플랜 생성하기#

쿼리 플랜 출력을 얻는 방법은 몇 가지가 있습니다. 물론 psql 콘솔에서 EXPLAIN 쿼리를 직접 실행하거나, 아래의 다른 옵션 중 하나를 따를 수 있습니다.

Database Lab Engine#

GitLab 팀원은 Database Lab Engine과 그 동반 SQL 최적화 도구인 Joe Bot을 사용할 수 있습니다.

Database Lab Engine은 개발자에게 프로덕션 데이터베이스의 자체 클론을 제공하고, Joe Bot은 실행 플랜 탐색을 도와줍니다.

Joe Bot은 웹 인터페이스를 통해 사용할 수 있습니다.

Joe Bot을 사용하면 DDL 구문(인덱스, 테이블, 칼럼 생성 등)을 실행하고 SELECT, UPDATE, DELETE 구문에 대한 쿼리 플랜을 얻을 수 있습니다.

예를 들어, 프로덕션에 아직 없는 칼럼에 새 인덱스를 테스트하려면 다음과 같이 할 수 있습니다:

칼럼 생성:

exec ALTER TABLE projects ADD COLUMN last_at timestamp without time zone

인덱스 생성:

exec CREATE INDEX index_projects_last_activity ON projects (last_activity_at) WHERE last_activity_at IS NOT NULL

테이블 통계 업데이트를 위해 분석 실행:

exec ANALYZE projects

쿼리 플랜 가져오기:

explain SELECT * FROM projects WHERE last_activity_at < CURRENT_DATE

완료되면 변경 사항을 롤백할 수 있습니다:

reset

사용 가능한 옵션에 대한 자세한 정보를 보려면 다음을 실행하세요:

help

웹 인터페이스에는 다음 실행 플랜 시각화 도구가 포함되어 있습니다:

• Depesz

• PEV2

• FlameGraph

팁 & 트릭#

이제 데이터베이스 연결이 전체 세션 동안 유지되므로, exec set ...을 사용하여 세션 변수(예: enable_seqscan 또는 work_mem)를 설정할 수 있습니다. 이 설정은 재설정할 때까지 이후 모든 명령에 적용됩니다. 예를 들어, 다음 명령으로 병렬 쿼리를 비활성화할 수 있습니다:

exec SET max_parallel_workers_per_gather = 0

Rails 콘솔#

Rails 7.1 explain 메서드를 사용하여 Rails 콘솔에서 직접 쿼리 플랜을 생성할 수 있습니다:

pry(main)> Project.where('build_timeout > ?', 3600).explain(:analyze, :buffers, :verbose)
  Project Load (1.9ms)  SELECT "projects".* FROM "projects" WHERE (build_timeout > 3600)
  ↳ (pry):12
=> EXPLAIN for: SELECT "projects".* FROM "projects" WHERE (build_timeout > 3600)
Seq Scan on public.projects  (cost=0.00..2.17 rows=1 width=742) (actual time=0.040..0.041 rows=0 loops=1)
  Output: id, name, path, description, created_at, updated_at, creator_id, namespace_id, ...
   Filter: (projects.build_timeout > 3600)
   Rows Removed by Filter: 16
   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.230 ms
 Execution Time: 0.033 ms
(9 rows)

추가 자료#

쿼리 플랜 이해에 대한 보다 포괄적인 가이드는 Dalibo.org의 프레젠테이션에서 확인할 수 있습니다.

Depesz 블로그에도 쿼리 플랜에 전용된 훌륭한 섹션이 있습니다.