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입니다. 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”를 수행합니다.

노드 통계

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

projects의 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를 사용할 때 이러한 통계에는 실제 시간(밀리초) 및 다른 실행 시간 통계(예: 생성된 행의 실제 수)도 포함됩니다: 

projects의 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)를 사용하면 필터에 의해 제거된 행 수, 사용된 버퍼 수 등에 대한 정보를 얻을 수 있습니다. 예를 들어: 

projects의 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의 각 버퍼는 8KB(8192바이트)이므로, 위의 노드는 1.6GB의 버퍼를 사용합니다. 상당한 크기입니다!

일부 통계는 루프당 평균값이며, 다른 것들은 총값입니다:

필드 이름 값 유형
실제 총 시간 루프당 평균
실제 행 루프당 평균
Buffers Shared Hit 총값
Buffers Shared Read 총값
Buffers Shared Dirtied 총값
Buffers Shared Written 총값
I/O 읽기 시간 총값
I/O 읽기 쓰기 총값

예를 들어: 

 ->  public.users의 users_pkey 인덱스 스캔 (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를 사용하는 것과 유사합니다. 대량의 행을 검색할 때 순차 스캔은 상당히 느릴 수 있으므로 큰 테이블에 대해서는 피하는 것이 좋습니다.

인덱스만 검색

테이블에서 아무것도 가져오지 않고 인덱스를 검색합니다. 특정 경우에는 여전히 테이블에서 데이터를 가져올 수 있으며, 이 경우 노드에 Heap Fetches: 통계가 포함됩니다.

인덱스 검색

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

비트맵 인덱스 검색 및 비트맵 힙 검색

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

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

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

제한

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

정렬

ORDER BY 문을 사용하여 입력 행을 정렬합니다.

중첩 루프

중첩 루프는 앞선 노드가 생성한 각 행에 대해 해당 노드의 하위 노드를 실행합니다. 예를 들어: 

-> 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): 

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.58GB의 메모리를 사용합니다.
  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

이제 데이터를 가져오는 데 1.2초가 아닌 300밀리초 미만이 걸리게 되었습니다. 그러나 여전히 약 400MB의 메모리를 사용하는 51,854개의 버퍼를 사용합니다. 이는 매우 단순한 쿼리에 대해 매우 느린 시간입니다. 이 쿼리가 여전히 비용이 많이 드는 이유에 대해 이해하기 위해 다음을 살펴보겠습니다. 

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

우리는 그 자체로 인덱스만 스캔하는 것으로 시작하지만, 여전히 Filter를 적용하여 2,487,830개의 행을 필터링하는 것을 보실 수 있습니다. 그 이유는 무엇일까요? 이를 알아보기 위해 인덱스를 생성하는 방법을 살펴보겠습니다. 

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

우리는 ‘twitter’ 열의 모든 가능한 값을 색인화하도록 PostgreSQL에 지시했습니다. 그런데 이에 비해 쿼리는 WHERE twitter != ''를 사용합니다. 이는 인덱스가 순차 스캔을 수행할 필요가 없다는 의미이지만 여전히 빈 문자열을 사용할 수 있다는 것입니다. 이는 PostgreSQL이 그런 값 걸러내기 위해 인덱스 결과에 Filter를 적용해야 한다는 의미입니다.

다행히도 이를 개선하여 “부분 인덱스”를 사용하여 더 나아갈 수 있습니다. 부분 인덱스는 데이터를 색인화할 때 적용되는 WHERE 조건을 가진 인덱스입니다. 예를들어: 

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

이 인덱스는 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 값만을 포함하는 인덱스만 포함하고 더 이상 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%를 검색합니다. 대부분의 시간이 정확히 그렇게 보내지므로, 이 쿼리를 개선할 수 있는 방법은 실제로 많지는 않습니다. 단순히 이 쿼리를 아예 실행하지 않는 것 외에는요.

여기서 중요한 점은 몇몇 사람이 순차 스캔을 보자마자 인덱스를 추가하는 것을 권장할 수 있지만, 쿼리가 정확히 무엇을 하는지, 얼마나 많은 데이터를 검색하는지 등을 먼저 이해하는 것이 훨씬 중요하다는 것입입니다. 어차피 이해하지 못한 것은 최적화할 수 없습니다.

Cardinality(기수) 및 Selectivity(선택도)

앞서 쿼리에서 테이블의 행 98%를 가져와야 했다는 것을 보았습니다. 데이터베이스에서 사용되는 두 가지 용어가 있습니다: Cardinality(기수)와 Selectivity(선택도). Cardinality는 특정 테이블의 특정 열에 있는 고유한 값의 수를 나타냅니다.

선택도는 연산(예: 인덱스 스캔 또는 필터)에 의해 생성된 고유한 값의 수를 전체 행 수에 상대적으로 나타냅니다. 선택도가 높을수록 PostgreSQL이 인덱스를 사용할 가능성이 높습니다.

앞의 예제에서 고유한 값은 0, 10, 20으로 총 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하여, projects에서는 user_interacted_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

여기에서 데이터를 가져오는 데 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이며, 이를 145배하여 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밀리초가 걸렸습니다.

가장 비용이 많이 드는 부분은 이 두 인덱스 스캔의 결과에 따라 작용하는 “네스티드 루프”입니다: 

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밀리초가 걸려 1회 반목으로 143행이 생성되었습니다.

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

나쁜 계획의 특징

이 질문에 대답하는 것은 약간 어려운 문제입니다. 왜냐하면 “나쁜”의 정의는 해결하려는 문제에 상대적이기 때문입니다. 그러나 대부분의 경우에는 다음과 같은 패턴을 피하는 것이 좋습니다.

  • 대규모 테이블에서 순차 스캔
  • 많은 행을 제거하는 필터
  • **(GitLab.com의 인덱스 스캔과 같이 512MB 이상이 필요한 경우처럼) 많은 양의 버퍼를 필요로 하는 특정 단계를 수행하는 것.

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

  1. 최대 10밀리초 이상이 걸리지 않아야 합니다. 요청당 SQL에서의 목표 시간은 대략 100밀리초이므로 모든 쿼리는 가능한 한 빨라야 합니다.
  2. 워크로드에 대비하여 과도한 양의 버퍼를 사용하지 않아야 합니다. 예를 들어, 10개의 행을 가져오는 데 1GB의 버퍼가 필요하지 않아야 합니다.
  3. 디스크 IO 작업을 수행하는 데 오랜 시간이 걸리지 않아야 합니다. track_io_timing 설정은 EXPLAIN (ANALYZE, BUFFERS)의 출력에 이 데이터를 포함하도록 활성화되어 있어야 합니다.
  4. 반환된 행을 집계하지 않고 검색할 때 LIMIT를 적용해야 합니다. 예를 들어, SELECT * FROM users와 같은 경우입니다.
  5. 특히 쿼리에서 LIMIT를 사용하지 않는 상태에서 많은 행을 필터링하기 위해 Filter를 사용해서는 안 됩니다. (부분적인) 인덱스를 추가함으로써 필터를 보통 제거할 수 있습니다.

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

쿼리 계획 생성

쿼리 계획의 출력을 가져오는 몇 가지 방법이 있습니다. 물론 psql 콘솔에서 직접 EXPLAIN 쿼리를 실행하거나 다음에서 선택한 다른 옵션을 따를 수 있습니다.

데이터베이스 랩 엔진

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