Postgre에서 인덱스를 조회, 생성, 삭제, 확인하는 방법과 인덱스를 어디에 어떻게 생성해야 하는지
인덱스는 조회 속도를 향상시킬 수 있는 중요한 역할을 한다 .하지만 인덱스를 막 사용하게 되면 인덱스를 효울적으로 사용하지 못하기도 하고, 오히려 삽입, 수정, 삭제 등의 연산 작업을 느리게 하여 역효과가 날 수 있다.
인덱스는 어떤 컬럼에 걸어야 할까?
기본키(Primary Key):
기본키는 테이블에서 각 행을 고유하게 식별하는데 사용되기 때문에 자동으로 인덱스가 생성된다.
외래키(Foreign Key):
외래키를 포함하는 컬럼은 다른 테이블과의 관계를 나타내므로 조인 연산 및 외래키 제약조건 검색을 최적화하는데 도움된다.
조회(검색)가 자주 발생하는 컬럼:
데이터 검색 (WHERE, JOIN, ORDER BY, GROUP BY, BETWEEN)이 자주 일어나는 컬럼에 사용하는 것이 효과적이다.
데이터 카디널리티가 높은 컬럼:
중복된 값이 적은 컬럼에 인덱스를 걸으면 그만큼 중복 값을 확인하지 않아도 되기 때문에 더 높은 효과를 기대할 수 있다.
인덱스는 보통 한 테이블에 3~5개 정도를 사용하며, 이 외에도 다양한 상황을 고려하여 적정한 테이블의 컬럼에 적용하면 된다.
인덱스 사용하기
인덱스 조회
PostgreSQL의 시스템 카탈로그 테이블 중 인덱스에 대한 정보를 가지고 있는 pg_indexes에서 인덱스 이름(indexname), 인덱스 정의(indexdef) 조회.
- select indexname, indexdef from pg_catalog.pg_indexes where tablename = '테이블명';
select indexname, indexdef from pg_catalog.pg_indexes where tablename = 'user_role';
인덱스 생성
생성할 인덱스 이름과 적용할 테이블명(컬럼명)으로 작성하며, 여러 컬럼을 지정하여 복합 인덱스(Composite Index)를 생성할 수 있다.
인덱스명 작성 규칙?) 테이블명_인덱스명 or 테이블명_설명
- create index 인덱스명 on 테이블명(컬럼1, 컬럼2 .. );
create index user_role_id_dept on user_role(id, dept);
인덱스 삭제
인덱스를 제거할 때는 어느 스키마에 속한 인덱스인지 명시해 줘야 한다.
- drop index 스키마명.인덱스명;
drop index master.user_role_id_dept;
인덱스 사용 확인 및 분석
Explain은 쿼리의 실행 계획을 보여주고, Analyze는 실제로 쿼리를 실행하면서 통계 정보를 수집하는 명령문이다.
두 명령문을 함께 사용하여 쿼리문의 상단에 붙인 후 실행하면 PostgreSQL 옵티마이저가 어떤 방식으로 쿼리를 해석하고 실행하는지, 각 단계에서 어떤 비용이 소요되는지 등에 대한 통계(분석) 정보를 확인할 수 있다.
- EXPLAIN ANALYZE SELECT * FROM 테이블명 WHERE 인덱스 사용 컬럼명 = '조건값';
EXPLAIN ANALYZE SELECT * FROM user_role WHERE id = 'admin123';
Ex >
Analyze:
키워드가 표시되면, 쿼리를 실행하면서 통계 정보를 수집했다는 것을 의미.
Query Plan:
쿼리의 실행 계획.
Node Types:
각 실행 계획 노드의 유형. (Seq Scan, Index Scan, Nested Loop 등)
Total Cost:
쿼리 실행에 소요된 총 예상 비용. (비용 => 자원 사용량)
Startup Cost:
쿼리가 처음 실행될 때 소요되는 예상 비용으로 인덱스 스캔, 정렬과 같은 초기 작업 비용.
Rows:
각 실행 계획 노드에서 반환되는 행의 예상 수.
Width:
각 행의 평균 폭(너비).
Actual Total Time:
실제로 쿼리가 실행된 총 시간.
Actual Startup Time:
실제로 쿼리가 시작된 시간.
Actual Rows:
각 실행 계획 노드에서 실제로 반환된 행의 수.
Actual Loops:
실제로 반복된 노드의 수. (일부 노드는 반복적 실행될 수 있다)
인덱스 통계 정보
PostgreSQL에서 현재 사용자에 대한 인덱스 통계 정보를 가지고 있는 pg_stat_user_indexes에서 특정 테이블의 인덱스 통계 정보를 간단하게 조회하여 인덱스 성능 모니터링 및 최적화를 수행할 수 있다.
- SELECT * FROM pg_stat_user_indexes WHERE relname = '테이블명';
SELECT * FROM pg_stat_user_indexes WHERE relname = 'user_role';
Ex >
idx_scan:
인덱스가 스캔된 횟수를 나타낸다.
- 이 값이 높을수록 해당 인덱스카 쿼리에서 자주 사용된다는 것을 의미한다.
- 인덱스 스캔은 특정 값이나 범위를 검색하는데 사용된다.
idx_tup_read:
인덱스로부터 읽은 튜플(레코드)의 수를 나타낸다.
- 특정 인덱스의 스캔 작업으로 인해 얼마나 많은 행이 읽혔는지를 보여준다.
- 이 값이 높을 경우 해당 인덱스가 검색에 많이 활용되고 있다고 판단할 수 있다.
idx_tup_fetch:
인덱스로부터 가져온 튜플(레코드)의 수를 나타낸다.
- 인덱스 스캔 후에 가져온 행의 수를 나타낸다.
- 이 값이 높을 경우 해당 인덱스가 효과적으로 데이터를 추출하는데 활용되고 있다고 볼 수 있다.
자세한 내용은 Postgres 공식 사이트의 문서에서 확인 할 수 있다.
https://www.postgresql.org/docs/current/sql-explain.html
- 끝 -
reference.
'💾 데이터베이스(Database) > PostgreSQL' 카테고리의 다른 글
[PostgreSQL] postgres 특징? 차이? (Vacuum) (0) | 2024.03.17 |
---|---|
[PostgreSQL] DB Test 서버 세팅하기 (feat. Data dump) (0) | 2024.03.17 |
[PostgreSQL] generate_series() 숫자/날짜 시퀸스 생성 (1) | 2024.03.10 |
[PostgreSQL] Json 활용 (0) | 2023.08.25 |
[PostgreSQL] PostgreSQL이란? (0) | 2023.08.25 |