DB(PostgreSQL) 인덱스와 활용방법 (컴포지트 인덱스, 커버링 인덱스)

DB를 활용하다 보면 빠른 동작을 위해 인덱스를 많이 활용합니다.

컬럼에 인덱스를 생성하면 빠르게 검색된다는건 알지만,  그 장점을 잘 활용못하는 경우가 많아 작성해보았습니다.

 

DB 인덱스란?

 

 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조

자료 구조 - 위키백과, 우리 모두의 백과사전

 

 

인덱스 활용 방법

1.  컴포지트 인덱스 (Composite Index )  vs  인덱스 머지 (Index merge)

• 컴포지트 인덱스 (Composite Index ): 두 개 이상의 컬럼에 대하여 생성한 인덱스   

CREATE INDEX ON table (column_a, column_b)

• 인덱스 머지 (Index merge): 개별 컬럼에 인덱스를 생성하여 모두 WHERE절에 사용

CREATE INDEX ON table (column_a)
CREATE INDEX ON table (column_b)

 

Q. 위의 두가지 인덱스 방식중에 어떤게 더빠를까?
A. 인덱스 머지보다 컴포지트 인덱스(Composite Index )가 10배 빠름

 

why?

PostgreSQL 인덱스 머지가 들어가는 쿼리에 대해서 인덱스-온리 스캔을 지원하지 않기 때문 (MySQL보다 PostgreSQL에서 차이가 더 큼)

 

Example

1천만개 레코드 삽입후 아래의 쿼리 실행

SELECT count(*) FROM table WHERE int1000 = 1 AND int100 = 1 쿼리에 대해서

# 컴포지트 인덱스 : 5ms
# 인덱스 머지 : MySQL은 30~40ms, PostgreSQL는 30~90ms

 

2.  커버링 인덱스 (Covering Index 혹은 Covered Index)

• 커버링 인덱스는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 뜻한다. SELECT / WHERE / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼이 인덱스의 구성 요소인 경우를 말한다.

예를들어, z 열이 있는 테이블의 x 및 y 열에 인덱스가 있는 경우

SELECT x, y FROM tab WHERE x = 'key';   # 커버링 인덱스 (O)
SELECT x FROM tab WHERE x = 'key' AND y < 42;  # 커버링 인덱스 (O)
SELECT x, z FROM tab WHERE x = 'key';  # 커버링 인덱스 (X)
SELECT x FROM tab WHERE x = 'key' AND z < 42;  # 커버링 인덱스 (X)

 

 

참조

https://sirupsen.com/index-merges

Index Merges vs Composite Indexes in Postgres and MySQL

https://ko.wikipedia.org/wiki/%EC%9D%B8%EB%8D%B1%EC%8A%A4_(%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4) 

인덱스 (데이터베이스) - 위키백과, 우리 모두의 백과사전

https://www.youtube.com/watch?v=zMAX7g6rO_Yhttps://www.youtube.com/watch?v=zMAX7g6rO_Y 

https://runebook.dev/ko/docs/postgresql/indexes-index-only-scans

PostgreSQL - 11.9. 인덱스 전용 스캔 및 커버링 인덱스 모든 인덱스 PostgreSQL은 각각이 테이블의 기본

https://simpledb.tistory.com/76

액세스 방식

https://overcome-the-limits.tistory.com/698

[Project] 프로젝트 삽질기17 (feat Table Scan 실행계획)