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 사용 가이드를 참조하십시오.

노드

모든 쿼리 계획에는 노드가 포함됩니다. 노드는 중첩될 수 있으며 안쪽에서 바깥쪽으로 실행됩니다. 이는 중첩된 함수 호출처럼 생각할 수 있으며 결과를 풀어내는 것입니다. 예를 들어, Aggregate로 시작하는 계획은 Nested Loop을 거쳐 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개임을 추정합니다. width 통계는 추정된 각 행의 너비(바이트)를 설명합니다.

costs 필드는 노드의 비용을 지정합니다. 비용은 쿼리 플래너의 비용 매개변수에 의해 결정된 임의의 단위로 메트릭됩니다. 비용을 결정하는 것은 seq_page_cost, cpu_tuple_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의 각 버퍼는 8KB(8192바이트)이므로 이 노드는 1.6GB의 버퍼를 사용합니다. 상당한 양입니다!

일부 통계는 반복당 평균으로, 다른 통계는 총 값입니다:

필드 이름 값 종류
실제 총 시간 반복당 평균
실제 행수 반복당 평균
공유 버퍼 히트 총 값
공유 버퍼 읽기 총 값
공유 버퍼 더티드 총 값
공유 버퍼 쓰기 총 값
I/O 읽기 시간 총 값
I/O 읽기/쓰기 총 값

예를 들어: 

 ->  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의 소스 코드는 비트맵 스캔에 대해 다음과 같이 설명합니다:

비트맵 인덱스 스캔은 잠재적인 튜플 위치의 비트맵을 제공하며 힙 자체에는 액세스하지 않습니다. 이 비트맵은 조상 비트맵 힙 스캔 노드에 의해 사용됩니다. 다른 비트맵 인덱스 스캔의 결과와 결합하기 위해 중간 비트맵 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

여기서 첫 번째 자식 노드(users_pkey를 사용한 Index Only Scan 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)로 실행하면 다음과 같은 계획이 생성됩니다: 

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. 이 순차 검색으로 2,487,813개의 행을 Filter로 제거했습니다.
  3. 202,622개의 버퍼를 사용했으며, 이는 1.58GB에 해당합니다.
  4. 이 모든 작업에 1.2초가 소요되었습니다.

Twitter 사용자만 계산하는 것에 비하면 상당히 비용이 많이 드는 것으로 볼 수 있습니다!

어떤 변경을 하기 전에, 사용자 테이블에 이미 존재하는 인덱스가 있다면 해당 인덱스를 재사용할 수 있는지 확인해보겠습니다. 이러한 정보는 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밀리초가 걸립니다. 그러나 여전히 약 400MB에 해당하는 51,854개의 버퍼를 사용합니다. 이는 매우 단순한 쿼리에 대해 300밀리초가 상당히 느린 시간이기도 합니다. 결과가 더뎌요!

위 내용이 한글로 번역되었습니다. 원본 마크다운과 일부 어휘가 일치하나, 문맥상의 해석을 위해 원문을 살펴봐 주세요.

최적화할 수 없는 쿼리

이제 쿼리를 최적화하는 방법을 살펴보았으니, 최적화할 수 없는 다른 쿼리를 살펴보겠습니다. 

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이 인덱스를 사용하지 않을 수 있습니다.

두 번째로 쿼리가 하는 일을 잠시 생각해봅시다. 쿼리는 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이며, 이 중 0 또는 20 단계인 프로젝트는 5,746,126개입니다. 전체 테이블의 98%에 해당합니다!

따라서 이 쿼리는 전체 테이블의 98%를 검색하므로 어떤 일을 해도 이 쿼리를 최적화할 방법은 실제로 많지 않습니다. 단순히 이 쿼리를 아예 실행하지 않는 것 외에는요.

여기서 중요한 점은 순차 스캔을 볼 때 바로 인덱스를 추가하는 것을 권장하는 사람들이 있을 수 있지만, 쿼리가 하는 일, 검색한 데이터의 양 등을 먼저 이해하는 것이 _훨씬 더 중요_하다는 것입니다. 결국 이해하지 못하는 것을 최적화할 수는 없기 때문입니다.

