[PostgreSQL] 데이터모델링과 인덱싱

데이터베이스에서 하드웨어 개선 없이도 내부 구조나 기능을 활용하여 속도를 향상시키는 대표적인 방법이 데이터 모델링과 인덱싱을 잘 하는 것입니다.

테이블 사이의 관계

같은 의미의 데이터라도 어떻게 구성할지 전략적으로 정하지 않으면 효율적인 데이터 처리를 수행할 수 없습니다. 이처럼 저장할 데이터의 구조를 정하는 작업을 데이터 모델링이라고 합니다. 데이터 모델링은 많은 양의 데이터를 빈번하게 조회하는 서비스에서 특히 더 중요합니다.

일대다 관계

하나의 테이블이 다른 테이블에 속하는 관계를 일대다 관계라고 합니다. 일대다 관계를 의미하는 정보는 일반적으로 속하는 테이블에 속해지는 테이블 id 값을 저장합니다.

이러한 방식을 이용하면 정보의 중복을 최소화하면서 정보를 저장할 수 있습니다. 이와 같이 서로간의 불필요한 정보의 중복이 없는 상태를 정규화된 상태라고 합니다.그렇지만 반드시 이런 방법으로만 서로의 관계를 표현할 수 있는 것은 아닙니다. 데이터의 중복을 허용한다면 다음과 같이 게시글 테이블에 게시판 이름을 여러번 적어서 일대다 관계를 표현할 수 있습니다.

하나의 테이블을 일대다 관계로 표현하면 두 테이블을 동시에 불러오는 경우에 읽기 효율이 더 좋습니다. 하지만 하나의 테이블로 일대다 관계를 표현하면 중복되는 정볼르 수정할 때 효율이 나빠지고 일관성이 깨질 가능성이 높아집니다.

다대다 관계

두 테이블이 서로 다수의 관계를 가지는 경우 다대다 관계라고 합니다. 3개의 테이블을 두 번의 일대다 관계로 연결을 하면 정규화 된 다대다 관계를 만들 수 있습니다.

한쪽 테이블 로우에서 다른 쪽 테이블 로우의 id 값을 배열로 가지고 있으면 다른 방식으로 다대다 관계를 표현할 수 있습니다.

이런 경우 조회 테이블 없이 두 테이블만으로 다대다 관계를 유지할 수 있습니다. 저장되는 데이터의 양이 줄어들고, 정보 교환도 줄어듭니다. 그러나 배열로 id 값을 저장하면 한 방향으로는 정보 접근이 쉽지만 반대쪽 방향으로는 정보 접근이 어려워지는 문제가 생깁니다. 또한 관계 연결을 수정할 때도 복잡해지는 문제가 있습니다.

이러한 문제점 때문에 두 테이블 사이의 id 값들을 양쪽에 배열로 저장하는 방식도 있습니다. 이렇게 하면 양쪽 데이터로 접근할 때 더 편하지만 그만큼 데이터를 더 많이 저장해야 하는 단점이 있습니다.

이와 같은 방법 외에도 정규화 되지 않은 형대로 데이터를 모델링 하는 경우는 더 많이 있습니다. 다대다 관계를 일대다 관계 2개의 결합으로 보고 앞서 일대다 관계를 표현한 것처럼 테이블 한개에 모든 정보를 표현할 수도 있습니다. 물론 이렇게 하는 경우 두 테이블의 조합한 형태를 조회하는 성능이 더 좋아지고 테이블의 정보를 수정하는 경우 성능이 더 내려가게 됩니다.

일대일 관계

두 테이블 사이에는 일대일 관계도 존재합니다. 일대일 관계는 말 그대로 2개의 테이블이 서로 일대 일로 연결되어 있는 관계입니다. 일대일 관계를 연결하는 정보는 일대다 관계와 마찬가지로 상대방 테이블의 id 값을 저장하면서 이루어집니다.

