인덱싱 실습

100만개의 행을 가지고 있는 employees을 생성하였다.

 

id(row_id)와 name(이름)

• 1000001개의 행
• ID : Integer | NOT NULL | auto increment (인덱스 가지고 있음)
• Name : 랜덤으로 만들어진 이름

 

실행계획과 쿼리의 수행 시간을 확인할 수 있는 postgre 의 명령어

explain analyze [쿼리]

 

 

 

실습1

• 인덱스가 존재하는 컬럼을 조회(Scan)

explain analyze select id from employees where id = 2000;

 

• Index Only Scan using employees_pkey on employees ~~ :인덱스를 활용하여 스캔을 진행
• Heap Fetches : 0 -> Heap영역에 접근할 필요 없음(why? 인덱스로 접근했기 때문)
• Planning Time : 0.123 ms 
  • 실행 계획 : 쿼리를 계획하고 어떤 결정을 내려야 하는지 실행 계획을 짜는데 까지 0.123ms가 걸림
• Execution Time : 0.164 ms
  •  실행 시간 : 실제 쿼리가 수행되어 걸린 시간

 

실습2

• 비교 예시로 인덱스가 존재하는 컬럼을 조회

• 인덱스가 없는 컬럼(name)을 조회할 때의 Cost를 비교

• 원래는 Execution Time이 더 느리게 나와야 정상인거 같은데 그렇게 나오지 않는다.
• 이유로는 
  • 페이지 캐싱 : 처음 조회했기 때문에 (X)
  • I/O 최적화
    • PostgreSQL은 데이터를 효율적으로 읽고 쓰기 위해 여러 가지 방법을 사용
    • 예를 들어, 읽기 작업을 위해 데이터 파일을 순차적으로 읽을 수도 있고, 데이터를 읽기 위해 필요한 페이지를 한 번에 읽어들일 수도 있다.
    • 이러한 최적화는 실행 시간을 단축시키는 데 도움이 된다.
  • 쿼리 최적화
    • PostgreSQL은 실행 계획을 수립할 때 쿼리를 최적화하여 효율적으로 실행합니다. 이 때문에 실행 시간이 예상보다 빠를 수 있다.
• 그래서 결국 원하는 실습 결과로는?
  • 인덱스가 걸려있지만, heap 영역을 한번 더 접근해야하기 때문에 실행 시간(Execution Time)이 더 오래걸린다.
  • 내가 알지 못하는 PostgreSQL의 최적화 방법 때문에 더 빠르게 수행된 것 같다.

 

 

실습3

• 인덱스가 걸려 있지 않은 컬럼(name)을 조건문(where)로 찾아 id를 조회

• 의도대로 Execution Time에 매우 느리게 나왔다.
• Planning Time : 0.0002218초
• Execution Time : 0.035초
• 느려진 걸 볼 수있다.
• 이유
  • name에는 인덱스가 없기 때문에 where절에서 조회를 할 때 heap 영역을 계속 접근하면서 페이지 단위로 메모리에 올려 원하는 name을 찾아야한다. (잦은 IO 발생)

 

실습4

• 최악의 쿼리 like '%단어%'

• 모든 행을 흝어야 하기 때문
• 모든 케이스를 매칭시켜야 함

create index employees_name on employees(name);

 

실습5

• name에 인덱스를 걸어두자.
• 실습 3번의 예시와 비교하면, 속도가 빨라졌다.

• Bitmap Heap Scan on employees:
  • 'employees' 테이블에서 Bitmap Heap Scan을 수행
  • 이는 쿼리가 특정 조건에 맞는 행을 찾기 위해 힙(Heap)을 스캔하는 것을 의미
• Recheck Cond: (name = 'Zs'::text)
  • 조건을 다시 확인
  • 'name' 열이 'Zs'와 일치하는지 다시 확인하는 작업
• Heap Blocks: exact=37:
  • 힙 블록을 37개(행) 스캔 (하나의 Block단위 - 인덱스를 통해 접근해서 가져온 행의 개수가 37개)
  • 인덱스 덕분에 한번에 정확히 원하는 Page(단위)를 읽어올 수 있었음
  • 실제 데이터를 찾기 위해 힙에서 읽은 블록의 수
• Bitmap Index Scan on employees_name:
  • 'employees_name' 인덱스를 사용하여 Bitmap Index Scan을 수행
  • 인덱스를 사용하여 조건에 맞는 행을 찾는 것을 의미
• Index Cond: (name = 'Zs'::text):
  • 인덱스를 사용하여 조건을 확인
  • 'name' 열이 'Zs'와 일치하는지 확인
• Planning Time: 0.370 ms
• Execution Time: 0.532 ms

 

• 실행 계획에서는 쿼리가 'name' 열이 'Zs'와 일치하는 행을 찾는 데 힙 스캔과 인덱스 스캔을 모두 사용한다는 것을 보여 준다.

 

실습6

• name과 id에 인덱스를 걸어두었는데도 여전히 느린 실행 속도
• 최악의 쿼리 : like '%필드%'

• 이유
  • 정확한 값을 요청하지 않은 것!(name에 인덱스가 걸려있다고 해도)
  • 표현식(expression)을 요청한 것
  • 표현식을 만족시킬 인덱스가 없음
• 병렬 스캔을 수행했음에도 느릴 수 밖에 없는 구조.

 

 

Like 쿼리를 보통 검색에서 자주 쓰고, 사이드 프로젝트 현업에서도 쓰이기 때문에 별 문제가 없을 것이라고 생각했는데, 데이터가 많아질수록 이용자 수가 많아지면 시간이 배로 걸릴 수도 있는 문제점이 발생할 수 있겠다는 생각이 들었습니다.

인덱스를 만들어도 실습 6의 경우에는 인덱스를 탈 수 없게 되었으니..

 

이 문제에 대해 인덱스에 대해 정리한 블로그가 있어서 링크 추가

https://vprog1215.tistory.com/383

[DB] LIKE 와 INDEX