Cardinality와 Selectivity

이전에 쿼리가 테이블에서 행의 98%를 검색해야 했던 것을 보았습니다. 데이터베이스에서 일반적으로 사용되는 두 용어인 Cardinality와 Selectivity가 있습니다. Cardinality는 테이블의 특정 열에 있는 고유한 값의 수를 나타냅니다.

Selectivity는 작업(예: 인덱스 스캔 또는 필터)에 의해 생성된 고유한 값의 수를 전체 행의 수에 대한 상대적인 비율을 의미합니다. Selectivity가 높을수록 PostgreSQL이 인덱스를 사용할 가능성이 높습니다.

위의 예에서 고유 값은 0, 10, 20 세 가지뿐입니다. 이는 Cardinality가 3이라는 것을 의미합니다. 그에 따른 Selectivity 역시 매우 낮습니다: 0.0000003% (2 / 5,811,804)이며, 우리의 Filter는 0과 20 두 가지 값을 이용하여 필터링하기 때문에 이런 낮은 Selectivity 값은 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. user_interacted_projects와 INNER JOIN하여 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밀리초밖에 걸리지 않았습니다. 우리는 훨씬 적은 프로젝트를 검색하고 있다는 것도 볼 수 있습니다.

어쨌든, 계획을 보면 우리의 비용이 매우 낮은 것을 볼 수 있습니다.

이로써 쿼리의 일부를 다시 작성하여 성능을 높이는 경우가 있습니다. 때로는 성능을 높이기 위해 기능을 약간 변경해야할 수도 있습니다.

나쁜 계획의 원인

“나쁜”의 정의는 해결하려는 문제에 상대적이기 때문에 이 질문에 대답하는 것이 약간 어렵습니다. 그러나 대부분의 경우에 피하는 것이 좋은 몇 가지 패턴이 있습니다. 예를 들면:

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

일반적으로 쿼리는 다음을 목표로 해야합니다:

  1. 10밀리초 이상 소요되지 않는 것입니다. 요청당 SQL에서 소요되는 시간은 약 100밀리초이므로 모든 쿼리는 가능한 빠르게 처리되어야 합니다.
  2. 워크로드에 비해 과도한 버퍼를 사용하지 않는 것입니다. 예를 들어, 열 개의 행을 검색하는 데 1GB의 버퍼가 필요하지 않아야 합니다.
  3. 디스크 입출력 작업을 오랜 시간 동안 실행하지 않는 것입니다. track_io_timing 설정에서 이 데이터를 EXPLAIN ANALYZE 출력에 포함하려면 이 설정이 활성화되어 있어야 합니다.
  4. 단일 행을 집계하지 않고 검색할 때 LIMIT를 적용하는 것입니다. 즉, SELECT * FROM users와 같은 경우입니다.
  5. 행을 반환하는 데 LIMIT를 사용하지 않고 많은 행을 걸러내기 위해 Filter를 사용하지 않는 것입니다. 필터는 일반적으로 (부분적인) 인덱스를 추가함으로써 제거할 수 있습니다.

이것들은 _지침(guidelines)_이며 각각의 요구에 따라 다양한 쿼리가 필요할 수 있기 때문에 엄격한 요구사항은 아닙니다. 유일한 _규칙(rule)_은 쿼리를 _항상 메트릭해야_한다는 것입니다(가능하면 프로덕션과 유사한 데이터베이스를 사용하여). 이 때 EXPLAIN (ANALYZE, BUFFERS) 및 다음과 같은 관련 도구를 사용해야 합니다:

쿼리 계획 생성

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

데이터베이스 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

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

팁 & 트릭

데이터베이스 연결은 이제 세션 동안 유지되므로 세션 변수(예: 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 블로그에는 쿼리 계획에 특별히 중점을 둔 좋은 섹션이 있습니다.