일대일 관계는 저장하는 정보를 하나의 데이블으로 붂기에는 의미적으로 맞지 않거나, 성능을 향상시키기 위해 2개의 테이블로 나눠서 사용해야 할 때 사용하게 됩니다. 테이블 내에 쓰기 작업이 빈번한 컬럼과 읽기 작업이 빈번한 컬럼을 서로 다른 테이블로 나누어 관리하고 싶을 때 이런 방식으로 한 테이블을 두 테이블로 쪼개기도 합니다.

정규화와 비정규화

정규화 이해하기

정규화란 데이터의 중복성을 제거하거나 줄이기 위해 데이터를 구성하는 과정입니다.

일반적인 의미의 정규화란 크고, 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 의미합니다.

하나의 테이블을 정규화하게 되면 테이블이 나누어지고 각각의 테이블에 프라이머리 키 id 값을 추가하게 됩니다. 복잡해보이지만, 데이터의 중복이 최소화되고, 더 적은 양의 데이터로 같은 내용을 저장하게 됩니다.

테이블을 정규화 하는 것은 대규모 데이터베이스를 어떻게 구축할 지 계획을 세울 때 매우 용이합니다. 우선 데이터를 표현하는 방법이 가장 간결한 방법 한 가지로 통일되고 정보들 사이의 관계를 명확하게 표현할 수 있기 때문입니다.

비정규화를 하는 이유

정규화된 테이블이 보기에 간결하고 명확하긴 하지만 실제 데이터베이스에 모든 테이블은 정규화된 형태로 저장하는 것은 데이터 조회시 비효율적일 수 있습니다. 정규화된 테이블들은 정보가 여러 테이블에 분산되어 있는 특징을 가지게 되는데, 이런 특징이 너무 많은 JOIN문을 사용하게 만들어 성능이 낮아지게 하는 원인이 됩니다.

정규화된 모델은 데이터를 조회하는데에 복잡한 쿼리문이 필요하지만 테이블을 하나만 사용하면 중간에 연산하는 과정이 없으니 더 빠르게 결과를 출력할 수 있습니다. 물론 로우의 양이나 하드웨어 상황에 따라 실제로 성능 차이가 나지 않을 수도 있습니다.

하지만 비정규화된 모델은 데이터의 중복이 있기 때문에 쓰기 작업시 더 불리해지는 경향이 있습니다. 또한 쓰기 작업을 할 때 잘못된 코드가 작성되어 있다면 데이터의 무결성이 깨질 위험도 함께 존재합니다.

특징정규화 모델비정규화 모델

정보가 테이블에 분산된 정도	많이 분산됨	적게 분산됨
조회 속도	느림	빠름
쓰기 속도	빠름	느림
데이터 무결성	깨지지 않음	깨질 수 있음

비정규화의 유형

자주 함께 조회하는 테이블

자주 함께 조회하는 테이블은 데이터를 중복해서 저장하면 JOIN 연산 없이 빠르게 원하는 정보를 조회할 수 있습니다. 하지만 중복해서 데이터를 저장하기 때문에 데이터 무결성이 훼손될 우려가 있습니다. 이런 문제를 해결하기 위해서 트리거나 Materialized View를 활용하면 피할 수 있습니다.

집계 연산이 빈번하게 일어나는 상황

집계 연산이 빈번하게 일어나는 경우 매번 집계를 하는 것 보다 미리 집계된 정보를 테이블에 미리 저장해놓는 것이 성능 측면에서 유리합니다. 집계된 정보가 항상 정확할 필요가 없다면, 트리거 기능을 사용하면 일정 시간마다 집계 계산을 하여 반영해줄 수도 있습니다.

자주 조회되지 않는 정보가 포함된 테이블이 있는 경우

자주 조회되지 않는 정보가 포함된 테이블의 경우 이러한 정보는 다른 테이블로 분리할 수 있습니다. 의도적으로 정보를 분리하면 성능상의 이점을 가질 수도 있습니다.

이 외의 상황에도 NULL 값을 갖는 컬럼만 따로 다른 테이블로 떼어내거나 쓰기 잠금을 피하기 위해 다른 테이블로 분리하는 상황도 존재합니다.

인덱싱의 이해

인덱스의 특징

