Postgres EXPLAIN: 순차 스캔이 항상 성능 저하의 원인일까?

소개

관계형 데이터베이스의 세계에서 성능 최적화는 끊임없는 추구입니다. 개발자와 데이터베이스 관리자는 병목 현상을 식별하고 효율성을 개선하기 위해 쿼리 실행 계획을 자주 살펴봅니다. PostgreSQL의 EXPLAIN을 통해 드러나는 다양한 작업 중 '순차 스캔(Sequential Scan)'은 종종 비효율적인 '전체 테이블 스캔'으로 인식되어 즉각적인 우려를 불러일으키며, 어떻게든 제거해야 할 대상으로 여겨집니다. 그러나 이러한 광범위한 가정은 중요한 미묘한 차이를 간과합니다. 순차 스캔이 항상 나쁜 징조일까요? 반드시 인덱스가 필요한 성능 저하를 의미할까요? 이 글은 PostgreSQL의 EXPLAIN 출력의 복잡한 내용을, 특히 순차 스캔에 초점을 맞춰 이러한 단순한 관점에 이의를 제기하고 쿼리 실행에서 그 역할에 대한 보다 균형 잡힌 이해를 제공합니다.

순차 스캔의 본질

순차 스캔의 '나쁨'을 분석하기 전에 PostgreSQL 내에서 쿼리 계획 및 실행과 관련된 핵심 개념에 대한 공통된 이해를 확립해 보겠습니다.

주요 용어

• EXPLAIN: SQL 문에 대한 실행 계획을 표시하는 PostgreSQL 명령입니다. 데이터베이스 시스템이 쿼리를 어떻게 실행할 것인지, 수행할 작업과 수행 순서를 포함하여 보여줍니다.
• 순차 스캔 (Sequential Scan, Seq Scan): PostgreSQL이 시작부터 끝까지 테이블의 모든 행을 읽는 데이터베이스 작업입니다. 종종 '전체 테이블 스캔'이라고도 합니다.
• 인덱스 스캔 (Index Scan): PostgreSQL이 인덱스를 사용하여 테이블에서 특정 행을 찾는 데이터베이스 작업입니다. 전체 테이블을 읽는 대신 인덱스를 탐색하여 데이터를 빠르게 찾습니다.
• 비용 (Cost): EXPLAIN 출력에서 작업의 상대적인 예상 지출입니다. 시간 단위가 아니라 디스크 I/O, CPU 사용량, 메모리 액세스와 같은 요소를 기반으로 한 무차원 값입니다. 일반적으로 비용이 낮을수록 실행이 빠릅니다.
• 행 (Rows): 작업에서 반환되는 행의 예상 수입니다.
• 너비 (Width): 작업에서 반환되는 행의 예상 평균 너비(바이트)입니다.
• 버퍼 (Buffers): (EXPLAIN (ANALYZE, BUFFERS)) 실행 중에 히트/읽기/더티된 공유 및 로컬 버퍼 수를 보여주며 I/O 활동을 나타냅니다.

순차 스캔 작동 방식

본질적으로 PostgreSQL의 순차 스캔은 전체 테이블(또는 특정 쿼리에 대한 관련 부분)이 처리될 때까지 저장 장치에서 데이터 블록을 순차적으로 읽는 것을 포함합니다. 데이터베이스 시스템은 각 행을 검사하여 WHERE 절에 지정된 조건을 충족하는지 확인합니다. 행이 조건을 충족하면 결과 집합에 포함되거나 쿼리 계획의 다음 작업으로 전달됩니다.

순차 스캔이 그다지 나쁘지 않은 경우: 맥락 이해

순차 스캔에 대한 일반적인 반사적 반응은 즉시 인덱스를 생성하는 것입니다. 그러나 PostgreSQL의 쿼리 플래너는 정교합니다. 특정 상황에서 순차 스캔이 가장 효율적인 전략이 될 수 있음을 이해합니다.

실제 예를 들어 보겠습니다. 수백만 행이 있는 큰 products 테이블을 상상해 보세요.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 샘플 데이터 삽입 (수백만 행)
INSERT INTO products (name, description, price, category)
SELECT
    'Product ' || generate_series,
    'Description for product ' || generate_series,
    (random() * 1000)::numeric(10, 2),
    CASE (generate_series % 5)
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Books'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Home Goods'
        ELSE 'Food'
    END
FROM generate_series(1, 5000000); -- 5백만 행

이제 EXPLAIN으로 몇 가지 쿼리를 분석해 보겠습니다.

시나리오 1: 대량의 행 검색

쿼리가 테이블의 상당 부분을 검색해야 하는 경우 순차 스캔이 인덱스 스캔보다 빠를 수 있습니다. 왜 그럴까요? 인덱스 스캔에는 두 단계가 포함됩니다. 첫째, 인덱스를 탐색하여 행 포인터(TID)를 찾고, 둘째, 해당 포인터를 사용하여 테이블에서 실제 행 데이터를 검색합니다. 테이블을 여기저기 점프하는 이러한 '무작위 I/O'는 많은 행이 필요한 경우 테이블을 연속적으로 읽는 것보다 더 비용이 많이 들 수 있습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE price > 10;

가격이 10보다 큰 제품 비율이 많으면(샘플 데이터에서 임의 가격이 최대 1000이므로 매우 가능성이 높음), PostgreSQL은 순차 스캔을 선택할 수 있습니다. EXPLAIN 출력은 다음과 같이 표시될 것입니다.

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on products  (cost=0.00..109375.00 rows=4999999 width=472) (actual time=0.046..1237.498 rows=4999999 loops=1) 
   Filter: (price > '10'::numeric)
   Rows Removed by Filter: 0
   Buffers: shared hit=43750 read=0 dirtied=0 written=0
 Planning Time: 0.160 ms
 Execution Time: 1251.234 ms

