postgreSQL의 index 그리고 쿼리 최적화의 관계

0. intro

database를 공부하며 index를 사용하면 조회시에 성능을 높일 수 있다고만 알았었다. 그리고 이번에 postgreSQL을 사용하는데 query 최적화를 진행할 수 있는 방법에 대해서 알아보면서 index의 중요성에 대해서 깨닫게되었다.
알게된 사실에 대해서 공유를 하려고한다!

1. query최적화란?

query 성능 향상을 의미한다.
최적화 되지 않은 쿼리는 데이터베이스의 성능 저하로 영향을 미칠 것이다.

이때, 어떤 것을 최적화 해야할까?

1. 비효율적으로 설계된 스키마
2. 비효율적으로 설계된 쿼리
3. large table에 걸쳐서 사용될 때의 index의 결함
4. 사용되지 않는 index를 insert, update, delete 연산을 수행할 때
5. n+1 문제
   등등이 있을 것이다.

여기서 index라는 것을 많이 볼 수 있다.
postgreSQL은 firebase와 같은 platform인 supabase에서 활용도가 높다.
supabase에서의 문서를 참고하여 query최적화에 대해서 알아보았는데 이때 index를 많이 확인할 수 있었다.

공부를 하고 난 지금은 "index를 활용해서 쿼리 튜닝이 가능하다"라는 것을 알고있다.
물론 적절하게 사용할 때만 사용해야한다.
아니면,,,, 성능이 더 나빠질 수도있다.

2. postgreSQL의 구조

postgreSQL의 기본 구조와 작동원리에 대해서 이해해야 쿼리 튜닝에 대해서 잘 이해할 수 있다.

• postgreSQL의 기본구조

postgreSQL의 내부 구조에 대해서 보면, file system의 구조는 크게 3가지로 구성되고 있다.

하나의 member table이 존재한다고 해보자!
그렇다면 row들이 존재할 것이고 각 row마다 data가 존재할 것이다.
이때, row 하나 하나 각각을 tuple이라고 하는데 이들을 모아 하나의 테이블 속의 데이터들을 구성하는데, 특정 단위로 묶은것이 page이고 이들을 전체 모아 테이블을 구성하는데 heap file을 구성한다.

1. item(tuple)
   item은 곧 tuple이 된다. 즉, 특정 테이블의 row 하나를 의미한다. 이것들이 모여서 page를 구성한다.
2. 데이터 페이지
   데이터 페이지는 하나의 페이지는 확장 가능한 slotted page의 구조를 가진다. 이때 페이지는 테이블의 모든 row 데이터를 담고 있는 공간이다. block이라고도 한다.page 정해진 사이즈만큼의 tuple을 받을 수 있다.
3. heap
   하나의 페이지인 row가 page를 구성하는데 이러한 page는 결국 연결되어 하나의 heap file을 생성하게 되는데 이는 결국 모든 데이터를 담고 있게 될 것이다.

• postgreSQL의 join방법

1. nested join
   중첩 루프조인이다. 두개의 table이 있을 때 외부 테이블의 각 행을 반복하고 내부 테이블의 각 행과 비교하면서 동작한다. 중첩 루프조인인만큼 루프를 돌면서 외부 테이블 하나에 대해서 내부 테이블 전체를 스캔한다.
   full scan이 이루어지는 조인 방식이다.
   따라서, 이 경우는 어떤 경우에 사용해야할까?
   외부 테이블 하나의 행에서 스캔하면서 비교해야할 내부 테이블의 사이즈가 적을 때 유리할 것이다.

2. hash join
   해시조인은 더 큰 테이블에 대해서 중첩 루프 조인보다 효율적이다.
   말그대로 해시를 사용하겠다는 것인데 조인 조건에 따라서 더 작은 테이블에 대한 해시 테이블을 생성한다. 예를 들어서 주문과 고객이 있을 때 고객1개당 여러 주문이 가능할 것이다. 그렇다면 고객이 내부테이블인데! 내부 테이블인 고객 한명을 key로 가지도록하고 그 고객의 주문 전체를 해시 구조를 사용해서 고객에 대한 여러 주문을 관리하도록한다.
   kotlin에서 map, java에서 hashmap 그리고 python에서 dictionary, c에서 structure,,.와 같이 만들겠다는 것이다.
   그렇다면 찾을 때 고객 한명이 정해지면 주문을 scan해야하는게 줄어들게 될 것이다.

3. merge join
   병합 조인은 두개의 정렬된 테이블을 조인하는 효율적인 알고리즘을 의미한다. 이때 중요한 것은 "정렬"이다. 두 테이블을 동시에 스캔하고 조인 조건에 따라서 행을 비교하면서 작동한다. 조인 조건이 충족되면 행이 결합되어 결과 집합에 추가된다.

