[PostgreSQL] 7. 데이터 인덱싱_(3)

5. 인덱스 유지 관리하기

  1) EXPLAIN 명령어

    EXPLAIN 쿼리문;
    EXPLAIN ANALYZE 쿼리문;

    -EXPLAIN 명령어는 쿼리문 앞에 붙이면 쿼리문이 어떻게 실행되는지를 예상한 쿼리 계획을 출력하게 된다
    -EXPLAIN 명령어 뒤어 ANALYZE를 붙이면 

      실제로 쿼리를 실행한 후 실행한 쿼리계획과 실제 소요된 시간이 함께 출력된다
    -쿼리계획 이란 말그대로 쿼리를 실제로 실행하기 위한 계획이다
    -쿼리계획에는 어떤 작업을 먼저 하고 나중애 했는지와 같은 정보와 인덱스를 사용했는지 여부가 담겨있다

  2) EXPLAIN과 쿼리 계획

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

    -이 가운데 인덱스의 사용과 연관이 깊은 실행계획은
     순차탐색(Sequential Scan) , 인덱스탐색(Index Scan) , 비트맵탐색(Bitmap Scan) 이다

    (1) 사전작업
      -데이터를 불러오고 B-Tree 인덱스를 생성한다
      -EX) CREATE Index occured_at_idx ON accident(발생일시);
           CREATE Index province_idx ON accident(시도);

    (2) 순차 탐색(Sequential Scan)
      -순차탐색은 말 그대로 순차적으로 테이블 전체를 조회하여 원하는 정보를 얻어내는 작업을 말한다
      -테이블 전체를 조회하기 때문에 단순하면서 비용이 꽤 든다
      -원하는 정보를 찾으려고 할 때 인덱스가 없거나, 
        인덱스를 활용하기에는 불러오는 정보의 양이 너무 많을 때 순차 탐색을 사용한다
      -인덱스를 생성했음에도 순차탐색이 일어나는 경우는 인덱스 구조를 조회하느느 시간에
        순차탐색 하는 것이 더 효율적이기 때문이다
      -인덱스가 없는 테이블을 조회한다면, 대부분의 경우 순차탐색이 쿼리 과정에 포함될 것이다
      -보통 Seq Scan 으로 작성한다

    (3) 인덱스 탐색(Index Scan)
      -인덱스에서 찾고자 하는 값을 먼저 찾은 후 테이블에 그 값이 들어있는 로우를 찾는 작업이다
      -만약 인덱스를 찾고자 하는 값이 전부 들어있다면 굳이 테이블에 로우를 조회할 필요가 없다
       이런 경우 인덱스만 탐색하는 Index-Only Scan 작업을 한다
      -인덱스 탐색은 보통 일치하는 적절한 개수의 로우를 찾는 작업을 할 때 자주 볼 수 있다

    (4) 비트맵 탐색(Bitmap Scan)
      -인덱스에서 조회한 내용을 잠시 비트맵에 저장한 후 비트맵에 저장된 내용을 기반으로 
       테이블에서 원하는 결과를 가져오는 작업이다
      -순차탐색을 하기에는 탐색하고자 하는 로우의 수가 적고, 인덱스 탐색을 하기에는 
       탐색하고자 하는 로우의 수가 많을 때 사용한다
      -두 컬럼에 값을 동시에 만족하는 로우를 각각의 컬럼에 걸려있는 단일 컬럼 인덱스 2개를 이용해서 효율적이다

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


  3) 인덱스 사용 통계
    -사용되지 않으면서 쓰기 성능을 저하시키는 인덱스들을 쉽게 찾는 방법
     인덱스 통계인 pg_stst_user_indexes 뷰를 활용하는 것이다

    -pg_stst_user_indexes 뷰는 현재 접속한 데이터베이스에 속한 모든 인덱스에 대해서 
     한 로우씩 현재 유저가 접근할 수 있는 인덱스 사용에 대한 통계정보를 보여준다
    
    -통계수집기가 일정 시간마다 정보를 갱신하기 때문에 완전히 실시간은 아니다

    EX)
    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_stst_user_indexes
    ORDER BY idx_scan ASC;

    > schema_name, table_name, index_name은 각각 스키마, 테이블, 인덱스의 이름을 가리킨다
    > index_size은 인덱스의 크기를 의미한다

    > idx_scan은 인덱스 스캔 횟수
    > idx_tup_read는 인덱스 스캔의 결과로 얻는 인덱스의 수
    > idx_tup_fetch는 인덱스를 이용하여 테이블에서 불러온 로우의 수
    > 만약 위의 세개의 수치가 0이라면 이 인덱스는 사용된 적이 없는 것이다

    SELECT stats_reset FROM pg_stat_bgwriter;
    >통계 정보가 언제부터 축척되었는지 알아보는 쿼리문

    SELECT * FROM pg_stat_reser();
    >기존의 통계정보를 초기화시키는 쿼리문
     (현재 데이터베이스 통계정보가 전부 지워지기 때문에 미리 통계관련 정보들을 저장해놓은 후 실행할 것)