여기서 플래너는 거의 모든 행이 조건을 충족할 것이라고 올바르게 추정했습니다. 이 경우 price에 인덱스를 생성하면 인덱스를 사용하는 오버헤드(인덱스 페이지 읽기, 그런 다음 무작위로 데이터 페이지 가져오기)가 건너뛰는 행의 이점보다 크기 때문에 쿼리가 더 느려질 가능성이 높습니다.

시나리오 2: 소규모 테이블

매우 작은 테이블의 경우 인덱스를 읽고 탐색하는 오버헤드가 단순 순차 스캔 비용을 초과할 수 있습니다. 쿼리 플래너는 이를 인식할 만큼 똑똑합니다.

CREATE TABLE small_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);
INSERT INTO small_table (data) SELECT 'Some data ' || generate_series FROM generate_series(1, 100);

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM small_table WHERE id = 50;

id는 기본 키(따라서 인덱싱됨)이지만, 전체 테이블을 읽는 것보다 빠르다고 판단하면 플래너는 여전히 작은 테이블의 순차 스캔을 선택할 수 있습니다. 그러나 PRIMARY KEY의 단일 정확한 일치에 대해서는 일반적으로 Index Scan이 선호되지만, 작은 테이블의 인덱싱되지 않은 열에 대한 필터에는 이 원칙이 더 일반적으로 적용됩니다.

시나리오 3: 데이터의 지역성과 캐시 효율성

순차 스캔은 종종 현대 하드웨어 아키텍처, 특히 CPU 캐싱 및 디스크 사전 가져오기의 이점을 얻습니다. 데이터가 순차적으로 읽히면 디스크의 연속 블록에 있을 가능성이 높으며, 이를 통해 운영 체제와 스토리지 장치에서 데이터를 사전 가져올 수 있습니다. 이는 매우 빠른 데이터 전송 속도로 이어질 수 있습니다. 데이터에 자주 액세스하는 경우 이미 운영 체제 파일 시스템 캐시 또는 데이터베이스 공유 버퍼에 있을 수 있으므로 '디스크 읽기'가 효과적으로 '메모리 읽기'가 됩니다.

시나리오 4: 유용한 인덱스의 부족

쿼리의 WHERE 절을 효율적으로 충족할 수 있는 인덱스가 없으면 순차 스캔이 유일한 옵션입니다. 이러한 경우 인덱스가 성능을 향상시킬 수 있지만, 순차 스캔 자체가 '나쁜' 것은 아닙니다. 단순히 필요한 폴백(fallback)일 뿐입니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name, price FROM products WHERE description ILIKE '%amazing%';

전체 텍스트 검색 인덱스(다른 종류의 인덱스)가 없는 한, 긴 description 필드 내에서 ILIKE를 사용하여 텍스트를 검색하는 것은 B-트리 인덱스가 임의 텍스트 내의 패턴 일치를 위해 설계되지 않았으므로 거의 확실하게 순차 스캔으로 이어질 것입니다.

순차 스캔에 대해 우려해야 할 때

항상 나쁜 것은 아니지만, 순차 스캔은 특히 다음과 같은 상황에서 종종 놓친 최적화 기회를 나타냅니다.

1. 대형 테이블에서 소량의 행 필터링: 쿼리가 대형 테이블에서 소수의 행만 반환하고 99.9%의 행을 필터링하는 경우, 인덱스가 거의 확실하게 올바른 선택입니다. 몇 개의 행을 찾기 위해 수백만 행을 읽는 비용은 엄청납니다.
2. 인덱스 없는 Order By / Group By: 쿼리에 인덱싱되지 않은 열에 대한 ORDER BY 또는 GROUP BY 절이 있고 쿼리가 순차 스캔을 수행하는 경우, PostgreSQL은 메모리 또는 디스크에서 전체 결과 집합을 정렬해야 할 수 있습니다( 'filesort'). 대규모 데이터 세트의 경우 매우 비용이 많이 듭니다. 인덱스는 사전 정렬된 데이터를 제공하여 이 추가 단계를 피할 수 있습니다.
3. 대형 테이블에서의 높은 I/O: EXPLAIN (ANALYZE, BUFFERS)가 적은 행을 반환하는 대형 테이블의 순차 스캔에 대해 높은 수의 read 버퍼를 표시하면 불필요하게 많은 데이터가 디스크에서 로드되고 있음을 나타냅니다. 이는 인덱싱의 주요 후보입니다.

결론

종종 단순하게 '나쁘다'고 표시되는 순차 스캔은 실제로 PostgreSQL에서 매우 효과적이고 때로는 피할 수 없는 작업입니다. 핵심은 맥락입니다. 테이블 데이터의 상당 부분을 검색하는 쿼리, 작은 테이블, 또는 캐시 효율성이 높은 경우 순차 스캔은 인덱스 스캔보다 더 나은 성능을 제공할 수 있습니다. 그러나 거대한 테이블의 아주 작은 하위 집합을 필터링할 때, 특히 ORDER BY 또는 GROUP BY를 사용할 때, 또는 과도한 디스크 I/O를 경험할 때 순차 스캔은 인덱스 최적화 기회를 나타낼 가능성이 높습니다. EXPLAIN과 기본 데이터 분포에 대한 숙련된 이해는 정보에 입각한 성능 튜닝 결정을 내리는 데 필수적입니다. 순차 스캔을 맹목적으로 두려워하지 마십시오. 그 목적을 이해하고 진정으로 중요한 곳에서 최적화하십시오.