• 쿼리를 수행할 때 인덱스가 없다면 모든 로우를 일일히 조회해야 합니다. 인덱스는 쿼리 작업을 매우 효율적으로 만듭니다.
• 하지만 인덱스를 만들면 새로운 로우를 생성하거나 제거하는 작업을 빈번하게 할 때 속도의 저하가 일어날 수 있습니다 .매번 인덱스를 업데이트 해야 하기 때문입니다.
• 단순 인덱스를 만들면 해당 컬럼만 조회할 때 사용할 수 있고, 다수의 컬럼을 대상으로 조회를 할 때에는 복합 인덱스가 효울적입니다.
• 복합 인덱스는 순서가 중요합니다.
• a-b 복합 인덱스는 a 단순 인덱스와 같은 기능을 하기 때문에 대체할 수 있습니다.

다양한 인덱스의 종류

인덱스는 대상 컬럼 정보와 테이블의 로우 위치 정보를 매핑해서 저장하여 기록합니다.

postgreSQL에서는 인덱스를 다양한 자료구조로 생성할 수 있습니다. 그 중 기본적으로 가장 많이 사용하는 인덱스 자료구조는 바로 B-Tree 구조이다. 그 외에도 Hash, GiST, SP-GiST, GIN, BRIN 인덱스 형식을 지원하고 있습니다.

B-Tree

B-Tree 방식의 인덱슨는 가장 기본적인 형태의 인덱스입니다. 프라이머리 키를 생성하면 자동으로 B-Tree 방식의 인덱스를 생성하는데, 프라이머리 키 값을 이용해서 정보를 찾는 일이 빈번하기 때문입니다.

B-Tree 구조는 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리 구조를 말합니다.

이 구조에는 몇 가지 규칙이 있는데, 우선 각각의 노드에 있는 키들은 전부 정렬되어 있어야 합니다. 그리고 부모 노드의 키들 사이사이와 자식 노드들이 연결되어 있습니다. 이 사이에 연결된 노드는 두 키 값 사이의 값들만 가져야 합니다.

postgreSQL은 루트 노드부터 크기를 비교하면서 자식 노드를 조회하는 방법을 이용해 검색을 실행합니다.

트리 구조에서 자식 노드를 엄청 많이 만들거나, 반대로 하나의 노드에 키 값이 적게 들어있게 된다면 검색 효율이 떨어집니다. 그래서 검색할 때 가장 적은 횟수의 조회를 하도록 정해진 규칙에 맞춰서 인덱스를 정리해야 합니다. 각각의 노드에 키를 추가 하기도 하고 어떨 때는 노드를 쪼개거나 합치기도 합니다. 이런 과정을 균형 맞춤이라고 합니다.

인덱스에서는 새로운 데이터를 추가하게 되면 이런 균형 맞춤 작업을 해야 하기 때문에 정말 필요한 인덱스가 아니면 함부로 생성하지 않는 것이 좋고, 생성 및 삭제 작업이 빈번하다면 인덱스를 만들지 읺는 것도 떄로는 고려해보아야 합니다.

해시 인덱스

해시 인덱싱은 값을 직접 인덱싱하지 않고 값을 해시화 함수를 통해 더 작은 크기의 값으로 변형한 뒤 이 값을 기준으로 B-Tree 구조를 만듭니다. 이런 방식을 이용하면 그냥 B-Tree 구조만 사용했을 때보다 인덱스 크기면에서 훨씬 작아지게 됩니다. 인덱스의 크기가 작아지면 인덱스의 메모리에 캐싱할 때도 이점이 있기 때문에 장점은 꽤나 의미가 있습니다. 또한 값 검색 속도가 기존의 방법보다 조금 더 빠릅니다.

하지만 해시화 함수를 거치만서 원래의 값을 변형하기 때문에 인덱스한 컬럼 값 사이의 크기 비교가 불가능해져 절렬과 비교 연산에 이 방식의 인덱스를 활용할 수 없습니다. 등호를 이용해서 값이 일치하는지 확인할 때만 해시 인덱스를 사용할 수 있습니다.