3. postgreSQL과 index

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위해서 만들어진 자료구조를 의미한다. (라고 설명한다ㅎㅎ 공부할 때는 발견하지 못했는데,,, https://mangkyu.tistory.com/96 해당 블로그에 잘 정리가 되어있더라!)

그렇다면 본격적으로 index에 대해서 알아보자!
index는 데이터베이스 테이블의 검색 속도를 향상 시키기 위해서 만든다.
이때, b-tree구조를 사용하는데 데이터베이스와 파일시스템에서 주로 사용되는 트리자료구조의 일종이다.

이진트리가 익숙할텐데 이진트리는 자식노드가 left, right 총 2개가 존재한다. 그러나, B-Tree는 자식 노드의 개수가 2개 이상인 트리를 의미한다. B-Tree는 노드내에 데이터가 여러개 존재할 수 있는데 이는 B-Tree의 차수를 결정짓게 된다.

노드 안에서의 데이터별로는 정렬된 상태를 가지며 노드역시도 자식 노드들의 데이터들은 왼쪽에는 작은 값, 오른쪽에는 큰값을 가져야한다.
이때, 왼쪽 오른쪽의 기준은 노드자체라기보다 노드 안의 정렬된 상태로 있는 데이터를 의미하고 데이터 사이사이에 자식 노드들이 연결된다고 생각하면 된다.

탐색의 경우에는 탐색하고자하는 값을 root에서 시작해서 하향식으로 탐색해나간다. 정렬이 키값을 기준으로 되어있는데 이점이 중요하다!

테이블에서 값을 읽어올 때 페이지 단위로 읽어오게 되어있다. 이때, 페이지를 찾기 위해서는 pk를 알아야한다. 인덱스는 페이지 단위로 저장되게 되는ㄴ데 키를 바탕으로 항상 정렬된 상태를 유지한다.

데이터베이스에서는 pk를 먼저 찾고 pk를 레코드를 pk를 통해서 찾게된다. pk를 알기 위해서 순차적으로 탐색하는 방법을 사용한다면 시간이 오래걸릴 것이다. 반면에, 인덱스를 사용하면 pk를 찾을 때 수월하게 찾을 수 있다. 인덱스는 트리구조이고 정렬된 데이터를 가지고 있다. 찾으려는 데이터를 찾기 위해서 가지치기를 해나갈 것이다. 이것은 당연히 순차적으로 모든 데이터를 훑으며 pk를 찾는 과정보다 시간이 적게 걸려서 효율적일 것이다.

• 어떤 index를 사용해야할까?

구분을 높여주는 index를 사용할수록 좋을 것이다.
카디널리티가 높을수록 구분을 잘 해준다고 생각하면 되는데 그 속성을 index로 설정하면 좋다!

• index가 있다고 무조건 좋을까?
  데이터도 별로 없는데 index를 넣었다. 그리고 조회보다 update 작업이 많이 이루어진다면? index도 역시 memory차지를 하게 될 것이다. 그리고 update를 할때마다 index가 불러와지게 된다. 그렇다면 데이터는 무거워지고 index는 잘 사용되지 않는다...이것은 가장 처음에 말했던 쿼리 튜닝이 필요한 이유중에 하나를 만들게 될 것이다.

• postgreSQL과 index
  postgreSQL에서는 pk를 생성하면 pk로 index를 생성해준다.
  내가 임의로 test DB를 만들고 chapter라는 table에 데이터를 몇개 넣어봤다.
  
  pg_indexes를 통해서 index가 해당 테이블에 존재하는지 체크해보니, 존재하는 것을 확인할 수 있었다.
  추가) 추가로 부분 index를 사용할 수 있다.
  부분 index란, 원하는 부분에만 index를 사용하겠다는 것이다. 데이터가 테이블의 1%여도 엄청 많고 조회할일이 많다고 해보자. 1%가 과자이고 99%가 아이스크림일 때 사람들이 여름이라 아이스크림은 먹지 않는다고 해보자. 그렇다면 99%에는 인덱스를 걸 필요가 없을 것이다. 많은 데이터라고 무조건 인덱스가 좋은게 아니니까 그렇다. 반면에 1%라도 데이터가 많고 지속적인 조회가 일어난다면 index를 부분적으로 '과자일때'라는 조건을 where절에 함께 사용하면 될 것이다.

• postgreSQL과 explain
  explain <<쿼리>> 를 작성하면 해당 쿼리에 대한 query plan을 볼 수 있다.
  쿼리플랜이란 postgreSQL에서 이 쿼리는 어떤 알고리즘을 사용해야 가장 효율적인 것인지 판단하고 그 것을 사용할 계획을 한다는 것이다.
  
  seq scan을 사용한다는 것은 full scan을 하겠다는 것이다. 만약 나중에 규모가 커졌을 때도 seq scan을 사용한다면? index를 사용해서 쿼리 성능 튜닝을 해야겠다고 생각하였다!