Source: Dev.to

How Postgres Indexing Is More Efficient Than MySQL

요약

PostgreSQL은 다양한 인덱스 유형과 고급 기능을 제공하여 복잡한 쿼리를 더 빠르게 처리할 수 있습니다. MySQL은 기본적으로 B‑tree 인덱스에 의존하지만, PostgreSQL은 GIN, GiST, BRIN, hash 등 여러 인덱스 메커니즘을 지원합니다. 또한 partial indexes, expression indexes, covering indexes와 같은 기능을 통해 불필요한 스캔을 최소화하고 디스크 I/O를 줄일 수 있습니다.

주요 차이점

1. 다양한 인덱스 타입

GIN (Generalized Inverted Index)

• 용도: 배열, JSONB, full‑text search 등 다중 값 컬럼에 최적화.
• 예시

CREATE INDEX idx_jsonb_data ON documents USING GIN (data);

GiST (Generalized Search Tree)

• 용도: 범위 검색, 지리공간 데이터, cube, hstore 등.
• 예시

CREATE INDEX idx_geom ON locations USING GiST (geom);

BRIN (Block Range INdex)

• 용도: 매우 큰 테이블에서 물리적 순서가 연관된 컬럼에 적합.
• 예시

CREATE INDEX idx_timestamp_brn ON logs USING BRIN (created_at);

2. 부분 인덱스 (Partial Index)

MySQL에서는 전체 테이블에 인덱스를 생성해야 하지만, PostgreSQL에서는 조건을 지정해 필요한 행만 인덱싱할 수 있습니다.

CREATE INDEX idx_active_users ON users (email) WHERE active = true;

이렇게 하면 active = false인 행은 인덱스에 포함되지 않아 인덱스 크기가 크게 감소하고, 쿼리 플래너가 더 효율적인 경로를 선택합니다.

3. 표현식 인덱스 (Expression Index)

컬럼 자체가 아니라 컬럼에 적용된 함수 결과에 인덱스를 만들 수 있습니다.

CREATE INDEX idx_lower_username ON users (LOWER(username));

이 인덱스는 WHERE LOWER(username) = 'john'와 같은 조건에 대해 시작부터 인덱스를 활용하므로, MySQL에서 LOWER() 함수를 사용하면 전체 테이블 스캔이 발생하는 문제를 피할 수 있습니다.

4. 커버링 인덱스 (Covering Index)

PostgreSQL 11부터 INCLUDE 절을 사용해 인덱스에 추가 컬럼을 저장할 수 있습니다. 이렇게 하면 Index Only Scan이 가능해져서 힙 페이지를 읽지 않아도 됩니다.

CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (order_date, total_amount);

위 인덱스를 사용하면 SELECT order_date, total_amount FROM orders WHERE customer_id = 123; 쿼리가 힙 접근 없이 인덱스만으로 처리됩니다.

5. 인덱스 스캔 최적화

Bitmap Index Scan

여러 인덱스를 결합해야 할 때 PostgreSQL은 Bitmap Index Scan을 수행합니다. 이는 각각의 인덱스를 비트맵 형태로 결합한 뒤 한 번에 힙 페이지를 읽어 효율성을 높입니다.

SELECT * FROM sales
WHERE region = 'APAC' AND product_category = 'Electronics';

위 쿼리는 region과 product_category에 각각 만든 B‑tree 인덱스를 비트맵으로 결합해 빠르게 결과를 반환합니다.

Index Only Scan

인덱스에 모든 필요한 컬럼이 포함돼 있으면 힙(테이블) 페이지를 전혀 읽지 않습니다. 이는 특히 읽기 전용 워크로드에서 큰 성능 향상을 제공합니다.

EXPLAIN ANALYZE
SELECT id, created_at FROM events WHERE event_type = 'login';

event_type에 인덱스가 있고 id, created_at이 INCLUDE된 경우, 실행 계획에 Index Only Scan이 표시됩니다.

6. 통계와 자동 튜닝

PostgreSQL은 ANALYZE를 통해 컬럼별 히스토그램, NDV(Unique 값 개수) 등을 수집합니다. 이 정보는 옵티마이저가 가장 적합한 인덱스와 조인 순서를 선택하도록 돕습니다.

VACUUM ANALYZE;

MySQL도 ANALYZE TABLE을 제공하지만, 수집되는 통계가 제한적이며 자동 업데이트가 덜 빈번합니다.

결론

• 다양한 인덱스 타입: GIN, GiST, BRIN 등 특수 목적에 맞는 인덱스를 선택할 수 있다.
• 부분·표현식·커버링 인덱스: 불필요한 데이터 스캔을 최소화하고 디스크 I/O를 크게 줄인다.
• 고급 스캔 기법: Bitmap Index Scan, Index Only Scan 등으로 복합 쿼리 성능을 최적화한다.
• 통계 기반 옵티마이저: 더 정확한 비용 추정으로 최적의 실행 계획을 만든다.

