• 노드
• 노드 통계
• 노드 유형
  • Seq Scan
  • Index Only Scan
  • Index Scan
  • Bitmap Index Scan 및 Bitmap Heap Scan
  • Limit
  • Sort
  • Nested Loop
• 쿼리 최적화하기
• 최적화할 수 없는 쿼리
  • 기본값과 선택도
• 쿼리 다시 작성하기
• 나쁜 계획의 특징
• 쿼리 계획 생성
  • Database Lab Engine
    • 팁 및 기술
  • Rails console
• 더 많은 읽을거리

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으로 계획된 계획은 다음과 같은 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”을 수행합니다.

노드 통계

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

projects의 Seq Scan  (cost=0.00..908044.47 rows=5746914 width=0)

여기서 비용은 0.00..908044.47 범위에 있으며 (이에 대해 곧 다룰 것입니다), 우리는 이 노드에서 총 5,746,914개의 행이 생성될 것으로 추정합니다(EXPLAIN ANALYZE를 사용하는 것이 아니라면). 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의 각 버퍼는 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
      ->  users_pkey를 사용한 Index Only Scan 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
      ->  users_pkey를 사용한 Index Only Scan 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. 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)
    ...

여기서 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개의 버퍼를 사용하며, 약 400MB의 메모리를 사용합니다. 이는 매우 간단한 쿼리에 대해 꽤 느린 시간이 걸리는 것입니다. 이 쿼리가 여전히 비용이 많이 드는 이유를 이해하기 위해 다음을 살펴보겠습니다.

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

우리는 PostgreSQL에게 twitter 열의 모든 가능한 값을 인덱싱하도록 지시했습니다. 그러나 우리의 쿼리는 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이 더는 Filter를 적용할 필요가 없으므로 인덱스에 빈 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가 있습니다.

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개의 고유한 값(0, 10, 20)만 있습니다. 이는 기본값이 3임을 의미합니다. 선택도는 매우 낮습니다. 정확히 0.0000003% (2 / 5,811,804)인데요, 이는 우리의 Filter가 두 가지 값(0 및 20)을 사용하여 필터링하기 때문입니다. 이러한 낮은 선택도 값으로 인해 PostgreSQL은 인덱스 사용이 가치가 없다고 결정하는 것은 놀라운 일이 아닙니다. 거의 고유한 행이 생성되지 않기 때문입니다.

쿼리 다시 작성하기

따라서 위의 쿼리는 현재 상태에서 실제로는 크게 최적화할 수 없거나 적어도 그렇습니다. 그러나 우리가 그 목적을 약간 변경한다면 어떨까요? visibility_level이 0 또는 20인 모든 프로젝트를 검색하는 대신에 어떤 사용자가 어떤 방식으로 상호작용한 프로젝트를 검색하는 것으로 목적을 변경하면 어떨까요?

GitLab 16.7 이전에, GitLab은 사용자가 프로젝트와 상호작용을 추적하기 위해 user_interacted_projects라는 테이블을 사용했습니다. 이 테이블은 다음과 같은 스키마를 가졌습니다.

Table "public.user_interacted_projects"
   Column   |  Type   | Modifiers
------------+---------+-----------
 user_id    | integer | not null
 project_id | integer | not null
Indexes:
    "index_user_interacted_projects_on_project_id_and_user_id" UNIQUE, btree (project_id, user_id)
    "index_user_interacted_projects_on_user_id" btree (user_id)
Foreign-key constraints:
    "fk_rails_0894651f08" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    "fk_rails_722ceba4f7" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE

위의 테이블을 우리의 프로젝트에 JOIN하고 특정 사용자의 프로젝트를 얻는 쿼리로 변경해 봅시다.

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM projects
INNER JOIN user_interacted_projects ON user_interacted_projects.project_id = projects.id
WHERE projects.visibility_level IN (0, 20)
AND user_interacted_projects.user_id = 1;

우리가 하는 일은 다음과 같습니다.

1. 우리의 프로젝트를 가져옵니다.
2. 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이며, 이것을 수행하는 데 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), actual time=... 출력을 기반으로 약 2.5밀리초가 걸립니다.

가장 비싼 부분은 이 두 가지 인덱스 스캔의 결과에 영향을 미치는 “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에 대해 512MB 이상의 인덱스 스캔)가 필요한 특정 단계 수행

일반적인 지침으로, 다음을 목표로 쿼리를 작성하세요:

1. 10밀리초를 넘지 않는 쿼리. 요청당 SQL에서의 목표 시간은 약 100밀리초이므로 모든 쿼리는 가능한 한 빨라야 합니다.
2. 워크로드에 비해 과도한 버퍼를 사용하지 않는다. 예를 들어, 10개의 행을 검색하는 데 1GB의 버퍼가 필요하지 않아야 합니다.
3. 디스크 IO 작업을 수행하는 데 긴 시간을 소비하지 않는다. track_io_timing 설정은 EXPLAIN ANALYZE 출력에 이 데이터를 포함해야 합니다.
4. 집계하지 않고 행을 검색할 때 LIMIT를 적용합니다. 예를 들어, SELECT * FROM users와 같은 쿼리.
5. 특히 쿼리가 결과 행의 수를 제한하지 않는 경우 Filter를 사용하여 너무 많은 행을 필터링하지 않습니다. 일반적으로 (부분적인) 인덱스를 추가함으로써 필터를 제거할 수 있습니다.

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

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

쿼리 계획 생성

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

Database Lab Engine

GitLab 팀 멤버들은 Database Lab Engine을 사용할 수 있으며, 함께 제공되는 Joe Bot - SQL 최적화 도구를 사용할 수 있습니다.

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

웹 인터페이스에는 다음과 같은 실행 계획 visualizer가 포함되어 있습니다:

• Depesz
• PEV2
• FlameGraph

팁 및 기술

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

exec SET max_parallel_workers_per_gather = 0

Rails console

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