해시 인덱스는 이런 여러 가지 단점 때문에 일반적인 상황에서 사용하기 힘듭니다. 만약 인덱싱하려고 하는 컬럼 값들의 크기가 크고 값이 잘 겹치지 않으며, 등호를 이용한 빠른 값 검색을 해야 하는 상황이라면 해시 인덱스를 한번쯤 고려해볼만 합니다.

GIN

GIN은 Generalized Inverted Index 에서 앞글자를 딴 약자로 전문 검색을 주 목적으로 하는 인덱스입니다. LIKE 연산자나 tsvector 관련 연산을 할 때 효율적인 검색이 가능하게 하는 인덱스입니다. 주로 긴 문자열들이 들어가는 컬럼에 설정합니다. 원래의 인덱스에서 키와 값을 뒤바꾼 역인덱스의 한 종류입니다.

컬럼 수에 따른 인덱스의 종류

단일 컬럼 인덱스

한 가지 종류의 인덱스 컬럼 값을 갖는 방식을 단일 컬럼 인덱스라고 부릅니다. 가장 대표적으로 프라이머리 키 컬럼을 갖는 인덱스가 있습니다. 이 방식의 인덱스를 이용하면 해당 컬럼에 어떤 값을 검색하거나 값의 범위에 해당하는 로우를 불러올 떄 사용할 수 있습니다. 또한 해당 컬럼에 대해서 정렬할 때도 도움을 받을 수 있습니다.

하지만 만약 어떠한 컬럼에 대해서 정렬 후 또 다른 컬럼에 대한 정렬을 실행하려고 하면 이런 경우에는 다시 정렬을 실행할 경우 앞에서 정렬이 실행된 컬럼 또한 함께 비교하여 정렬을 완성해야합니다.

또한 두 가지 컬럼의 값을 동시에 일치하도록 검색하는 경우 한 가지 컬럼의 값을 먼저 찾은 후 다른 컬럼의 값을 대조해서 찾아야 하는 한계가 있습니다.

복합 컬럼 인덱스

여러 가지 컬럼을 동시에 작업할 경우 더 효율적으로 하기 위해서는 복합 컬럼 인덱스가 필요합니다. 복합 컬럼 인덱스는 인덱스를 대상으로 하는 값을 여러 개의 컬럼으로 하는 인덱스입니다. 복합 컬럼 인덱스는 두 컬럼의 값들을 어떻게 정렬해서 설정할지 정해주어야 합니다.

복합 컬럼 인덱스는 어떤 컬럼을 우선으로 정할지가 매우 중요합니다. 예를들어 점수에 대해서 오름차순, 제목에 대해서 내림차순으로 인덱스를 설정하고 점수에 대해서 우선 정렬 후 문자열을 사전 순서대로 정렬할 수 있습니다. 대부분의 DBMS는 숫자 이외에 모든 데이터 타입에 대해 대소비교가 가능하기 때문에 어떤 형식이든 인덱스를 만들 수 있습니다.

이 인덱스를 활용하면 점수 항목에 대해 우선 정렬이 되어 있기 때문에 점수 단일 컬럼 인덱스와 같은 기능을 수행할 수 있습니다. 이런 특징을 생각해보면 복합 컬럼 인덱스는 단일 컬럼 인덱스의 기능을 모두 포함하고 있습니다. 복합 컬럼 인덱스는 여러 가지 컬럼의 범위 검색과 값 검색, 정렬이 가능합니다.

인덱스를 생성한 두 컬럼에 대해 정렬을 해야 하는 상황이라면 검색하려는 순서와 인덱스의 순서가 다르기 때문에 하나의 컬럼에 대한 정렬만 사용하고 나머지 컬럼 인덱스는 활용하지 못하고 특수한 방법으로 활용해야 합니다.

만약 모두 오름차순해야 하는 상황이라면 두 컬럼에 대해 같은 방향으로 정렬한 인덱스가 없기 때문에 현재 가지고 있는 인덱스만 가지고 온전히 사용할 수 없습니다. 이처럼 정렬을 할 때는 복합 컬럼 인덱스의 정렬 방향과 선후 관계가 효율성에 있어서 중요합니다. 어떻게 설정하는지에 따라 활용하는 효율성이 달라집니다.