이러한 기능 덕분에 PostgreSQL은 복잡한 읽기 중심 워크로드에서 MySQL보다 일반적으로 더 나은 인덱싱 성능을 제공합니다. 물론 MySQL도 InnoDB의 개선으로 많은 부분이 향상되었지만, PostgreSQL이 제공하는 풍부한 인덱스 옵션과 자동 튜닝 메커니즘은 여전히 차별화된 강점으로 작용합니다.

문제 설정 (두 DB에서 인덱스 스캔이 내부적으로 작동하는 방식)

테이블 정의 (MySQL & PostgreSQL)

CREATE TABLE "user" (
  id   BIGINT PRIMARY KEY,
  name VARCHAR(100),
  age  INT
);

인덱스

CREATE INDEX idx_user_name ON "user"(name);

쿼리

SELECT age FROM "user" WHERE name = 'Rahim';

• 테이블 크기: 1천만 행
• 인덱스 유형: B‑Tree
• name은 고유하지 않음
• age는 인덱스에 포함되지 않음

MySQL 실행

중요한 InnoDB 규칙

보조 인덱스는 물리적 행 위치가 아니라 PRIMARY KEY를 저장합니다.
idx_user_name 항목은 다음과 같습니다: (name, primary_key_id).

단계별

1. 보조 인덱스 (name) 탐색
   MySQL은 B‑Tree에서 'Rahim'을 검색합니다.
   비용: O(log N)

   예시 리프 항목: ('Rahim', id=73482)

2. PRIMARY KEY 인덱스 (클러스터드 인덱스) 탐색
   InnoDB에서 기본 키 인덱스는 테이블 자체이며, 행은 PK 순서대로 저장됩니다.
   MySQL은 id = 73482를 사용해 PK B‑Tree를 검색합니다.
   비용: O(log N)

   리프 항목은 전체 행을 반환합니다: (id=73482, name='Rahim', age=29)

요약

• ✅ 클러스터드 인덱스는 좋은 지역성을 제공합니다
• ❌ 두 번의 B‑Tree 탐색이 필요합니다
• ❌ PK 조회 비용은 테이블 크기에 따라 증가합니다

PostgreSQL 실행

중요한 PostgreSQL 규칙

인덱스는 TID (tuple ID) 를 저장합니다 – 힙 위치를 가리키는 포인터.
idx_user_name 항목은 다음과 같습니다: (name, (block_id, offset)).

단계별

1. B‑Tree 인덱스 (name) 탐색
   PostgreSQL은 'Rahim'에 대한 인덱스를 검색합니다.
   Cost: O(log N)

   Leaf entry: ('Rahim', TID=(block=102345, offset=7))

2. 힙 가져오기 (직접 포인터)
   PostgreSQL은 힙 페이지 102345로 바로 이동하여 오프셋 7에 있는 행을 읽습니다.
   Cost: O(1) (conceptually)

   Row returned: (id=73482, name='Rahim', age=29)

요약

• ✅ B‑Tree 탐색은 한 번만 수행됩니다
• ✅ 힙 가져오기는 상수 시간입니다
• ❌ 힙 페이지가 흩어져 있을 수 있어 지역성이 낮습니다
• ❌ MVCC로 인한 추가 가시성 검사

Performance considerations (Big‑O is not the full story)

When MySQL can be faster

• Primary key is small → 프라이머리 키가 작다
• Data fits in the buffer pool → 데이터가 버퍼 풀에 들어간다
• Rows are accessed sequentially → 행이 순차적으로 접근된다
• Heavy read workloads (OLTP) → 읽기 작업이 많은 워크로드 (OLTP)

➡️ Clustered index provides better locality, making MySQL win in these cases. → 클러스터드 인덱스가 로컬리티를 향상시켜 MySQL이 이러한 경우에 우위를 점한다.

When PostgreSQL can be faster

• Very large tables → 매우 큰 테이블
• Many secondary indexes → 다수의 보조 인덱스
• Random access patterns → 무작위 접근 패턴
• Index‑only scans are possible → 인덱스 전용 스캔이 가능하다

➡️ Pointer‑based access and constant‑time heap fetch give PostgreSQL an advantage. → 포인터 기반 접근과 상수 시간 힙 페치가 PostgreSQL에 이점을 제공한다.

인덱스 전용 스캔 예시

If you change the index to include age:

CREATE INDEX idx_user_name_age ON "user"(name, age);

Running the same query:

SELECT age FROM "user" WHERE name = 'Rahim';

• PostgreSQL은 힙에 접근하지 않고 결과를 반환할 수 있습니다 (인덱스 전용 스캔).
• MySQL은 보조 인덱스에 필요한 컬럼이 포함되지 않기 때문에 동일한 방식으로 진정한 인덱스 전용 스캔을 수행할 수 없습니다.

비교 요약

결론

• MySQL은 “인덱스 찾기 → PK 찾기 → 행 찾기” 경로를 따르며, 소규모에서 중간 규모의 순차적 워크로드에 효율적입니다.
• PostgreSQL의 포인터 기반 접근법(O(log N) + O(1))은 대규모 랜덤 액세스 워크로드에 대해 더 나은 확장성을 제공하고 실제 인덱스 전용 스캔을 가능하게 합니다.