InfoGrab DocsInfoGrab Docs

EXPLAIN 플랜 이해하기

요약

PostgreSQL에서는 EXPLAIN 명령으로 쿼리 플랜을 얻을 수 있습니다. GitLab.com에서 이를 실행하면 다음과 같은 출력이 표시됩니다: 오직 EXPLAIN만 사용하면 PostgreSQL은 쿼리를 실제로 실행하지 않고, 사용 가능한 통계를 기반으로 추정 실행 플랜을 생성합니다.

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 ANALYZE가 아니라 EXPLAIN을 사용하므로 추정값입니다). 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를 사용합니다. 매우 많은 양입니다.

일부 통계는 루프당 평균값이고 다른 통계는 총값이라는 점에 유의합니다:

필드명 값 유형
Actual Total Time 루프당 평균
Actual Rows 루프당 평균
Buffers Shared Hit 총값
Buffers Shared Read 총값
Buffers Shared Dirtied 총값
Buffers Shared Written 총값
I/O Read Time 총값
I/O Write Time 총값

예를 들면 다음과 같습니다:

 ->  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#

테이블에서 아무것도 가져오지 않아도 되는 인덱스 스캔입니다. 경우에 따라서는 Index Only Scan도 테이블에서 데이터를 가져올 수 있습니다. 이때는 노드에 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

이 쿼리 플랜에서 다음을 알 수 있습니다:

  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

이제 데이터를 가져오는 데 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

인덱스에 대해 Index Only Scan으로 시작하지만, 그럼에도 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밀리초밖에 걸리지 않고, 버퍼도 (원래 1.58 GB 대신) 약 344 MB만 사용합니다. 이렇게 되는 이유는 인덱스에 비어 있지 않은 twitter 값만 들어 있어서 PostgreSQL이 더 이상 Filter를 적용할 필요가 없기 때문입니다.

쿼리를 최적화하고 싶을 때마다 부분 인덱스를 추가하면 된다고 생각해서는 안 됩니다. 모든 인덱스는 쓰기마다 업데이트되어야 하고, 인덱싱된 데이터의 양에 따라 상당한 공간을 차지할 수 있습니다. 그러므로 먼저 재사용할 수 있는 기존 인덱스가 있는지 확인합니다. 없다면 기존 인덱스를 조금 바꿔서 기존 쿼리와 새 쿼리 모두에 맞출 수 있는지 확인합니다. 기존 인덱스를 어떤 방식으로도 사용할 수 없을 때만 새 인덱스를 추가합니다.

실행 플랜을 비교할 때 타이밍만을 유일하게 중요한 지표로 삼지 않습니다. 좋은 타이밍은 모든 최적화의 주된 목표이지만, 비교에 쓰기에는 변동이 너무 클 수 있습니다(예를 들어 캐시 상태에 크게 좌우됩니다). 쿼리를 최적화할 때는 보통 다루는 데이터의 양을 줄여야 합니다. 인덱스는 더 적은 페이지(버퍼)로 결과를 얻는 수단이므로, 최적화하는 동안 사용된 버퍼 수(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

필터가 제거한 행 수를 보면, 이 순차 스캔 + 필터를 어떻게든 index-only scan으로 바꾸려고 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이 인덱스를 사용할 수 있는 가능성이 커집니다.

위 예에서는 고유값이 0, 10, 20 세 개뿐입니다. 즉 카디널리티는 3입니다. 이어서 선택도 역시 매우 낮습니다. Filter가 두 개의 값(0과 20)만으로 필터링하므로 0.0000003%(2 / 5,811,804)입니다. 이처럼 선택도 값이 낮으면 고유한 행이 거의 생성되지 않으므로, 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합니다. 그러면 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개를 가져오는 데 버퍼 1 GB가 필요해서는 안 됩니다.
  3. 디스크 IO 작업에 오랜 시간을 쓰지 않습니다. 이 데이터가 EXPLAIN ANALYZE 출력에 포함되려면 track_io_timing 설정이 활성화되어 있어야 합니다.
  4. SELECT * FROM users처럼 행을 집계하지 않고 가져올 때는 LIMIT를 적용합니다.
  5. 너무 많은 행을 걸러내는 데 Filter를 사용하지 않습니다. 특히 반환되는 행 수를 제한하는 LIMIT를 쿼리가 사용하지 않는 경우에 그렇습니다. 필터는 보통 (부분) 인덱스를 추가해 제거할 수 있습니다.

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

쿼리 플랜 생성하기#

쿼리 플랜의 출력을 얻는 방법은 몇 가지 있습니다. 물론 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

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

팁과 트릭#

이제 데이터베이스 연결이 세션 전체에 걸쳐 유지되므로, 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)

추가 자료#

플래너가 IN (...) 조건자를 잘못 추정해 Seq Scan이 나타나는 경우, 값마다 인덱스 탐색을 한 번씩 강제하는 재작성 방법은 LATERAL 조인으로 인덱스 탐색 강제하기를 참고합니다.

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

Depesz 블로그에도 쿼리 플랜을 다루는 좋은 섹션이 있습니다.