그러나 값과 범위를 검색할 때는 인덱스 설정 컬럼의 순서가 다르더라도 원하는 컬럼이 모두 포함되어 있다면 사용할 수 있습니다. 만약 두 컬럼의 우선수위가 중요하지 않은 상황일 때에는 더 포괄적인 컬럼에 우선권을 주는 것이 좋습니다. 그래야 상대적으로 적은 횟수의 탐색으로 결과를 얻을 수 있기 때문입니다.

인덱스 명령어

종류별 인덱스 생성하기

CREATE INDEX <인덱스 이름> ON <테이블 이름> [USING <인덱스 종류>]
(
  <컬럼이름> [ASC | DESC],
  ...
)

단일 컬럼 인덱스

가장 기본이 되는 단일 컬럼 인덱스를 생성하기 위해서는 CREATE INDEX 명령어를 사용해야 합니다.

CREATE INDEX <인덱스 이름> ON [테이블 이름](<컬럼이름>);

방향을 설정해주지 않으면 자동으로 오름차순으로 인덱스가 생성됩니다.

복합 컬럼 인덱스

CREATE INDEX <인덱스 이름> ON [테이블 이름](<컬럼1> [ASC | DESC], <컬럼2> [ASC | DESC])

테이블명 뒤의 괄호에 컬럼이름과 방향을 순차적으로 작성하면 인덱스를 생성할 수 있습니다.

고유 인덱스

고유 인덱스는 대상으로 지정한 컬럼에 중복되는 값을 갖지 않도록 하는 인덱스입니다.

CREATE UNIQUE INDEX <인덱스 이름> ON [테이블 이름](<컬럼이름>);

앞선 명령어와 같은 구조에서 UNIQUE라는 설정 값을 중간에 끼워넣으면 됩니다. 이처럼 인덱스를 생성하면 같은 이름을 갖는 정보를 추가하려고 할 때 에러가 발생합니다.

프라이머리 키를 설정하면 B-Treee 구조의 고유 인덱스가 생성되기 때문에 프라이머리 키로 설정된 컬럼은 같은 값을 설정하려고 하면 에러가 발생합니다.

해시 인덱스

해시 인덱스를 생성하기 위해서는 테이블이름과 컬럼 설정 사이에 USING HASH를 추가해야 합니다.

CREATE INDEX <인덱스 이름> ON [테이블 이름] USING HASH(<컬럼이름>);

이와 같이 이름에 해시 인덱스를 생성하면 '=' 연산자를 이용한 일치 검색을 B-Tree 방식의 인덱스보다 더 빠르게 수행할 수 있습니다.

GIN 인덱스

GIN 인덱스도 해시 인덱스와 마찬가지로 USING 절에 GIN이라고 표시하여 인덱스를 생성할 수 있습니다. GIN 인덱스를 활용해서 전문 검색을 효율적으로 하기 위해서 tsvector라는 데이터 형식을 사용하여 쿼리를 해야 합니다. 또한, GIN 인덱스 역시 tsvector 데이터 형식에 대해서만 인덱스를 생성할 수 있고 일반적인 text 타입에 바로 인덱스를 생성할 수 없습니다. 따라서 to_tsvector라는 함수를 이용해 다음과 같이 text 형식을 tsvector로 변환해야 합니다.

SELECT to_tsvector('english', content) FROM boards;

tsvector로 변환하면 의미를 갖는 단어만 남게 됩니다. 만약 특정 내용을 검색하기 위해서는 ts_query라는 함수를 사용해서 연산해야 합니다.

SELECT id, title
FROM boards
WHERE to_tsvector('english', content) @@ to_tsquery('time')

검색하고자 하는 tsvector에 tsquery를 @@로 연산하면 해당 단어가 퐇마된 로우를 찾을 수 있습니다. 이러한 연산 시 미리 인덱스를 만들려면 GIN 인덱스를 만들어주면 됩니다.

