• 노드
• 노드 통계
• 노드 유형
  • Seq Scan
  • Index Only Scan
  • Index Scan
  • Bitmap Index Scan 및 Bitmap Heap Scan
  • Limit
  • Sort
  • Nested Loop
• 쿼리 최적화
• 최적화할 수 없는 쿼리
  • 카디널리티와 선택도
• 쿼리 재작성
• 나쁜 계획의 원인
• 쿼리 계획 생성
  • Database Lab Engine
    • 팁 & 트릭
  • 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 문서와 EXPLAIN 사용 가이드](https://www.postgresql.org/docs/current/using-explain.html)를 참조하세요.

노드

모든 쿼리 계획은 노드로 구성됩니다. 노드는 중첩될 수 있으며, 안쪽 노드부터 바깥쪽 노드까지 실행됩니다. 이는 중첩된 함수 호출로 생각할 수 있으며, 결과가 해제될 때까지 결과를 반환합니다. 예를 들어, 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  (cost=0.00..908044.47 rows=5746914 width=0)

여기에서 비용이 0.00..908044.47의 범위에 걸쳐있는 것을 볼 수 있습니다 (잠시 후에 이에 대해 다루겠습니다), 그리고 우리는 (우리가 EXPLAIN이 아닌 EXPLAIN ANALYZE를 사용하고 있기 때문에) 이 노드에서 생성될 행 수를 총 5,746,914개로 추정합니다. width 통계는 각 행의 추정폭을 바이트 단위로 설명합니다.

costs 필드는 노드가 얼마나 비싼지를 지정합니다. 비용은 쿼리 플래너의 비용 매개변수에 의해 결정된 임의의 단위로 메트릭됩니다. 비용에 영향을 주는 것은 seq_page_cost, cpu_tuple_cost 등과 같은 다양한 설정에 따라 다릅니다. 비용 필드의 형식은 다음과 같습니다:

시작 비용..총 비용

시작 비용은 노드를 시작하는 데 얼마나 비싼지를 나타내며, 총 비용은 전체 노드가 얼마나 비싼지를 나타냅니다. 일반적으로 값이 클수록, 노드가 더 비쌉니다.

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

프로젝트에서 Seq Scan  (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  (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.6GB의 버퍼를 사용합니다. 정말 많은 양이군요!

어떤 통계는 루프당 평균이고, 다른 것은 총 값을 나타냅니다:

예를 들어:

 ->  public.users에서 users_pkey를 사용한 Index Scan  (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의 소스 코드는 비트맵 스캔에 대해 다음과 같이 설명합니다:

비트맵 인덱스 스캔은 잠재적인 투플 위치의 비트맵을 제공하며 힙 자체에 액세스하지 않습니다. 비트맵은 조상 비트맵 힙 스캔 노드에 의해 사용되며, 다른 비트맵 인덱스 스캔의 결과와 중간 비트맵 And 및/또는 비트맵 Or 노드를 거쳐 결합될 수 있습니다.

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. 1.58GB에 해당하는 202,622개의 버퍼를 사용합니다.
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)
    ...
    (중략)
    ...
    "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밀리초가 걸립니다. 그러나 약 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개의 행이 필터링되고 있습니다. 왜 그럴까요? 이제 인덱스를 만드는 방법을 살펴보겠습니다.

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밀리초가 걸리며, 원래의 1.58GB가 아닌 약 344MB의 버퍼를 사용합니다. 이 작업이 잘 작동하는 이유는 이제 PostgreSQL이 빈 값이 아닌 twitter 값만 포함하는 인덱스만 가지고 있기 때문입니다.

쿼리를 최적화할 때 모든 인덱스에 부분 인덱스를 추가하는 것만 생각해서는 안 됩니다. 모든 인덱스는 모든 쓰기에 대해 업데이트되어야 하며, 인덱싱된 데이터의 양에 따라 상당한 공간을 필요로 할 수 있습니다. 결과적으로 먼저 재사용할 수 있는 기존 인덱스가 있는지 확인하세요. 만약 없다면, 기존 쿼리와 새 쿼리 모두에 맞는 기존 인덱스를 약간 변경할 수 있는지 확인하세요. 기존의 어떤 인덱스도 어떠한 방법으로도 사용할 수 없는 경우에만 새 인덱스를 추가하세요.

실행 계획을 비교할 때, 시간을 유일한 중요한 메트릭치로 삼으면 안 됩니다. 좋은 시간 메트릭은 모든 최적화의 주요 목표이지만, 비교를 위해 너무 불안정할 수 있습니다 (예: 캐시 상태에 매우 의존한다). 쿼리를 최적화할 때, 보통 다루어야 하는 데이터 양을 줄여야 합니다. 인덱스는 결과를 얻기 위해 사용되는 페이지(버퍼)의 수를 줄이는 방법입니다. 따라서 최적화 과정에서 사용된 버퍼 수(읽기 및 히트)를 확인하고 이러한 수를 줄이는 데 중점을 두세요. 감소된 시간은 감소된 버퍼 수의 결과입니다. 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%를 검색해야 했습니다. 데이터베이스에서 사용되는 두 가지 용어가 있습니다: 카디널리티와 선택도입니다. 카디널리티는 특정 열의 고유한 값의 수를 의미합니다.

선택도는 작업(예: 인덱스 스캔 또는 필터)에서 생성된 고유한 값의 수를 전체 행 수 대비로 나타낸 것입니다. 선택도가 높을수록 PostgreSQL이 인덱스를 사용할 가능성이 높아집니다.

위의 예에서 3개의 고유한 값만 있는데, 이는 카디널리티가 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

이제 우리의 쿼리를 이 테이블과 조인하여 특정 사용자의 프로젝트를 가져오는 방식으로 변경해 봅시다:

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. user_interacted_projects와 INNER JOIN합니다. 이는 user_interacted_projects에 해당하는 행만 projects에 남기는 것을 의미합니다.
3. 이를 가시성 레벨이 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

여기서 데이터를 가져오는 데 10ms 미만만 소요되었습니다. 또한 가져오는 프로젝트가 훨씬 적다는 것을 볼 수 있습니다:

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.25ms가 소요됩니다(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.5ms(실제 시간=…. 값을 기반으로)가 소요됩니다.

가장 비싼 부분은 이 두 개의 인덱스 스캔에 대한 결과에 작용하는 “중첩 루프”입니다:

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.748ms가 소요되었고, 한 번의 루프에서 143개의 행을 생성했습니다.

여기서 가장 중요한 점은 때로는 쿼리(의 일부)를 다시 작성해야 한다는 것입니다. 때로는 성능을 개선하기 위해 기능을 약간 변경해야 할 수도 있습니다.

나쁜 계획의 원인

이 질문에 대답하기가 다소 어렵습니다. 왜냐하면 “나쁜”이란 정의는 해결하려는 문제에 상대적이기 때문입니다. 그러나 대부분의 경우 피하는 것이 좋은 몇 가지 패턴이 있습니다. 그러한 패턴은 다음과 같습니다:

• 대규모 테이블의 순차 스캔
• 많은 행을 제거하는 필터
• 매우 많은 버퍼(예: GitLab.com에 대한 인덱스 스캔으로 512MB 이상이 필요한 경우)를 필요로하는 특정 단계 수행

일반적인 지침으로, 다음과 같은 쿼리를 목표로 합니다:

1. 10밀리초 이상 소요되지 않습니다. 요청당 SQL에서 소비되는 대상 시간은 대략 100밀리초이므로 모든 쿼리는 가능한 빨라야 합니다.
2. 워크로드에 상대적으로 과도한 수의 버퍼를 사용하지 않습니다. 예를 들어, 10개의 행을 검색하는 데 1GB의 버퍼가 필요하지 않아야 합니다.
3. 디스크 IO 작업을 수행하는 데 오랜 시간을 소비하지 않습니다. 이 데이터가 EXPLAIN ANALYZE의 결과에 포함되려면 track_io_timing 설정이 활성화되어 있어야 합니다.
4. SELECT * FROM 사용자와 같이 집계하지 않은 행을 검색할 때 LIMIT를 적용하지 않습니다.
5. 행을 검색할 때 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을 사용하면 SELECT, UPDATE 및 DELETE 문에 대한 DDL 문(인덱스, 테이블 및 열 생성과 같은)을 실행하고 쿼리 계획을 얻을 수 있습니다.

예를 들어, 프로덕션에 아직 존재하지 않는 열에 대한 새 인덱스를 테스트하려면 다음을 수행할 수 있습니다:

열 생성:

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

팁 & 트릭

데이터베이스 연결은 현재 세션 동안 유지됩니다. 따라서 세션 변수(예: enable_seqscan 또는 work_mem)에 대해 exec set ...을 사용할 수 있습니다. 이 설정은 리셋할 때까지 모든 후속 명령에 적용됩니다. 예를 들어 다음과 같이 병렬 쿼리를 비활성화할 수 있습니다:

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 블로그에는 쿼리 계획에 특화된 좋은 섹션이 있습니다.