EXPLAIN 플랜 이해하기

GitLab v19.4
원문 보기

요약

PostgreSQL에서는 EXPLAIN 명령으로 쿼리 플랜을 얻을 수 있습니다. GitLab.com에서 이를 실행하면 다음과 같은 출력이 표시됩니다: 오직 EXPLAIN만 사용하면 PostgreSQL은 쿼리를 실제로 실행하지 않고, 사용 가능한 통계를 기반으로 추정 실행 플랜을 생성합니다.

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 ANALYZE가 아니라 EXPLAIN을 사용하므로 추정값입니다). 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를 사용합니다. 매우 많은 양입니다.

일부 통계는 루프당 평균값이고 다른 통계는 총값이라는 점에 유의합니다:

필드명 값 유형
Actual Total Time 루프당 평균
Actual Rows 루프당 평균
Buffers Shared Hit 총값
Buffers Shared Read 총값
Buffers Shared Dirtied 총값
Buffers Shared Written 총값
I/O Read Time 총값
I/O Write Time 총값

예를 들면 다음과 같습니다:

 ->  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#

테이블에서 아무것도 가져오지 않아도 되는 인덱스 스캔입니다. 경우에 따라서는 Index Only Scan도 테이블에서 데이터를 가져올 수 있습니다. 이때는 노드에 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

이 쿼리 플랜에서 다음을 알 수 있습니다:

  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

이제 데이터를 가져오는 데 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

인덱스에 대해 Index Only Scan으로 시작하지만, 그럼에도 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밀리초밖에 걸리지 않고, 버퍼도 (원래 1.58 GB 대신) 약 344 MB만 사용합니다. 이렇게 되는 이유는 인덱스에 비어 있지 않은 twitter 값만 들어 있어서 PostgreSQL이 더 이상 Filter를 적용할 필요가 없기 때문입니다.

쿼리를 최적화하고 싶을 때마다 부분 인덱스를 추가하면 된다고 생각해서는 안 됩니다. 모든 인덱스는 쓰기마다 업데이트되어야 하고, 인덱싱된 데이터의 양에 따라 상당한 공간을 차지할 수 있습니다. 그러므로 먼저 재사용할 수 있는 기존 인덱스가 있는지 확인합니다. 없다면 기존 인덱스를 조금 바꿔서 기존 쿼리와 새 쿼리 모두에 맞출 수 있는지 확인합니다. 기존 인덱스를 어떤 방식으로도 사용할 수 없을 때만 새 인덱스를 추가합니다.

실행 플랜을 비교할 때 타이밍만을 유일하게 중요한 지표로 삼지 않습니다. 좋은 타이밍은 모든 최적화의 주된 목표이지만, 비교에 쓰기에는 변동이 너무 클 수 있습니다(예를 들어 캐시 상태에 크게 좌우됩니다). 쿼리를 최적화할 때는 보통 다루는 데이터의 양을 줄여야 합니다. 인덱스는 더 적은 페이지(버퍼)로 결과를 얻는 수단이므로, 최적화하는 동안 사용된 버퍼 수(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

필터가 제거한 행 수를 보면, 이 순차 스캔 + 필터를 어떻게든 index-only scan으로 바꾸려고 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이 인덱스를 사용할 수 있는 가능성이 커집니다.

위 예에서는 고유값이 0, 10, 20 세 개뿐입니다. 즉 카디널리티는 3입니다. 이어서 선택도 역시 매우 낮습니다. Filter가 두 개의 값(0과 20)만으로 필터링하므로 0.0000003%(2 / 5,811,804)입니다. 이처럼 선택도 값이 낮으면 고유한 행이 거의 생성되지 않으므로, 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합니다. 그러면 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개를 가져오는 데 버퍼 1 GB가 필요해서는 안 됩니다.
  3. 디스크 IO 작업에 오랜 시간을 쓰지 않습니다. 이 데이터가 EXPLAIN ANALYZE 출력에 포함되려면 track_io_timing 설정이 활성화되어 있어야 합니다.
  4. SELECT * FROM users처럼 행을 집계하지 않고 가져올 때는 LIMIT를 적용합니다.
  5. 너무 많은 행을 걸러내는 데 Filter를 사용하지 않습니다. 특히 반환되는 행 수를 제한하는 LIMIT를 쿼리가 사용하지 않는 경우에 그렇습니다. 필터는 보통 (부분) 인덱스를 추가해 제거할 수 있습니다.

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

쿼리 플랜 생성하기#

쿼리 플랜의 출력을 얻는 방법은 몇 가지 있습니다. 물론 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

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

팁과 트릭#

이제 데이터베이스 연결이 세션 전체에 걸쳐 유지되므로, 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)

추가 자료#

플래너가 IN (...) 조건자를 잘못 추정해 Seq Scan이 나타나는 경우, 값마다 인덱스 탐색을 한 번씩 강제하는 재작성 방법은 LATERAL 조인으로 인덱스 탐색 강제하기를 참고합니다.

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

Depesz 블로그에도 쿼리 플랜을 다루는 좋은 섹션이 있습니다.