CREATE INDEX <인덱스 이름> ON [테이블 이름] USING GIN(to_tsvector('english', <컬럼 이름>));

이렇게 GIN 인덱스를 생성하면 tsvector 관련 연산을 할 때 더 효율적인 쿼리가 가능합니다. 만약 pg_trgm이라는 확장 프로그램을 설치하고 이를 이용해서 인덱스를 생성하면 LIKE 연산에도 쿼리 속도 향상에 도움을 줄 수 있습니다.

tsvector를 이용한 전문검색 시 한국어를 지원하고 있지 않기 때문에 한국어를 분석하여 단어만을 추출한 tsvector를 만들기 위해서는 textsearch_ko라는 확장 프로그램을 설치해야 합니다.

부분 인덱스

부분 인덱스란 컬럼의 모든 값에 대해서 인덱스를 생성하는 것이 아니라 특정 조건에 맞는 값들에 대해서만 생성한 인덱스를 말합니다. 이렇게 부분적으로 인덱스를 생성하면 적은 용량으로 효율적인 쿼리를 실행시킬 수 있습니다.

CREATE INDEX <인덱스 이름> ON [테이블 이름](<컬럼1>) WHERE <컬럼2>;

인덱스 수정 및 삭제하기

인덱스를 미리 생성하고 운영하다가 만약 인덱스의 이름을 체계적으로 수정하기 위해서는 ALTER INDEX 명령어를 사용하면 됩니다. 이 명령으로 인덱스의 이름과 테이블스페이스를 설정할 수는 있지만, 인덱스가 대상으로 하는 컬럼이나 테이블 속성을 변경할 수 는 없습니다. 만약 컬럼과 테이블 정보를 변경해야 한다면 인덱스를 삭제하고 새로 생성해야 합니다.

ALTER INDEX <이전 인덱스 이름> RENAME TO <새로운 인덱스 이름>;

변경할 인덱스의 이름을 먼저 적고 RENAME TO라는 명령 뒤에 변경할 이름을 작성하면 됩니다. 이 인덱스를 삭제하려면 DROP INDEX 명령어를 사용합니다.

DROP INDEX <인덱스 이름>;

실사용 데이터베이스 인덱스 설정하기

인덱스를 생성하려고 하는 테이블의 로우 수가 이미 엄청나게 많은 경우 인덱스를 생성하려고 하면 시간이 오래 걸리게 됩니다. 특히 이런 상황에서 인덱스를 생성하려고 하면 인덱스를 생성하는 도중에는 해당 테이블이 “읽기 전용”으로 잠금이 걸리기 때문에 해당 테이블에 새로운 정보를 추가하거나 수정할 수 없게 되고 서비스에 에러가 발생하는 상황이 발생하곤 합니다.

당장 데이터를 실시간으로 받아서 처리해야 하는데 인덱스 생성에 몇 시간이 걸리게 되면 곤란해집니다. 이런 문제를 해결하기 위해 인덱스 생성 및 삭제에 동시처리 기능을 탑재하고 있습니다. CONCURRENTLY라는 옵션을 추가해서 인덱스를 생성하면 “읽기 전용” 잠금이 발생하지 않습니다.

CREATE INDEX CONCURRENTLY <인덱스 이름> ON [테이블 이름](<컬럼1>) WHERE <컬럼2>;
DROP INDEX ONCURRENTLY <인덱스 이름>;

CREATE INDEX, DROP INDEX 명령어 바로 뒤쪽에 붙여서 사용하면 됩니다. 동시 처리 기능을 사용하면 사용하지 않을 때보다 인덱스 생성 시간이 오래걸리게되는 단점이 있기 때문에 인덱스 생성 시 테이블에 쓰기 작업이 얼마나 필요한지, 그 작업의 실시간성이 필요한지 고려하고 옵션의 사용을 결정해야 합니다.

인덱스 유지 관리하기

EXPLAIN을 이용하여 느린 쿼리를 탐지하고 인덱스가 제대로 사용되는지 확인하고 비번하게 사용되지 않는 인덱스를 색출할 수 있습니다.

