• 노드
• 노드 통계
• 노드 유형
  • Seq Scan
  • Index Only Scan
  • Index Scan
  • Bitmap Index Scan 및 Bitmap Heap scan
  • Limit
  • Sort
  • Nested Loop
• 쿼리 최적화
• 최적화할 수 없는 쿼리
  • 카디널리티와 선택성
• 쿼리 재작성
• 나쁜 계획이란 무엇인가
• 쿼리 계획 생성
  • 데이터베이스 랩 엔진
    • 팁 & 요령
  • Rails 콘솔
• 추가 읽기

EXPLAIN 계획 이해하기

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 문서 및 using 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

시작 비용은 노드 시작에 드는 비용을 나타내고, 총 비용은 전체 노드의 비용을 설명합니다. 일반적으로 값이 클수록 노드가 더 비쌉니다.

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

테이블에서 아무것도 가져오지 않는 인덱스 스캔입니다. 특정 경우에는 인덱스만 스캔하더라도 테이블에서 데이터를 가져와야 할 수 있으며, 이 경우 노드에는 Heap Fetches: 통계가 포함됩니다.

Index Scan

테이블에서 일부 데이터를 가져와야 하는 인덱스 스캔입니다.

Bitmap Index Scan 및 Bitmap Heap scan

비트맵 스캔은 순차 스캔과 인덱스 스캔 사이에 위치합니다. 일반적으로 인덱스 스캔에서 너무 많은 데이터를 읽을 때나 순차 스캔을 수행하기에는 너무 적은 데이터를 읽을 때 사용됩니다. 비트맵 스캔은 작업을 수행하기 위해 비트맵 인덱스를 사용합니다.

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

Bitmap Index Scan은 잠재적 튜플 위치의 비트를 전달하며; 실제 힙에는 접근하지 않습니다. 비트맵은 조상 Bitmap Heap Scan 노드에 의해 사용되며, 가능하면 중간 Bitmap And 및/또는 Bitmap Or 노드를 거쳐 다른 Bitmap Index Scan 결과와 결합됩니다.

Limit

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

Sort

ORDER BY 문에 따라 입력 행을 정렬합니다.

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개의 행을 생성했기 때문입니다.

즉, 중첩 루프는 다양한 자식 노드가 지속적으로 많은 행을 생성할 경우 쿼리를 빠르게 느리게 만들 수 있습니다.

쿼리 최적화

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

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

이 쿼리 계획에서 알 수 있는 내용은 다음과 같습니다:

1. 우리는 users 테이블에서 순차 스캔을 수행해야 합니다.

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

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

4. 이 모든 작업을 수행하는 데 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

이제 데이터를 가져오는 데 300밀리초가 소요되며, 이제는 1.2초가 아니라 약 400MB의 메모리를 사용하여 51,854개의 버퍼를 사용합니다. 그러나 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

우리는 인덱스에서 인덱스 전용 스캔을 시작하지만, 여전히 2,487,830개의 행을 필터링하는 Filter를 적용합니다. 그 이유는 인덱스를 생성할 때 어떻게 설정했는지 살펴봐야 합니다:

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

우리는 PostgreSQL에게 twitter 열의 가능한 모든 값을 인덱싱하도록 지시했으며, 빈 문자열도 포함되어 있습니다. 우리의 쿼리는 WHERE twitter != ''를 사용합니다. 이는 인덱스가 개선 사항을 주긴 하지만, 여전히 빈 문자열이 발생할 수 있음을 의미합니다. 이로 인해 PostgreSQL은 인덱스 결과에서 이 값을 제거하기 위해 Filter를 적용해야 합니다.

다행히도 “부분 인덱스”를 사용하여 이를 더욱 개선할 수 있습니다. 부분 인덱스는 데이터 인덱싱 시 적용되는 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밀리초만 소요되며, 약 344MB의 버퍼만 사용합니다(최초의 1.58GB 대신). 이 방법이 작동하는 이유는 이제 PostgreSQL이 빈 값이 없는 twitter 값만 있는 인덱스를 가지고 있기 때문에 Filter를 적용할 필요가 없기 때문입니다.