EXPLAIN 명령어

EXPLAIN 쿼리문;
EXPLAIN ANALYZE 쿼리문;

EXPLAIN 명령어는 쿼리문 앞에 붙이면 쿼리문이 어떻게 실행될지를 예상한 쿼리 계획을 출력하게 됩니다. 만약 EXPLAIN 명령어 뒤에 ANALYZE를 붙이면 실제로 쿼리를 실행한 후 실행한 쿼리 계획과 실제 소요된 시간이 함께 출력됩니다.

쿼리 계획에는 어떤 작업을 먼저하고 나중에 했는지와 같은 정보와 인덱스를 사용했는지 여부가 담겨 있습니다.

EXPLAIN과 쿼리 계획

쿼리 계획을 읽고 이해하기 위해서는 쿼리 계획에 등장하는 방법의 종류에 대해서 이해해야 합니다.

실행 계획설명

Filter	원하는 정보만을 걸러내는 작업
Sequential Scan	순차적으로 정보를 읽어내는 작업
Index Scan	인덱스를 조회하여 원하는 정보를 찾고 테이블에서 해당 정보를 불러오는 작업
Index-Only Scan	인덱스를 조회하여 원하는 정보를 바로 불러오는 작업
Bitmap Heap Scan	Bitmap Index Scan을 통해 얻은 위치 정보로 원하는 정보를 테이블에서 불러오는 작업
Bitmap Index Scan	인덱스를 조회하여 다수의 정보 위치를 비트맵에 저장하는 작업
Subquery Scan	서브쿼리의 결과로 얻은 테이블을 탐색하는 작업

순차 탐색

순차 탐색은 말 그대로 순차적으로 테이블 전체를 조회하여 원하는 정보를 얻어내는 작업을 말합니다. 테이블 전체를 조회하기 때문에 가장 단순하면서도 비용이 꽤 드는 작업입니다.

원하는 정보를 찾으려고 할 때 인덱스가 없거나, 인덱스를 활용하기에는 불러오려는 정보의 양이 너무 많을 떄 순차 탐색을 사용하게 됩니다.

EXPLAIN SELECT * FROM public.accident
WHERE 발생일시 < '2019-08-12 00:00:00';

  QUERY PLAN
-------------------------------------------------------------------------------------
  Seq Scan on accident (cost=0.00..101.41 rows=1894 width=117)
    Filter: ("뱔생일시" < '2019-08-12 00:00:00+09'::timestamp with time zone)
  (2 rows)

인덱스가 없는 테이블을 조회한다면 대부분의 경우 순차 탐색이 쿼리 과정에 포함될 것입니다. 어떤 순차 탐색이 들어가즌지를 미리 파악한다면 앞으로 어떤 인덱스를 만드는 것이 효율적인지 계획을 세울 수 있습니다.

  QUERY PLAN
-------------------------------------------------------------------------------------
  Nested Loop (cost=0.00..2.36 rows=1 width=71)
    Join Filter: (rating.item_id = ramen.id)
    -> Seq Scan on rating (cost=0.00..1.25 rows=1 width=12)
    Filter: (item_type::text = 'ramen'::text)
    -> Seq Scan on ramen (cost=0.00..1.05 rows=5 width=71)
  (5 rows)

두 테이블이 모두 순차 탐색이 이루어진 경우 두 테이블에 각각 단일 컬럼 인덱스를 만들지에 대해 고민해 볼 수 있습니다. 또는 굳이 인덱스를 만들 필요는 없다는 결론을 내릴 수도 있습니다. 왜냐하면 탐색하고자 하는 로우의 수가 너무 적어서 cost가 적게 나왔기 때문입니다. 순차 탐색을 하는데도 불구하고 적은 비용이 든다면 인덱스를 만든다고 해도 사용하지 않을 확률이 높습니다.

인덱스 탐색