부분 인덱스를 최적화를 위해 매번 추가하지 말아야 한다는 점에 유의하세요. 모든 인덱스는 모든 쓰기 작업에 대해 업데이트해야 하며, 인덱싱된 데이터의 양에 따라 상당한 공간을 필요로 할 수 있습니다. 따라서 우선 재사용할 수 있는 기존 인덱스가 있는지 확인해야 합니다. 없으면 기존 인덱스 중에서 기존 쿼리와 새로운 쿼리 모두에 맞게 약간 변경할 수 있는지 확인하세요. 기존 인덱스를 사용할 수 있는 방법이 없다면 새 인덱스를 추가하세요.

실행 계획을 비교할 때 시간만 중요한 지표로 삼지 마세요. 좋은 시간은 최적화의 주요 목표이지만, 이는 비교에 사용하기에는 너무 변동성이 클 수 있습니다(예를 들어, 캐시의 상태에 따라 달라질 수 있습니다). 쿼리를 최적화할 때는 우리가 다루고 있는 데이터의 양을 줄이는 것이 필요합니다. 인덱스는 결과를 얻기 위해 페이지(버퍼)를 적게 처리하는 방법이므로 최적화하는 동안 사용된 버퍼 수(읽기 및 적중)를 확인하고 이러한 숫자를 줄이는 데 초점을 맞춰야 합니다. 줄어든 시간은 줄어든 버퍼 수의 결과입니다. 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: (visibility_level = ANY ('{0,20}'::integer[]))
Rows Removed by Filter: 65677

필터에 의해 제거된 행의 수를 보고, projects.visibility_level에 인덱스를 추가하여 이 순차 스캔 + 필터를 인덱스 전용 스캔으로 전환해 보려고 할 수도 있습니다.

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

두 번째로, 잠시만 우리의 쿼리가 무엇을 하는지 생각해 봅시다: 이는 가시성 수준이 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개가 수준 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;

여기서 우리가 하는 일은 다음과 같습니다:

1. 프로젝트를 가져옵니다.
2. INNER JOIN user_interacted_projects하여, user_interacted_projects에 해당하는 행이 있는 projects 행만 남깁니다.
3. 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 이상의 버퍼를 요구).

일반적인 지침으로는 다음과 같은 쿼리를 목표로 하세요:

1. 10밀리초를 초과하지 않아야 합니다. 요청당 SQL에 소요되는 목표 시간은 약 100밀리초이므로, 모든 쿼리는 최대한 빠를수록 좋습니다.

2. 작업량에 비례하여 과도한 수의 버퍼를 사용하지 않아야 합니다. 예를 들어, 10개의 행을 검색하는 데 1GB의 버퍼가 필요하지 않아야 합니다.

3. 디스크 IO 작업에서 오랜 시간을 소모하지 않아야 합니다. 이 데이터가 EXPLAIN ANALYZE 출력에 포함되려면 track_io_timing 설정이 활성화되어 있어야 합니다.

4. 행을 검색할 때 집계없이 LIMIT을 적용해야 합니다. 예: SELECT * FROM users.

5. 너무 많은 행을 걸러내기 위해 Filter를 사용하지 않아야 합니다. 특히 쿼리가 반환될 행의 수를 제한하기 위해 LIMIT을 사용하지 않는 경우에 그렇습니다. 필터는 일반적으로 (부분) 인덱스를 추가하여 제거할 수 있습니다.

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

• explain.depesz.com.
• explain.dalibo.com/.

쿼리 계획 생성

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

데이터베이스 랩 엔진

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 콘솔

activerecord-explain-analyze를 사용하면 Rails 콘솔에서 직접 쿼리 계획을 생성할 수 있습니다:

pry(main)> require 'activerecord-explain-analyze'
=> true
pry(main)> Project.where('build_timeout > ?', 3600).explain(analyze: true)
  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: 14
  Buffers: shared hit=2
Planning time: 0.411 ms
Execution time: 0.113 ms

추가 읽기

쿼리 계획을 이해하는 데 관한 더 포괄적인 가이드는 Dalibo.org의 발표에서 찾을 수 있습니다.

Depesz 블로그에도 쿼리 계획에 전념하는 좋은 섹션이 있습니다.