인덱스 탐색은 인덱스에서 찾고자 하는 값을 먼저 찾은 후 테이블에 그 값이 들어있는 로우를 찾는 작업입니다. 인덱스를 먼저 탐색하고 테이블을 조회하지만, 모든 로우를 탐색하는 순차 탐색 작업보다는 더 적은 시간이 걸립니다. 만약 인덱스에 찾고자 하는 값이 전부 들어있다면 구딩 테이블에 로우를 조회할 필요가 없습니다. 이런 경우 인덱스만 탐색하는 Index-Only Scan 작업을 합니다.

EXPLAIN SELECT * FROM public.accident
WHERE 발생일시 < '2019-08-11 00:00:00';

  QUERY PLAN
-------------------------------------------------------------------------------------
  Index Scan using occured_at_idx on accident (cost=0.00..100.40 rows=1888 width=117)
    Filter: ("뱔생일시" < '2019-08-11 00:00:00+09'::timestamp with time zone)
  (2 rows)

비트맵 탐색

비트맵 탐색은 인덱스에서 조회한 내용을 잠시 비트맵에 저장한 후 비트맵에 저장된 내용을 기반으로 테이블에서 원하는 결과를 가져오는 작업입니다. 앞선 비트맵 도식은 두 단일 컬럼 인덱스를 조회해서 2개의 비트맵을 만들고 두 미트맵이 둘 다 가리키는 로우를 테이블에서 찾아내는 과정을 나타내고 있습니다.

비트맵 탐색은 순차 탐색을 하기에는 탐색하고자 하는 로우의 수가 적고, 인덱스 탐색을 하기에는 탐색하고자 하는 로우의 수가 많을 때 그 절충안으로 선택할 수 있는 탐색 방식입니다. 또한 비트맵 탐색을 이용하면 두 컬럼에 값을 동시에 만족하는 로우를 각각의 컬럼에 걸려있는 단일 컬럼 인덱스 2개를 이용해서 효율적으로 찾아낼 수 있습니다.

EXPLAIN SELECT * FROM public.accident
WHERE 발생일시 < '2019-08-11 00:00:00' AND 시도 = '충북';

  QUERY PLAN
-------------------------------------------------------------------------------------
  Bitmap Heap Scan on accident (cost=5.64..69.31 rows=104 width=117)
    Recheck Cond: (("시도")::text = '충북'::text)
    Filter: ("뱔생일시" < '2019-08-11 00:00:00+09'::timestamp with time zone)
    -> Bitmap Index Scan on province_idx (cost=0.00..5.62 rows=178 width=0)
      Index Cond: (("시도")::text = '충북'::text)
  (5 rows)

Bitmap Index Scan 작업은 인덱스를 조회해서 원하는 정보가 어디에 있는지 비트맵에 저장합니다. 이후 BItmap Heap Scan 작업이 저장된 비트맵 정보를 기반으로 테이블 로우에 접근하여 원하는 정보를 불러오게 됩니다.

인덱스 사용 통계

인덱스 통계인 pg_stat_user_indexes 뷰를 활용하면 사용되지도 않으면서 쓰기 성능을 저하시키는 인덱스들을 쉽게 찾을 수 있습니다. 이 뷰는 현재 접속한 데이터베이스에 속한 모든 인덱스에 대해서 한 로우씩 현재 유저가 접근할 수 있는 인덱스 사용에 대한 통계 정보를 보여줍니다. 통계 수집기가 일정 시간마다 정보를 갱신하기 때문에 완전히 실시간으로 정보를 보여주지는 않습니다.

SELECT
  schemaname AS schema_name,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC:

idx_scan은 인덱스 스캔 횟수, idx_tup_read는 인덱스 스캔의 결과로 얻은 인덱스의 수, idx_tup_fetch는 인덱스를 이용하여 테이블에서 불러온 로우의 수를 의미합니다.

다음의 쿼리문을 실행하면 이렇게 불러온 정보가 언제부터 축적되었는지 확인인할 수 있습니다.

SELECT stats_reset FROM pg_stat_bgwriter;

만약 통계 정보가 너무 오래되어 최신의 정보를 받고 싶다면 기존의 통계정보를 초기화시키고 최신의 통계정보를 다시 축적해서 확인할 수도 있습니다.

SELECT * FROM pg_stat_rest();